Tricky data type error

[RB Smissaert]

Was caught out by something tricky that I thought might be worth it to pass
on.

Had coded, simplified, this code:

Sub test()

Dim strTest As String

If strTest = 4 Then
MsgBox strTest
End If

End Sub

Strangely, this sometimes compiles and sometimes doesn't.
Unfortunately, it did with me, but it can't run as there obviously will be
an error:
Type mismatch (Error 13)
Strangely also it can sometimes run on Excel 2003, but as far as I can see
never on 2000.
Just completely unpredictable.

Would there be a way to catch out this coding error reliably at compile
time?


RBS

 

[RB Smissaert]

Strangely (at least to me) this runs:

Sub test()

Dim strTest As String

strTest = 2

If strTest = 4 Then
MsgBox strTest
End If

End Sub

But this doesn't:


Sub test()

Dim strTest As String

strTest = "a"

If strTest = 4 Then
MsgBox strTest
End If

End Sub


This might in fact explain the different behaviour on the different
machines, rather than the Excel version.


RBS

 

[Chip Pearson]

I've never seen a situation in which that code wouldn't compile.
Of course, it blows up at run-time, but it should compile.

No, there's no way to catch such errors when you write or compile
the code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[RB Smissaert]

Well, I definitely have seen a compile error with this.
Thanks for confirming it can't be caught normally at compile time.

RBS

 

[Chip Pearson]

Do you remember what the Compiler Error was?

Well, I definitely have seen a compile error with this.
Thanks for confirming it can't be caught normally at compile
time.

RBS

 

[RB Smissaert]

My mistake not to write it down, but I think it was similar
to the runtime error message.

RBS

 

[Willot]

I think it has to do with certain assumption excel makes during runtime

If you try to place a non Compatable value in a variable. It will tr
to convert that variable into the type required and place the value i
it. However depending on what goes on with the variable beforehand i
may or May not be able to do this. When you declare a variable

Dim strtest as Strin

You DIMension up an area of memory in a "format" to receive strin
type. BUT strtest ISNT yet a string. This is decided when it gets i
first value IE strtest = 2 or StrTest = "a" Depending on what you d
from then on determines whether you get an error. It has something t
do with the fact that a String data type is really just a singl
dimension array of characters

While excel will try to work around your errors there are times when i
will just throw up its hands and go "Sod you Im Not Dealing with thi
Rubbish! YOu FIX it!!" IE you get a runtime erro

From memory.....It been awhil