Catégorie : Excel VBA Course

You can assign object variables step-by-step following the object hierarchy. This approach makes your code clearer and easier to maintain, especially when working with multiple objects located at different levels within the hierarchy.

Here is an example:

Sub HierarchicalObjectVariableAssignment()
    Dim WS As Worksheet
    Dim Rg1 As Range, Rg2 As Range   
    Set WS = ThisWorkbook.Worksheets("Sheet1")
    Set Rg1 = WS.Range("C1:C2")
    Set Rg2 = WS.Range("D1:D2")   
    Rg1.Value = 1.7
    Rg2.Value = 3.4  
    Set Rg1 = Nothing
    Set Rg2 = Nothing
    Set WS = Nothing
End Sub

Explanation:

• First, the variable WS is assigned as a reference to the worksheet named « Sheet1 ».
• Then, the variables Rg1 and Rg2 are assigned as references to different ranges within that worksheet.
• This hierarchical assignment avoids repeatedly typing the full path and helps organize code logically.
• After modifying the range values, the object variables are set to Nothing to release the references.

You are already familiar with variables of basic data types such as Integer, Double, and others. Object variables, however, do not store numbers or text; instead, they hold references (pointers) to objects. These objects can be existing ones you want to refer to with a shorter or more convenient name, or they can be new objects you create yourself.

Here is an example:

Sub ObjectVariableExample()
    Dim Rg1 As Range
    Dim Rg2 As Range
    Set Rg1 = ThisWorkbook.Worksheets("Sheet1").Range("B1:B3")
    Set Rg2 = Rg1   
    Rg1.Value = 18.2
    Rg1.NumberFormat = "0.000"
    Rg2.Font.Size = 24  
    ApplyBorder Rg2   
    Set Rg1 = Nothing
    Set Rg2 = Nothing
End Sub

Sub ApplyBorder(x As Range)
    x.Borders.Weight = xlThick
End Sub

Explanation:

• The variables Rg1 and Rg2 are declared as references to objects of the Range type using the As Range syntax.
• Alternatively, they could be declared more generally as Object type (e.g., Dim Rg1 As Object), but declaring them with the specific type Range is clearer and allows for faster execution and better IntelliSense support.
• Assigning an object reference to a variable requires the Set keyword. Here, Rg1 is assigned a range object corresponding to the cell range « B1:B3 » on worksheet « Sheet1 ».
• Rg2 is then assigned the same reference as Rg1, so both variables point to the same range object.
• You can access and modify this range using either variable. In the example, the cell values, number format, and font size are changed.
• Object references can also be passed as parameters to procedures or functions. Here, the procedure ApplyBorder receives a Range parameter x and sets the border weight for the range.
• After finishing with the object variables, assigning Nothing to Rg1 and Rg2 releases their references, meaning they no longer point to any object.

Additional Notes:

• After declaring the variable Rg1 as a Range, the VBA editor provides IntelliSense: when you type Rg1. it shows a list of properties and methods available for the Range object. This feature is not available if you use an expression like Range(« B1:B3 »)..
• Pressing the F1 key opens the help documentation related to the selected property or method for the appropriate object type, providing quick access to detailed information.

The With statement in VBA allows you to execute a series of instructions on a single object efficiently. By establishing the reference to the object once, you can repeatedly apply multiple properties or methods to it without repeating the full object path. This is especially useful when dealing with long object names, making the code clearer and easier to read.

The basic structure is:

With Object
    [Statements]
End With

Here is an example:

Sub WithStatementExample()
    With ThisWorkbook.Worksheets("Sheet1").Range("A1:A3")
        .Value = 12.8
        .NumberFormat = "0.000"
        .Font.Size = 24
        .Borders.Weight = xlThick
    End With
End Sub

Explanation:

In this example, several properties of the range A1:A3 are changed within the With block. The value is set to 12.8, the number format is customized, the font size is enlarged, and the border thickness is set to a thick line.

Within the With block, each property or method reference begins with a dot (.), which clearly indicates that it belongs to the object specified after With. This shorthand avoids the need to repeat the full object reference in each line, improving both readability and performance.

Nested With Statements

You can also nest With statements to work with sub-objects or collections. To illustrate, the example from Section 2.4.19, “Sorting a Range with Multiple Keys”, has been rewritten as follows:

Sub NestedWithExample()
    ThisWorkbook.Worksheets("Sheet4").Activate
    With ActiveSheet.Sort
        With .SortFields
            .Clear
            .Add Range("A1:A5")
            .Add Range("B1:B5")
            .Add Range("C1:C5")
            .Add Range("D1:D5")
            .Add Range("E1:E5")
        End With
        .SetRange Range("A1:E6")
        .Apply
    End With
End Sub

Explanation:

• The outer With works with the Sort object of the active worksheet. This includes accessing the SortFields collection and calling methods like .SetRange and .Apply.
• The inner With operates on the SortFields collection itself, allowing you to call .Clear and .Add methods multiple times on this collection without repeating the full reference.

This nesting makes the code more concise and logically structured when working with complex object hierarchies.

When accessing individual objects in VBA, it is best to do so as directly as possible—that is, without unnecessary intermediate steps. Unfortunately, many VBA applications follow a pattern like this:

• A workbook is activated.
• A worksheet is activated.
• A range is selected.
• Properties of the selected range are modified or methods are applied to it.

While this approach of activating and selecting is easy to understand, it tends to be slower and more prone to errors.

Here is an example illustrating this less efficient method:

Sub ActivateAndSelect()
    ThisWorkbook.Activate
    Worksheets("Sheet1").Activate
    Range("A7:A9").Select
    Selection.Interior.Color = vbYellow
End Sub

By referencing objects directly instead, you create applications that run faster and are easier to maintain. Here is an improved version using direct referencing:

Sub ReferenceDirectly()
    ThisWorkbook.Worksheets("Sheet1"). _
    Range("A10:A12").Interior.Color = vbYellow
End Sub

At first glance, this VBA code might seem more complex. However, it follows the object hierarchy logically: workbook, worksheet, cell range, property. Such statements can be longer, but they can be split across multiple lines for readability, as demonstrated above.

This method reduces unnecessary activation and selection steps, which improves performance and reduces the chance of runtime errors caused by unexpected selections or activations.

In the Visual Basic Editor (VBE), you can open the Object Browser by pressing the F2 key. This tool provides a comprehensive list of all available classes, along with their properties, methods, and events.

When you select a class—for example, WORKSHEETS as shown detailed list of that class’s properties, methods, and events appears on the right side of the window.

Additionally, further assistance is available through the Help menu in the VBE. By choosing the menu item Microsoft Visual Basic for Applications Help, you access Microsoft’s online documentation, which includes the VBA reference for Excel.

This reference is an invaluable resource for exploring VBA objects, understanding their members, and learning how to use them effectively in your projects.