I get this question now and then..
How am I supposed to declare these things in order to get Intellisense or just because I want to declare everything?
My Students
Firstly, I would say, "Remove Option Explicit" from the top of your macros as you don't generally need this handicap, but some would argue that they were classically trained to do it, and thusly it is a firm law to them.
Related article Forget Option Explicit, Excel VBA Declaring Variables: 6 Reasons Why You No Longer Need It
But that is beside the point.
I want to show you how to determine what a variable or Object variable should be declared as, regardless of why you need it declared.
There's a neat built-in function called TypeName() that reveals what something's variable or object type currently is. In order to test this, please temporarily (or forever) remove Option Explicit from the top of your current code module.
Now, as you're stepping through the code in your macro using F8, go ahead and step right past the variable(s) you want to spy on and then open the Immediate Window (Ctrl+G on Windows) and try printing the answer. You can use a question mark (?) or Debug.Print to print the answer to a question, so try something like this.
Copy the below code and paste it into a code module and then step through the code using F8 Key (Cmd+Shift+i on a Mac).
Sub testfso()
Dim wb As Workbook, ws As Worksheet
Set fso = CreateObject("Scripting.FileSystemObject")
Set fldr = fso.GetFolder("C:\")
End Sub
Once you've stepped past the variables fso and fldr, try opening the Immediate Window and using ? typename(fso) or ? typename(fldr) and pressing Enter!
That's it! Now you can discover how something should be declared or what current state of being an object or a string, date, etc is in!
Happy Coding!
Dan Strong
ExcelVbaIsFun.com Founder
Hi Dan,
Thank you for this blog. I'm not classical trained but am always using Option Explicit. What about typos? For example you have declared sht as worksheet and you are referring to sjt.Range("A1")
BTW, I'm also the guy who always uses call when calling a procedure 🙂
Cheers,
Jan
Hi Jan,
Great to hear from you again!
There are a few good reasons for Option Explicit and a few reasons for getting rid of it. Please take a look at my post here about the topic and see what Mr. Excel says in a video clip as well.
The call method is really good and has bonus features that you can use, like calling dynamically different macros based on the name.
Thanks
Dan