Entering Long Array Formulas in VBA

Use the FormulaArray property of the Range object to enter array formulas in a cell from VBA. The array formula must be 255 characters or less or you’ll see the ever-helpful “Unable to set the FormulaArray property of the Range class” error.

Dave sent me a solution to this problem that’s shown below. It replaces part of the formula with a place holder, then uses the Replace method of the Range object to add the rest. Apparently Replace doesn’t have such limitations as FormulaArray does.

By the way, he uses JWalk’s cool calendar formula (as seen in the Ugly Formulas comments) as his example. I love that formula, although that doesn’t really have anything to do with this post.

”’ If the long formula can be broken into parts, where the second part can
”’ be replaced by a dummy function, this approach can be used…
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

Nice work, Dave. Thanks for the suggestion.

60 Comments

  1. David Wasserman says:

    While I consider myself fairly proficient at Excel in general, I get all weak in the knees when confronted with Array Formulas. Is there anywhere I can find a good tutorial dealing exclusively with creating and using Array Formulas?

    Thank you.

    David

  2. gokhan says:

    I was wondering whether you guys can help me out with this one

    setup: win2000, excel 2000
    problem:

    i have a long array formula

    X_TABLE_FORMULA = “=SUM(IF(B$1=HISTORIC_DATA!$E$5:$E$1130;IF($A2=HISTORIC_DATA!$C$5:$C$1130;HISTORIC_DATA!$H$5:$H$1130;0)))/SUM(IF(B$1=HISTORIC_DATA!$E$5:$E$1130;IF($A2=HISTORIC_DATA!$C$5:$C$1130;HISTORIC_DATA!$B$5:$B$1130;0)))”

    of course entering it via:

    Selection.FormulaArray = X_TABLE_FORMULA

    returns the well known error message.
    however if i split it down, e.g.

    X_TABLE_FORMULA_1 = “=IF(B$1=HISTORIC_DATA!$E$5:$E$1130;”"YO”";”"NO”")”

    returns this error. you guys have any idea?
    any comment, suggestion is highly appreciated.

  3. jkpieterse says:

    I guess replacing the semicolons with comma’s should help fix this.

  4. Paul says:

    Has anyone got a technique for doing this with chart series formulas? I’m trying to delink a chart’s data which I can do by hand because of the 1024 formula limit, but from VBA I’m restricted to 255.

    I can’t use the Replace method as the ChartSeries.Formula property is not a Range object. Any ideas would be really useful, as I’m stumped…

  5. Jon Peltier says:

    Paul -

    Please pardon me for plugging my own site. I demonstrate the use of WorksheetFunction.Substitute to change the chart series formula on this page:

    http://peltiertech.com/Excel/Charts/ChgSrsFmla.html

    I also discuss the ins and outs of delinking chart series here:

    http://peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html

    - Jon

  6. Paul says:

    Hi Jon,

    Thanks for your reply. I had actually stumbled acorss your site whilst searching for an answer to this question.

    Unfortunately your solution doesn’t work. The problem is that setting the formula property on a chart series with anything over 255 characters causes a failure. WorksheetFunction.Substitute just returns a string and so the problem still exists. The only way I’ve find to do it, is to use the technique that involves saving your chart in the user gallery, and then copying the chart from there.

    Why does Excel (I’m using 97) have a 1024 character limit when a user types a formula in, and a 255 one from within VBA? It seems to be a silly limitation.

  7. Jon Peltier says:

    The stated limit of 1024 characters for the series formula is misleading, because each component of the formula (name, xvalues, values, and plot order) behaves as if it has a limit of around 255. The practical limit is therefore much lower than 1024.

    Are you trying to use assign a VBA array to a series, or is there a reason you don’t want data in the worksheet? If it’s the former, dump the data into worksheet ranges, and point the series formula at these ranges.

  8. Paul says:

    Thanks for the speedy reply.

    I’m just trying to delink the data as the chart is created in another workbook. There’s definitely some discrepancy between manually entering a formula and assigning it in VBA as I can hit F9 in the formula bar and it works fine, but by using the same formula from within VBA (or assigning an array, etc) it fails. I’ve also ensured that each section of the formula is less than 255 characters but setting the formula property still fails.

    If possible, I’d rather not have the raw data stored anywhere apart from in the chart. I realise that I can store the data in another (possibly hidden) sheet, but it’s just frustrating when I have this much trouble automating something I can do by hand.

  9. Jon Peltier says:

    You can F9 the series formula, and it /might/ work (I just ran off a quick example, and it didn’t), but as soon as you touch the formula again, you’ll be swarmed with errors.

    The ultimate delink is copy picture.

  10. Jonathan says:

    Maybe someone stumbles over this post -
    even if its way in the past…

    I’ve got a problem:
    I need to replace the current year in formulas of many sheets, those formulas are right next to the limit of 1024, as the year is contained in some network-file-name from which to include data;

    So i went about trying to cope with those long strings -
    as an automatic replace won’t work (“Formula too long”).

    In test i’m able to write a formula which is exactly 1024 characters long into a Cell (from VBA),

    BUT – herein lies my real problem – i cannot read it in again from VBA;

    e.g.
    Range(“B5?).Formula = str ‘works, with str containin 1024 long formula

    str2 = Range(“B5?).Formula ‘fails with 1004 – application or objectdefined error

    Anyone got an idea for me?
    -Jonathan

  11. Jonathan says:

    BTW: reading the formula stops working when setting a formula of length greater than 1019 – 1020 cannot be read….

  12. Stephanie says:

    I am receiving the Excel message “Formula is too long” – is this formula limitation documented anywhere? How did you come to find that to be the limitation?

  13. Dick,

    I hit the same problem with array formulae, and have just tried the “replace” trick. It doesn’t work for me, though (on Office Excel 2003). To test it, I put into the range a very simple formula that is just “X_X_X()”, and then replace that with a long formula that I’ve built in a loop, in variable s. But the replace fails with a “type mismatch” error when s becomes 256 or so long. Here’s the code. Any solutions would be very welcome.

    Sub test()
    Dim s As String
    Dim r As Range

    s = “0?
    For i = 1 To 88
    s = s & “+” & i
    Next i
    MsgBox Len(s)
    Set r = Range(“a1:a10?)

    r.FormulaArray = “=X_X_X()”
    r.Replace “X_X_X()”, s
    End Sub

  14. Jocelyn,

    this works
    Sub test()
    Dim s As String
    Dim r As Range

    s = “0?
    For i = 1 To 86
    s = s & “+” & i
    Next i
    MsgBox Len(s)
    Set r = Range(“a1:a10?)

    r.FormulaArray = “=fred1+fred2?
    r.Replace “fred1?, s
    r.Replace “fred2?, s
    End Sub

    Another useful trick is to rename all the worksheets to very short names before inserting the array formulae, then rename them back afterwards.

    You should also be aware that the 256 character transfer limits are not the same in different versions of excel.

  15. Chuck Grob says:

    Using XCL 2003 VBA, I create strings of dates that are pipe-delimited and paste each of them into one cell (my application requires this). The date format is mm/dd/yyyy. Since the number of dates is a variable and it may be up to 30 or 35 dates, my strings that exceed 255 characters are being truncated. Is there hope for pasting all of my complete strings in my worksheet?

    Thanks for a response.

  16. jkpieterse says:

    Chuck,

    As long as you use the Value property to write the data to the cells, you should be fine and able to write up to 32,767 charaters worth of dates into a cell.

  17. Helen says:

    Please help. I’ve tried the same solution but it did not work for me. The X_X_X()) never get replaced. Is there any other way for me to do this.

    Thanks

    ‘Update for sequence array: T120-T136

    theFormulaPart1 = “=IF(ISERROR(INDEX(SGData!$A$1:$L$” & SGDataLastRow _
    & “,SMALL(IF(SGData!$A$1:$A$” & SGDataLastRow _
    & “=F120:G120,ROW(SGData!$A$1:$A$” & SGDataLastRow _
    & “)),ROW(SGData!$1:$” & SGDataLastRow & “)),9)),”"”",X_X_X())”

    theFormulaPart2 = “INDEX(SGData!$A$1:$L$” & SGDataLastRow _
    & “,SMALL(IF(SGData!$A$1:$A$” & SGDataLastRow _
    & “=F120:G120,ROW(SGData!$A$1:$A$” & SGDataLastRow _
    & “)),ROW(SGData!$1:$” & SGDataLastRow & “)),9)”

    FormulaString = theFormulaPart1 + theFormulaPart2

    With ActiveSheet.Range(“T120:T136?)
    .FormulaArray = theFormulaPart1
    .Replace “X_X_X())”, theFormulaPart2
    End With

  18. jkpieterse says:

    I think you must change:

    .Replace “X_X_X())”, theFormulaPart2

    To

    .Replace “X_X_X())”, FormulaString

  19. Piyush says:

    I get the same error but my ArrayFormula is only 121 characters long.
    My formula is:

    =SUM(IF(FREQUENCY(IF(LEN(B4:B150)>0,MATCH(B4:B150,B4:B150,0),”"), IF(LEN(B4:B150)>0,MATCH(B4:B150,B4:B150,0),”"))>0,1))

    It finds the total no of unique values in a range of duplicating values.

  20. RUSSOOL says:

    I have been trying to spread the formula below in 2 parts. I have not been able to spread it !!

    “=SUM(IF(‘Jalons et Niv Serv’!$F$1:$F$1000 = $G” & i & “,IF(‘Jalons et Niv Serv’!$Q$1:$Q$1000 “”"”,IF(‘Jalons et Niv Serv’!$U$1:$U$1000 = “”X”" ,IF(‘Jalons et Niv Serv’!$Q$1:$Q$1000 Paramètres!$G$7,IF(‘Jalons et Niv Serv’!$Q$1:$Q$1000 Paramètres!$G$8,IF(‘Jalons et Niv Serv’!$Q$1:$Q$1000 Paramètres!$G$9,IF(‘Jalons et Niv Serv’!$Q$1:$Q$1000 Paramètres!$G$11,1*(‘Jalons et Niv Serv’!$Q$1:$Q$1000),0),0),0),0),0),0),0))”

    Could someone please help me.

    Thanks a lot

  21. Max Mulawa says:

    Hi Dick,

    I was wondering if you tried changing ‘Regional and Language Options’->’Standards and Formats’ on your windows installation to let say German (Switzerland).
    In my case this resulted in ‘funny’ situation when calls to Replace function were just ignored.

    After doing some debugging I’ve come up with rather obscure solution:

    With ActiveSheet.Range(“E2:K7?)
    .FormulaArray = theFormulaPart1
    if .FormulaLocal.IndexOf(“;”) > 0 then
    theFormulaPart2 = theFormulaPart2.Replace(“,”,”;”)
    end if
    .Replace “X_X_X())”, theFormulaPart2
    .NumberFormat = “mmm dd”
    End With

    So, when using Replace function the 2 other formula parts must be changed from commas (,) to semicolons (;) before being passed to Replace function.
    If comma=>semicolon transformation wouldn’t be applied, the Replace function would ignore both operations as they would violate the FormulaArray.

    The code is far from being perfect, but it works at least for the SWISS ;)

  22. jkpieterse says:

    If you build the partial formulas by using FormulaLocal (which is Localised) iso FormulaArray (which is US) does it work as expected?

  23. Max Mulawa says:

    Hmm, if in first place I would assign part 1 of the formula array to FormulaLocal, and execute Replace function i’m not getting FormulaArray at the end.

    It seems that Range.Replace function modifies FormulaLocal content rather than FormulaArray.

  24. jkpieterse says:

    Correct, the replace method of a range object works on the Localised formulas. Hence my remark.

  25. Here is what I’ve found:
    When you try to set the formula (the formulaArray, formula, formulalocal, etc.) via any method, the formula you use must be parsable. It does not need to value, it just needs to be syntactically correct so that Excel can parse it. Then, it seems, each part of the parse tree must be less than 256 characters. Thus, any quoted strings or token names in an expression must be less than 256 characters, though the whole expression can be more than 256 characters. If you try rng.replace(token,replacementString), the replacement string must be less than 256 characters. Repeated application of rng.replace can be used to build up a large formula, but each replacement must have a replacement string of less than 256 characters. Furthermore, the limit of 255 characters on tokens in the parsed formula prevent you from using this strategy:
    rng.arrayFormula = “=”"Function(param1,param2,zzzzz”"”
    rng.replace “zzzzz”, “param3,param4,zzzzz”
    rng.replace “zzzzz”, “param5,param6,zzzzz”

    rng.replace “zzzzz”, “paramN,paramM)”
    rng.replace “”"”, “”
    This would work beautifully (and be a general, generic work-around, so long as the formula has no quotes in it), except that as soon as the string in the formula gets to be >255 characters, the replace function ceases working. One can do this:
    rng.arrayFormula = “=”"Function(param1,param2,zzzzz”"”
    rng.replace “zzzzz”"”, “”"&”"param3,param4,zzzzz”
    rng.replace “zzzzz”"”, “”"&”"param5,param6,zzzzz”

    rng.replace “zzzzz”"”, “”"&”"paramN,paramM)”
    rng.replace “”"&”"”, “”
    rng.replace “”"”, “”

    but the second-last replacement fails. If I could think of a single replacement command that would strip out the ‘”&”‘ and the leading ” and trailing ” all in one replacement, then this would work as a general solution.

  26. Mala says:

    What if we needed to to the long formula trick with FormulaArray in R1C1 style? I find that the Replace part is simply ignored…

  27. Is Excel set to R1C1 mode when you do the replace trick?

  28. shg says:

    I know SendKeys is deprecated, but this also works:

    Public Sub LongArrayFormula()
        Dim theFormula  As String
        theFormula = “=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),”“”“,” & _
                     “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(“A1:G5″)
            .NumberFormat = “@”
            .Value = theFormula
            .NumberFormat = “mmm dd”
            .Select
            DoEvents
            Application.SendKeys “{F2}^+~”
        End With
    End Sub

    But you can’t step through it unless you want to end up in the Object Browser …

  29. shg says:

    Sorry for lousing up the closing code tag!

  30. Nick says:

    Another workaround to this is to define a name first and then use the name. It works, just tested it.

    Here is the code… strLongFormula contains the text of your long formula.

    objWorkbook.Names.Add “LongFormula”, “=” & strLongFormula
    With objDataSheet.Range(objDataSheet.Cells(2, 1), objDataSheet.Cells(200, 1))
    .FormulaArray = “=LongFormula”
    End With

  31. Brian says:

    Here is the code… strLongFormula contains the text of your long formula.

    objWorkbook.Names.Add “LongFormula”, “=” & strLongFormula
    With objDataSheet.Range(objDataSheet.Cells(2, 1), objDataSheet.Cells(200, 1))
    .FormulaArray = “=LongFormula”
    End With

    –
    sounds great, however I couldn’t get it to work in 2007. VBA doesn’t seem to appreciate objWorkbook,objDataSheet, etc. and no idea how to get it to work with these. I’ve tried using workbook / worksheet objects but it doesn’t like those either. any idea what I’m doing wrong?

  32. Brian says:

    OK I declared the objects and used Set to assign them as needed in my workbook, however during this statement,

    objWorkbook.Names.Add “LongFormula”, “=” & strLongFormula

    I still get: Run Time Error ’1004′ – Application-defined or object-defined error

  33. Nick says:

    Brian,

    You will get ‘Runtime Error 1004′ when there is a problem with the formula itself. When your code paused, go to the VB debug window and type:
    ? strLongFormula
    Hit enter and copy the result to the clipboard. Try to enter that formula manually into Excel using the Formulas>Name Manager tool.

  34. Brian says:

    Thanks for the feedback. Didn’t find what that error all about, it goes right through the code with no error when I test it now. It writes the formula string in the Name “refers to” field, which seems correct.

    The only hangup is when I write to the cell with the last statement, what I get is {=LongFormula} in the cell, instead of the formula itself.

  35. Brian says:

    Ahh I think I see now, your algorithm stores the formula in the name permanently, whereas here I was thinking it was trying to writing it back to the cell.

  36. BeRniTo says:

    Brian, how did you make the replacement happen? It isn’t working for me.
    Could you please post a simple example?

  37. Andrew Kalemkarian says:

    I’m not entirely sure why, but in implementation I had to add LookAt:=xlPart to my Replace implementation.

    This is not necessary to get the example to work! — but, it fixed my specific implementation.

    I made my Replace look like this:

            .Replace “X_X_X())”, theFormulaPart2, LookAt:=xlPart
  38. Walter says:

    Hi everybody,

    I am trying to use the same solution that Dave gave but it is not working for me. The Replace does not do anything. This is the code:

    Public Sub LongArrayFormula()
     
        Dim theFormulaPart1 As String
        Dim theFormulaPart2 As String
       
        theFormulaPart1 = “=SUM(IF(FREQUENCY(IF((R4C1:R2000C1″“”“)*(R4C2:R2000C2=R10C2)*(R4C4:R2000C4=R7C2)*” & “X_X_X()” & “,MATCH(R4C1:R2000C1,R4C1:R2000C1,0)),ROW(INDIRECT(““1:”“&ROWS(R4C1:R2000C1))))>0,1,0))”
        theFormulaPart2 = “(IF(R3C4=”“”“,1,R4C6:R2000C6=R3C4))*(IF(R3C6=”“”“,1,R4C10:R2000C10=R3C6))*(IF(R3C9=”“”“,1,R4C5:R2000C5=R3C9))*(IF(R3C12=”“”“,1,R4C13:R2000C13=R3C12)),MATCH(R4C1:R2000C1,R4C1:R2000C1,0)),ROW(INDIRECT(““1:”“&ROWS(R4C1:R2000C1))))>0,1,0))”
       
        With ActiveSheet.Range(“E2:E2″)
            .FormulaArray = theFormulaPart1
            .Replace “X_X_X()”, theFormulaPart2
        End With
       
    End Sub

    I appreciate all your help to resolve this issue.

    Thanks a lot in advance.

    Walter.

  39. Walter: If I try to do it manually (after converting to A1 style reference) it errors because there are mismatched parentheses. I think that may be the problem, but I’m not sure. Can you record the formula manually and paste it here? Use code tags so you don’t lose the greater-than signs.

  40. Walter says:

    Thanks you Dick for your help, I just saw the error.

  41. Andrew Kalemkarian says:

    For fun, I wrote function to easily use the alternative Named Formula method to insert a long array formula. It’s convenient (i.e. no need to break apart the formula), but at the end of the day I don’t like this method because the formula does never appears in the formula bar, only the name.

    Public Sub LongArrayFormula(objRng As Range, strFormula As String)
        Dim strName As String
           
        ‘Create a “unique” name
       strName = “LongFormula” & (10 * Rnd + 1) * Format(Now(), “yyyymmddHhNnSs”)
       
        objRng.Worksheet.Names.Add strName, strFormula  ‘assumes formula already begins with =
       objRng.FormulaArray = “=” & strName
    End Sub
  42. [...] 2. This is probably a very well thought out solution and easier in terms of operational complexity but requires more work in terms of code. It’s best read directly at Dick Kusleika’s blog – http://www.dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/. [...]

  43. Basava says:

    PLs help me on this…

    This is my recorded code for Array formula..
    I could not able run the same because of the lenght of the formula.

    Range(“b14″).Select
    Selection.FormulaArray = _
    “=SUMIFS(All!C[6],All!C2,SUBSTITUTE(R4C2,”" “”,”"”"),All!C8,SUBSTITUTE(RC2,”" “”,”"”"))+(SUMIFS(Data!C[6],Data!C2,SUBSTITUTE(R4C2,”" “”,”"”"),Data!C8,SUBSTITUTE(RC2,”" “”,”"”")))-SUM(SUMIFS(Data!C[6],Data!C2,SUBSTITUTE(R4C2,”" “”,”"”"),Data!C8,SUBSTITUTE(RC2,”" “”,”"”"),Data!C1,INDEX(OFFSET(All!R3C109,,,COUNTA(All!C109)),)))”

    I have seen your slution for the same kind but i did get this as i’m very new for the VBA…
    I strongly believe you can help on this.

  44. Felix says:

    Hello,
    this is workaround is like magic.
    Thank you very much.

  45. AdamT says:

    I concur with felix. An excellent and very useful article and extremely comprehensive set of comments too. Well done all round!

  46. Jason says:

    I found this article very helpful, thanks to the original poster, and all the commenters.

    The long array formulas that I’m trying to get into the cells are dynamically generated, and there’s not a natural split point where both sides are parsable. What I was able to exploit however was that my formulas had lots of long string constants in them. I was able to replace these with short placeholders, the expand the long strings back out.

    For example, if the formula was something like
    =someSub(“longstring”, “anotherlongstring”, “morelongstring”)
    this became
    =someSub(“$0″, “$1″, “$2″)
    which is < 255 chars so it can be set, then finally expand the strings again using the Replace technique shown in the article.

    My code (just drafted so can't say it's perfect)

    Public Sub setFormulaArray(cellRange As Range, formula As String)
    ' For short formulas, just do it the simple way
    If Len(formula) <= 255 Then
    cellRange.FormulaArray = formula
    Return
    End If

    ' Create the replacement formula, replacing strings as "$1" placeholders
    Dim replacementArray(25) As String
    replacementArraySize = 0
    shortFormula = ""
    insideQuote = False
    For i = 1 To Len(formula)
    char = Mid(formula, i, 1)
    If (char = """") Then
    If (insideQuote) Then
    insideQuote = False
    replacementArraySize = replacementArraySize + 1
    Else
    insideQuote = True
    shortFormula = shortFormula & """$" & replacementArraySize & """"
    End If
    Else
    If (insideQuote) Then
    replacementArray(replacementArraySize) = replacementArray(replacementArraySize) & char
    Else
    shortFormula = shortFormula & char
    End If
    End If
    Next i

    ' Set the cell's formula to the shortened version, which should now be < 255 chars
    cellRange.FormulaArray = shortFormula

    ' Expand the created placeholders back out
    For i = 0 To replacementArraySize – 1
    cellRange.Replace "$" & i, replacementArray(i)
    Next i
    End Sub

  47. Nick Perkins says:

    For those situations where you can have a hidden sheet, you can use a copy and paste/pastespecial to get the formula into the cell.

    I wasn’t able to get the above code options to work but did manage to get the copy/paste option going.

  48. Dave Plume says:

    As Shg suggests above, SendKeys seems to work and was the least painful approach for me. In C#:

    if (formula.Length < 255)
    {
    range.FormulaArray = formula;
    }
    else
    {
    range.Formula = formula;
    range.Select();
    Globals.ThisAddIn.Application.SendKeys("{F2}");
    Globals.ThisAddIn.Application.SendKeys("^+{ENTER}");
    }

    (Globals was created for me by Visual Studio when I created my add-in).

  49. Cheryl says:

    Does anyone know if this method of replacing the arrayformula also works on Mac Excel 2011?

    I received an error 1004 (application-defined or object-defined error)

  50. Artur says:

    Hi, Could anyone please tell me why the following code doesn’t work and how I can fix it?

    —–

    Dim p_1, p_2 As String

    For y = 1 To 100

    p_1 = "COVARIANCE.S(IF(Bstrap!" & ColumnLetter(10 + y) & 305 & "" & ":" & ColumnLetter(10 + y) & j_end + 304 & "<AVERAGE(Bstrap!" & ColumnLetter(10 + y) & 305 & "" & ":" & ColumnLetter(10 + y) & j_end + 304 & ")," & _
    "IF(Bstrap!" & ColumnLetter(10 + y) & 13 & "" & ":" & ColumnLetter(10 + y) & j_end + 12 & "  " & """""" & ", " & _
    "p_2p()))"

    p_2 = "Bstrap!" & ColumnLetter(10 + y) & 13 & "" & ":" & ColumnLetter(10 + y) & j_end + 12 & "))," & _
    "IF(Bstrap!" & ColumnLetter(10 + y) & 305 & "" & ":" & ColumnLetter(10 + y) & j_end + 304 & "<AVERAGE(Bstrap!" & ColumnLetter(10 + y) & 305 & "" & ":" & ColumnLetter(10 + y) & j_end + 304 & ")," & _
    "IF(Bstrap!" & ColumnLetter(10 + y) & 305 & "" & ":" & ColumnLetter(10 + y) & j_end + 304 & "  " & """""" & ", " & _
    "Bstrap!" & ColumnLetter(10 + y) & 305 & "" & ":" & ColumnLetter(10 + y) & j_end + 304 & ")))" & _
    "/VAR.S(IF(Bstrap!" & ColumnLetter(10 + y) & 305 & "" & ":" & ColumnLetter(10 + y) & j_end + 304 & "<" & _
    "AVERAGE(Bstrap!" & ColumnLetter(10 + y) & 305 & "" & ":" & ColumnLetter(10 + y) & j_end + 304 & "), " & _
    "IF(Bstrap!" & ColumnLetter(10 + y) & 305 & "" & ":" & ColumnLetter(10 + y) & j_end + 304 & "  " & """""" & "," & _
    "Bstrap!" & ColumnLetter(10 + y) & 305 & "" & ":" & ColumnLetter(10 + y) & j_end + 304 & ")))"

    With Cells(12 + y, 4)
       .FormulaArray = p_1
       .Replace "p_2p()))", p_2
    End With

    —–

    When I run this code, the same error appears (“Unable to set the FormulaArray property of the Range class”).

    The funny thing is that when I remove “=” from before “COVARIANCE.S”, VBA does not show any error and it displays the formula correctly (just does not calculate it, since “=” is missing. When I put “=” myself in Excel, the formula performs the calculations correctly.

    Thank you for your time and help!

  51. Artur says:

    Sorry, I forgot to add “=” before “COVARIANCE.S” in the code and “Next y” at the end.

  52. Chris says:

    Hello,
    I’ve hit a brick wall with this one, can anyone help me get the following code to work?

    Sub LongArrayFormula1()

    Dim frmla1 As String
    Dim frmla2 As String
    Dim frmla3 As String

    ‘fullfrmla = “=SUM(IF(FREQUENCY(IF(A2:A10000&B2:B10000&C2:C10000″”"”,MATCH(“”~”"&A2:A10000&B2:B10000&C2:C10000,A2:A10000&B2:B10000&C2:C10000&”"”",0)),ROW(A2:A10000)-ROW(A2)+1),1))”

    frmla1 = “=SUM(IF(FREQUENCY(IF(A2:A10000&B2:B10000&C2:C10000″”"”,” & _
    “X(“”~”"&X,X&”"”",0)),ROW(X)-ROW(X)+1),1))”
    frmla2 = “MATCH(“”~”"&A2:A10000&B2:B10000&C2:C10000,A2:A10000&B2:B10000&C2:C10000&”"”",0)),” & _
    “ROW(X)-ROW(X)+1),1))”
    frmla3 = “ROW(A2:A10000)-ROW(A2)+1),1)”

    With ActiveSheet.Range(“AF2″)
    .FormulaArray = frmla1
    .Replace What:=”X(“”~”"&X,X&”"”",0)),ROW(X)-ROW(X)+1),1))”, Replacement:=”frmla2″, LookAt:=xlPart
    .Replace What:=”ROW(X)-ROW(X)+1),1))”, Replacement:=”frmla3″, LookAt:=xlPart
    End With

    End Sub

    At the moment it is just displaying the following in cell AF2:
    =SUM(IF(FREQUENCY(IF(A2:A10000&B2:B10000&C2:C10000″”"”,X(“”~”"&X,X&”"”",0)),ROW(X)-ROW(X)+1),1))

    No error, but the replace doesn’t appear to be working.

    I have data in three columns (A,B, and C) and I want the cell AF2 to display the number of unique values when those three cells are concatenated. (A10000 is only used to make sure that all cells are included, as the populated rows will change).

    Thanks.

  53. Dick Kusleika says:

    Chris: I changed it around a bit, but I can’t get it to work either. Actually, if I step through the code it works. If I just run the code, it doesn’t. I tried putting some .Calculate calls in there as well as some Application.Wait calls to see if I could get it to go, but no dice.

    Sub LongArrayFormula1()

        Dim frmla1 As String
        Dim frmla2 As String
        Dim frmla3 As String
       
        'fullfrmla = "=SUM(IF(FREQUENCY(IF(A2:A10000&B2:B10000&C2:C10000<>"""",MATCH(""~""&A2:A10000&B2:B10000&C2:C10000,A2:A10000&B2:B10000&C2:C10000&"""",0)),ROW(A2:A10000)-ROW(A2)+1),1))"
       
        frmla1 = "=SUM(IF(FREQUENCY(IF(A2:A10000&B2:B10000&C2:C10000<>"""",YYYY),ZZZZ),1))"
       
        frmla2 = "MATCH(""~""&A2:A10000&B2:B10000&C2:C10000,A2:A10000&B2:B10000&C2:C10000&"""",0)"
       
        frmla3 = "ROW(A2:A10000)-ROW(A2)+1"
       
        With ActiveSheet.Range("AF2")
            .FormulaArray = frmla1
            .Replace What:="YYYY", Replacement:=frmla2, LookAt:=xlPart
            .Replace What:="ZZZZ", Replacement:=frmla3, LookAt:=xlPart
        End With

    End Sub
  54. formulaArray, call my attention I didn’t found anything about frequency function as formula array in a macro.
    my concern is I would like to know how to loop the frequency function.
    Range(“Q2:Q54″).Select
    Selection.FormulaArray = “=FREQUENCY(R[1]C[-15]:R[18]C[-10],RC[-1]:R[52]C[-1])”

    Range(“R2:R54″).Select
    Selection.FormulaArray = “=FREQUENCY(R[2]C[-16]:R[19]C[-11],RC[-2]:R[52]C[-2])”

    from here how can I apply the for .. next loop ideas. thanks.

  55. Jack says:

    In .Replace “X_X_X())”, theFormulaPart2

    What if size of theFormulaPart2 is greater than 255 chars, I am getting a type mismatch error if theFormulaPart2 is greater than 255 chars

  56. Vic says:

    I worked on this issue for a few hours and was able to make my array formula with the replace function to work properly. I realized that each of the parsed formula should be executable with the correct syntax. You cannot subdivide your long formula to just random divisions. Not sure that I am clear on this comment. Here’s my code that works:

     formula3 = "=IF(B5=""Armouring"","""",Y5*COUNT(IF((($E$2:$K$2=""Mon"")+($E$2:$K$2=""Tue"")+($E$2:$K$2=""Wed"")+($E$2:$K$2=""Thu"")+($E$2:$K$2=""Fri""))*(E5:K5=0),(E5:K5)))+" & _
                            "dummy1())"
        formula4 = "IF(Y5=""12"",SUM(IF((($E$2:$K$2=""Mon"")+($E$2:$K$2=""Tue"")+($E$2:$K$2=""Wed"")+($E$2:$K$2=""Thu"")+($E$2:$K$2=""Fri""))*(E5:K5<12)*(E5:K5>0),Y5-(E5:K5))),dummy2())-W5"
        formula5 = "SUM(IF((($E$2:$K$2=""Mon"")+($E$2:$K$2=""Tue"")+($E$2:$K$2=""Wed"")+($E$2:$K$2=""Thu"")+($E$2:$K$2=""Fri""))*(E5:K5<8)*(E5:K5>0),Y5-(E5:K5))))"
        range("R5").Select
        With ActiveSheet.range("R5")  'ABS Formula
            .FormulaArray = formula3
            .Replace "dummy1()", formula4, LookAt:=xlPart
            .Replace "dummy2())", formula5, LookAt:=xlPart
           
        End With

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: