• 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
 

Hui

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
 

vijaySharma

Member
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

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.


Jesse
 

JesseH77

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


Jesse
 
Top