Vba - := ????

[ajocius]

Group,
I often see examples of VBA code using the := (colon equal). Wha
is this and what is it typically used for? It appears to be some for
of assignment.

Thank you in advance........

Ton

 

[Bernie Deitrick]

Tony,

For many methods, named parameters are used, and the := is used to assign a value to the named
parameter. For example, you could use either of these with the find method:

Dim c As Range
Set c = Cells.Find(What:="myFindString", LookIn:=xlValues, lookAt:=xlWhole)
This example leaves out the After:= parameter (but doesn't require a space holder for it).

Set c = Cells.Find("myFindString", , , xlWhole)
This one leaves out the parameter names (and skips the After and LookIn parameters) but requires
space holders (the commas).

HTH,
Bernie
MS Excel MVP

 

[TommySzalapski]

It's mostly to make things easier with argument lists
Example:
You want to open a workbook from a macro and have it go in the recently
used file list. The syntax for the open method is:

expression.Open(FileName, UpdateLinks, ReadOnly, Format, Password,
WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter,
Editable, Notify, Converter, AddToMRU)

You need to set AddToMRU to true. You could go through and re-enter
all the defaults for the other arguments like
Workbooks.Open("file.xls", false, false,....., true) and you have to go
figure out what all needs to go there or you could type:

Workbooks.Open "file.xls", AddToMRU := true

Saves a lot of typing.

 

[Chip Pearson]

You could go through and re-enter

all the defaults for the other arguments like
Workbooks.Open("file.xls", false, false,....., true) and you
have to go
figure out what all needs to go there

Or you can omit the arguments altogether and use commas as place
holders. But in any case, named arguments are by far the best way
to go.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"TommySzalapski"
<[email protected]>
wrote in message

 

[Tom Ogilvy]

Not quite as bad as you make out

expression.Open(FileName, UpdateLinks, ReadOnly, Format, Password,
WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter,
Editable, Notify, Converter, AddToMRU)

You need to set AddToMRU to true. You could go through and re-enter

all the defaults for the other arguments like

or just
Workbooks.Open("file.xls",,,,,,,,,,,,true)

but using named arguments still saves some typing in many cases.