Make UDFs Dynamic Array-able

Retrofit User Defined Functions (UDFs) to be Dynamic Array (DA) Aware

I recently wrote a UDF. I had not created one since DAs came out. And since DAs were out, I thought it only appropriate to make my UDF DA aware. As I added DA code, I realized it would be simple to isolate that section so I could paste it into any existing single-parameter UDF making it accept DAs in its parameter and, if appropriate, produce DAs as its result. Here is the basic strategy:

1. Declare the parameter as a Variant so it can accept anything.

2. Declare the UDF as a Variant so it can produce anything

3. Declare two variables as Variants: one to hold the parameter's array, and one to hold the resulting array

4. Add a block of code to process the array when the parameter contains one, else run the original UDF code

The Code
To explain this strategy I'm using UDF Amt2Wrd(). When using this technique for your UDFs, insert the code shown in the next sections and be sure to change Amt2Wrd to your UDF's name.

Step 1 and 2. To convert Amt2Wrd I must declare the parameter (vAmount) as a Variant and to declare the function as a Variant as well. This allows our UDF to receive anything, including DAs, as input and produce single values or an array of values.

Public Function Amt2Wrd(ByVal vAmount As Variant) As Variant

Step 3. We then must add four variables to process arrays when present. I prefer to declare all variables together at the top of my routines.

' Dynamic Array Portion

Dim lRow As Long 'Array Row #

Dim lCol As Long 'Array Column #

Dim vValues As Variant 'Input Array

Dim vResult As Variant 'Result Array

Step 4. Lastly, we need to add a block of code before our UDF's procedural code to process arrays when present in the parameter. Here is the block of code. Below is this block broken down into sections to make the explanation clearer.

' Dynamic Array Processing

If IsArray(vAmount) Then

vValues = vAmount

ReDim vResult(LBound(vValues, 1) To UBound(vValues, 1), _

LBound(vValues, 2) To UBound(vValues, 2))

For lRow = LBound(vValues, 1) To UBound(vValues, 1)

For lCol = LBound(vValues, 2) To UBound(vValues, 2)

vResult(lRow, lCol) = Amt2Wrd(vValues(lRow, lCol))

Next

Next

Amt2Wrd = vResult

Else

Step 4a. We only want this section to run when our parameter contains an array

' Dynamic Array Processing

If IsArray(vAmount) Then

Step 4b. Place the array into our input array: vValues()

vValues = vAmount

Step 4c. Now we need to make our result array (vResult) the same size as our input array (vValues). We could just infer vResult's size by setting it equal to vValues. While that works, it feels a bit wonky to me because it puts values into vResult that I don't want and will replace. Besides, this is a teaching moment and this shows beginners the textbook way to re-dimension an array.

ReDim vResult(LBound(vValues, 1) To UBound(vValues, 1), _

LBound(vValues, 2) To UBound(vValues, 2))

Step 4d. It's time to loop through the array. With each loop we call our routine (Amt2Wrd) passing it a single value from our Inputs array. Because we are passing a single value, our array processing block will not run and our original UDF's code will run. When our routine returns, we place the result into our results array (vResult).

For lRow = LBound(vValues, 1) To UBound(vValues, 1)

For lCol = LBound(vValues, 2) To UBound(vValues, 2)

vResult(lRow, lCol) = Amt2Wrd(vValues(lRow, lCol))

Next

Next

Step 4e. After looping through the entire input array, we put the entire results array into our function and that is what our UDF will return - an array!

Amt2Wrd = vResult

Else

NOTE! At the end of our array processing block is an Else statement. After the Else is where our original UDF's code goes so be sure to place an End If at the very end of the original code.

That's it! Using this technique we can easily retrofit our single-parameter UDFs to work with and produce dynamic arrays.

To see the completed code, click Amt2Wrd()