Re: Max/Min Functions

Jay,

You said the Array function won't work here. I agree that with the a value
in the () in the Dim statement it won't. However, if leave that blank I can
use the Array function like:

Sub CallMacro()
Dim myArray() As Variant
myArray = Array(3, 1.5, -13.32, 5000.34)
MsgBox MaxOfArray(myArray)
MsgBox MinOfArray(myArray)
End Sub

Is there hidden dangers here?

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

Jay Freedman wrote:
> 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.
>
>
>> 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 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)
  • Re: Max/Min Functions
    ... I am stuck now on creating the array on numbers to pass. ... Dim myArray() As Long ... Dim vMax As Long ... > Function MaxOfArrayas Variant) As Variant ...
    (microsoft.public.word.vba.general)