fbpx

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!

Using TypeName() function to discover how things should be declared in VBA

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

X

Forgot Password?

Join Us

Discover the Secrets of Excel Your Coworkers Don't Want You To Know and Your Boss Will Love You For...

Thanks... Video will load in just a sec...

Discover the Secrets of Excel Your Coworkers Don't Want You To Know and Your Boss Will Love You For...

Thanks... Video will load in just a sec...

0
    0
    Your Cart
    Your cart is emptyReturn to Shop