With Statement in Excel VBA

With Statement in Excel VBA

What is VBA With Statement

    VBA with is a statement to specify an object for once and then run multiple statements for it. In simple words, by using the “WITH” statement to specify an object, and after that, you can access all the properties and methods in one go. It starts with the “With” and ends with “End With”

    With statement is used in VBA to access all the properties and methods of a mentioned object. We need to supply which VBA object we are referring to first then close the with a statement with End with , then inside this statement. We can perform all the property changes and methods of the mentioned object.

    In this article, we will see an outline on Excel VBA With. With is a statement in programming languages. It is one of the most commonly used statement, which is used with an END statement. The object which is referred to is mentioned after the with keyword and the whole process is ended with End With statement. A VBA with Statement cannot be without an end with a statement so it is mandatory to close the series of statements. The key feature of the with the statement is that we can access the properties of the object without referring to the object multiple times. The method to access the properties of the object lies in the dot keyword. When we put a dot keyword, we can see a multiple number of options to access the properties of the object. Let us learn how to use with statement

Syntax of VBA with Statement

Following is the syntax that you need to use for using with statement in VBA:

With [OBJECT]
    [Statement]
End With

1.With: It is the starting keyword for the statement that tells VBA that you are specifying the object mentioned.

2.Object: Here, you need to specify the object that you want to use.

3.Statement: A statement(s) that you want to run with the specified object.

4.End With: It is the ending keyword for the statement.

Nested With Statement

I told you in the starting that you can only specify a single object to use the with statement. However, there is a way to use more than one or even multiple objects with it. objects and use the “With”.

Example

sub block_ChangeFont()
With Range("B1:B20").Font
    .Name='Baskerville Old Face"
    .Size=20
    .Size=10
    .underline=True
    .Italic=True
End with
End Sub

Please Watching My Video is Below

Design by Vannak Vannaro