Я создал пару формул в Excel ( UDF ). Эти формулы подгружают данные с сервера и не зависят от других ячеек. Какой код VBA в Excel может пересчитать значения этих формул для активной книги с сервера принудительно ( данные поменялись только на сервере и Excel об этом "не знает" ).
Обычный Calculate не помогает ( он пересчитывает только если формула зависит от других ячеек, которые поменялись), CalculateAll помогает, но он пересчитывает во всех книгах Excel — так не подходит.

Здравствуйте, Аноним, Вы писали:

А>Я создал пару формул в Excel ( UDF ). Эти формулы подгружают данные с сервера и не зависят от других ячеек. Какой код VBA в Excel может пересчитать значения этих формул для активной книги с сервера принудительно ( данные поменялись только на сервере и Excel об этом "не знает" ).
А>Обычный Calculate не помогает ( он пересчитывает только если формула зависит от других ячеек, которые поменялись), CalculateAll помогает, но он пересчитывает во всех книгах Excel — так не подходит.


см. Application.Volatile в начале функции, тогда будут пересчитываться по Calculate

так я тоже пробовал, у меня 10 страничек и на каждой по 10 000 формул и когда начинается любые изменения на страничке всё невероятно медленно перерисовываются. Есть ещё предложения?

Здравствуйте, Elena_, Вы писали:

E_>Здравствуйте, Аноним, Вы писали:

А>>Я создал пару формул в Excel ( UDF ). Эти формулы подгружают данные с сервера и не зависят от других ячеек. Какой код VBA в Excel может пересчитать значения этих формул для активной книги с сервера принудительно ( данные поменялись только на сервере и Excel об этом "не знает" ).
А>>Обычный Calculate не помогает ( он пересчитывает только если формула зависит от других ячеек, которые поменялись), CalculateAll помогает, но он пересчитывает во всех книгах Excel — так не подходит.


E_>см. Application.Volatile в начале функции, тогда будут пересчитываться по Calculate

Здравствуйте, Аноним, Вы писали:

А>так я тоже пробовал, у меня 10 страничек и на каждой по 10 000 формул и когда начинается любые изменения на страничке всё невероятно медленно перерисовываются. Есть ещё предложения?

1) Если речь именно о данных из базы, возможно, имеет смысл использовать QueryTable — такая разновидность связанных таблиц — лист привязывается к данным, может обновляться автоматически или по запросу.

2) Если формулы, то использовать формулы массива, поскольку по каждой формулы идет вызов, лучше если сразу для нескольких ячеек делается. Обычно, если много формул, они именно относятся к однородным данным, которые можно передать в виде массива, и в виде массива вернуть результат.

3) Я в таких случаях предлагаю довольно нудную конструкцию, предполагающую переход на ручной пересчет и пересчет только конкретных формул. Имеет смысл, если функции в одной книге собраны функции из базы, например, а других нет. Примерно это выглядит так:

При открытии книги для всех листов свойство EnableCalculation устанавливается в False, а пересчет в Автоматический
Перед сохранением книги пересчет устанавливается в ручной, а после сохранения восстанавливается автоматический, чтобы не влиять на другие книги.

Таким образом, мы добиваемся того, что автоматически вообще ничего не пересчитывается.

А потом, для расчета конкретного диапазона используется конструкция следующего типа, то есть в любой момент автоматический расчет отключен, поэтому ничего лишнего не происходит. Такой "шлюз" образуется.

    Application.Calculation = xlCalculationManual
    wks.EnableCalculation = True
    wks.Range("D2:D500").Calculate
    wks.EnableCalculation = False
    Application.Calculation = xlCalculationAutomatic

Но это вариант на крайний случай. По 10 000 формул — первое — это попробовать свести их к формулам массива.

Здравствуйте, Elena_, Вы писали:

И совсем забыла, вариант 4) часто совсем это не должны быть собственно формулы, а структура листа известна заранее. Тогда можно просто сделать выборку, подключаясь из программы, например при помощи ADO, получить курсор, выложить данные на лист функцией CopyFromRecordset — очень быстрая операция, просто данные как массив копируются во внутреннюю память листа. Это очень быстрый вариант, формулы для получения данных из базы нужны только тогда, когда пользователь хочет лист по-своему макетировать каждый раз и по-разному, это довольно редко бывает. Это, на мой взгляд, предпочтительнее QueryTable, так как гибче, хотя qt позволяют настроить автоматическое обновление.

большое спасибо за большой ответ, но он не подходит
Здравствуйте, Elena_, Вы писали:

E_>Здравствуйте, Аноним, Вы писали:

А>>так я тоже пробовал, у меня 10 страничек и на каждой по 10 000 формул и когда начинается любые изменения на страничке всё невероятно медленно перерисовываются. Есть ещё предложения?

