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
– user4039065, Commented Sep 10, 2017 at 14:43
Also why are a and b declared as Integer when you are expecting to use them as Range? — BruceWayne
– BruceWayne, Commented Sep 10, 2017 at 15:22
@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
– barlop, Commented Sep 10, 2017 at 20:08

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

5

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.

answered Sep 10, 2017 at 15:14

Florent B.

42.7k7 gold badges92 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

BruceWayne Over a year ago

Oh cool, I never knew of the HasArray thing. Nice thinking, I'll have to remember that.

barlop Over a year ago

Is there any way for an array formula to be aware of and respond to the specific row? So for example, for the multiply function to not just multiply them but to append to the result based on what row it is e.g. i.imgur.com/cITiW6Q.png

Florent B. Over a year ago

An array formula processes all the rows in a single call and returns the results in an array. So yes it's possible. In this example r is the index of the row.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related