Excel VBA : With End-with Constructs

With End-with Constructs

The With-End With construct enables the user to perform multiple operations on a single object. If you are going to perform several different actions on the same object and typing the same object over and over, use the With ....... End With. After an object is assigned to a variable, VBA can access it more quickly than it can a lengthy reference that has to be resolved.

For example, if you want to change multiple properties on a single object, place the property assignment statements inside the With...End With block, wherein the objectExpression will refer to the object only once instead of once for each property assignment.

Benefits of With End-with Constructs

If in your code, you need to access the same object in multiple statements, you can advance the following advantages by using the With end statement:

• You don't need to assess a complicated expression or object numerous times or assign the result to a temporary variable to refer to its members various times.
• You make your code more readable by eliminating repetitive qualifying expressions.
• You can improve the speed of the code and can faster the processing time.

Syntax

With objectExpression
    Statement1
    Statement2
    ….
    StaementN
 End With 

Parameter With End-with Constructs

objectExpression (Required) – This parameter represents an expression that evaluates to an object. The expression is evaluated only once and can be arbitrarily complex. The expression can assess to any data type, including primary types.

statements (Optional) – This parameter represents one or more statements coded between With and End With that may refer to members of an object that's produced by the evaluation of objectExpression object.

End With (required) - Terminates the definition of the With block.

Nested With End

Nested With...End With statements may be a bit confusing if the objects that are being referred to aren't clear from the point of context. The programmer must provide a fully qualified reference to an object that's in an outer With block when the object is referenced from within an inner With block.

Example 1: Write a macro demonstrating the need of with end constructs.

Sub Without_with_End_option()
 'putting string value in the active cell
 ActiveCell.Value = "VBA is easy to learn."
 'enabling the text to be bold
 ActiveCell.Font.Bold = True
 'changing the font color to red
 ActiveCell.Font.Color = vbRed
 'changing the font
 ActiveCell.Font.Name = "Algerian"
 'altering the font size to 22
 ActiveCell.Font.Size = 22
 'enabling the Italic option for the activecell.
 ActiveCell.Font.Italic = True
 End Sub 

Output

In the above code, you will notice that we have accessed the ‘ActiveCell’ object in multiple statements which can also be performed on a single object.  

Example 2

Sub With_End_Example()
 'putting string value in the active cell
 ActiveCell.Value = "VBA is easy to learn."
     With ActiveCell.Font
         'enabling the text to be bold
         .Bold = True
         'changing the font color to red
         .Color = vbRed
         'changing the font
         .Name = "Algerian"
         'altering the font size to 22
         .Size = 22
         'enabling the Italic option for the activecell.
         .Italic = True
     End With
 End Sub 

Output

Nested With..End

Example 3

Option Explicit
 Public add As Integer
 Public Name As String
 Public x As Integer
 Public y As Integer
 'Declaring a class
 Sub sum()
 add = x + y
     'with end-with construct
     With ActiveCell
         .Value = "Hello " + Name
             'nested with
             With .Font
                 .Bold = True
                 .Color = vbRed
                 .Name = "Algerian"
             End With
         .Offset(1, 0) = "Sum = "
         .Offset(1, 1).Value = add
     End With
 End Sub 
 'Declaring a module
 Sub Math()
 Dim obj As New Class1
 Dim IB As String
 Dim MB As String
 'Declaring input box
 IB = InputBox("Enter your name")
 'Declaring message box
 MB = MsgBox("Do you want to add the numbers!", vbYesNo)
 'If user selects yes option
 If MB = vbYes Then
     'With End-with Constructs
     With obj 
         .Name = IB
         .x = 5
         .y = 6
         .sum
     End With
 End If
 'If user selects No option, program will terminate
 If MB = vbNo Then Exit Sub
 End Sub 

Output

Press F5 to run the VBA code. A prompt box will pop up. Type your name and click on OK.

Msgbox will pop up. If you want to add the number click on YES else on NO button. We have clicked on YES in the following code.

You will notice in the excel sheet in the active cell the changes have occurred (as shown below).