VBA With Statement

Have you ever found yourself repeating the same object or property multiple times in your VBA code? This not only takes up space but also makes the code less readable and harder to maintain.

The With statement in VBA is a simple yet powerful tool that can make your code more concise, efficient, and easier to read. In this blog post, we will explore the purpose, syntax, examples, and important notes and remarks of using the With statement in VBA.

VBA With Statement

Purpose

The With statement in VBA allows you to perform a series of operations on a single object without having to refer to the object multiple times. It acts as a shortcut by letting you specify the object only once and then executing the subsequent statements for that object. This can save you a significant amount of time and effort while coding.

Syntax

The syntax for using the With statement in VBA is as follows:
With object[statements] End With
Here, ‘object’ can be any object in VBA such as a worksheet, range, workbook, chart, etc. The ‘statements’ inside the block are the operations that you want to perform on the specified object. These statements can include properties and methods of the object.

Examples of VBA With Statement

Formatting Cells in a Worksheet

The With statement can be used to format cells in a worksheet efficiently. For example, if you want to format cell A1 to have a bold and italic font, you can use the With statement as follows:

Sub FormatCells()
    With ThisWorkbook.Worksheets("Sheet1").Range("A1")
        .Font.Bold = True
        .Font.Italic = True
    End With
End Sub

Updating Chart Properties

The With statement can also be used to update properties of a chart. For instance, if you want to change the chart title to “Sales Analysis” and the font color to blue, you can use the With statement as shown below:

Sub UpdateChart()
    With ThisWorkbook.Charts("Chart1").ChartTitle
        .Text = "Sales Analysis"
        .Font.Color = RGB(0,0,255)
    End With
End Sub

Changing Font Properties

You can also use the With statement to change multiple font properties at once. For example, if you want to change the font size and color of cells A1:B5 to 12 and red respectively, you can write the following code:

Sub ChangeFont()
    With ThisWorkbook.Worksheets("Sheet1").Range("A1:B5").Font
        .Size = 12
        .Color = RGB(255,0,0)
    End With
End Sub

Nested With Statements

The With statement can also be nested within another With statement. This is particularly useful when you have multiple objects that you want to perform operations on. For instance, if you want to change both the font color and background color of cells A1:A10 to green, you can nest one With statement inside another as follows:

Sub ChangeFontAndColor()
    With ThisWorkbook.Worksheets("Sheet1").Range("A1:A10").Font
        .Color = RGB(0,255,0)
        With ThisWorkbook.Worksheets("Sheet1").Range("A1:A10").Interior
            .Color = RGB(0,255,0)
        End With
    End With
End Sub

Looping Through a Range

The With statement is also useful when looping through a range of cells. It allows you to specify the object only once, making the code more efficient and readable. For example, if you want to iterate through cells A1:A100 and multiply the values by 10, you can use the With statement as shown below:

Sub LoopThroughRange()
    Dim cell As Range
    With ThisWorkbook.Worksheets("Sheet1").Range("A1:A100")
        For Each cell In .Cells
            cell.Value = cell.Value * 10
        Next cell
    End With
End Sub

Important Notes & Remarks

• The With statement can only be used on a single object. You cannot use it on a collection of objects.
• The statements inside the With block must be indented for better readability and easier maintenance.
• The object specified in the With statement must be the same as the object in the subsequent statements.
• The ‘End With’ statement is mandatory and must be placed after the last statement in the With block.
• Using the With statement does not affect the performance of your code. It is solely for the purpose of making your code more concise and readable.

Concluded Post

The With statement in VBA is a useful tool that can make your code more efficient, readable, and easier to maintain. In this post, we learned about its purpose, syntax, examples, and important notes and remarks. The With statement can save you a significant amount of time when dealing with multiple objects, properties, and methods in your VBA code. Give it a try in your next VBA project and experience the benefits yourself.

Thank you for reading this blog post on the VBA With statement. I hope you found it informative and helpful in your VBA coding journey. I would love to hear your feedback and views on using the With statement. Have you used it before? What has been your experience with it? Share your thoughts in the comments below.