E_>1) Если речь именно о данных из базы, возможно, имеет смысл использовать QueryTable — такая разновидность связанных таблиц — лист привязывается к данным, может обновляться автоматически или по запросу.

знаю есть такое, но у меня пользователи выстраивают данные как им угодно и привязки не подойдут, потому что тогда будет прямая связь Excel — БД, что не приемлимо, у меня идет связь через IIS сервер который отдает xml по запросам из Excel.

E_>2) Если формулы, то использовать формулы массива, поскольку по каждой формулы идет вызов, лучше если сразу для нескольких ячеек делается. Обычно, если много формул, они именно относятся к однородным данным, которые можно передать в виде массива, и в виде массива вернуть результат.

Вместо массива я пользую xml, почти то же самое но проще в связи со спецификой данных. И вот как раз то что xml изменился Excel не знает и когда я жму кнопку "перегрузить", этот внутренний xml перегружается с сервера а данные в формулах не меняются.

E_>3) Я в таких случаях предлагаю довольно нудную конструкцию, предполагающую переход на ручной пересчет и пересчет только конкретных формул. Имеет смысл, если функции в одной книге собраны функции из базы, например, а других нет. Примерно это выглядит так:

E_>При открытии книги для всех листов свойство EnableCalculation устанавливается в False, а пересчет в Автоматический
E_>Перед сохранением книги пересчет устанавливается в ручной, а после сохранения восстанавливается автоматический, чтобы не влиять на другие книги.

E_>Таким образом, мы добиваемся того, что автоматически вообще ничего не пересчитывается.

E_>А потом, для расчета конкретного диапазона используется конструкция следующего типа, то есть в любой момент автоматический расчет отключен, поэтому ничего лишнего не происходит. Такой "шлюз" образуется.
E_>

E_>    Application.Calculation = xlCalculationManual
E_>    wks.EnableCalculation = True
E_>    wks.Range("D2:D500").Calculate
E_>    wks.EnableCalculation = False
E_>    Application.Calculation = xlCalculationAutomatic
E_>

E_>Но это вариант на крайний случай. По 10 000 формул — первое — это попробовать свести их к формулам массива.

этот вариант тоже не подходит, потому что он не пересчитает формулы строчкой worksheet.calculate которые зависят от глобального xml, Excel просто не видит что их надо пересчитывать.

Здравствуйте, Аноним, Вы писали:

А>Вместо массива я пользую xml, почти то же самое но проще в связи со спецификой данных. И вот как раз то что xml изменился Excel не знает и когда я жму кнопку "перегрузить", этот внутренний xml перегружается с сервера а данные в формулах не меняются.

Формулы массива — это разновидность формул Excel, которые получают в качестве входных параметров диапазоны (что иногда и обычные формулы получают) и возвращают массивы, которые размещаются в диапазонах Excel. Главная экономия идет за счет того, что один вызов заполняет сразу много ячеек. К xml не имеет вроде бы никакого отношения.

Если xml возвращается по запросам из Excel, можно сразу данные из него просто загрузить на скрытый лист, а формулы привязать к этому листу. Или это так и делается — тогда торможение связано исключительно с количеством пользовательских формул и к базе вообще не имеет отношения? Тогда вопрос в том, как ускорить работу пользовательских формул в книге, а есть база или нет — вообще не при чем? Можно оптимизировать пользовательские формулы различным кэшированием, например. Это кроме формул массива.

Здравствуйте, Elena_, Вы писали:

E_>Здравствуйте, Аноним, Вы писали:

Уважаемый эксперт, мы совсем отошли от темы. Ведь вопрос был вообще простой — "как принудительно заставить Excel пересчитать формулы".
Application.CalculateAll — не подходит, он пересчитает все книги, этого делать не надо,
*.Calculate — не подходит, он пересчитает только если исходные данные поменялись
Application.Volatile — не подходит, он тормозит
Менять идиалогию и объяснять пользователю что для того чтобы работали данные ему прийдется брать определенный Excel файл с кодом или научить его пользоваться мапингом xml-ячейка, вообще не надо.
Необходимо простое решение задачи: "как принудительно заставить Excel пересчитать формулы"

А>>Вместо массива я пользую xml, почти то же самое но проще в связи со спецификой данных. И вот как раз то что xml изменился Excel не знает и когда я жму кнопку "перегрузить", этот внутренний xml перегружается с сервера а данные в формулах не меняются.

E_>Формулы массива — это разновидность формул Excel, которые получают в качестве входных параметров диапазоны (что иногда и обычные формулы получают) и возвращают массивы, которые размещаются в диапазонах Excel. Главная экономия идет за счет того, что один вызов заполняет сразу много ячеек. К xml не имеет вроде бы никакого отношения.

