For I = LBound(X) + 1 To UBound(X)

Function MyMax(ParamArray X())

The function FirstVal identifies the data type of its argument and returns the first or only element in it. The code assumes that in the case of an array it is a single dimension array.

FirstVal = X

ElseIf InStr(1, TypeName(X(I)), "(") > 0 Then 'array

End Function

So far, we have worked with a range, a single dimension array, and individual elements passed to the ParamArray variable. These are the common data types encountered in the context of a user defined function or UDF. The other common data types that one may want to consider are a 2D array, a Collection object, and a Dictionary object. Each of them would require additional processing code. Dealing with these objects would have distracted from understanding the ParamArray variable. This section adds the code to process a 2D array.

Function processArray(X)

Theres one thing that the reader may have already noticed. Nowhere in the code is the value of MyMax initialized. The first reference to it is in a comparison operation! One way to initialize it is to set it to the smallest value possible. Then, the first comparison will adjust it correctly. Unfortunately, since the ParamArray variable can be any data type, we dont know what smallest value possible means. It could be a number. It could be a string. Or, it could be something else altogether!

Option Explicit

Dim J As Long

FirstVal = X.Cells(1).Value

Sub PA2(ParamArray Y())

The ParamArray argument

The ParamArray argument makes it possible for a procedure (a function or a subroutine) to accept an arbitrary number of arguments, each of a possibly different type. This enables the developer to write a function that will work with one or two or however many arguments that the user of the function passes to it. The function in Code Sample 1 has a single ParamArray argument. When used with a User Defined Function (UDF), it works as an Excel function.

If Rslt > MyMax Then MyMax = Rslt

So, in the above example, the first call to MyMax will have X as a one element array. That one element will contain an array, where the three elements of the array are the values 1, 2, and 3, respectively. Since X is an array and the first element of X is an array, 2 indexes are required to access each value, e.g., X(0)(0) see Figure 1, which shows the Visual Basic Editors Locals Window while executing code in the MyMax function.

End Sub

For J = LBound(X, 2) To UBound(X, 2)

process2DArr = Rslt

Dim I As Long

FirstVal = X(LBound(X)) 'assume 1D array

Code Sample 7

Option Explicit

Dim I As Long

For I = LBound(X) To UBound(X)

The issue arises when a subroutine, say PA1, with a ParamArray variable passes that variable to another subroutine, call it PA2, which, of course, has a ParamArray argument. Now, suppose that when PA1 gets called its ParamArray variable has several elements. When PA1 calls PA2, its variable (X) shows up in the 1st element of PA2s ParamArray variable (Y). So, PA2 has an additional level of nesting, as it were, to go through to get to the original arguments. Confused? An example should clarify the issue.

End Function

Dim Rslt

Rslt = processRange(X(I))

Case 2: processArray = process2DArr(X)

End Select

End Function

processArray = Rslt

If Rslt > MyMax Then MyMax = Rslt

NbrDim = DimIdx - 1

Dim DimIdx As Integer

'Function assumes X is a 1D array

For the third call, X will contain 2 elements. The first will be a range object, the second an array.

The second call will cause X to have three elements, each of which contains one of the numbers passed to the function.

Case 1: processArray = process1DArr(X)

End Function

Function MyMax(ParamArray X())

MyMax = X(I)

'X should be an array but cannot be declared as such

'X should be a range but cannot be declared as such

Function NbrDim(X)

For Each aCell In X.Cells

If TypeOf X Is Range Then

Process the Collection and the Dictionary object similar to a 1D array. Step through each element and recursively call the MyMax function with the element as the argument.

Code Sample 1

If aCell.Value > Rslt Then Rslt = aCell.Value

On Error GoTo XIT

Since each of the elements of X is of type variant, the functions that process those elements must declare their sole argument as a variant.

Rslt = processArray(X(I))

End Function

End Sub

If ThisRslt > Rslt Then Rslt = ThisRslt

For I = LBound(X) + 1 To UBound(X)

Debug.Print MyMax(11, 222, 3333)

Code Sample 3

Split("d,c,b,a", ",")

Function FirstVal(X)

Debug.Print MyMax(Range("A1:A10"), Array(10, 20, 40))

Figure 1

Next I

Select Case NbrDim(X)

Rslt = MyMax(X(LBound(X)))

For I = LBound(X) To UBound(X)

Case Else:

processRange = Rslt

MyMax = X(I)

'Function assumes X is a 1D array

Function MyMax(ParamArray X())

The processRange function steps through every cell in the range and returns the largest value. Even though we know that it will only process a range, we cannot declare the argument as type Range. This is because the type of the variable in the main routine is type variant.

PA1 ActiveCell.CurrentRegion, Array(1, 2, 3), _

End Sub

Next J

Download the example file

End If

A way to handle this uncertainty is to initialize MyMax to the value in the first element of the ParamArray variable. But, of course, the first element itself may be an object (such as a range) or a structured data type (such as an array). So, a simple MyMax=X(LBound(X)) is likely to fail. Just like the check for the type of each element before processing it (Code Sample 2), first check the type of the first element before processing it. Rather than add the code to the main routine, delegate the responsibility to a function and keep the main code simpler.

End Sub

Rslt = X.Cells(1)

'Function assumes X is a 2D array

Function MyLarge(X As Long, ParamArray Y())

