PDA

View Full Version : Excel VBA Variant Variable Assigned a Number Tests as Number when assigned to ??



nb-
05-12-2008, 09:57 AM
This problem originally arose when I was dealing with an array, but the same happens when with plane variable, and its easier to see the problem like this;

I have a variant variable that I assign to be a number, say 5, then when I test to see if it is greater than equal to 1, it obviously tests positive. The problem is I later set the variable = "" and it also tests positive as greater than equal to 1.

See code below;



Option Explicit

Sub var_test()

Dim test_var As Variant
test_var = 1

If test_var >= 1 Then
Debug.Print "Test_var_" & test_var
End If

test_var = ""

If test_var >= 1 Then
Debug.Print "Test_var_" & test_var
End If

End Sub


As you will see it prints two lines to the immediate window, when it should only print one in my opinion. There is obviously something that I don?t understand about the way vba works, can someone please explain whats going on?

Thanks.

Bob Phillips
05-12-2008, 10:06 AM
Sub var_test()

Dim test_var As Variant
test_var = 1

If test_var >= 1 Then
Debug.Print "Test_var_" & test_var
End If

test_var = Empty

If test_var >= 1 Then
Debug.Print "Test_var_" & test_var
End If

End Sub

nb-
05-12-2008, 10:10 AM
Thank you xld, but can anyone explain why it tests positive for being greater than equal to a number?

MattKlein
05-12-2008, 10:38 AM
You can try IsNumeric()

if IsNumeric(test_var) and test_var >= 1 then

nb-
05-12-2008, 10:43 AM
Thanks, MattKlein, but I was not after a solution, there are many, I was after an explanation of why when i asign



test_var = ""


it tests as being greater than equal to 1?

Norie
05-12-2008, 11:19 AM
That's a string.

Any string is greater than any number in VBA.

Might not make much sense I suppose but that's how it is.:)

Zack Barresse
05-12-2008, 02:46 PM
That's a string.

Any string is greater than any number in VBA.

Might not make much sense I suppose but that's how it is.:)
More pointedly it's a Variant with a sub type of String. Using just a String variable will error when compared strictly to a numeric (i.e. Single, Long, Double).

Take these for example:
Sub bbb()

Dim myDouble As Double, mySingle As Single, myLong As Long, myString As String
Dim myVariantString As Variant, myVariantSingle As Variant, myVariantDouble As Variant
Dim strPrompt As String
Const NL As String = vbNewLine

Let myDouble = CDbl(1.234)
Let mySingle = CSng(1.234)
Let myLong = CLng(1.234)
Let myString = CStr("1.234")

Let myVariantDouble = CDbl(1.234)
Let myVariantSingle = CSng(1.234)
Let myVariantString = "1.234"

strPrompt = "Double: " & CStr(myDouble = CLng(myString)) & NL
strPrompt = strPrompt & "Single: " & CStr(mySingle = CSng(myString)) & NL
strPrompt = strPrompt & "Long: " & CStr(myLong = CLng(myString)) & NL & NL

strPrompt = strPrompt & "VarDouble: " & CStr(myVariantDouble = CLng(myVariantString)) & NL
strPrompt = strPrompt & "VarSingle: " & CStr(myVariantSingle = CSng(myVariantString))

MsgBox strPrompt, vbInformation, "RESULTS"

End Sub

As you should be able to tell, you can force values to look at one another. If you change the Variant/String type to an actual string (i.e. add an alpha character) you'll see something else entirely and not error out...
Sub bbb2()

Dim myDouble As Double, mySingle As Single, myLong As Long, myString As String
Dim myVariantString As Variant, myVariantSingle As Variant, myVariantDouble As Variant
Dim strPrompt As String
Const NL As String = vbNewLine

Let myDouble = CDbl(1.234)
Let mySingle = CSng(1.234)
Let myLong = CLng(1.234)
Let myString = CStr("1.234")

Let myVariantDouble = CDbl(1.234)
Let myVariantSingle = CSng(1.234)
Let myVariantString = "a1.234"

strPrompt = "Double: " & CStr(myDouble = CLng(myString)) & NL
strPrompt = strPrompt & "Single: " & CStr(mySingle = CSng(myString)) & NL
strPrompt = strPrompt & "Long: " & CStr(myLong = CLng(myString)) & NL & NL

strPrompt = strPrompt & "VarDouble: " & CStr(myVariantDouble = myVariantString) & NL
strPrompt = strPrompt & "VarSingle: " & CStr(myVariantSingle = myVariantString)

MsgBox strPrompt, vbInformation, "RESULTS"

End Sub

HTH

nb-
05-13-2008, 03:50 AM
Thank you very much Norie and Zack Barresse.

So to summarise be careful in VBA because strings can be compared with numbers without throwing an error, and always test as being greater than numbers.

Make sure you compare numbers with numbers.



String > Number

And that is just the way it is, there is no further explanation of why you can test strings against numbers, and get them being greater, and not an error?

Zack Barresse
05-13-2008, 11:18 AM
Maybe you could fill me in as to why you're looking to investigate an anomolytic response? Strings do not get compared to numerics. Strings compared to numerics will error out. Variant/Strings compared to numerics will not error but give erroneous results. Do you really want to get into the bits and bytes of it? If so, why?

Bob Phillips
05-13-2008, 11:43 AM
... anomolytic response?

:eek: Good grief.

anomalistic - adj. - pertaining to the anomaly - how apt!

Zack Barresse
05-13-2008, 12:14 PM
LOL! Was awaiting your post, Bob. :*)

Seems you are policing the forums fairly well... :devil2:

Bob Phillips
05-13-2008, 12:16 PM
I take my direction seriously young sir!

Zack Barresse
05-13-2008, 12:25 PM
Hey! You hear that everyone?! Bob called me sir! :D

Careful there Bob, that line you're dancing comes with great powers... ;)

nb-
05-13-2008, 12:38 PM
Maybe you could fill me in as to why you're looking to investigate an anomolytic response? Strings do not get compared to numerics. Strings compared to numerics will error out. Variant/Strings compared to numerics will not error but give erroneous results. Do you really want to get into the bits and bytes of it? If so, why?

Curiosity, because I hate black boxes where I dont know whats going on.

Bob Phillips
05-13-2008, 04:45 PM
Hey! You hear that everyone?! Bob called me sir! :D

Darn, I misspelt it, I meant cur!

Zack Barresse
05-14-2008, 04:02 PM
Well, 1 outta 3 ain't bad. ;)