In Excel VBA, how do I write a function that can work in an array formula?

Question

In Excel VBA, I know I can use an array formula on multiple cells, by selecting a range of cells of Col C, and doing =A1:10*B1:B10 Ctrl-shift-enter.

But suppose instead of the multiplication operator, I want it to be my mymult.

How would I write the mymult function?

Function MyMult(a As Integer, b As Integer)
  MyMult = a * b
End Function

What I have isn't working

Your example is inadequate since the best way to fulfill the requirements would be to put =a1*b1 in C1 and then double-click the fill handle. — user4039065
Also why are a and b declared as Integer when you are expecting to use them as Range? — BruceWayne
@BruceWayne You ask if my expected result is that one number, the product of them all. No, it's not one number. I said this is an array formula on multiple cells. That's where you select multiple cells before doing ctrl-shift-enter — barlop

Florent B. Florent B. · Accepted Answer · 2017-09-10T15:14:05

Declare the arguments as variant. Then use Application.Caller.HasArray to check if the UDF is used as an array formula:

Public Function MyMult(a, b)
  If Application.Caller.HasArray Then
    Dim valsA(), valsB(), r&
    valsA = a.Value
    valsB = b.Value

    For r = LBound(valsA) To UBound(valsA)
      valsB(r, 1) = valsA(r, 1) * valsB(r, 1)
    Next

    MyMult = valsB
  Else
    MyMult = a * b
  End If
End Function

Note that you need to select C1:C3 before pressing CTRL + SHIFT + ENTER.

In Excel VBA, how do I write a function that can work in an array formula?

1 Answers