working with arrays why the ":=" operator?

WilliamAshe · Sep 30, 2011

Sorry for the noob question. On Chip Pearson's page:
http://www.cpearson.com/excel/passingandreturningarrays.htm

when he passes an array to the next sub he uses ":=" instead of "=". Why?

Scott Huish · Sep 30, 2011

Because he is passing parameters to a function with an array.

shg · Sep 30, 2011

When you pass arguments (arrays or anything else) to a procedure, you can do so by position or name.

When you pass argments by position, the order must be the same as the arguments in the procedure's signature.

When you arguments by name, you can put them in any order you want, and the argument name is separated from the argument with :=

WilliamAshe · Oct 4, 2011

Thanks guys, Chip responded to my direct e-mail with:

*******
 William,

The use of the := operator is to support named arguments to a procedure. This allows you to specify the name of the parameter when assigning a value to the parameter. This is helpful in several ways. First, it is self-documenting. Consider the function:

Sub AAA(One As Long, Two As Long, _
Optional Three As Long, Optional Four As Long, Optional Five As Long)
' code
End Sub

If you omit the named arguments, you must pass them in positional order and include empty parameters for optional parameter if optional parameters are defined in the procedure. Without named arguments, you might call AAA with

Sub TestOne()
AAA 1, 2, , , 5
End Sub

Here, it is not clear which values belong with which parameters, and you have to count to columns to position a parameter after one or more optional parameters. In procedures that have many parameters, it can be difficult, time consuming, and bug-prone to call a procedure with positional parameters. You end up with code like

AAA 1,,,,,,,5,,,,,,7,,,,8

Quick, what parameter gets the value 5?

A much better way is to use named arguments. You could write the same function call as:

Sub TestTwo()
AAA One:=1, Two:=2, Five:=5
End Sub

Here, it is very clear what parameters values are being passed as arguments and clear that some arguments are optional.

Named arguments need not be in the same order as they appear in the called procedure. This is not a great convenience but it is possible.

For example,

AAA One:=1, Two:=2, Five:=5

Is the same as

AAA Two:=2, Five:=5, One:=1

I find that using the := named argument method well worth the effort just for the documentation even if there is only one parameter. It is a habit well worth adopting.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional (MVP)
Excel Product Group, 1998 - 2011
President,
Pearson Software Consulting, LLC
Senior Partner And Chief Of Software Design,
The San Diego Project Group, LLC

working with arrays why the ":=" operator?

WilliamAshe

New Member

Excel Facts

Scott Huish

MrExcel MVP

shg

MrExcel MVP

WilliamAshe

New Member

Similar threads

Forum statistics

Share this page

working with arrays why the ":=" operator?

WilliamAshe

New Member

Excel Facts

Scott Huish

MrExcel MVP

shg

MrExcel MVP

WilliamAshe

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock