Resetting Variant type

[JesseH77]

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


TestInput(53)


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


TestInput("Test")


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?


Jesse

 

[Hui]

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

 

[vijaySharma]

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...


~Vijay

 

[JesseH77]

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.


Jesse

 

[JesseH77]

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???


Jesse