8

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

moodymudskipper
  • 46,417
  • 11
  • 121
  • 167
Moreno
  • 608
  • 1
  • 9
  • 24
  • 5
    I wouldn't call `:=` an "operator" - it's more like a "named argument specifier". – Comintern Nov 23 '16 at 15:21
  • `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` – Nathan_Sav Nov 23 '16 at 15:27

2 Answers2

12

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 , ,.

Martin Dreher
  • 1,514
  • 2
  • 12
  • 22
  • 5
    `=` 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. – YowE3K Nov 23 '16 at 15:44
4

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)
Chrismas007
  • 6,085
  • 4
  • 24
  • 47
  • 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*. – Mathieu Guindon Nov 23 '16 at 16:09
  • @Mat'sMug Clarified you would have to use commas to get through optional values. – Chrismas007 Nov 23 '16 at 16:10
  • 1
    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`) – ashleedawg Apr 10 '18 at 01:21