• 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

    Hui...

  • 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

JesseH77

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


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


~Vijay
 
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
 
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
 
Back
Top