What is the difference between := and = in Excel VBA

Question

I have been working with Excel for a while, yet i have never read what is the difference between these two operators ("regardless of i have used both") := and = in Excel VBA

Salmonella

I wouldn't call := an "operator" - it's more like a "named argument specifier". – Typhon

call function (argument:=value) say all arguments are optional, and there are 10, you want to pass the 5th by name, then variable setting such as and let variable=x – Lysenko

Answer 1

As you already know, = is used to assign values or set objects - e.g. i=1

:= on the other hand (like Comintern mentioned), is used to to assign a value to a certain named argument, afaik only ever inside a method or function.

Consider the following example: you could use something like MsgBox "Hello World", , "Title1" - specifying MsgBox's arguments in the default order - the prompt, the default Buttons-style, then the Title.

Alternatively, one could use := to write MsgBox Title:="Title1", prompt:="Hello world"

Notice that

• the order of the arguments is of no importance here and

• there is no need to specify empty placeholders for default-arguments , ,.

Separate

= is also used as a boolean operator, which is why it is important not to accidentally use = instead of := - e.g. MsgBox Title="Title1", prompt="Hello world" would probably equate to MsgBox False, False if Option Explicit wasn't being used. – Unguinous

Answer 2

Let us take for example the Range.Find method

expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

That is a LOT of conditions to set! But you just want a simple search of the number 2 in Range("A1:A500"):

Without the := operator, you would have to use commas to get to any optional variables to set:

Range("A1:A500").Find(2, , xlValue, , , , , , )

With the := operator, you can specify which conditions you want without delineating through all the default settings:

Range("A1:A500").Find(what:=2, lookin:=xlValues)

Catatonia

The only required argument to Range.Find is What; it's not true that you need to specify all arguments - you need to specify all arguments that you're not providing a value for. – Teacup

@Mat'sMug Clarified you would have to use commas to get through optional values. – Catatonia

Just to point out, in the example above, this would also function the same as the above examples: Range("A1:A500").Find 2,, xlValues. Which one looks more tidy is a matter of opinion. If fewer keystroke is the goal, even this functions the same: [A1:A500].Find 2,,-4163. Also, parentheses are optional if the function is not returning a value (Such as MsgBox "Hi"), and if all the rest of the parameters are blank and/or default, no further comma's need to be included (ie. …xlValue, , , , , , = …xlValue) – Guardsman