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.
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
Awesome! Thanks Rich!
thanks – I got a good explanation and satisfactory answer of my query
Thank you Jon. Good enough explanation for me.
I’m just starting to practice VBA :-).
Great explanation. VBA beginner.
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.
For me it was helpful, too.
MsgBox “Thank you, Jon” 🙂
Thanks, you explained this very well.
Thanks. It is surprising how hard it is to find this information in the VBA reference manual. Most languages would have an assignment operator and be done with it but not VBA. No sir, if there is a more obscure way to do things VBA will find it.