Clever Geek Handbook

Finding Max of 3 VBA inputs

I am trying to find a maximum of 3 inputs. The problem is not with the algorithm, as when I did the same script in python it worked fine. The problem is, it doesn't work as expected. I'll write some scripts and what was the result:

8 5 12 - Max: 12
5 8 12 - Max: 12
12 5 8 - Max: 8
12 8 5 - Max: 8
5 12 8 - Max: 8
8 12 5 - Max: 8
100 22 33 - Max: 33
22 3 100 - Max: 100
100 22 3 - Max: 22

This seems to work for some combination, but not every one. I haven't been able to find a pattern yet, and I can't figure out what's going wrong.

I am attaching the code:

Sub Maxthree()
'Calculates the maximum of three numbers'
Dim x, y, z As Single
x = InputBox("Enter the first number!")
y = InputBox("Enter the second number!")
z = InputBox("Enter the third number!")
MsgBox ("X: " & x & " Y: " & y & " Z: " & z)
If x > y Then
    If x > z Then
        MsgBox ("the maximum is : " & x)
    Else
        MsgBox ("the maximum is : " & z)
    End If
Else
    If y > z Then
        MsgBox ("the maximum is : " & y)
    Else
        MsgBox ("the maximum is : " & z)
    End If
End If
End Sub
+3


source to share


3 answers


Since they are entered using InputBox, it compares the text values. So, for example, "8" is greater than "12". Instead, try converting to Longs

, for example:

x = CLng(InputBox("Enter the first number!"))


You can also simplify your code:

MsgBox WorksheetFunction.Max(x, y, z)
+5


source


Here is the template you were looking for.

Since X and Y are Variant and Z is Single, this is how VBA will perform comparisons:

X vs Y: string vs string (this is what causes all problems)

X vs Z: numeric (X will be automatically converted)



Y vs Z: numeric (Y will be automatically converted)

Reevaluate all 9 of your scenarios where X and Y are compared as strings and (X or Y) are compared to Z as numbers. The results you observed, while unexpected, are correct.

It's just lucky that you don't program in PHP, which is much worse!

Microsoft is to blame for allowing Variant to be the default datatype if no other type is specified. They support "Option Explicit" to enforce variable declarations. They should go further and be able to require datatypes in all declarations.

+1


source


Here is a function that returns the largest element of any number of them:

Function Largest(ParamArray a() As Variant) As Variant
'returns the largest element of list
'List is supposed to be consistent: all nummbers or all strings
'e.g:   largest(2,6,-9,7,3)         -> 7
'       largest("d", "z", "c", "x") -> "z"
'by Patrick Honorez --- www.idevlop.com

    Dim result As Variant
    Dim i As Integer

    result = Null

    For i = LBound(a) To UBound(a)
        If result > a(i) Then
            'nothing to do. This construct will properly handle null values
        Else
            result = a(i)
        End If
    Next i
    Largest = result
End Function
0


source






More articles:


All Articles