Solved Pass an array of Type variable

[Cronk]

Is it possible to pass a type variable to a suboutine in a module?

Option Compare Database
Option Explicit

Type ItemList
   FeeCode As String
   UnitValue As Currency
End Type

Public Sub Test()
   Dim ItemArray(3) As ItemList
   TestPass (ItemArray)
End Sub

Sub TestPass(ItemArray As Variant)
   ItemArray(0).FeeCode = "ABC"
   ItemArray(0).UnitValue = 10.9
End Sub

 

[Josef P.]

As an array this would be possible explicitly declared.

Sub TestPass(ByRef ItemArray() As ItemList)
   ItemArray(0).FeeCode = "ABC"
   ItemArray(0).UnitValue = 10.9
End Sub

If you need an undeclared passing, you could make a class out of the type.

 

[Cronk]

That coding gives me a compile error in the calling routine.

 

[Josef P.]

Forgot to mention the erroneous call.

TestPass (ItemArray)

This is a ByVal transfer because of the brackets.
Note the space after the procedure name.

Correct:

TestPass ItemArray
'or
Call TestPass(ItemArray)

 

[The_Doc_Man]

Is it possible to pass a type variable to a suboutine in a module?

Just declare the type externally in a general module.

Type statement (VBA)

Office VBA reference topic

learn.microsoft.com

If you declare it publicly, then any other module in that project will be able to see it and use it in an argument declaration.

 

[cheekybuddha]

Here are some old threads by ChrisO (RIP) exploring the use of UDT's as parameters:

Passing and returning of multiple variables and data types. (2004)

Functions return only one value. (2006)

Passing and inheritance while using the user defined data type.(2007)

 

[Cronk]

Thanks Josef. #4 fixed it.

 

[Galaxiom]

TestPass (ItemArray)

This is a ByVal transfer because of the brackets.

I used to think that too, and while it is mostly effectively correct, it is not technically correct and will behave unexpectedly in some rare cases if that is the expectation. I am guilty of having misled other developers so I will set the record right here.

The brackets actually mean "evaluate the expression". I have never seen the meaning of the brackets around an argument documented in VBA and only discovered the truth recently in the documentation for another flavour of Basic. Thinking back, I believe ChrisO (RIP) said brackets meant Evaluate in a thread so long ago that I didn't have the skill or understanding at the time to appreciate the subtle difference between what he had said and the conventional wisdom I had learnt from another developer. Unfortunately Chris did not elaborate.

Of course evaluating a scalar variable will return the value and cause it to be effectively passed ByVal even when the parameter is declared ByRef.

Evaluating an expression where the expression is an Access Control will result in the Value property being passed because Value is the default property of a Control. Hence it behaves as a though it has been passed as a ByVal argument even when the parameter is declared ByRef and fails if the Sub is expecting an Object. Little wonder that the conventional wisdom prevails.

The reality of brackets meaning Evaluate rather than ByVal can be demonstrated by passing a Field from an ADODB Recordset. The ADODB Field object does not have a default property so surrounding it with brackets has zero effect and the object is still passed ByRef where that is what is specified in the sub definition.

To further disguise the reality, a Sub parameter declared as anything that can be a value (including a Variant) will cause the Value property to be processed in the Sub, even for an ADODB Field, when the Value property was never even mentioned. Only when the parameter is defined as an Object will the the reality be evident.

 

[The_Doc_Man]

As a "mechanistic" view, G., I would conjecture that this statement has a specific explanation:

Of course evaluating a scalar variable will return the value and cause it to be effectively passed ByVal even when the parameter is declared ByRef.

First, I agree that putting the brackets DOES have the effect of forcing an "Evaluate" - but second, it is possible that the resulting parameter really WAS passed ByRef, not ByVal. It is just that the reference is to a temporary (and unnamed) variable that exists only because there was an (unnamed) expression there, not a simple variable. If so, you might get the same effect by putting a + or - sign in front of the parameter, whether or not it had parentheses.

It might or might not be possible for you to actually modify the value - but in either case it would be the value of an unnamed expression and therefore you could never verify via Debug.Print that you HAD modified it - because the temporary variable vanishes when that line's execution completes. It's in the VBA local expression stack and has no name by which to check its value.

This is somewhat similar to the way that expressions in the query builder become Expr1, Expr2, etc., and the name is only because SQL requires it to have a name.

I'm not sure that it matters a lot, but if this is what actually happens, it would represent VBA actually behaving consistently.

 

[Josef P.]

Thanks @Galaxiom!
"Evaluate" is the correct word.

AProcedure (ObjectReferenceWithoutScalarDefault) will not work.

Examples:
Scalar variabe: Evaluate creates a copy

Private Sub TestWithScalarVariable()

   Dim S As String
   S = "abc"

   Debug.Print "String pointer:"; StrPtr(S)
   Debug.Print String(5, "-")

   CheckScalarByVal S
   Debug.Print "After CheckScalarByVal:"; S, StrPtr(S)
   Debug.Print String(5, "-")

   CheckScalarByRef S
   Debug.Print "After CheckScalarByRef:"; S, StrPtr(S)
   Debug.Print String(5, "-")

   S = "abc"
   Debug.Print "String pointer:"; StrPtr(S)
   CheckScalarByRef (S)
   Debug.Print "After CheckScalarByRef (S):"; S, StrPtr(S)
   Debug.Print String(5, "-")

End Sub

Private Sub CheckScalarByVal(ByVal X As String)

   Debug.Print "CheckScalarByVal:"; X, StrPtr(X)
   X = "Changed!"
   Debug.Print "CheckScalarByVal (after change X):"; X, StrPtr(X)

End Sub

Private Sub CheckScalarByRef(ByRef X As String)

   Debug.Print "CheckScalarByRef:"; X, StrPtr(X)
   X = "Changed!"
   Debug.Print "CheckScalarByRef (after change X):"; X, StrPtr(X)

End Sub

Object without default property: (objRef) => Error

Private Sub TestWithObject()

   Dim ao As AccessObject

   Set ao = CurrentProject.AllForms(0)

   Debug.Print "Object pointer:"; ObjPtr(ao)

   CheckReferenceByVal ao
   Debug.Print "After CheckReferenceByVal:"; ObjPtr(ao)

   CheckReferenceByRef ao
   ' => ao = nothing (CheckReferenceByRef: set X = Nothing)
   Debug.Print "After CheckReferenceByRef:"; ObjPtr(ao)
   'renew ao reference
   Set ao = CurrentProject.AllForms(0)

' Error:
   CheckReferenceByVal (ao)
   CheckReferenceByRef (ao)

End Sub

Private Sub CheckReferenceByRef(ByRef X As AccessObject)

   Dim r As AccessObject
   Set r = X

   Debug.Print r.Name, ObjPtr(r)
   Set X = Nothing

End Sub

Private Sub CheckReferenceByVal(ByVal X As AccessObject)

   Dim r As AccessObject
   Set r = X

   Debug.Print r.Name, ObjPtr(r)
   Set X = Nothing

End Sub