Re: Max/Min Functions

Jonathan,

I really tried, but I admit that I have little experience and lots of
problems with "arrays" in general and passing things between macros and
subroutines in particular. I am stuck. Here is a simple example of what I
am trying to do. While the problem has moved around as I tried to find a
solution, I am stuck now on creating the array on numbers to pass. I think
if I could get passed the line

myArry = ... that I might be home free. Please advise.

Sub CallMacro()
Dim myArray() As Long
Dim oMaxValue As Long
myArray = Split("3 7 13")
oMaxValue = MaxOfArray(myArray)
MsgBox oMaxValue
End Sub

Function MaxOfArray(vArray() As Long) 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

--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.
"Jonathan West" <jwest@xxxxxxxx> wrote in message
news:eaSH%23YA%23FHA.912@xxxxxxxxxxxxxxxxxxxxxxx
> Hi Greg,
>
> Ii go along with Jezebel's comment regarding the efficiency of your
> comparison. Getting stuff onto a single line is not necessarily going to
> make things run faster.
>
> 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
>
> You can adapt the code as needed depending on what kind of items you are
> comparing. If they are all integers for instance, you can speed things up
> a bit by using Long instead of variant everywhere.
>
> You should also have no problem working out the Min equivalent of the
> function
>
> --
> Regards
> Jonathan West - Word MVP
> www.intelligentdocuments.co.uk
> Please reply to the newsgroup
>
>
> "Greg Maxey" <gmaxey@xxxxxxxxxxxxxxxxxxx> wrote in message
> news:%237m8Y579FHA.2320@xxxxxxxxxxxxxxxxxxxxxxx
>>I put together the following little snippet of code to determine Max/Min
>>values with two arguments. I think it was JGM that posted the min
>>function a while back and I just reversed the equality signs to make the
>>max function work:
>>
>> Sub Test()
>> Dim i As Long
>> i = min(1, 1000)
>> MsgBox i
>> i = max(5, 500)
>> MsgBox i
>> End Sub
>> Private Function min(a As Long, b As Long)
>> min = -((a < b) * a + (a >= b) * b)
>> End Function
>> Private Function max(a As Long, b As Long)
>> max = -((a > b) * a + (a <= b) * b)
>> End Function
>>
>> Does anyone know if someone has figure out how to determine max or min
>> values in an array using Word VBA? Thanks.
>>
>> --
>> Greg Maxey/Word MVP
>> See:
>> http://gregmaxey.mvps.org/word_tips.htm
>> For some helpful tips using Word.
>>
>
>


.

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)