Как обеспечить, чтобы вычисление Excel было завершено в рамках процедуры VBA

В расширенной таблице пользовательские функции используются для вычисления основных результатов в виде матриц расширенных листов (значения поперечного сечения составных элементов).

Public Function XSValues(inputRange as Range) as variant [...] ' returns an array and used as matrix-formula (Ctrl-Shift-Enter) End Function 

Эти результаты используются в электронной таблице, с одной стороны. С другой стороны, на основании некоторых значений из этих результатов электронных таблиц для выполнения относительно сложного и трудоемкого вычисления (статический анализ структурной модели) используется VBA-процедура. Эта процедура запускается кнопкой.

 Public Sub Recalculate() [...] myValue = range("SomeXLResult").Value ' calculation ' notification updates ' drawing ' places different results into cells End Sub 

Теперь мои проблемы состоят в том, что расчёт с расширением листа устарел, когда вызывается Sub Recalculate . Я обнаружил, что в Excel 2016 расчет с расширением листа разбивается на несколько потоков. И опыт в том, что взаимодействие с пользователем иногда происходит быстрее, чем вычисление электронных таблиц.

Поэтому я получаю обесцененные значения для дальнейшей обработки в VBA-процедуре. Мой вопрос: как гарантировать обновленные значения из диапазонов расширенных листов?

Если решение, как объяснено в вашем ответе, работает для вас тогда отлично. Мне просто интересно, знаете ли вы о событии AfterCalculate приложения ( https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-aftercalculate-event-excel ):

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

Разработчики надстроек используют событие AfterCalculate, чтобы знать, когда все данные в книге полностью обновлены любыми запросами и / или вычислениями, которые, возможно, были выполнены.

Это событие происходит после всех рабочих листов. Рассчитать, диаграмму. Вычислять, AfterRefresh и SheetChange. Это последнее событие возникает после завершения обработки обновления и обработки всех вычислений, и это происходит после приложения. CalculationState имеет значение xlDone.

Это может быть проще для вас. Трюк для доступа к событиям объекта приложения заключается в объявлении его WithEvents в модуле класса. Например, я назвал класс clsAppEvents :

 Option Explicit Private WithEvents mApp As Application Private Sub Class_Initialize() Set mApp = Application End Sub Private Sub mApp_AfterCalculate() Debug.Print "Calc ended at: " & Now ConsumeAfterCalculate End Sub 

В вашем модуле вы просто получите код обработки вызовов и событий:

 Option Explicit Private mAppEvents As clsAppEvents Private mEnableConsume As Boolean Public Sub RunMe() Set mAppEvents = New clsAppEvents End Sub Public Sub ConsumeAfterCalculate() If mEnableConsume Then Debug.Print "Sub called at: " & Now mEnableConsume = False End If End Sub Public Sub ConsumeButtonClick() Debug.Print "Button clicked at: " & Now mEnableConsume = True 'For demo purposes I'm just forcing a calculation on existing data. Sheet1.EnableCalculation = False Sheet1.EnableCalculation = True End Sub 

FYI, результаты отладки были следующими:

Кнопка нажата на: 25/10/2017 16:49:20

Calc закончил: 25/10/2017 4:49:22 pm

Sub позвонил по адресу: 25/10/2017 4:49:22 pm

Наконец, решение, удовлетворяющее моим потребностям:

Когда нажата кнопка для пересчета, vba проверяет текущее состояние вычисления Excel. В случае, если выполняется расчет, VBA-Процедура для расчета « Recalculate запускается напрямую. В случае, если расчетный режим находится в ожидании или вычислении, тогда только локальная переменная p_RecalcButtonClicked установлена ​​в значение true. Когда вычисление excel выполняется, каждый рабочий лист запускает событие Worksheet_Calculate после его вычисления. И поэтому мы можем указать Excel для Recalculate .

В качестве меры безопасности я сохранил решение, описанное в связанных двух вопросах из вышеприведенного комментария, прежде чем в начале суб- Recalculate использовать функцию waitForRecalculation . Чтобы избежать неактивности, я ввел таймер, чтобы рассказать пользователю, если расчет не может быть завершен за заданный промежуток времени.

Это код основного рабочего листа:

 ' ##### Worksheet-Code ''' ' Private Worksheet-Variable to determine, ' if the button was pressed prior to worksheet calculated-event ' Private p_RecalcButtonClicked As Boolean ''' ' Procedure to handle Button Clicked ' (either using a shape with a macro assigned or ' an Active-X-Button with this procedure as event handler: best is to use {Button}_MouseUp as {Button}_clicked is fired occasionally by excel itself) ' Public Sub ButtonClicked() ' ' depending on the calculation state ... ' Select Case Application.CalculationState Case xlDone ' ' ... all done, fine ... ' ... directly call the calculation procedure sub Recalculate ' p_RecalcButtonClicked = False Recalculate Case xlPending ' ' ... pending ... ' ... set local worksheet variable true in order to call sub Recalculate ' later, when the calculated-event was raised ' p_RecalcButtonClicked = True ' ' instruct excel to recalculate ' Application.CalculateFullRebuild ' ' now let excel perform until worksheet calculated event is raised ' Case xlCalculating ' ' ... calculating ... ' ... set local worksheet variable true in order to call sub Recalculate ' later, when the calculated-event was raised ' p_RecalcButtonClicked = True ' ' let excel continue until worksheet calculated event is raised ' Case Else End Select End Sub ''' ' worksheet calculation finished ' this event is raised AFTER calculation was finished ' (shold actually be named Worksheet_Calculated) ' Private Sub Worksheet_Calculate() ' check if the RecalcButton was clicked If p_RecalcButtonClicked Then p_RecalcButtonClicked = False Recalculate End If End Sub ''' ' Recalculation ' Public Sub wm_Recalculate() ' ' wait for calculation to be done ' just in case... ' If Not waitForRecalculation Then MsgBox "Press Ctrl+Alt+F9 for full recalculation", vbCritical + vbOKOnly, "Excel-calculation not done" Exit Sub End If ' [...] Your calculation here... End Sub ''' ' Helper function to wait and do events until Excel-calculations are done ' returns true if calculation is done within the given time ' Public Function waitForRecalculation() As Boolean Const MAXTIME_S = 10 Dim t As Double t = Timer() ' in case of sql-async queries this might be required ' ' Application.CalculateUntilAsyncQueriesDone ' ' As a safety net, ' the second solution is to ' do System events until calculation is done ' If Application.CalculationState <> xlDone Then Do DoEvents If Timer() - t > MAXTIME_S Then Exit Do Loop Until Application.CalculationState = xlDone End If ' ' return true if calculations are done ' waitForRecalculation = (Application.CalculationState = xlDone) End Function 
Давайте будем гением компьютера.