• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Resetting Variant type


New Member
I'm trying to write a sub that will accept either text or a value as a parameter and have encountered some behavior I don't understand.

If I take this code:

Sub TestInput(strText As Variant)

MsgBox VarType(strText)

End Sub

and call it from the immediate window


I get a messagebox with "2" as expected. If I run it again from immediate with:


I get a compile error, sub or function not defined. If I close excel, open again, run it again with "test" I get a messagebox with "8".

Question is, why doesn't the variant accept a different data type when called successive times?



Excel Ninja
Staff member
I can't replicate your problem?

With Excel 2007 & 2010 I get:

testinput("hello") Msgbox = 8

testinput(8) Msgbox = 2

testinput(10/2/2011) Msgbox = 5

testinput(TRUE) Msgbox = 11


Same here... I have tried calling using another SUB and used the immediate window as well..

Using Excel 2007.... not able to replicate your problem...

What version of excel are you using...



New Member
I'm using Excel 2007. I'll try it at home tonight and see if I get the same.

I'm glad it's working for you guys, makes me feel less crazy.



New Member
So I narrowed it down. I have an Addin that has all my tools. When the code is in the addin it behaves exactly as above, fails when I change input type.

When I copy the same code to a workbook module and change the names to make them unique, it works exactly like I'd expect.

So now the question is why doesn't this work in an addin???