How to automatically input an array formula as string with more than 255 characters in length into an excel cell using VBA?

arrays excel vba

My codes work for non-array formulas, but when I try it as an array formula, it ends up pasting the whole line of code instead of the result it should give out. This is how it looks like:

enter image description here

My VBA code is as shown below (no need to read whole code):

Worksheets("DTR").Cells(Total_Rows_Formulas + 1, 20) = "{=IF(INDEX('Payroll Tables and Settings'!D$2:D$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))=""Extra"",P" & Total_Rows_Formulas + 1 & "*INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8,IF(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))>0,IF(AI" & Total_Rows_Formulas + 1 & "=""Sunday"",0,IF(OR(AF2>=24,AF2<=8)=TRUE," & _
        "(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/2)/(INDEX('Payroll Tables and Settings'!AC$2:AC$538,MATCH(1,IF(DTR!C" & Total_Rows_Formulas + 1 & ">='Payroll Tables and Settings'!Z$2:Z$538,IF(DTR!C" & Total_Rows_Formulas + 1 & "<='Payroll Tables and Settings'!AA$2:AA$538,1)),0))-INDEX('Payroll Tables and Settings'!AB$2:AB$538,MATCH(1,IF(DTR!C" & Total_Rows_Formulas + 1 & ">='Payroll Tables and Settings'!Z$2:Z$538,IF(DTR!C" & Total_Rows_Formulas + 1 & "<='Payroll Tables and Settings'!AA$2:AA$538,1)),0)))" & _
        "-IF(DTR!AI" & Total_Rows_Formulas + 1 & "=""Sunday"",0,IF(SUM(DTR!P" & Total_Rows_Formulas + 1 & ":S" & Total_Rows_Formulas + 1 & ")<8,IF(IFERROR(INDEX('Holidays Table'!B$2:B$1048576,MATCH(C" & Total_Rows_Formulas + 1 & ",'Holidays Table'!A$2:A$1048576,0)),0)=0,(8-SUM(DTR!P" & Total_Rows_Formulas + 1 & ":S" & Total_Rows_Formulas + 1 & "))*(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8),0),0))," & _
        "(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/2)/(INDEX('Payroll Tables and Settings'!AG$2:AG$538,MATCH(1,IF(DTR!C" & Total_Rows_Formulas + 1 & ">='Payroll Tables and Settings'!AD$2:AD$538,IF(DTR!C" & Total_Rows_Formulas + 1 & "<='Payroll Tables and Settings'!AE$2:AE$538,1)),0))-INDEX('Payroll Tables and Settings'!AF$2:AF$538,MATCH(1,IF(DTR!C" & Total_Rows_Formulas + 1 & ">='Payroll Tables and Settings'!AD$2:AD$538,IF(DTR!C" & Total_Rows_Formulas + 1 & "<='Payroll Tables and Settings'!AE$2:AE$538,1)),0)))" & _
        "-IF(DTR!AI" & Total_Rows_Formulas + 1 & "=""Sunday"",0,IF(SUM(DTR!P" & Total_Rows_Formulas + 1 & ":S" & Total_Rows_Formulas + 1 & ")<8,IF(IFERROR(INDEX('Holidays Table'!B$2:B$1048576,MATCH(C" & Total_Rows_Formulas + 1 & ",'Holidays Table'!A$2:A$1048576,0)),0)=0,(8-SUM(DTR!P" & Total_Rows_Formulas + 1 & ":S" & Total_Rows_Formulas + 1 & "))*(INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8),0),0)))),IF(Z" & Total_Rows_Formulas + 1 & ">0,0,IF(AI" & Total_Rows_Formulas + 1 & "=""Sunday"",0,P" & Total_Rows_Formulas + 1 & "*INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))/8))))}"

After trying Worksheets("DTR").Cells(Total_Rows_Formulas + 1, 20).FormulaArray = ...:

enter image description here

Latest Attempt:

