Set array formula

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,825
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I have manually created an array formula which returns the correct results.

I need to adjust the range, so recorded a macro.

When I tried to run the macro, I got an error:

Rich (BB code):
Unable to set the FormulaArray property of the Range class

Could it be the formula is too long and complicated for VBA?

This is the formula:

Rich (BB code):
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Selection.FormulaArray = _
        "=IFERROR(IF(RC[-27]=""London"",MATCH(1,(VALUE(RIGHT(LondonData!R8C3:R286C3,9))=RC[-22])*(Start!RC[-2]=LondonData!R8C8:R286C8)*(Start!RC[-26]>LondonData!R8C1:R286C1),0),MATCH(1,(RC[-22]=Paris!R8C3:R29553C3)*(Start!RC[-2]=Paris!R8C4:R29553C4)*(Start!RC[-26]>Paris!R8C1:R29553C1),0)),""Missing"")"


[/FONT]
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
You can’t enter an array formula longer than 255 characters via VBA in a single go. At http://www.dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/, **** Kusleika shows how a formula might be divided it into parts and entered piecemeal, entering the first part, and then the balance via substitution. Here's the example:

Code:
Public Sub LongArrayFormula()
  Dim theFormulaPart1 As String
  Dim theFormulaPart2 As String
  theFormulaPart1 = "=IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))-" & _
             "MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1)-" & _
             "(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+" & _
             "{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),""""," & _
             "X_X_X())"
 
  theFormulaPart2 = "DATE(YEAR(NOW()),MONTH(NOW()),1)-" & _
           "(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+" & _
           "{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)"
 
  With ActiveSheet.Range("E2:K7")
    .FormulaArray = theFormulaPart1
    .Replace "X_X_X())", theFormulaPart2
    .NumberFormat = "mmm dd"
  End With
End Sub

The problem is that the formula must be syntactically correct both initially and following each substitution. Here’s an alternative using SendKeys. The last argument in the function call must agree with the reference style of the formula passed to the function.
Code:
Sub demo()
  Const sFrm   As String = _
     "=IF(AND(RC[-12]:RC[-1]=""""),""No RoB assessments performed""," & vbLf & _
     "IF(OR(CHOOSE({1,2,3,4,5}, RC[-10], RC[-8],RC[-6], RC[-4], RC[-2])=""no""),""HIGH""," & vbLf & _
     "IF(OR(CHOOSE({1,2,3,4,5}, RC[-10], RC[-8],RC[-6], RC[-4], RC[-2])=""High Risk""),""HIGH""," & vbLf & _
     "IF(OR(CHOOSE({1,2,3,4,5}, RC[-10], RC[-8],RC[-6], RC[-4], RC[-2])=""UNCLEAR""),""UNCLEAR""," & vbLf & _
     "IF(OR(CHOOSE({1,2,3,4,5}, RC[-10], RC[-8],RC[-6], RC[-4], RC[-2])=""Unclear Risk""),""UNCLEAR"",""LOW"")))))"

  Debug.Print InsertArrayFormula(Range("A1"), sFrm, xlR1C1)
End Sub

Function InsertArrayFormula(r As Range, _
                            ByVal sFrm As String, _
                            iRef As XlReferenceStyle, _
                            Optional ByVal sFmt As String = "") As Boolean
  ' shg 2009, 2012, 2016

  ' Inserts array formula sFrm into (the first area of) range r.
  ' iRef specifies whether sFrm contains an R1C1- or A1-style formula
  ' sFmt contains an optional format for r. If omitted, the format is unchanged

  ' >>>> NB: The VBE CANNOT have focus when this runs, <<<<
  ' >>>> or you'll end up in the Object Browser!       <<<<

  Dim iRefSav       As XlReferenceStyle   ' current ref style
  Dim rSel          As Range              ' current selection

  If r.Worksheet.ProtectContents Then Exit Function

  Set rSel = ActiveWindow.RangeSelection

  With Application
    iRefSav = .ReferenceStyle
    .ReferenceStyle = iRef
    If iRef = xlA1 Then sFrm = Application.ConvertFormula(Formula:=sFrm, _
                                                          FromReferenceStyle:=xlA1, _
                                                          ToReferenceStyle:=xlR1C1, _
                                                          RelativeTo:=r.Cells(1))

    On Error GoTo Oops
    .ScreenUpdating = False

    With r.Areas(1)
      ' Can't put an array formula in cells that are
      ' not either all locked or all unlocked, so ...
      .Locked = .Cells(1).Locked

      ' Cache the number format, set to text, insert formula, restore format
      If Len(sFmt) = 0 Then sFmt = .NumberFormat
      .NumberFormat = "@"
      .Value = sFrm
      .NumberFormat = sFmt

      Application.Goto .Cells
    End With

    DoEvents
    .SendKeys "{F2}^+~"
    DoEvents
    r.EntireRow.AutoFit
    .Goto rSel
    InsertArrayFormula = True

Outtahere:
    .ReferenceStyle = iRefSav
    .ScreenUpdating = True
    Exit Function
  End With

Oops:
  Resume Outtahere
End Function

The tricky part about the routine above is that the VBE window cannot have focus when it runs, otherwise you end up in the Object Browser.

SendKeys is widely regarded as unreliable—I’ve never had a problem with it, but use it so infrequently that that doesn't say much.
 
Upvote 0
Hi,

You could test Evaluate() ...

Hope this will help
 
Upvote 0
Thanks for the suggestions.

I'll try after the weekend and get back with my findings.
 
Upvote 0
I have decided to break down my long array formula into several columns to ease comprehension and also make it VBA compliant.

Thanks to all for your helpful suggestions.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top