The Set Keyword in VBA: How to Use and Examples

Before we get into the Set keyword, I’m going to quickly go over a couple fundamentals about objects in VBA. If you already know this stuff, just skip ahead a couple sections.

Introduction to Objects

An object in a programming language is a class with some properties and methods. For example, a textbox is an object. This object can have properties like: 

  1. font color
  2. size 
  3. background color
  4. font styles like bold, italics and underline, etc. 

It can have methods like: 

  1. onchange 
  2. onclick 
  3. onload, etc.

An object is a data type that can hold values, properties, and change behavior too. In simple terms, we can also call an object a collection of several variables. In that respect, arrays, dictionaries, and lists are all objects, because they hold several values.

Assigning a Value to an Object

Unlike variables, objects cannot be assigned references using a mere “=" (equal to) sign. They are special since they hold several / large values.

The Set Keyword

The “set" keyword must be used in order to assign object references to any variable or empty the contents of an object.

Syntax

Set < object var > = {[ New ] < object expression >} | Nothing 

Where 

  1. < object var > is the name of the variable which will hold the object
  2. New is an optional keyword that can be used during declaration to implicitly enable object creation
  3. < object expression > is the expression that points to the object
  4. Nothing is used to empty the space the object is using and make is equal to null. “Nothing" is the keyword that has to be used for objects. This statement discontinues the association of the object with its variable name.

Examples

An Object for a Range in a Worksheet

Here we use the Set keyword to define a range of cells on a worksheet.

I = 5
J = 10
Set range_new1 = Worksheets("My demo sheet").Rows( i &amp;amp;amp; ":" &amp;amp;amp; j )

Open a Browser (Ex: Internet Explorer)

We create an object that defines a browser (in this case, Internet Explorer). From there onwards, we can easily refer to the same object or work on it using a single word.

Set IE_demo = CreateObject("InternetExplorer.Application")
    IE_demo.Visible = True

    IE_demo.Navigate "https://www.google.com"

The code below is a simple example to perform some actions on a browser window. We define an object that holds the document, i.e. all html code of the specific browser window. Then, using that object, we try to list out objects with the same tagname or classname or specific elements (as defined in the html content of the doc object).

' this line of code sets the “doc" as an object containing the browser’s document content
Set doc = IE_demo.document

Set the value for object “inputtags" . It is set to contain all input tags of the doc object.

Set inputtags = doc.getElementsByTagName("input").Value

Here the “elem" object is set to contain all element(s) of the document object that have the class name “all-title". Then we click on the first item ( with index 0) in that list.

Set elem = doc.getElementsByClassName("all-title").Item
' now the first item in the list of elem object list is clicked
elem.all.Item(0).Click   
' close the document
doc.close
' empty the doc object and release resources
Set doc = Nothing

Setting a File as an Object

There may be situations where we keep referring to a file to perform many actions on it like open, read, write, append, validate, close, open file again, save data, and many more. Instead of referring to the whole file path again and again, it can instead be defined as an object with a short object name that can be used throughout the code. Here is an example that demonstrates this:

Set elem=doc.getElementsByClassName("all-title").Item
' now the first item in the list of elem object is clicked
elem.all.Item(0).Click
' close the document
doc.close
'empty the doc object and release resources
Set doc = Nothing
Sub open_a_file_demo()

' declare variables
Dim str_path_name
Dim wb1 As Workbook

' assigning a value
str_path_name = "C:\Users\LAKSHMI RAMAKRISHNAN\Downloads\Project 2.xlsx"

' now let us open the file using the this statement and assign it to the wb1 object so that it can be used in the code further. The set keyword is used to do this.
Set wb1 = Workbooks.Open(Filename:=str_path_name, ReadOnly:=True)
‘Try reading the file using the object
Msgbox wb1.Sheets(2).cells(3, 4).Value
' Try writing after opening the file in "read only mode". This should throw an error.
Wb1.Sheets(0).Cells(2, 2).Value = "Try writing"
End Sub

Using Set Keyword to Create a Shell Object

There are several operations that use the shell commands to perform some things on the windows. For example, killing a task from the task manager, opening a file, running a thread at a scheduled time, and many more. 

Here is a short program to create the necessary variables and objects, as well to open a notepad and a browser window using the created shell object. The id of the object opened last is also printed at the end.

Sub shell_demo_4()

'Declaration of objects and variables
Dim objsh_obj As Object
Dim intcounter As Integer
Dim str_ie_pr_id

'Assign all values
Set str_ie_pr_id = Nothing
intcounter = 0
Set objsh_obj = CreateObject("shell.application")
counter = objsh_obj.Windows.Count

' open an internet explorer
IE = Shell("C:\Program Files\Internet Explorer\iexplore.exe -nosessionmerging , -noframemerging", vbNormalFocus)

'open a notepad
ret_val = Shell("C:\WINDOWS\NOTEPAD.EXE", 1)

'wait till the window opens
Do Until objsh_obj.Windows.Count = intcounter + 1
Loop

'set the new process ids for the opened objects

' the last but first that was opened
Set str_ie_pr_id = objsh_obj.Windows(objsh_obj.Windows.Count - 2)

' the last object that was opened
Set str_ie_pr_id = objsh_obj.Windows(objsh_obj.Windows.Count - 1)

'Print the last process id alone
Debug.Print str_ie_pr_id

End Sub 

Conclusion

The above examples demonstrate the use of the “Set" keyword. We can create “short word" references to any object that can be easily referred to or reused in the code window further. It can help us avoid repeated use of lengthy expressions which might also be cumbersome and difficult to maintain.

Leave a Reply

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