Re: Max/Min Functions

Hi Greg,

I know how you feel about this stuff. It's almost deliberately
confusing. :-b

The root of the problem is the Variant data type, which is a
chameleon. You can stuff a value of any other data type into a Variant
variable, including String, Long, Double, etc. You can also declare an
array of Variants with the syntax

Dim myArray(3) As Variant

This creates four separate memory locations numbered 0 through 3, each
of which holds a Variant value.

The confusing part is that, unlike any other data type, you can stuff
a whole array into a single Variant variable, which is what the
Split() and Array() functions do:

Dim myVar As Variant
myVar = Split("3 7 13")
or
myVar = Array(3, 1, 13)

In this case, the single variable named myVar contains a whole array
of three integers or strings, respectively.

This paragraph is buried in the help topic about the Array function,
although the topic on the Variant data type doesn't mention it:

"Note - A Variant that is not declared as an array can still contain
an array. A Variant variable can contain an array of any type, except
fixed-length strings and user-defined types. Although a Variant
containing an array is conceptually different from an array whose
elements are of type Variant, the array elements are accessed in the
same way."

Not nice, not nice at all.

Now, on to the idea of passing an array as an argument...

If the function is declared as Jonathan's was:

Function MaxOfArray(vArray() as Variant) As Variant

it says that the argument coming from the main routine will be an
array of Variant variables, each containing one value. (Well,
technically each could itself contain an array, but we won't go
there.) The empty parentheses after vArray in this syntax mean "the
argument will be an array of Variant elements, but I don't know how
many elements it will have; that information will come from the call
in the main routine at run time".

When the function starts executing, the interpreter knows how many
elements were passed in the argument; that's why you can use the
LBound() and UBound() functions on it.

Your function below, in contrast, defines the argument as a single
Variant variable that (presumably) contains an array:

Function MaxOfArray(vArray As Variant) As Long

Inside the function, the LBound() and UBound() functions give you the
bounds of the array that's in that single variable.

****

Here's how you would have to call Jonathan's function. Notice the
different manner of assigning values to the array in the main routine;
the Array() function won't work here.

Sub CallMacro()
Dim myArray(2) As Variant
Dim oMaxValue As Variant

myArray(0) = 3
myArray(1) = 7
myArray(2) = 13
oMaxValue = MaxOfArray(myArray)
MsgBox oMaxValue
End Sub

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

****

As a separate issue, you should change your functions so that the
return value (the last As clause in the Function line) and the vMax
variable are Variants, as in Jonathan's code. That way, the functions
can be called from other main routines that assign data types other
than Long to the argument array. For example, Jonathan's function will
return the correct maximum value when called by this routine:

Sub CallMacro2()
Dim myArray(2) As Variant
Dim oMaxValue As Variant

myArray(0) = 3.14159
myArray(1) = 0.75
myArray(2) = 1.3333
oMaxValue = MaxOfArray(myArray)
MsgBox oMaxValue
End Sub

Your function, though, assigns a value such as 3.14159 to vMax as
Long, which truncates it to just 3 and returns that. Your function
operates correctly only if the main routine passes it an array of Long
values to begin with.

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

On Sat, 3 Dec 2005 10:57:19 -0500, "Greg Maxey"
<gmaxey@xxxxxxxxxxxxxxxxxxx> wrote:

>Jonathan,
>
>I solved my immediate problem with the below code. I was searching Google
>and found the following tip that Jezebel had posted in response to a
>similiar question:
>
>"Put your arguments into an array, and pass that as a single argument. "
>
>I knew I had the array and so I removed the "()" following vArray in the
>Function you provided
>
>
>This only confirms my near complete lack of understanding of 1) Arrays, 2)
>Passing arguments. I really don't understand the basic concept and usually
>simply stumble on a workable solution. If you (or any other benevolent
>soul) have time, I would certainly appreciate it if you could provide a
>sample of how you intended your code to be employed with an explanation of
>how it works. Of all the thing is VBA, it is those empty parens "()" that
>confound me the most. I feel that I am close to an epiphany but I could use
>divine intervention. Thanks for everything
>
>Sub CallMacro()
>Dim myArray
>Dim oMaxValue As Long
>Dim oMinValue As Long
>Dim i As Long
>myArray = Array(3, 1, 13)
>oMaxValue = MaxOfArray(myArray)
>oMinValue = MinOfArray(myArray)
>MsgBox oMaxValue
>MsgBox oMinValue
>End Sub
>
>Function MaxOfArray(vArray As Variant) As Long
>Dim iStart As Long
>Dim iEnd As Long
>Dim vMax As Long
>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
>
>Function MinOfArray(vArray As Variant) As Long
>Dim iStart As Long
>Dim iEnd As Long
>Dim vMin As Long
>Dim i As Long
>iStart = LBound(vArray)
>iEnd = UBound(vArray)
>vMin = vArray(iStart)
>For i = iStart + 1 To iEnd
> If vArray(i) < vMin Then vMin = vArray(i)
>Next i
>MinOfArray = vMin
>End Function
.

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: Same Contents, Regardless of Order
    ... Dim Arr1As Variant ... ' Second Array ... Dim blnFound As Boolean ...
    (microsoft.public.excel.programming)
  • Re: transferring array to range
    ... Dim arrValues(NumRows, NumColumns) As Variant ... declared an array of variants instead of a variant that contains an array. ...
    (microsoft.public.excel.programming)
  • Re: Re:workdays
    ... Optional Holidays As Variant = Nothing, ... Dim arrayH As Variant ... ' or not an array or cell range with at least one numeric value between ... ReDim arrayH) As Variant ...
    (microsoft.public.excel)