Re: Need Help With Arrays and Passing Arguments

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.

--
Enjoy,
Tony


"Greg Maxey" <gmaxey@xxxxxxxxxxxxxxxxxxx> wrote in message
news:#0fmVcH#FHA.1444@xxxxxxxxxxxxxxxxxxxxxxx
> 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.
> --
> Greg Maxey/Word MVP
> See:
> http://gregmaxey.mvps.org/word_tips.htm
> For some helpful tips using Word.
>
> Tony Jollans wrote:
> > 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" <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)
Loading