VBA Projects

Full Access with Source Code
  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Share Post

The Set statement is one of the fundamental elements for programming. It is used to create a reference variable, also known as an object variable, which points to a specific object in the Excel application. The Set statement is used in conjunction with the ‘New’ keyword to create new instances of objects or classes. This statement provides a convenient way to access and manipulate objects in Excel through VBA code, making it an essential concept for any VBA developer.

VBA Set Statement Understanding


The general syntax for the Set statement is as follows:

Set VariableName = Object

Here, VariableName is the name given to the reference variable, and Object is the specific object to which it will point.

Examples of  VBA Set Statement

Example 1: Creating a new instance of an object

Let’s say we want to create a new workbook object and assign it to a reference variable for further manipulation. This can be achieved using the Set statement as follows:

Sub NewWorkbook()
    Dim mainWorkbook As Workbook
    Set mainWorkbook = New Workbook
    'Code to manipulate the new workbook goes here
End Sub

In this example, we declare a variable mainWorkbook of type Workbook and then assign a new instance of the Workbook object to it using the Set statement. This new workbook can now be referenced and manipulated using the mainWorkbook variable.

Example 2: Pointing to an existing object

We can also use the Set statement to point to an already existing object in the Excel application. For example, if we want to reference and manipulate the ActiveSheet object, we can use the following code:

Sub ManipulateActiveSheet()
    Dim activeSheet As Worksheet
    Set activeSheet = ActiveSheet
    'Code to manipulate the active sheet goes here
End Sub

In this example, we declare a variable activeSheet of type Worksheet and then use the Set statement to assign the value of the ActiveSheet object (in this case, the currently active sheet) to it.

Example 3: Setting a range object

The Set statement is particularly useful when working with ranges in Excel. We can use it to assign a Range object to a reference variable for manipulation. For example, if we want to assign a cell range A1:B5 to a variable called myRange, we can use the following code:

Sub AssignRange()
    Dim myRange As Range
    Set myRange = Range("A1:B5")
    'Code to manipulate the range goes here
End Sub

Here, the Range(“A1:B5”) statement creates a Range object representing the cells A1 to B5, and then the Set statement assigns this object to the myRange variable for further manipulation.

Example 4: Working with charts

The Set statement is also frequently used when working with charts in Excel. For instance, if we want to manipulate a specific chart, we can use the Set statement to assign it to a reference variable and then use that variable to invoke various chart properties and methods. Consider the following code:

Sub ManipulateChart()
    Dim myChart As Chart
    Set myChart = ActiveSheet.ChartObjects("Chart 1").Chart
    'Code to manipulate the chart goes here
End Sub

In this example, we use the ChartObjects() method to refer to a specific chart object on the active sheet (in this case, “Chart 1”) and then use the Set statement to assign the Chart object belonging to that chart to the myChart variable.

Example 5: Assigning an object to an existing variable

The Set statement can also be used to assign an object to an existing reference variable. Take the following code as an example:

Sub AssignObject()
    Dim myRange As Range
    'Code to determine range goes here
    Set myRange = Range("B1:F10")
    'Code to manipulate range goes here
End Sub

In this example, we first declare the myRange variable, and then use the Set statement to assign a Range object to it after determining which range should be assigned.

Important Notes & Remarks:

  • The Set statement is only used for assigning objects to reference variables. It cannot be used to assign primitive data types such as integers, strings, or doubles. Instead, the = operator is used for assigning these types of values.
  • When assigning an object to a reference variable, the object being assigned must be of the same type as the variable. For example, a Workbook object must be assigned to a variable of type Workbook.
  • Unlike the Set statement in earlier versions of Visual Basic, the Set statement in VBA is mandatory for creating a reference variable. Failure to use it will result in a runtime error.
  • The Set statement can only be used with objects within the Excel application. It cannot be used to set references to objects in other applications.
  • The Set statement cannot be used with the special Nothing keyword, which represents an uninitialized object reference.

Concluding Thoughts:

The Set statement is a powerful and essential element in VBA programming. It allows us to create reference variables, assign objects to them, and manipulate those objects through the variable. Understanding how to use the Set statement correctly can greatly enhance your VBA coding skills and make your code more efficient and effective.

I hope this post has helped you understand the Set statement better and its importance in VBA programming. Have you encountered any difficulties while using the Set statement or do you have any additional tips or tricks to share? Please feel free to share your thoughts and feedback in the comments below. Your feedback is highly appreciated and will help me enhance the quality of my future posts. Thank you for reading!

Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: VBA StatementsTags: , Last Updated: September 28, 2023

Leave A Comment