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

by

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

More Related Contents:

  1. Excel-VBA – Insert new first column in datafield array without loops or API calls
  2. Creating an Array from a Range in VBA
  3. Expanding column cells for each column cell
  4. How do I slice an array in Excel VBA?
  5. Check if a value is in an array or not with Excel VBA
  6. Matching values in string array
  7. Deleting Elements in an Array if Element is a Certain value VBA
  8. Is it possible to fill an array with row numbers which match a certain criteria without looping?
  9. Return Index of an Element in an Array Excel VBA
  10. Writing an array to a range. Only getting first value of array
  11. Excel VBA: Range to String Array in 1 step
  12. Faster way of using Sumifs()
  13. How to initialize a multidimensional array variable in vba for excel
  14. VBA using ubound on a multidimensional array
  15. Turn Excel range into VBA string
  16. Get values from union of non-contiguous ranges into array with VBA with a simple command (no loops)
  17. Why am I having issues assigning a Range to an Array of Variants
  18. Subscript out of range error with an array – no idea why?
  19. VBA Excel large data manipulation taking forever
  20. Performance difference between looping range vs looping array
  21. Create Chart from Array data and not range
  22. Excel VBA Quickest way to sort an array of numbers in descending order?
  23. Simple VBA array join not working
  24. Excel VBA – How to Redim a 2D array?
  25. ReDim Preserve “Subscript Out of Range”
  26. Excel – Extract substring(s) from string using FILTERXML
  27. Can Excel’s INDEX function return array?
  28. How Do I Make FLATTEN Work for Non-Contiguous Ranges?
  29. Dynamically Dimensioning A VBA Array?
  30. VBA – Returning array from Property Get

Leave a Comment