0

I am working on a complex Excel-VBA project, and found that my Sub sometimes fails with Error Code 1004. I iterated through many experiments with the code and the input data and concluded finally that the error is caused by the length of the formula.

Here is a macro to prove that FormulaArray cannot be set to a string of arbitrary length (although Formula can be):

Public Sub FormulaArrayLengthTest()
    Dim rngTarget As Range: Set rngTarget = ActiveSheet.Range("B1")
    Dim i As Long: For i = 0 To 321
        Dim strFormula As String: strFormula = "=SUM(A1:A2" & String(i, " ") & ")"
        Dim lngLength As Long: lngLength = Len(strFormula)
        Dim blnSuccess As Boolean: blnSuccess = True
        On Error GoTo ErrorHandler
        ' rngTarget.Formula = strFormula
        rngTarget.CurrentArray.FormulaArray = strFormula ' Note: Cell contains an array formula initially
        On Error GoTo 0
        Debug.Print "Formula of length " & lngLength & " " & IIf(blnSuccess <> False, "succeeded", "failed")
    Next i
    Exit Sub
ErrorHandler:
    ' Err.Number=1004, Err.HelpContext=1001004, Source="Microsoft Office Excel", Description="Failed to set FormulaArray property of Range class"
    ' Note: If I enter the exact same formula that causes this error into the formula editor on the user interface, then it is accepted
    ' Test Environment: Windows 7 (6.1.7601), Microsoft Excel 2007 (12.0.6665.5003) SP3 MSO (12.0.6662.5000), VBA Version 1054 Retail 6.5.1054 Forms3: 12.0.6604.1000
    ' rngTarget.Formula = strFormula ' Succeeds at least until 4321
    ' rngTarget.CurrentArray.FormulaArray = strFormula ' Succeeds until 241, fails from 242
    blnSuccess = False
    Resume Next
End Sub

Did someone face this same problem? Does it exist in later versions of Excel? Do you know of a workaround?

I suspect that earlier versions of Excel had an upper limit on this, and while the UI access the latest COM interface to the Range object, the reference in VBA points to an older one. I hope by converting my Range variable to a newer version of the interface, the issue can be solved. Do you know anything about this?

z32a7ul
  • 3,695
  • 3
  • 21
  • 45
  • `FormulaArray` limits the characters to 255. There are many answers to how to get around it on this site and others. – Scott Craner Nov 04 '17 at 18:06
  • Also, as of Excel 2016, `Formula` is limited to 8'192 characters. – Excelosaurus Nov 04 '17 at 18:23
  • @ScottCraner, can you show me a link? – z32a7ul Nov 04 '17 at 19:00
  • 2
    That is [Google's job](https://www.google.com/search?q=workaround+formulaarray+limit&rlz=1C1CHBF_enUS715US715&oq=workaround+formulaarray+limit&aqs=chrome..69i57j69i59.5663j0j7&sourceid=chrome&ie=UTF-8) – Scott Craner Nov 04 '17 at 19:29
  • 1
    Possible duplicate of [Array formula with more than 255 characters](https://stackoverflow.com/questions/22949221/array-formula-with-more-than-255-characters) – Robin Mackenzie Nov 05 '17 at 02:27
  • @ScottCraner: Thanks for the googling. Unfortunately, I cannot apply any of the solutions described in the first ~20 links to my problem because I do not know the formula at compile time, only at run time. – z32a7ul Nov 05 '17 at 08:59
  • @RobinMackenzie: It is not a duplicate because I need a solution which works for any formula, I cannot hard-code it into my macro. – z32a7ul Nov 05 '17 at 09:01
  • You can try `worksheet().Evaluate`. – Scott Craner Nov 05 '17 at 14:55

0 Answers0