find maximum of 4 variables in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Within VBA code I have Dim i,j,k,l as integer. Each will have a value eg
2,0,4,1 and I want to get to 4 the maximum value. Can I sort an array? BTW
i,j,k,l may go to m,n,,etc. say to 10 elements. If there's a tie then I
don't care just give me the max!
Thanks.
 
Within VBA code I have
Dim i,j,k,l as integer
Click to expand...

Okay, that's three Variants and one Integer variable.
Each will have a value
eg 2,0,4,1 and I want to get to 4 the maximum value.
Click to expand...

This seems to be the sort of thing you might want:

Public Function MaxOf(Values As Variant) As Integer

Dim i As Integer
Dim maxSoFar As Integer
Const vbErrInvalidProcedure As Long = 5

' don't like using variants for arrays, but it does make
' life easier for the client
'
If VarType(Values) And vbArray = 0 Then
Err.Raise vbErrInvalidProcedure, , "Need an array"
End If

' start with the first member
maxSoFar = Values(LBound(Values))

' now go through the rest of them
For i = LBound(Values) + 1 To UBound(Values)
' keep track of the highest one
If Values(i) > maxSoFar Then maxSoFar = Values(i)
Next i

MaxOf = maxSoFar

End Function


This works with calls like

? MaxOf(Array(2,1,0,4,2))

and even

? MaxOf(Array(4))

but not

? MaxOf(Array())

If it's important you could probably fix that. It would probably be a
good idea to convert the whole thing to a ParamArray -- still, that is
left as an exercise for the reader...
Can I sort an
array? BTW i,j,k,l may go to m,n,,etc. say to 10 elements. If
there's a tie then I don't care just give me the max!
Click to expand...

Sorting arays is covered usually in chapter 2 of any decent programming
structures/ algorithms book. Sorting is easy; knowing which sort to use
is hard.

Hope that helps

Tim F
 
Whew, thank you.
--
Doug F.


Tim Ferguson said:
Okay, that's three Variants and one Integer variable.


This seems to be the sort of thing you might want:

Public Function MaxOf(Values As Variant) As Integer

Dim i As Integer
Dim maxSoFar As Integer
Const vbErrInvalidProcedure As Long = 5

' don't like using variants for arrays, but it does make
' life easier for the client
'
If VarType(Values) And vbArray = 0 Then
Err.Raise vbErrInvalidProcedure, , "Need an array"
End If

' start with the first member
maxSoFar = Values(LBound(Values))

' now go through the rest of them
For i = LBound(Values) + 1 To UBound(Values)
' keep track of the highest one
If Values(i) > maxSoFar Then maxSoFar = Values(i)
Next i

MaxOf = maxSoFar

End Function


This works with calls like

? MaxOf(Array(2,1,0,4,2))

and even

? MaxOf(Array(4))

but not

? MaxOf(Array())

If it's important you could probably fix that. It would probably be a
good idea to convert the whole thing to a ParamArray -- still, that is
left as an exercise for the reader...


Sorting arays is covered usually in chapter 2 of any decent programming
structures/ algorithms book. Sorting is easy; knowing which sort to use
is hard.

Hope that helps

Tim F
Click to expand...
 

Sorry to follow up on myself, but did you get the point about how Dim
works in VBA (which is different from C, C#, VB.Net etc)?
Whew, thank you.
Click to expand...

Don't know about the "whew" but you're welcome. FWIW, here is the fully
generalised version:

myMaximumNumber = MaxOf(12, 4, 5, 25, 1)

'...

Public Function MaxOf(ParamArray Values() As Variant) As Integer

Dim i As Integer, l As Integer, u As Integer
Dim maxSoFar As Integer
Const vbErrInvalidProcedure As Long = 5

' remember these for later
l = LBound(Values)
u = UBound(Values)

' if there are no parameters, we don't want an out of bound errors:
' we'll raise an invalid procedure instead, much more meaningful
If l > u Then
' empty array
Err.Raise vbErrInvalidProcedure

Else
' VB does not provide anyway of type checking with a ParamArray
' so we have to do it on each parameter at a time
If VarType(Values(l)) <> vbInteger Then
Err.Raise vbErrInvalidProcedure
End If

' remember the first one
maxSoFar = Values(l)

' now go through the rest of them
For i = l + 1 To u
' ditto for type checking
If VarType(Values(i)) <> vbInteger Then
Err.Raise vbErrInvalidProcedure
End If

' then just remember the biggest one
If Values(i) > maxSoFar Then maxSoFar = Values(i)

Next i

' and return the value
MaxOf = maxSoFar

End If

End Function



Something to do with the evening, I guess... <g>


HTH


Tim F
 
Back
Top