With Worksheets("DTR").Cells(Total_Rows_Formulas + 1, 20)
        .FormulaArray = "=IF(INDEX('Payroll Tables and Settings'!D$2:D$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))=""Extra"",P2*INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))/8,2424)"
        .Replace "2424", "IF(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))>0, IF(AI2=""Sunday"",0, IF(OR(AF2>=24,AF2<=8)=TRUE,1111+2222+3333,b.)),4444+5555+6666)"
        .Replace "1111", "(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))/2)"
        .Replace "+2222", "/(INDEX('Payroll Tables and Settings'!AC$2:AC$538,MATCH(1,IF(DTR!C2>='Payroll Tables and Settings'!Z$2:Z$538,IF(DTR!C2<='Payroll Tables and Settings'!AA$2:AA$538,1)),0))+8888)"
        .Replace "+8888)", "-INDEX('Payroll Tables and Settings'!AB$2:AB$538,MATCH(1,IF(DTR!C2>='Payroll Tables and Settings'!Z$2:Z$538,IF(DTR!C2<='Payroll Tables and Settings'!AA$2:AA$538,1)),0)))"
        .Replace "+3333", "IF(SUM(DTR!P2:S2)<8,IF(IFERROR(INDEX('Holidays Table'!B$2:B$104857,MATCH(C2,'Holidays Table'!A$2:A$104857,0)),0)=0,(8-SUM(DTR!P2:S2))*(INDEX('Payroll Tables and Settings'!B$2:B$104857,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$104857,0))/8),0),0)"
        .Replace "4444", "(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))/2)"
        .Replace "+5555", "/(INDEX('Payroll Tables and Settings'!AG$2:AG$538,MATCH(1,IF(DTR!C2>='Payroll Tables and Settings'!AD$2:AD$538,IF(DTR!C2<='Payroll Tables and Settings'!AE$2:AE$538,1)),0))+1212)"
        .Replace "+1212)", "-INDEX('Payroll Tables and Settings'!AF$2:AF$538,MATCH(1,IF(DTR!C2>='Payroll Tables and Settings'!AD$2:AD$538,IF(DTR!C2<='Payroll Tables and Settings'!AE$2:AE$538,1)),0)))"
        .Replace "+6666", "IF(SUM(DTR!P2:S2)<8,IF(IFERROR(INDEX('Holidays Table'!B$2:B$104857,MATCH(C2,'Holidays Table'!A$2:A$104857,0)),0)=0,(8-SUM(DTR!P2:S2))*(INDEX('Payroll Tables and Settings'!B$2:B$104857,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$104857,0))/8),0),0)"
End With

Still does not work with the same run-time error as shown above

Working Code (thanks to Chris Mack):

With Worksheets("DTR").Cells(Total_Rows_Formulas + 1, 20)
        .FormulaArray = "=IF(INDEX('Payroll Tables and Settings'!D$2:D$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))=""Extra"",P2*7777/8,2424)"
        .Replace "7777", "INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))"
        .Replace "2424", "IF(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))>0, IF(AI2=""Sunday"",0, IF(OR(AF2>=24,AF2<=8)=TRUE,1111+2222+3333,4444+5555+6666)),3434)"
        .Replace "1111", "(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))/2)"
        .Replace "+2222", "/(INDEX('Payroll Tables and Settings'!AC$2:AC$538,MATCH(1,IF(DTR!C2>='Payroll Tables and Settings'!Z$2:Z$538,IF(DTR!C2<='Payroll Tables and Settings'!AA$2:AA$538,1)),0))+8888)"
        .Replace "+8888)", "-INDEX('Payroll Tables and Settings'!AB$2:AB$538,MATCH(1,IF(DTR!C2>='Payroll Tables and Settings'!Z$2:Z$538,IF(DTR!C2<='Payroll Tables and Settings'!AA$2:AA$538,1)),0)))"
        .Replace "+3333", "-IF(SUM(DTR!P2:S2)<8,IF(IFERROR(INDEX('Holidays Table'!B$2:B$104857,MATCH(C2,'Holidays Table'!A$2:A$104857,0)),0)=0,(8-SUM(DTR!P2:S2))*(INDEX('Payroll Tables and Settings'!B$2:B$104857,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$104857,0))/8),0),0)"
        .Replace "4444", "(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))/2)"
        .Replace "+5555", "/(INDEX('Payroll Tables and Settings'!AG$2:AG$538,MATCH(1,IF(DTR!C2>='Payroll Tables and Settings'!AD$2:AD$538,IF(DTR!C2<='Payroll Tables and Settings'!AE$2:AE$538,1)),0))+1212)"
        .Replace "+1212)", "-INDEX('Payroll Tables and Settings'!AF$2:AF$538,MATCH(1,IF(DTR!C2>='Payroll Tables and Settings'!AD$2:AD$538,IF(DTR!C2<='Payroll Tables and Settings'!AE$2:AE$538,1)),0)))"
        .Replace "+6666", "-IF(SUM(DTR!P2:S2)<8,IF(IFERROR(INDEX('Holidays Table'!B$2:B$104857,MATCH(C2,'Holidays Table'!A$2:A$104857,0)),0)=0,(8-SUM(DTR!P2:S2))*(INDEX('Payroll Tables and Settings'!B$2:B$104857,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$104857,0))/8),0),0)"
        .Replace "3434", "IF(Z2>0,0,IF(AI2=""Sunday"",0,P2*INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))/8))"
End With

Solution 1:

Try:

Worksheets("DTR").Cells(Total_Rows_Formulas + 1, 20).FormulaArray = ...

Update:

With regards to overcoming the limit when setting the FormulaArray property, one way to get around this is to split your formula into components and then use an alias to represent them. You can then replace the alias with the actual formula string.

Two rules:

  1. At each stage your formula must respect the syntax rules of Excel formulas.
  2. Each replacement string must be less than or equal to 255 characters in length.

(Update: Per https://colinlegg.wordpress.com/2012/05/23/working-with-range-formulaarray-in-vba/: "If you are using A1 notation then the R1C1 equivalent must be less than 255 characters.")

Good candidates for individual components are any sections of the formula that produce a value to be used against another value via an operator (e.g., addition, multiplication, greater than). So if you can get the initial formula you enter looking something like the following you'll be on the right track (you could even use 'Evaluate Formula' to get some ideas for this, as it will evaluate components within your formula and show you each step).

{=IF(FirstPart=SecondPart,ThirdPart*FourthPath,FifthPart)}

Again, remember that each of these parts must be less than or equal to 255 characters in length. If they aren't, it's usually the case that they can be broken down into further components.

Example:

Sub OvercomeFormulaArrayLimit()

    Selection.FormulaArray = "=SUM(IF(A2:A9=12,IF(B2:B9=23,C2:C9)))+XYZ"

    For r = 1 To 10

        Selection.Replace "XYZ", "SUM(IF(Sheet1!A2:A9=12,IF(Sheet1!B2:B9=23,Sheet1!C2:C9)))+XYZ"

    Next

    Selection.Replace "XYZ", "SUM(IF(Sheet1!A2:A9=12,IF(Sheet1!B2:B9=23,Sheet1!C2:C9)))"

End Sub

So in your code, an example would be:

With Worksheets("DTR").Cells(Total_Rows_Formulas + 1, 20)

    .FormulaArray = "=IF(replExtra=""Extra"" ..."
    .Replace "replExtra", "INDEX('Payroll Tables and Settings'!D$2:D$1048576,MATCH(DTR!B" & Total_Rows_Formulas + 1 & ",'Payroll Tables and Settings'!A$2:A$1048576,0))"
    'etc.

End With

A working solution, based on your code above:

With Worksheets("DTR").Cells(Total_Rows_Formulas + 1, 20)
        .FormulaArray = "=IF(INDEX('Payroll Tables and Settings'!D$2:D$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))=""Extra"",P2*7777/8,2424)"
        .Replace "7777", "INDEX('Payroll Tables and Settings'!B$2:B$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))"
        .Replace "2424", "IF(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))>0, IF(AI2=""Sunday"",0, IF(OR(AF2>=24,AF2<=8)=TRUE,1111+2222+3333,b.)),4444+5555+6666)"
        .Replace "1111", "(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))/2)"
        .Replace "+2222", "/(INDEX('Payroll Tables and Settings'!AC$2:AC$538,MATCH(1,IF(DTR!C2>='Payroll Tables and Settings'!Z$2:Z$538,IF(DTR!C2<='Payroll Tables and Settings'!AA$2:AA$538,1)),0))+8888)"
        .Replace "+8888)", "-INDEX('Payroll Tables and Settings'!AB$2:AB$538,MATCH(1,IF(DTR!C2>='Payroll Tables and Settings'!Z$2:Z$538,IF(DTR!C2<='Payroll Tables and Settings'!AA$2:AA$538,1)),0)))"
        .Replace "+3333", "IF(SUM(DTR!P2:S2)<8,IF(IFERROR(INDEX('Holidays Table'!B$2:B$104857,MATCH(C2,'Holidays Table'!A$2:A$104857,0)),0)=0,(8-SUM(DTR!P2:S2))*(INDEX('Payroll Tables and Settings'!B$2:B$104857,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$104857,0))/8),0),0)"
        .Replace "4444", "(INDEX('Payroll Tables and Settings'!F$2:F$1048576,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$1048576,0))/2)"
        .Replace "+5555", "/(INDEX('Payroll Tables and Settings'!AG$2:AG$538,MATCH(1,IF(DTR!C2>='Payroll Tables and Settings'!AD$2:AD$538,IF(DTR!C2<='Payroll Tables and Settings'!AE$2:AE$538,1)),0))+1212)"
        .Replace "+1212)", "-INDEX('Payroll Tables and Settings'!AF$2:AF$538,MATCH(1,IF(DTR!C2>='Payroll Tables and Settings'!AD$2:AD$538,IF(DTR!C2<='Payroll Tables and Settings'!AE$2:AE$538,1)),0)))"
        .Replace "+6666", "IF(SUM(DTR!P2:S2)<8,IF(IFERROR(INDEX('Holidays Table'!B$2:B$104857,MATCH(C2,'Holidays Table'!A$2:A$104857,0)),0)=0,(8-SUM(DTR!P2:S2))*(INDEX('Payroll Tables and Settings'!B$2:B$104857,MATCH(DTR!B2,'Payroll Tables and Settings'!A$2:A$104857,0))/8),0),0)"
End With

Related

Looping through jQuery Deferreds after all promises have been called
Specify other flags in awk script header
Why does Java throw NullPointerException here?
Is there a way with python-selenium to wait until all elements of a page has loaded?
Find local copy of files in snapshot view
Date in a URL dd/mm/yyyy
Upgrading git from the source repository in Ubuntu
read.csv doesn't seem to detect factors in R 4.0.0
Google Maps API v3 | shows no map data
How to print datagridview table with its header in vb.net?
One timer per table view cell
Powershell Join-Path showing 2 dirs in result instead of 1 - accidental script/function output