VBA: when to use :=

[imaguy77]

When do you use the := and what does it mean?

Also, if you have option explicit on and you want to dimension the following

dim cell as ___

for something like

for each cell in selection

what do you dim cell to be? variant? or anything more specific?


Thanks!

 

[jproffer]

I'm not sure what := means, but about the only place I've used it is in a Find command...something like:

With ActiveSheet.Range("A1:Z100")
   .Find("Hello", Lookin:=xlValues)
    '.....etc, etc
End WIth

But as far as WHY is has to be " := " and not just " = ", I don't know but I'd be interested to hear from someone who does.

Dim Cell as Range

 

[imaguy77]

I saw it in this video by Mr. Excel
http://www.youtube.com/user/bjele123#p/c/76C979CEFD1A4B30/7/cGsbbjt5pIM

the line is:

worksheets.add after:=worksheets(worksheets.count)

this line adds a worksheet after that last worksheet in the workbook.

 

[jproffer]

Oh yea, ok...I suppose I've seen it there too, lol.

I'm sure there are more types of command lines that would require the :=, but even still, I'm not sure what is special about those types of commands. I'm curious as well.

 

[bethg7]

The basic structure of VBA is

Object.Method

​

When you see the := (colon-equals), you know that you are looking at parameters of how the Object should be performed.
Example:

Ball.Kick Direction:=Left, Force:=Hard, Elevation:=High, etc

HTH
Beth

 

[imaguy77]

awesome. thanks for the very clear analogy.

 

[pgc01]

Hi

Some more comments about the assignment operator ":="

":=" is an assignement operator for parameters. You can use it with object methods but also with for ex. functions. It's optional if you respect the order of the parameters

It's used usually in 2 cases

1 - for documentation or readability of the code

ex.: These 2 statements are equivalent:

Msgbox "Hi!",vbYesNo,"Greeting"
 
MsgBox Prompt:="Hi!", Buttons:=vbYesNo, Title:="Greeting"

You can argue, however, that the second is more informative or makes the code easier to read and understand since you are seeing the names of the parameters.

2 - To use just some parameters and avoid listing empty parameters

Ex.:

The Application.Inputbox method is defined in the help as:

expression.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type)

If you want to select a range you can use:

Dim r As Range
Set r = Application.InputBox(Prompt:="Select range", Type:=8)

Just listing the first and last parameters.

You could also use

Dim r As Range
Set r = Application.InputBox("Select range", , , , , , , 8)

but it's not so easy to read.

Remark:

1 - If you use the := operator you can write the parameters in whatever order you like, ex.:

MsgBox Title:="Greeting", Buttons:=vbYesNo, Prompt:="Hi!"

2 - You can also use the := with just the parameters that do not follow the default order, like

Dim r As Range
Set r = Application.InputBox("Select range", Type:=8)

 

[imaguy77]

great, thanks for the response. much clearer now.

 

[Smitty]

A side note is not to use reserved words like "Cell" as a variable (or "Range", "Target", etc). "c" works fine, and won't cause you confilicting problems with VB when it compiles.

HTH,