Declaring a User Data Type

[SteveMcL]

:banghead:

This is one area I've never been able to succeed in. I would like to declare a User Type in order to return to values from a function.

The declaration is made in a module as follows

Public Type Document
DocumentID As Long
IncidentID As Long
End Type


The variable Document itself will accept a value

Document=1

But when I try and read the value of an element in the Immediate Window,

? Document().DocumentID

I get Subscript Out of Range

 

[vbaInet]

It's not a function (so you don't need parens and to use it you need to declare a variable of that type:

Module

Public Type Document
    DocumentID As Long
    IncidentID As Long
End Type

Form class (in any event, sub or function):

Dim SomeVar As Document

With SomeVar
    .DocumentID = 1000
    .IncidentID = 2000
End With

Debug.Print SomeVar.DocumentID

 

[MarkK]

Beware though, there are many object models that expose a Document object.
If you do . . .

Public Type Document
    DocumentID As Long
    IncidentID As Long
End Type

. . . then you might need to explicitly qualify other libraries, like . . .

Dim mDoc As Document
Dim wDoc As [B]Word.[/B]Document

 

[SteveMcL]

Thank You. I had declared the User Type, but had not yet declared a variable as that User Type.

And yes, there was a conflict with User Security's Documents & Containers objects.

The end purpose was to return two values from a function. However I found
that I couldn't pass that function an argument to process, and so far I can't
retrieve the values of the two variables.

Should I assume that I have to assign the extention varibles (.DocumentID) to variables outside the function before the function ends?


Module - User Type Declaration:

Public Type RegistrationRecord
DocumentID As Long
IncidentID As Long
End Type


Module - Function Declaration:

Public Function RegisterCaseFile
Dim Registration As RegistrationRecord

With Registration
.DocumentID = 1000
.IncidentID = 2000
End With

End Function

Immediate Window:
? Registration.DocumentID
1000

 

[vbaInet]

The end purpose was to return two values from a function. However I found
that I couldn't pass that function an argument to process, and so far I can't
retrieve the values of the two variables.

Public Function [COLOR="blue"]RegisterCaseFile[/COLOR](MyParameter1 As Integer, MyParameter2 As Integer) As [COLOR="Red"]RegistrationRecord[/COLOR]

    With [COLOR="Blue"]RegisterCaseFile[/COLOR]
         .DocumentID = MyParameter1
         .IncidentID = MyParameter2
    End With
 
End Function

 

[Galaxiom]

BTW One alternative for returning multiple values is to modify an input parameter.

A ByRef argument (the VBA defualt) will reflect changes to the value that was passed to it when it is modified inside the function.

 

[vbaInet]

Since we're talking alternatives:

Function Get2Vals() As Variant()
    ReDim Get2Vals(1) As Variant
End Function

... returns a one-dimensional array of type Variant with 2 elements.

It's neater to return the value from the function itself and my preference would be a UDT or a class containing two properties.

 

[MarkK]

But vbaInet, wouldn't that ReDim the variable on each reference, effectively killing any assignments you made to array locations?

get2vals(0) = "Testing 123"
[COLOR="Green"]'this reference will Re-ReDim the array[/COLOR]
get2vals(1) = "oh no, erased my previous value"

You'd need to do . . .

private m_var

public property Get TwoVals As Variant
[COLOR="Green"]   'only ReDims on first reference, when that global variant m_var IsEmpty()[/COLOR]
   if IsEmpty(m_var) then ReDim m_var(1) As Variant
   TwoVals = m_var
end property

. . . and then your future references to TwoVals() will be fine . . .

TwoVals(0) = "Testing 123"
[COLOR="Green"]'and now this next reference doesn't re-ReDim the variable.[/COLOR]
TwoVals(1) = "Still Testing"

. . . or am I missing something?

 

[SteveMcL]

Thanks for the ByRef suggestion, but this is more of an in-out situation.

So staying with the model

Public Function [COLOR=blue]RegisterCaseFile[/COLOR](FieldToReturn As String) As [COLOR=red]RegistrationRecord[/COLOR]
[COLOR=green]'FieldToReturn Arguements:[/COLOR]
[COLOR=green]' "DocumentID" return   .DocumentID[/COLOR]
[COLOR=green]' "IncidentID" return   .IncidentID[/COLOR]
[COLOR=green]' "All"        return   .DocumentID,.IncidentID[/COLOR]
 
With [COLOR=blue]RegisterCaseFile[/COLOR]
   .DocumentID = Forms!frmPopup.txtDocumentID
   .IncidentID = Forms!frmPopup.txtIncidentID
End With
 
End Function

'--------------------------------------------------------------

On calling

? RegisterCaseFile("All")

in the Immediate Window, I get Error 13 Type mismatch, which is what
I meant when I said I couldn't pass an argument to the function.
(Same happens trying to pass an Integer to an Integer arguement.)

 

[Galaxiom]

Could either of the controls on the form be Null? That will cause a Type Mismatch error.

If that isn't the issue, try a full reference to the Value Property of the controls. Maybe it makes a difference if the result is going to a user defined type.

 

[vbaInet]

@Mark, here's what I meant:

Function Get2Vals(param1 as string, param2 as long) As Variant()
    ReDim myArray(1) As Variant

    myArray(0) = param1
    myArray(1) = param2

    Get2Vals = myArray
End Function

I just remembered that you can't redim an array function like how I did in my last post, you have to redim another array variable and assign it to the array function.
I was only throwing out another alternative, not necessarily advocating its use.

@SteveMcL, the function is a RegistrationRecord type so you must reference one of its elements, plus I noticed you're not making use of the function's parameter.

Public Function RegisterCaseFile(lngDoc As Long, lngInc As Long) As RegistrationRecord

    With RegisterCaseFile
        .DocumentID = lngDoc
        .IncidentID = lngInc
    End With
 
End Function

?RegisterCaseFile(Forms!frmPopup.txtDocumentID, Forms!frmPopup.txtIncidentID).DocumentID

You decide whether you want to ByRef or ByVal it and if you want it to accept Nulls (like Galaxiom highlighted) change both parameter types to Variant and test for Null inside the function. And if you do make them Variants, then I would advise that instead testing for Null just check that it's numeric using IsNumeric(). That'll cover both basis.