Array Formulas in Excel – Using VBA

Array Formula is generally used while working with Excel Worksheets – very handy when quick “Matrix Math” is required. For eg. cross products of columns/rows or multiple products across rows & columns with custom filters etc.

But if I wanted to automate this using VBA, how do I do it?

Range(“”).FormulaArray = <formula>

And we’re done!!!

So, what’s the limitation? <formula> cannot exceed 256 characters in length!

‘.FormulaArray’ only supports when the character length is under 256, an error throws up otherwise. Surprisingly, this is not the case with ‘.Formula’ – no such limit on this.

How do you know when this happens? You get a run-time error 1004, stating inability to accept Range. Check out Microsoft’s link to know more.

So, what’s the workaround? Or is there one?! Ofcourse! There always is one!

1. A potentially notorious workaround I should say – using the ‘Sendkeys’ from VBA to send a ‘F2+Shift+Ctrl+Enter’ after entering the ‘.FormulaArray’ as a ‘.Formula’!

Range(“”).Select

SendKeys “{F2}^+{ENTER}”, Wait:=True

Application.Wait (Now() + TimeValue(“00:00:01”)) ‘(Usually required since VBA doesn’t necessarily wait for the execution of the ‘SendKeys’ statement!)

Although slightly twisted, I liked this approach since it solves the problem quite easily. BUT, beware, this could create bugs in the code execution or final results in ways you can’t even think of! And this is primarily due to the disconnect between VBA & Excel interface!

2. This is probably a very well thought out solution and easier in terms of operational complexity but requires more work in terms of code. It’s best read directly at Dick Kusleika’s blog – http://www.dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/.

What this does is simply break the <formula> into multiple strings and submit the first ‘.FormulaArray‘ with the first segment with fillers that will be substituted the remaining bits of the broken formula! Ingenious!

And now you’re all set to be automating Array Formulas from VBA…

3 thoughts on “Array Formulas in Excel – Using VBA

  1. Nilesh

    Guys, I have used below function and it works perfect, however it takes a while to take effect(because of for loop)

    Function CreateArrayFunction(ByVal sRange As String, formula As String) As Boolean
    For Each oCell In Worksheets("sheetName").Range(sRange)
    oCell.FormulaArray = formula
    Next oCell
    End Function

    does anyone knows alternative to this?

    Thanks
    -N

    Reply
    1. Arun Post author

      Thanks Nilesh. This is very helpful stuff. I haven’t had the chance to look at this again, I’ll give this a try.

      Quick suggestion – You can make your function faster by:
      1. if you make it into a For loop with variables (For i) instead of Excel ranges (For each)
      2. Set ScreenUpdating to False

      Reply
    2. Arun Post author

      Oops! I just re-read my post! Lol..

      The FormulaArray works fine until you’re trying to make formulas beyond 256 characters in length – often complicated Array formulas using SUMIFS or DSUM etc.

      Alternatives I could find back in the day listed above.
      Hope this helps! Cheers.

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s