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.
Contents
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:
- font color
- size
- background color
- font styles like bold, italics and underline, etc.
It can have methods like:
- onchange
- onclick
- 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
< object var >
is the name of the variable which will hold the object- New is an optional keyword that can be used during declaration to implicitly enable object creation
< object expression >
is the expression that points to the objectNothing
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; 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.