Excel VBA: Passing a calculation result array as an argument to a function

If you're calling your function from another VBA subroutine or function, Excel will not interpret the range calculation for you. You have to perform any data manipulations over the range area yourself before calling your function.

But as long as you're using the function as a UDF from a worksheet cell, Excel will interpret the range parameters as you describe them. The caveat when applying any calculations to the range is you must "tag" the cell formula to excel as an array formula by typing with control-shift-enter to complete the cell formula.

Consider this setup:

Option Explicit

Public Function CalculateThis(inRange) As Double
    Dim answer As Double
    answer = 0#
    CalculateThis = answer
End Function

The range parameter A1:E1 comes in as expected, a Variant/Object/Range. Use the View-->Locals window in the VBA Editor to take a look.

Now, if you wanted to apply a calculation to the range before it gets to your function, you still can.

Consider the formula =CalculateThis(A1:E1^2). What you expect is an array of five values, each value squared. But if you enter this formula by typing a simple enter, what you get is a single value instead of an array. (Bonus points to someone who can explain why the value is 9.)

But enter the same formula with control-shift-enter and you get the expected array of five values, with each value squared.

You have to check for some error conditions if your range and array calculation don't quite match up. For example =CalculateThis(A1:E1^{1,2,3,4,5}) (entered with control-shift-enter) gets you the expected:

But =CalculateThis(A1:E1^{1,2,3}) does not:

(The errors in the variant array are strings "Error 2042" that you can check for.)