Difference Between Equal ‘=’ and ‘:=’ Colon Equal in VBA

Rich asked a great question about the difference between the use of the equal sign = and the colon equal sign := in VBA.

Often times you will see a combination of the two characters with the color before the equal sign. :=

= Equal Sign Sets a Property, Object, or Variable in VBA

Typically we use the equal sign to set a property of an object, or to write a value to a variable for temporary storage.

The following line of code will set the Value property of Cell A1 to the word hello.

Range("A1").Value = "Hello"

We can also use the equal sign to set the value of a variable. The following line will set the value of the variable lRow equal to 10.

lRow = 10

The equal sign can also be used in If statements as a comparison operator to evaluate a condition.

If (4+4) = 9 Then "All the textbooks in the world must be re-printed!"

:= The Colon Equal Sign Sets a Value of a Parameter for a Property or Method

The colon equal sign := is used to set the value of an parameter (argument) for a property or method.

We will use the Worksheets.Add method as an example. The Worksheets.Add method has four optional parameters.

Worksheets.Add ([Before], [After], [Count], [Type])

When using this method we reference the parameter name, followed by the := colon equal sign, then the parameter value. The following line of code will add a worksheet after the active sheet.

Worksheets.Add After:=Activesheet

Notice the use of the := colon equal sign symbols. This tells VBA that we are setting the parameter to a value or object.

We don't have to list out all the optional parameters of the method either. The following line of code will add 5 worksheets after the active sheet. Notice that I did not need to reference the other optional parameters or leave commas between the parameters. We are able to do this because we are referencing the parameter name before the parameter value.

Worksheets.Add After:=Activesheet, Count:=5

So to sum it up, the colon equal combination := is used to set the value of a parameter (argument) for a property or method. The equal sign is use to set the value of a property, object, variable, or as a comparison operator.

I hope that helps. Please leave a comment below with questions or suggestions. Thanks!