End If

Since ParamArray allows an arbitrary number of arguments, it becomes impossible to combine it with optional arguments. The Visual Basic Editor will raise an error for an unacceptable use of a ParamArray variable.

Function process2DArr(X)

If ThisRslt > Rslt Then Rslt = ThisRslt

Dim Rslt

Sub callPA1()

Do

End Function

Rslt = MyMax(X(LBound(X)))

ElseIf X(I) > MyMax Then 'if an object use the default value, if any

If TypeOf X(I) Is Range Then

PA2 X()

End Function

Dim I As Long

Code Sample 8

The next routine, processArray, assumes that its argument is a 1D array. However, it doesnt actually process the elements of the array. Since each element of an array of type variant can itself contain an array or an object such as a Range, it calls MyMax recursively to process the array element.

Dim ThisRslt: ThisRslt = MyMax(X(I, J))

Code Sample 6

Code Sample 2

Rslt = MyMax(X(LBound(X), LBound(X, 2)))

Function processRange(X)

Figure 5

For I = LBound(X) To UBound(X)

Debug.Print MyMax(Split("d,c,b,a", ","))

End Function

Code Sample 4

Rslt = processArray(X(I))

Debug.Print MyMax(Array(1, 2, 3), 10, 11, 12)

'Function assumes X is a 1D or 2D array

Figure 2

For example, in the MyMax function, I opted to assume that an unknown data type will either have a default property or VBA itself will generate a fault. So, the code checks if the type of the element is a range or an array and calls the appropriate function. If it is neither, then it is either a simple data type (integer, long, single, double, Boolean, string) or an object other than a range or an array. For an object the default property, if one exists, will be used. If there is no default property, VBA will raise an error.

Next I

Figure 3

If ThisRslt > Rslt Then Rslt = ThisRslt

Next aCell

This is one place where the implementation of ParamArray feels a bit convoluted, though it is technically sound. It is also not a major issue since whenever a ParamArray variable element contains an array we will anyway recursively call the subroutine. Nonetheless, its something to be aware of.

Dim Rslt

When callPA1 calls PA1 with three arguments, each of the arguments becomes an element in the ParamArray X (Figure 4). So, in this subroutine, X(2) refers to the array result of the Split function. Thus, X(2)(0) is the first character or d.

XIT:

'X should be an array but cannot be declared as such

Dim Temp: Temp = UBound(X, DimIdx)

Else

The example used here is a function (named MyMax) that returns the maximum value found in the argument passed to it. As part of understanding the ParamArray argument, we will also fill out the MyMax function.

Debug.Print MyMax(Array(1, 2, 3))

ElseIf InStr(1, TypeName(X), "(") > 0 Then 'array

End Function

If Rslt > MyMax Then MyMax = Rslt

Figure 4

process1DArr = Rslt

Dim aCell As Range

Dim Rslt

Dim ThisRslt: ThisRslt = MyMax(X(I))

ElseIf X(I) > MyMax Then 'if an object use the default value, if any

In Code Sample 7 the subroutine PA1 declares its single argument as ParamArray. When called, it proceeds to call PA2, which also declares its single argument as a ParamArray.

Next I

Dim Rslt

Code Sample 9

End Function

Then, when PA1 calls PA2 it passes its ParamArray variable X to PA2s ParamArray Y variable. Since Y is a ParamArray variable, X all of X shows up in the first element of Y (Figure 5). Practically, this adds an additional nesting level to the variables originally passed by callPA1. So, Y(0) refers to the array X(). Then, Y(0)(0) refers to the first element of X and Y(0)(2) refers to the 3rd element of X, which is the array result of the Split function. Thus, Y(0)(2)(0) refers to d, the 1st element of this array.

Each argument passed by the caller maps to an element in the ParamArray variable. Consequently, the ParamArray variable will have as many elements as the arguments passed by the calling statement.

Dim I As Long

ElseIf InStr(1, TypeName(X(I)), "(") > 0 Then 'array

Dim ThisRslt: ThisRslt = MyMax(X(I))

Dim Rslt

Function process1DArr(X)

Sub testMyMax()

Sub PA1(ParamArray X())

Function processArray(X)

Next I

In VBA, a function with a ParamArray variable can be called as shown in Code Sample 1.

MyMax = FirstVal(X(LBound(X)))

Loop

Rslt = processRange(X(I))

To handle a 2D array, we modify the processArray function so that it calls one of 2 different functions, process1DArr or process2DArr depending on the kind of array being processed. Because the above is modularized, we only modify the processArray routine and dont really need to retest any of the other data type routines.

Code Sample 5

As we saw in the previous section the ParamArray variable contains 1 element for every argument actually passed to the function. So, to work with the ParamArray we have to process each element of the array. Since each element is of type variant, it becomes necessary to check the type of the element in it and process it appropriately. Obviously, if the data type is something the developer does not expect, one can code a default way of process the data type or one can return an error.

DimIdx = DimIdx + 1

End Function

If TypeOf X(I) Is Range Then

Option Explicit

In the example used in this note, the ParamArray variable was the only argument. That doesnt have to be the case. However, since the ParamArray accommodates an arbitrary number of arguments as defined by the caller, it must be the last of the arguments.

Dim I As Long

Next I

End If

If Rslt > MyMax Then MyMax = Rslt

Option Explicit