Tricky data type error

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>

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


"RB Smissaert" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>>
>
>

Do you remember what the Compiler Error was?


"RB Smissaert" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in
>> message news:[email protected]...
>>> 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
>>>
>>>
>>>
>>
>>
>

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

RBS

"Chip Pearson" <[email protected]> wrote in message
news:%[email protected]...
> Do you remember what the Compiler Error was?
>
>
> "RB Smissaert" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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
>>>>
>>>>
>>>>
>>>
>>>
>>
>
>

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 try to convert that variable into the type required and place the value in it. However depending on what goes on with the variable beforehand it may or May not be able to do this. When you declare a variable

Dim strtest as String

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

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

From memory.....It been awhile