Re: Need Help With Arrays and Passing Arguments

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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?

--
Enjoy,
Tony


"Greg Maxey" <gmaxey@xxxxxxxxxxxxxxxxxxx> wrote in message
news:uuQudKE#FHA.2640@xxxxxxxxxxxxxxxxxxxxxxx
> 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.
>
> Greg Maxey wrote:
> > 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.
>
>


.

Relevant Pages

  • Re: Max/Min Functions
    ... You said the Array function won't work here. ... Dim myArray() As Variant ... > Dim vMax As Variant ...
    (microsoft.public.word.vba.general)
  • Re: Max/Min Functions
    ... You can stuff a value of any other data type into a Variant ... > Dim myArrayAs Variant ... > a whole array into a single Variant variable, ... > Dim vMax As Variant ...
    (microsoft.public.word.vba.general)
  • Re: For Each...Next not working
    ... Dim TestArrayAs Variant, e As Variant, I As Integer ... I do not want to fill the array with the indices (I can't imagine why ... End Sub ...
    (microsoft.public.excel.programming)
  • Re: Visual Basic Forgets What Variables are
    ... I put all of the dim statements in front of the sub (as you ... >> HoldName will go back to the default in your code. ... >> dim AddNameLine as variant ...
    (microsoft.public.excel)
  • Re: Tom Ogilvy - Need a little change
    ... I need little bit of calculation/validation within the array. ... Sub Combinations ... Dim n As Integer, m As Integer ... Dim rng1 As Range, ans As Variant ...
    (microsoft.public.excel.programming)