VBA If Test Gives Wrong Result

[ghborrmann]

I have just finished a considerable effort in tracking down a bug in one of my Excel macros. While I know how to fix this bit of code, I am unsure of how to deal with the generic problem this raises. The bug occurred in the following VBA statement:

If qty < oldqty Then
    Some code
End If

The above test failed, with qty and oldqty equal to 167 and 280, respectively. After much head-scratching, I finally figured out that, although both variables are Variants, they have different vartypes: qty is a string, and oldqty is an integer. If I convert qty to an integer, the code works as expected.

I have never worried much about variable types in VBA. Unlike strongly typed languages, I always assumed that VBA would convert types as needed. In fact, this is almost always what happens. Now, I am wondering what other bugs may exist in my other macros, and what I should do about this. Should I avoid using Variants altogether? I would appreciate any comments or advice.

 

[shg]

Absent a specific need for a Variant, I would always use strongly-typed variables. Faster, smaller memory footprint, and not subject to errors like that.