I’ve talked about Array Formulas at a very nascent level in some of my earlier posts, nothing particularly in detail though! There are many sites for that, and that’s what you can find in my older post Array Formula in Excel – A Quickie.
Then what is this post all about?! Well, Array Formulae are generally used while working with Excel Worksheets – very handy when quick matrix math is required. But if I wanted to automate this using VBA, how do I do it?
Range(“”).FormulaArray = <formula>
And we’re done!!!
All’s well till your <formula> exceeds 256 characters!
‘.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…