E_>Если xml возвращается по запросам из Excel, можно сразу данные из него просто загрузить на скрытый лист, а формулы привязать к этому листу. Или это так и делается — тогда торможение связано исключительно с количеством пользовательских формул и к базе вообще не имеет отношения? Тогда вопрос в том, как ускорить работу пользовательских формул в книге, а есть база или нет — вообще не при чем? Можно оптимизировать пользовательские формулы различным кэшированием, например. Это кроме формул массива.

Здравствуйте, Аноним, Вы писали:

А>Уважаемый эксперт, мы совсем отошли от темы. Ведь вопрос был вообще простой — "как принудительно заставить Excel пересчитать формулы".
А>Application.CalculateAll — не подходит, он пересчитает все книги, этого делать не надо,
А>*.Calculate — не подходит, он пересчитает только если исходные данные поменялись
А>Application.Volatile — не подходит, он тормозит
А>Менять идиалогию и объяснять пользователю что для того чтобы работали данные ему прийдется брать определенный Excel файл с кодом или научить его пользоваться мапингом xml-ячейка, вообще не надо.
А>Необходимо простое решение задачи: "как принудительно заставить Excel пересчитать формулы"

Дело в том, что Application.Volatile и есть ответ, и довольно простой ("Необходимо простое решение задачи"), но Вы говорите, что он тормозит, поэтому я и предложила другие решения. Тормозит не сам Application.Volatile, это просто указание Excel, что надо пересчитывать эту формулу.

Здравствуйте, Elena_, Вы писали:

E_>Здравствуйте, Аноним, Вы писали:

А>>Уважаемый эксперт, мы совсем отошли от темы. Ведь вопрос был вообще простой — "как принудительно заставить Excel пересчитать формулы".
А>>Application.CalculateAll — не подходит, он пересчитает все книги, этого делать не надо,
А>>*.Calculate — не подходит, он пересчитает только если исходные данные поменялись
А>>Application.Volatile — не подходит, он тормозит
А>>Менять идиалогию и объяснять пользователю что для того чтобы работали данные ему прийдется брать определенный Excel файл с кодом или научить его пользоваться мапингом xml-ячейка, вообще не надо.
А>>Необходимо простое решение задачи: "как принудительно заставить Excel пересчитать формулы"

E_>Дело в том, что Application.Volatile и есть ответ, и довольно простой ("Необходимо простое решение задачи"), но Вы говорите, что он тормозит, поэтому я и предложила другие решения. Тормозит не сам Application.Volatile, это просто указание Excel, что надо пересчитывать эту формулу.

Да решение простое, но Application.Volatile говорит о том что формулу нужно всегда пересчитывать при любых событиях в документе. И тогда например когда у меня в другой книге, связанной с этой начнуться сильные изменения то формулы будут пересчитываться ВСЕ а это больше 100 000 действий которые должно происходить тоько по моей команде.

Здравствуйте, Аноним, Вы писали:

А>Да решение простое, но Application.Volatile говорит о том что формулу нужно всегда пересчитывать при любых событиях в документе. И тогда например когда у меня в другой книге, связанной с этой начнуться сильные изменения то формулы будут пересчитываться ВСЕ а это больше 100 000 действий которые должно происходить тоько по моей команде.

Да, так это устроено в Excel. Дело в том, что для задачи получения данных из базы пользовательские функции не слишком подходят, я сама делала подобные решения, и на данный момент уверена в этом. Пользовательские функции по идее должны получать данные, от которых они зависят, в качестве параметров, тогда Excel правильно выстраивает стратегию пересчета. В рамках существующей парадигмы можно только придумывать всякие оптимизирующие трюки.

См. еще ссылку Excel User-Defined Functions и там пункт
UDF not recalculating or always recalculating or calculating in an unexpected sequence. Может быть, что-то оттуда пригодится.

Как вариант — пересчитывать только ячейки с определённой формулой:

Dim r As Range
    
For Each r In Application.Cells.SpecialCells(xlCellTypeFormulas, xlTextValues)
    If InStr(1, r.Formula, "=НужнаяФормула()", vbTextCompare) > 0 Then
        r.Dirty
    End If
Next
If Application.Calculation = xlCalculationManual Then Application.Calculate

Здравствуйте, PA, Вы писали:

PA>Как вариант — пересчитывать только ячейки с определённой формулой:


PA>

PA>Dim r As Range
    
PA>For Each r In Application.Cells.SpecialCells(xlCellTypeFormulas, xlTextValues)
PA>    If InStr(1, r.Formula, "=НужнаяФормула()", vbTextCompare) > 0 Then
PA>        r.Dirty
PA>    End If
PA>Next
PA>If Application.Calculation = xlCalculationManual Then Application.Calculate
PA>

Благодарю за ответ, я его немного развил и получил такое элегантное и быстрое решение:
For Each wCurSheet In ActiveWorkbook.Worksheets
wCurSheet.Cells.Dirty
' wCurSheet.Calculate -- можно убрать если Excel настроен так что идет автоматический пересчет формул
Next