Need Help With Arrays and Passing Arguments

[Greg Maxey]

Jonathan West posted the following for my benefit:

"Greg, As for getting a max from an array, something like this should work

Function MaxOfArray(vArray() as Variant) As Variant
Dim iStart as Long
Dim iEnd as Long
Dim vMax as Variant
Dim i As Long

iStart = LBound(vArray)
iEnd = UBound(vArray)
vMax = vArray(iStart)
For i = iStart + 1 to iEnd
If vArray(i) > vMax Then vMax = vArray(i)
Next i
MaxOfArray = vMax
End Function"

I really need help understanding arrays, passing arguements, and in
particular the understanding the empty parens "()" in Jonathans code (e.g.
vArray() As Variant).

Lets say I have the following numbers "1 99.99 -34.3 and 50" and I want to
"pass" them to Jonathans function to get a returned answer of "99.99."

What would the approptiate calling statement be? How is (vArray() As
Variant)
determined?

All help appreciated. Thanks.

P.S - Sorry for the double post in the customization newsgroup.

 

[Greg Maxey]

Ok, If figured out that if I used something like:

Sub CallMacro()
Dim myArray() As Variant
myArray = Array(1, 99.99, -34.3, 50)
MsgBox MaxOfArray(myArray)
End Sub

It would work.

Again, I was stumped by the "()." At first I was trying:

Dim myArray As Variant
and I was getting and error "Type mismatch: array or user-type expected.
on this line:
MsgBox MaxOfArray(myArray)

If I cleared the "()" and used:
Function MaxOfArray(vArray as Variant) As Variant
it would work

Through trial and error I added the "()" to the the dim statement. What
does it mean.
Why does the code appear to work equally well without the "()" in the Dim
statement and the Function statement?

Please help me lift the fog.

 

[Jezebel]

Posted a reply to the other thread...

Ok, If figured out that if I used something like:

Sub CallMacro()
Dim myArray() As Variant
myArray = Array(1, 99.99, -34.3, 50)
MsgBox MaxOfArray(myArray)
End Sub

It would work.

Again, I was stumped by the "()." At first I was trying:

Dim myArray As Variant
and I was getting and error "Type mismatch: array or user-type expected.
on this line:
MsgBox MaxOfArray(myArray)

If I cleared the "()" and used:
Function MaxOfArray(vArray as Variant) As Variant
it would work

Through trial and error I added the "()" to the the dim statement. What
does it mean.
Why does the code appear to work equally well without the "()" in the Dim
statement and the Function statement?

Please help me lift the fog.

--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

 

[Tony Jollans]

Hi Greg,

I'm going to try and write this up properly later --- but here's the short
version.

There are two ways to declare an argument to a Sub or Function - ByRef and
ByVal. There are slightly different rules depending on which you use. ByRef
is the default so let's stick with that.

ByRef means By Reference - in other words you pass a reference to the
variable, not a copy of it. The called routine actually uses the same
variable in memory as the caller. In order to use the same memory the two
definitions must be compatible - not necessarily *exactly* the same because
VBA can be a little bit clever with both Variants and Arrays (or is a little
bit restrictive, depending on your point of view, perhaps). Yes, I know
Variants and Arrays are what you're using but I wanted to lay a little
groundwork.

Variants are pretty straightforward. No matter what the type of variable
being passed, it can be received as a Variant. If a Variant is passed,
however, it must be received as a Variant.

Arrays are also pretty straightforward. If you want to pass an array you
must declare an array in both the calling and called routines (just like a
typed variable). You can not, however, declare the size of the array in the
called routine - VBA will work it out for itself at call time - you do
something like this ...

Dim myArray(2,3,4) as Long
myVar = myFunction(myArray)

Function myFunction(myArrayRef() as long)
myFunction = "Retuned Value"
End Function

Now, just as for non-arrays, the definition (of the type of data in the
array) in the called function may be Variant, so this is good ...

Dim myArray(2,3,4) as Long
myVar = myFunction(myArray)

Function myFunction(myArrayRef() as Variant)
myFunction = "Retuned Value"
End Function

But also, just as for non-array variables, a Variant variable can map to
*any* type of data, including an array, so this is also good ...

Dim myArray(2,3,4) as Long
myVar = myFunction(myArray)

Function myFunction(myArrayRef as variant)
myFunction = "Retuned Value"
End Function

Now to your code ...

Sub CallMacro()
Dim myArray() As Variant
myArray = Array(1, 99.99, -34.3, 50)
MsgBox MaxOfArray(myArray)
End Sub

Here you are defining myArray as an array of variants so it can be received
as either an array of variants (what it is) or as a variant (which can map
to anything). In other words, either of these should be good ...

Function MaxOfArray(myArray())
Function MaxOfArray(myArray)

Alternatively, ....

Sub CallMacro()
Dim myArray As Variant ' <== Difference here
myArray = Array(1, 99.99, -34.3, 50)
MsgBox MaxOfArray(myArray)
End Sub

Here you are defining myArray as a variant. It happens to contain an array
of variants when the call is executed but is, itself, just a variant. A
Variant can only be received as a variant so you must use ...

Function MaxOfArray(myArray)

User types are an extra complication for another time, but when you tried to
receive a variant as an array, VBA was having none of it.

Having just written that, I think what you're probably not grasping is the
difference between a variant and an array of variants. More generally a
Variant can contain any sort of data (array or non-array) but remains a
variant which happens to contain a particular type of data at a particular
time.

Does that help?

 

[Greg Maxey]

Tony,

Thanks for your time. Every little bit helps. This helps a lot. Now if I
can only keep it all straigt.

 

[Greg Maxey]

Tony,

While the text was helpful, I dindn't get anything form the examples. I
copied them into the editor to try to see how they worked. I cant get any
of them to do anything meaningful:

Sub CallTest1()
Dim myArray(2, 3, 4) As Long
Dim myVar
myVar = myFunction1(myArray)
MsgBox myVar
End Sub

Function myFunction1(myArrayRef() As Long)
myFunction1 = myArrayRef(0)
End Function

Sub CallTest2()
Dim myArray(2, 3, 4) As Long
Dim myVar
myVar = myFunction2(myArray)
End Sub

Function myFunction2(myArrayRef() As Variant)
myFunction2 = myArrayRef(1)
End Function

Sub CallTest3()
Dim myArray(2, 3, 4) As Long
Dim myVar
myVar = myFunction3(myArray)
End Sub

Function myFunction3(myArrayRef As Variant)
myFunction3 = myArrayRef(2)
End Function

I would think that these would return 2 then 3 then 4 respectively. All I
get is subscript out of range in all three.

Sorry if I appear thick.

 

[Tony Jollans]

Sorry, Greg, perhaps a poor choice of example. I just randomly typed in an
array definition to illustrate without really thinking about it.

Dim myArray(2, 3, 4) As Long

... declares (assuming you have option base 0 set) a 3*4*5
three-dimensional array. It does not put any actual values into the
individual array elements. To reference an individual element in such an
array requires three indexes. It is totally different from ...

Dim myArray() As Variant
myArray = Array(2, 3, 4)

... which declares an array of variants of indeterminate dimensions and
then, by loading values into it, gives it both defined size and content (a
one-dimensional array with 3 elements, values 2, 3 and 4 respectively). If
you use this in your examples (and change the function definition from long
to variant) instead of my original declaration you should get the results
you were expecting.