VBA  Array Formula : Run Time Error 1004 on formulaArray even though the formula looks right. Example inside
EDIT: The solution from Chronocidal was very helpful. I implemented it and it works like a charm. Thanks to all your help. The remarks from Ron Reisenfeld and EvR helped a lot too and sent me in the right direction
can somebody help me with an array formula I am trying to insert into some cells with the formula.local function? There is probably something not right in the formula, but I just can’t find the reason… It would be a great help if someone can test it, since I only have a german version of excel and it may be possible that I have overseen any language specific reasons. I attached a file to test the formula (xlsx)
If I debug.print the formula looks like this:
=(PRODUCT(IF(Daten!B1:B12353=SMALL(IF((Daten!B1:B12353>=$C$25),Daten!B1:B12353,999999),1),Daten!A1:A12353SUM(0,0),1),IF((Daten!B1:B12353>SMALL(IF((Daten!B1:B12353>=$C$25),Daten!B1:B12353),1))*((Daten!B1:B12353<=$D$33)),Daten!A1:A12353,1))^(1/(DAYS360($C$25,$D$33)/360))1)*100
I used the formula in this example workbook: https://drive.google.com/open?id=1xCth5vo6G82lNDsclbj3FpUfYzvXsuEJ
In VBA the formula consists of plenty of variables those are:
[edit] b = last row in the range i want to look at in the formula [/edit]
Daten!B1:B" & b = Row of Dates
sh_Ov.Cells(int_Ende + 1, Int_links).Address = Latest date
sh_Ov.Cells(int_Start, 3).Address = earliest date (but not necessarily in the date row – that’s why I am working with the “small” formula)
Daten!A1:A" & b = Numbers I want to calculate the product of
ThisWorkbook.Sheets(1).Cells(x,y).FormulaArray = "=(PRODUCT(IF(Daten!B1:B" & b & "=SMALL(IF((Daten!B1:B" & b & ">=" & sh_Ov.Cells(int_Start, 3).Address & "),Daten!B1:B" & b & ",999999),1),Daten!A1:A" & b & "SUM(0,0),1),IF((Daten!B1:B" & b & ">SMALL(IF((Daten!B1:B" & b & ">=" & sh_Ov.Cells(int_Start, 3).Address & "),Daten!B1:B" & b & "),1))*((Daten!B1:B" & b & "<=" & sh_Ov.Cells(int_Ende + 1, Int_links).Address & ")),Daten!A1:A" & b & ",1))^(1/(DAYS360(" & sh_Ov.Cells(int_Start, 3).Address & "," & sh_Ov.Cells(int_Ende + 1, Int_links).Address & ")/360))1)*100"
It would be great if anyone can have a look at this. So far I am not able to find the reason
Best regards Jens
2 answers

Shorten your arrayformula to 255 characters (with the help of defined names) and try again. (you exceed the maximum lenght)
https://docs.microsoft.com/enus/office/vba/api/excel.range.formulaarray

Your formula is over 255 characters, so you cannot enter it directly via VBA.
Either change it to be shorter (such as using Named Ranges like EvR suggests), or use (technically valid) dummy code and swap it out afterwards with
Range.Replace
, like so:With ThisWorkbook.Worksheets(1).Cells(x,y) 'Worksheets instead of Sheets will exclude Chart Sheets '214 Characters .FormulaArray = "=(PRODUCT(IF(MAX(1)=SMALL(IF((MAX(1)>=" & sh_Ov.Cells(int_Start, 3).Address & "),MAX(1),999999),1),Daten!A1:A" & b & "SUM(0,0),1),IF((MAX(1)>SMALL(IF((MAX(1)>=" & sh_Ov.Cells(int_Start, 3).Address & "),MAX(1)),1))*((MAX(1)<=" & sh_Ov.Cells(int_Ende + 1, Int_links).Address & ")),Daten!A1:A" & b & ",1))^(1/(DAYS360(" & sh_Ov.Cells(int_Start, 3).Address & "," & sh_Ov.Cells(int_Ende + 1, Int_links).Address & ")/360))1)*100" 'Swap all instances of MAX(1) for your range in the Daten sheet .Replace("MAX(1)", "Daten!B1:B" & b, LookAt:=xlPart) End With