[SOLVED] Finding Max of 3 Inputs VBA

Finding Max of 3 Inputs VBA

I am trying to find the maximum of 3 inputs. The problem is not in the algorithm, as when I made the same script in python it worked just fine. The problem is that it does not work as expected. I will write some scenarios and what the outcome was:

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

It seems that it works for quite some combination, but not for each and every one. I haven't managed to find a pattern yet, and I can't figure out what is 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

Solution

Because they are input using an InputBox, it's comparing text values. So, for example "8" is greater than "12". Instead try converting to Longs like:

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

You can also simplify your code to:

MsgBox WorksheetFunction.Max(x, y, z)