VBA - Array Formula : Run Time Error 1004 on formulaArray even though the formula looks right. Example inside

Question

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:A12353-SUM(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

Damn! It is supposed to say formula.array in the title.... -.- Sorry — Jens
– Jens, Commented Nov 8, 2018 at 8:19
.FormulaLocal is not the same as .FormulaArray. Did you mean .FormulaArray throughout your question as well as title? And what are the values of the variables you are using in the formula e.g. b ? — QHarr
– QHarr, Commented Nov 8, 2018 at 8:39
Hi, yes. It was supposed to be Formulaarray in the whole post. Sorry I messed it up. b stands for the last row in the range i want to look at in the formula — Jens
– Jens, Commented Nov 8, 2018 at 9:00
Have you got it working as an array formula in a cell? You can also use edit to update your question so no problem there! — QHarr
– QHarr, Commented Nov 8, 2018 at 9:01

Chronocidal · Accepted Answer · 2018-11-08 12:42:57Z

1

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

answered Nov 8, 2018 at 12:42

Chronocidal

8,3551 gold badge15 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

QHarr Over a year ago

Nice use of replace

Jens Over a year ago

Very nice use! I was splitting the formula up. The suggested way from Chronocidal is nice and easy. It worked fully when I copied it. Thank you for your help!!

EvR · Accepted Answer · 2018-11-08 10:16:33Z

0

Shorten your array-formula to 255 characters (with the help of defined names) and try again. (you exceed the maximum lenght)

https://learn.microsoft.com/en-us/office/vba/api/excel.range.formulaarray

answered Nov 8, 2018 at 10:16

EvR

3,5082 gold badges17 silver badges25 bronze badges

Collectives™ on Stack Overflow

VBA - Array Formula : Run Time Error 1004 on formulaArray even though the formula looks right. Example inside

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related