VBA - Формула массива: ошибка времени выполнения 1004 для FormArray, хотя формула выглядит правильно. Пример внутри

Question

РЕДАКТИРОВАТЬ: решение от Chronocidal было очень полезно. Я реализовал это, и это работает как шарм. Спасибо за вашу помощь. Замечания Рона Райзенфельда и EvR тоже очень помогли и направили меня в правильном направлении.

Может ли кто-нибудь помочь мне с формулой массива, которую я пытаюсь вставить в некоторые ячейки с помощью функции Form.local? Возможно, что-то не так в формуле, но я просто не могу найти причину... Было бы очень полезно, если бы кто-то смог это проверить, так как у меня есть только немецкая версия Excel, и, возможно, я наблюдал за ней. любые языковые причины. Я приложил файл для проверки формулы (xlsx)

Если я debug.print формула выглядит так:

=(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

Я использовал формулу в этом примере книги: https://drive.google.com/open?id=1xCth5vo6G82lNDsclbj3FpUfYzvXsuEJ

В VBA формула состоит из множества переменных:

[edit] b = последняя строка в диапазоне, на которую я хочу посмотреть в формуле [/ edit]

Daten! B1: B "& b = Ряд дат

sh_Ov.Cells(int_Ende + 1, Int_links).Address = Последняя дата

sh_Ov.Cells(int_Start, 3).Address = самая ранняя дата (но не обязательно в строке даты - поэтому я работаю с "маленькой" формулой)

Daten! A1: A "& b = Числа, для которых я хочу рассчитать произведение

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"

Было бы здорово, если бы кто-нибудь мог взглянуть на это. Пока не могу найти причину

С наилучшими пожеланиями, Йенс

Answer 1

Ваша формула содержит более 255 символов, поэтому вы не можете ввести ее напрямую через VBA.

Либо измените его на более короткий (например, используя именованные диапазоны, как предлагает EvR), либо используйте (технически действительный) фиктивный код и затем поменяйте его местами с помощью Range.Replace, вот так:

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

Answer 2

Сократите формулу массива до 255 символов (с помощью определенных имен) и повторите попытку. (вы превышаете максимальную длину)

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