Excel VBA Set Statement
VBA Set is a statement which is used to assign any value key says an object or a reference to a variable, we use this function to define the parameter for a certain variable, for example, if we write Set M = A which means now M reference has the same values and attributes similar to what A has.
In VBA, an object is a core of the excel because without objects we cannot do anything. Objects are Workbook, Worksheet, and Range. When we declare a variable we need to assign a data type to it and we can also assign objects as data types. To assign a value to declared object variables we need to use the word “SET”. The word “Set” used to refer to a new object in VBA, for example, referring to the particular range of the particular worksheet.
How to use Excel VBA Set?
#1 – Set with Range Object Variables
For example assume you want to use the range A1 to D5 quite often. Instead of writing the code as Range(“A1:D5”) every time we can declare the variable as range and set the range reference as Range(“A1:D5”)
Step 1: Declare the variable as a Range object.
Dim MyRange As Range
Step 2: The moment we assign the data type as range use the word “Set”.
Sub Set_Example() Dim MyRange As Range Set MyRange = End Sub
Step 3: Now mention the range.
Sub Set_Example() Dim MyRange As Range Set MyRange = Range("A1:D5") End Sub
Step 4: Now the variable “MyRange” is equal to the range A1 to D5. Using this variable we can access to all the properties and methods of this range.
We can copy, add a comment, and do many other things.
For example purpose, I have created some numbers here.
Now using the variable I will change the font size to 12.
Sub Set_Example() Dim MyRange As Range Set MyRange = Range("A1:D5") MyRange.Font.Size = 12 End Sub
This will change the font size of the assigned range.
Like this, we can do many things with a particular range by using the word “Set”.
#2 – Set with Worksheet Object Variables
We have seen how “set” works with a range object in VBA. It works exactly the same as the worksheet object as well.
Let’s say you have 5 worksheets in your workbook and you want to keep going back to the one particular worksheet, you can set that worksheet name to the defined object variable.
For example, look at the below code.
Sub Set_Worksheet_Example() Dim Ws As Worksheet Set Ws = Worksheets("Summary Sheet") End Sub
In the above code, the variable “Ws” defined as object variable and in the next line by using the word “Set” we set the variable to the worksheet named “Summary Sheet”.
Now by using this variable, we can do all the things associated with it. Take a look at the below two sets of code.
Without “Set” Word
Sub Set_Worksheet_Example1() 'To select the sheet Worksheets("Summary Sheet").Select 'To Activate the sheet Worksheets("Summary Sheet").Activate 'To hide the sheet Worksheets("Summary Sheet").Visible = xlVeryHidden 'To unhide the sheet Worksheets("Summary Sheet").Visible = xlVisible End Sub
Every time I have used the worksheets object to refer to the sheet “Summary Sheet”. This makes the code so lengthy and requires a lot of time to type.
As part of the huge code, it is frustrating to type the worksheet name like this every time you need to reference the worksheet.
Now take a look at the advantage of using the word Set in VBA Code.
With “Set” Word
Sub Set_Worksheet_Example() Dim Ws As Worksheet Set Ws = Worksheets("Summary Sheet") 'To select the sheet Ws.Select 'To Activate the sheet Ws.Activate 'To hide the sheet Ws.Visible = xlVeryHidden 'To unhide the sheet Ws.Visible = xlVisible End Sub
The moment we set the worksheet name we can see the variable name while entering the code as part of the list.
#3 – Set with Workbook Object Variables
The real advantage of the word “Set” in VBA arises when we need to reference different workbooks.
When we work with different workbooks it is so hard to type in the full name of the workbook along with its file extension.
Assume you have two different workbooks named as “Sales Summary File 2018.xlsx” and “Sales Summary File 2019.xlsx” we can set the two workbooks like the below code.
Sub Set_Workbook_Example1() Dim Wb1 As Workbook Dim Wb2 As Workbook Set Wb1 = Workbooks("Sales Summary File 2018.xlsx") Set Wb2 = Workbooks("Sales Summary File 2019.xlsx") End Sub
Now variable Wb1 is equal to the workbook named as “Sales Summary File 2018.xlsx” and variable Wb2 is equal to the workbook named as “Sales Summary File 2019.xlsx”.
Using this variable we can actually access all the properties and methods associated with the workbook.
We can shorten the code like the below.
Without Using VBA Set Key Word to activate the workbook:
Workbooks("Sales Summary File 2018.xlsx").Activate
Using the Set Key Word in Excel VBA to activate the workbook:
This makes the writing of the code lot simpler and also once the workbook name it set there is a worry of typo error of the workbook names.
This has been a guide to VBA Set. Here we learn how to use excel vba set statement to reference the object variable along with some simple to advanced examples. Below are some useful excel articles related to VBA –