Re: Max/Min Functions

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


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

Jonathan West wrote:
> 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
>
>
> "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
    ... 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)
  • Too Many Line continuations?? VB Macro
    ... I have a 10,000 line worksheet that I have constructed a macro for and ... Dim iStart As Long ... Dim iEnd As Long ...
    (microsoft.public.excel.programming)
  • Re: Max/Min Functions
    ... Function MaxOfArrayas Variant) As Variant ... Dim iStart as Long ... Dim vMax as Variant ... > Private Function min ...
    (microsoft.public.word.vba.general)
  • Re: Max/Min Functions
    ... So if you have a variable declared as a variant, ... have put an array of longs into that variant. ... Dim pDataas string ... > Dim vMax As Long ...
    (microsoft.public.word.vba.general)
  • Re: How do I select a range using a macro?
    ... Dim iLastRow As Long ... Dim iStart As Long ... Dim iEnd As Long ...
    (microsoft.public.excel.misc)
Loading