The With statement in VBA is used to simplify and streamline your code when you need to perform multiple operations on a specific object, such as a cell, range, or spreadsheet object, without having to reference that object repeatedly. Instead of typing the name of the object every time you want to perform an action on it, you can use the With statement to temporarily establish a context in which all operations will be applied to the specified object.

Sintaxis

With object
     ' Code to work with the object
     ' You can access the object's properties and methods here
End With

With: This is the keyword that starts the WITH construction.

object: Here you must specify the object you want to work with within the WITH construction. You can use a previously defined object, such as an object variable, an object property, or any other valid object in VBA.

Within the With block, you can perform multiple operations on the specified object, accessing its properties and methods without having to repeat the object name in each line of code. This makes the code more concise and readable.

How does it work

• Start the WITH block: You begin the WITH block with the With keyword followed by the name of the object you want to work with. This object can be a previously declared object variable, a property of an existing object, or any valid VBA object.
• Within the WITH block: Once you’ve initiated the WITH block, you can access the properties and methods of the specified object using a period (.) followed by the name of the property or method. The period indicates that you’re working with the object specified in the WITH block. You don’t need to repeat the object’s name in each line, making the code more concise and readable.
• End the WITH block: When you’re done performing operations on the object within the WITH block, you simply end the WITH block using the End With keyword. This signifies the end of the context in which you’re working with the specified object.

Here’s a simplified example to illustrate how it works:

Sub ExampleWithStatement()
    Dim myWorkbook As Workbook
    Set myWorkbook = Workbooks.Open("C:\myFile.xlsx")
    
    ' Use the WITH statement to work with the Workbook object
    With myWorkbook
        ' Perform operations on the Workbook object without repeating its name
        .Sheets(1).Range("A1").Value = "Hello, world"
        .Save
        .Close
    End With
    
    ' Clear the object variable after use
    Set myWorkbook = Nothing
End Sub

In this example, within the WITH block, we worked with the myWorkbook object (a Workbook object) and accessed its properties and methods, such as .Sheets, .Range, .Value, .Save, and .Close, without repeating the myWorkbook name in each line. This makes the code cleaner and easier to understand.

In summary, the With statement is useful for simplifying and making code more readable when you need to work with multiple properties or methods of the same object in VBA. It helps avoid unnecessary repetition of the object name and makes the code more efficient.

Switch the language

Spanish

Help us grow the project

Unlock Your Potential

Excel

Access

Power BI

Join our Facebook Group!