Max function in VBA?

[TyroneB]

I guess there's no Max (or Min) function in VBA. Is there a simpler way to do the following, which would greatly benefit from a max function?

Thanks.

Option Compare Database
Option Explicit

Public Function PF(Distance_Feet As Variant, HF1Adj As Variant, HF2Adj As Variant, HF3Adj As Variant, HF4Adj As Variant, HF5Adj As Variant, HFFinalAdj As Variant) As Variant

Dim A As Variant
Dim B As Variant
Dim C As Variant
Dim D As Variant
Dim E As Variant
Dim F As Variant
Dim AF As Variant
Dim FA As Variant
Dim FB As Variant
Dim BF As Variant
Dim AB As Variant
Dim BA As Variant

A = HF1Adj
B = HF2Adj
C = HF3Adj
D = HF4Adj
E = HF5Adj
F = HFFinalAdj
AF = (A - F) + 95
FA = (F - A) + 95
BF = (B - F) + 95
FB = (F - B) + 95
AB = (A - B) + 95
BA = (B - A) + 95


If Distance_Feet < 2310 Then
        If A > AF And A > FA Then
            PF = A
        ElseIf AF > A And AF > FA Then
            PF = AF
        Else: PF = FA
        End If
        
ElseIf Distance_Feet >= 2310 And Distance_Feet <= 3465 Then
        If A > B And A > AF And A > FA And A > BF And A > FB And A > BA And A > AB Then
            PF = A
        ElseIf B > A And B > AF And B > FA And B > BF And B > FB And B > BA And B > AB Then
            PF = B
        ElseIf AB > A And AB > AF And AB > FA And AB > BF And AB > FB And AB > BA And AB > B Then
            PF = AB
        ElseIf BA > A And BA > AF And BA > FA And BA > BF And BA > FB And BA > AB And BA > B Then
            PF = BA
        ElseIf FB > A And FB > AF And FB > FA And FB > BF And FB > BA And FB > AB And FB > B Then
            PF = FB
        ElseIf BF > A And BF > AF And BF > FA And BF > FB And BF > BA And BF > AB And BF > B Then
            PF = BF
        ElseIf AF > A And AF > BF And AF > FA And AF > FB And AF > BA And AF > AB And AF > B Then
            PF = AF
        ElseIf FA > A And FA > BF And FA > FA And AF > FB And FA > BA And FA > AB And FA > B Then
            PF = FA
        
End If
End If

End Function

 

[llkhoutx]

It's there, search the Access Help for "functions."

 

[TyroneB]

Thanks.

I'm looking for something that does this (in vba):

max(number1, number2, number3, etc)

But it appears that the Access max does this:

max(field1)

It doesn't even do this: max(field1, field2, field3)

Is this correct?

 

[llkhoutx]

Literally, yes, however the DMAX function's artgument can be a complex expression.

 

[TyroneB]

If found this function, which seems to do the trick, here:

http://groups.google.com/group/comp...nk=gst&q=max+function&rnum=1#187ae6b7650837a7

Public Function ListMax(ParamArray ListItems() As Variant)

        Dim I As Integer

        For I = 0 To UBound(ListItems())
            If ListItems(I) > ListMax Then ListMax = ListItems(I)
        Next I

End Function