How to use ‘With Statements’ in VBA Macro

In this blog post, we will learn about ‘With Statements’ in VBA macro.

Use With Statement, when we need to apply multiple formatting in a single range of the dataset.

Suppose that we have a dataset and we need to apply many formatting by VBA in the dataset.

So we will use the following VBA code to change the following dataset formatting.

Here we will use the below dataset to apply multiple formatting:-

Image-01

In the above dataset, we will apply the following formatting.

We want to change the below formatting:-

-    To change the font size to 16

-    To make the font Bold

-    A font name as Times New Roman

-    Interior color as Green

-    Font color as Yellow

-    Cell border as Continuous

See the below code for the above formatting.

Sub Range_With_Statements() Range("A1:A10").Font.Bold = True Range("A1:A10").Font.Size = 16 Range("A1:A10").Font.Name = "Times New Roman" Range("A1:A10").Interior.Color = vbYellow Range("A1:A10").Font.Color = vbGreen Range("A1:A10").Borders.LineStyle = xlContinuous End Sub

See the image below for the above coding:-

Here in this VBA macro, we are writing a single line for single formatting.

Also notice that we are using the same range (“A1:A10”) for every formatting.

In this situation, we apply the ‘With Statements’ to change the formatting.

‘With Statements’ reduce the length of the code because this will use a range (“A1:A10”) only a single time.

Now we will change the above formatting with the help of ‘With Statements’.

See the below macro to change formatting with ‘With Statement’.

Now we will apply the same formatting with the help of “With Statement”.

See below macro code:-

Sub With_Statements()             With Range("A1:A10")                         .Font.Bold = True                         .Font.Size = 16                         .Font.Name = "Times New Roman"                         .Interior.Color = vbYellow                         .Font.Color = vbGreen                         .Borders.LineStyle = xlContinuous             End With End Sub

See the image below for the above code:-

Here you can see that we are using range (“A1:A10”) in a single time and the rest of the macro coding will remain the same.

Now run the above macro by “F5” or “F8” by step by step execution.

Here you saw that how “With Statement” can reduce the length of macro coding.

Now, I hope that you understand the concept of ‘With Statements’ in VBA macro.

I hope you find this tutorial useful.

Please feel free to put your comments or suggestion in the below-given box.

Thank You

Narendra

Related Post

Easiest Way to Copy and Paste Data in Excel VBA

How to use the MsgBox in Excel VBA

What is Current Region Property in VBA

How to Change or Rename the Sheet Name in Excel VBA

How to Change Sheet Tab Color in Excel VBA

How to Change Font Color in Excel VBA

How to Change the Background Color in Excel VBA