GET ULTIMATE EXCEL

July 2, 2021

How To Declare Unknown Objects And Variables In Excel VBA?

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

2 comments on “How To Declare Unknown Objects And Variables In Excel VBA?”

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

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

Excel VBA Is Fun

We believe Excel is one of the most versatile programs out there - and learning to program and automate it makes you very valuable!

You can prevent wasted time and errors, speed up tasks and become indispensable to your organization. 

See how our trainees have gotten raises, promotions and bonuses using their Excel Automation skills while building real problem-solving programs in Excel!
LEARN MORE
Copyright © 2022 ExcelVbaIsFun
X

Forgot Password?

Join Us

0
    0
    Your Cart
    Your cart is emptyReturn to Shop
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram