WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Set Statement

VBA Set Statement

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.

VBA Set

How to use Excel VBA Set Statement?

You can download this VBA Set Statement Template here – VBA Set Statement Template

#1 – Set Statement 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.

Code:

Sub Set_Example()

Dim MyRange As Range

End Sub

VBA Set Example 1

Step 2: The moment we assign the data type as range, use the word “Set.”

Code:

Sub Set_Example()

Dim MyRange As Range

Set MyRange =

End Sub

VBA Set Example 1-1

Step 3: Now mention the range.

Code:

Sub Set_Example()

  Dim MyRange As Range

  Set MyRange = Range("A1:D5")

End Sub

VBA Set Example 1-2

Step 4: Now, the variable “MyRange” is equal to the range A1 to D5. Using this variable, we can access all the properties and methods of this range.

VBA Set Example 1-3

We can copy, add a comment in excel, and do many other things.

For example, purpose, I have created some numbers here.

VBA Set Example 1-4

Now using the variable, I will change the font size to 12.

Code:

Sub Set_Example()

  Dim MyRange As Range

  Set MyRange = Range("A1:D5")

  MyRange.Font.Size = 12

End Sub

VBA Set Example 1-5

This will change the font size of the assigned range.

VBA Set Example 1-6

Like this, we can do many things with a particular range by using the word “Set.”

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

#2 – Set Statement 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.

Code:

Sub Set_Worksheet_Example()

    Dim Ws As Worksheet

    Set Ws = Worksheets("Summary Sheet")

End Sub

Visual basic Application Example 2

In the above code, the variable “Ws” defined as an 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.

#1 – Without “Set” Word

Code:

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

Visual basic Application Example 2-1

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

#2 – With “Set” Word

Code:

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

Visual basic Appplication Example 2-2

The moment we set the worksheet name, we can see the variable name while entering the code as part of the list.

#3 – Set Statement 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 “Sales Summary File 2018.xlsx” and “Sales Summary File 2019.xlsx” we can set the two workbooks like the below code.

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

workbook object variables

Now variable Wb1 is equal to the workbook named “Sales Summary File 2018.xlsx,” and variable Wb2 is equal to the workbook named “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 Set Keyword to activate the workbook:

Workbooks("Sales Summary File 2018.xlsx").Activate

Using the Set Keyword to activate the workbook:

Wb1.Activate

This makes the writing of the code lot simpler, and also, once the workbook name is set, there is a worry of typo error of the workbook names.

Recommended Articles

This has been a guide to VBA Set. Here we learn how to use excel VBA set statements to reference the object variable along with some simple to advanced examples. Below are some useful excel articles related to VBA –

  • VBA ReDim Array
  • VBA LEN
  • VBA Code in Excel
  • VBA RGB
5 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Set Statement Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More