Excel VBA - Why does this arithmetic comparison of a split line containing numbers work?

Question

I am wondering why the code below works the way I hoped, given that I split the string into an array (which is also defined as a string) and then compares it using an arithmetic (numeric) way.

Option Explicit Sub test() Dim str As String, arr() As String Dim num As Integer, i As Integer str = "12 9 30" num = 20 arr() = Split(str, " ") For i = LBound(arr) To UBound(arr) If arr(i) > num Then MsgBox (arr(i) & " is larger than " & num) End If Next i End Sub 

As expected, msgBox within the if statement starts, indicating that:

• 12 no more than 20
• 9 no more than 20
• thirty more than 20

I did not know / did not think that such a comparison could work as I hope, since I basically compare a string with an integer. I suppose I don’t know something, but in this case, what is it?

PS. I had a little doubt about which forum to publish, but of my choice for this meta-question

Answer 1

For an answer, refer to the following article: https://msdn.microsoft.com/en-us/library/aa263418(v=vs.60).aspx

In short, if you are comparing a string variable with a numeric type, the string variable is converted to double * type.

* double based on information from the link of comparison operators VB.net ( https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/operators/comparison-operators ), VB 6.0, VBA and VBA .net is not the same thing, however the comparison logic should be the same.

Answer 2

VBA seems to implicitly convert the data type at runtime.

Consider the following code, which also works.

 Sub test2() Dim str As String, arr() As String, num As String Dim i As Integer str = "12 9 30" num = 12 '\\ Note the way number is being passed. arr() = Split(str, " ") For i = LBound(arr) To UBound(arr) If arr(i) = num Then MsgBox (arr(i) & " is equal to " & num) End If Next i End Sub 

And then below where the arithmetic operation forces it to be numeric at runtime.

 Sub test3() Dim str As String, arr() As String, num As String Dim i As Integer str = "12 9 30" num = 12 arr() = Split(str, " ") For i = LBound(arr) To UBound(arr) If (arr(i) - num) > 0 Then MsgBox (arr(i) & " is greater than " & num) End If Next i End Sub 

I know that he will not fully answer your question, but can explain why he gives the correct result. It is advisable to convert to the correct data type, rather than relying on the default values, i.e.

 If CInt(arr(i)) > num Then