VBA array syntax

Question

Looking over vba arrays and stumbled upon something and need someone to clear it up.

Sub AAATest()
Dim StaticArray(1 To 3) As Long
Dim N As Long
StaticArray(1) = 1
StaticArray(2) = 2
StaticArray(3) = 3
PopulatePassedArray Arr:=StaticArray
For N = LBound(StaticArray) To UBound(StaticArray)
    Debug.Print StaticArray(N)
Next N
End Sub

AND

Sub PopulatePassedArray(ByRef Arr() As Long)
''''''''''''''''''''''''''''''''''''
' PopulatePassedArray
' This puts some values in Arr.
''''''''''''''''''''''''''''''''''''
Dim N As Long
For N = LBound(Arr) To UBound(Arr)
    Arr(N) = N * 10
Next N
End Sub

What's happening at

PopulatePassedArray Arr:=StaticArray

in AAATest sub

First AAAtest is filling an array with three values 1,2,3. Then it is calling the sub PopulatePassedArray and passing the staticarray with its values. The sub is then replacing the 1,2,3 with 10,20,30. The original sub is then printing those values in the immediate window. Is this what you mean? — Scott Craner
– Scott Craner, Commented Oct 29, 2015 at 15:48
You are calling the procedure PopulatePassedArray with the named argument Arr set to StaticArray. — Vincent G
– Vincent G, Commented Oct 29, 2015 at 15:49
See: Visual Basic Concepts - Passing Arguments to Procedures. — agold
– agold, Commented Oct 29, 2015 at 15:50
Ar you asking about the use of a named argument Arr:= instead of just supplying the arguments in order? — Dick Kusleika
– Dick Kusleika, Commented Oct 29, 2015 at 18:31
@DickKusleika yes, the symbol ":=" but after a couple of practices, I've concluded that it copies EVERY value from one array to the other. In this case: all of Arr's values are copied into StaticArray — Kenny Nguyen
– Kenny Nguyen, Commented Oct 29, 2015 at 19:16

Dick Kusleika · Accepted Answer · 2015-10-29 20:53:48Z

There are two ways you can pass arguments to another procedure: using named arguments or in order. When you pass them in order, you must past them in the same order as the procedure definition.

Function DoTheThing(arg1 As Double, arg2 As String, arg3 As Boolean) As Double

When you call this function (in order), you call it like

x = DoTheThing(.01, "SomeString", TRUE)

When you call the function using named arguments, you use :=, the name of the argument, and the value of the argument. The := is not a special assignment operator - well I guess it kind of is. The upshot is that when you use named arguments, you can supply them in any order.

x = DoTheThing(arg2:="SomeString", arg3:=TRUE, arg1:=.01)

Some people also think that named arguments make your code more readable. I'm not one of those people. It clutters it up and if you're passing more than two or three arguments, you're doing it wrong anyway.

Collectives™ on Stack Overflow

VBA array syntax

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related