Excel vba formula string: array formula - how to simulate Ctrl+Shift+Enter press

Question

I have a worksheet with data and some columns which I fill with formulas via vba. The formula I struggle with is an array formula that looks like this:

Workbooks(job_file).Worksheets(1).Cells(h + b, 195).Formula = _
            "{=IF(MAX(IF(B2:M2>$FY" & currentRow & ",$B$1:$M$1))=0,0," & _
              "MAX(IF(sheet1!B2:M2>$FY" & currentRow & "," & _
                                "sheet1!$B$1:$M$1)))+1}"

It's supposed to be an array formula, so that's why I put {} there. However, when run it simply display the formula's text in a cell, without calculating it. I have to manually remove the brackets, and then press Ctrl+Shift+Enter myself.

Is there any way to avoid it? I have a great many rows and I can't ctrlshiftenter each.

I tried running it without brackets, it works, but gives a #VALUE! error, which can also be fixed by applying Ctrl+Shift+Enter.

Don't forget to mention the smaller character limit in your answer @ScottCraner. — user4039065
– user4039065, Commented Jul 11, 2018 at 14:41
Whenever you are wondering how to simulate any manual task in Excel, use the macro recorder and see what it makes. Then improve it a bit. — Vityata
– Vityata, Commented Jul 11, 2018 at 15:17
If you record a macro, you would see that the .FormulaArray gets formulas in R1C1 format only. Thus, you have to rewrite your code a bit. But the best case is to record it first. — Vityata
– Vityata, Commented Jul 11, 2018 at 15:21
@Vityata - good point about FormulaArray accepting both xlR1C1 and xlA1 style formulas. — user4039065
– user4039065, Commented Jul 11, 2018 at 15:36

score 4 · Accepted Answer · 2018-07-12 08:01:34Z

To create an array formula with a simulated ctrl+shift+enter (aka CSE), use the Range.FormulaArray Property instead of the Range.Formula Property and let Excel add the 'curly braces'.

with Workbooks(job_file).Worksheets(1)
    .Cells(h + b, 195).FormulaArray = _
        "=IF(MAX(IF(B2:M2>$FY" & currentRow & ",$B$1:$M$1))=0,0," & _
            "MAX(IF(sheet1!B2:M2>$FY" & currentRow & ", sheet1!$B$1:$M$1)))+1"
end with

I noticed in your formula that you use B2:M2 and sheet1!B2:M2. Shouldn't they both be sheet1!B2:M2?

There are some considerations.

Runtime Error: 1004 - Too long. There is a reduced character limit of 255 for FormulaArray but there are work-arounds.
Runtime Error: 1004 - Broken String. Remember that all quotes within a quoted string must be doubled up. This is easily one of the most common causes of errors when trying to write a formula into a cell through VBA. Hint: TEXT(,) can be used instead of "" so you don't have to type """" for a zero-length string.
FormulaArray accepts both xlR1C1 and xlA1 style formulas. If you can wrap your head around xlR1C1 style formula syntax, it is generally easier to construct a concatenated formula string in xlR1C1 since you can use digits to represent column numbers instead of trying to convert column ordinals to a column letter. However, do not try to mix-and-match xlA1 and xlR1C1 range references in the same formula; they must all be one style or the other.
If you are having trouble formulating a string that will be accepted as a formula, put a tick (e.g. ' ) in front of the first equals sign then run the code and return to the worksheet to see what was put in. Make modifications on the worksheet until you have a working formula then transfer those modifications to the VBA code.

tigeravatar · Accepted Answer · 2018-07-11 15:53:22Z

2

As a note, it looks like this can be done without an array formula, like so:

Workbooks(job_file).Worksheets(1).Cells(h + b, 195).Formula = _
        "=IF($FY" & currentRow & ">MAX(B2:M2),0," & _
         "MAX(INDEX((B2:M2>$FY" & currentRow & ")*$B$1:$M$1,)))+1"

answered Jul 11, 2018 at 15:53

tigeravatar

26.7k5 gold badges32 silver badges42 bronze badges

Collectives™ on Stack Overflow

Excel vba formula string: array formula - how to simulate Ctrl+Shift+Enter press

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related