Excel VBA Object Variables

Tips on using them

Object variables are the variable types that have their names written in black (right hand side) when you define them – see example image below for Range, Worksheet and Workbook. These are the most common objects used. There are two important things to know about using object variables.

Define Object Variable

The first important thing to note is that you have to use the Set command to make the variable equal anything.

Set Objects

If you leave out the Set command the code will generate an error. See below.

Missing Set error

The second important thing is that you should always set the object variable to Nothing at the end of your code. This frees up RAM and stops “memory leakage” which can slow Excel down.

Set Object to Nothing

The advantage with using object variables is that it becomes easy to work with the object.

When you type the object name and press the full stop it lists all the options available for that object – see images below.

Obj_6 Range Object List

This speeds up writing your code. It also shows you what you can use the object for.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.