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!

  • thanks – really good explanation

    I need to go back to where I saw it and look closer at what was happening

    Rich

    • I’m not sure the place you’re getting your information, but great topic. I must spend a while studying much more or figuring out more. Thanks for fantastic info I was looking for this information for my mission.

  • Search
    Generic filters
    Exact matches only

    JOIN US & LEARN EXCEL

    Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
    Plus weekly updates to help you learn Excel.

    Download the eBook

    Join Our Weekly Newsletter

    The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

    Join Our Free Newsletter

    About Me

    Jon Acampora Profile

    Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

    This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

    MVP_Horizontal_BlueOnly

    Free Excel Training Webinar Modern Power Tools

    >