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
Syntax:
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!
