如何确保Excel计算在VBA过程中完成
在excel-电子表格中，用户定义的函数用于计算基本结果作为扩展表矩阵(复合元素的横截面值)。
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-过程中进行进一步处理。我的问题是：如何保证从扩展表范围更新值？
回答 2
Stack Overflow用户
发布于 2017-10-25 03:54:58
如果你在答案中解释的解决方案对你有用，那就太好了。我只是想知道您是否知道应用程序的AfterCalculate事件(https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-aftercalculate-event-excel)：
只要计算完成，并且没有未完成的查询，就会发生此事件。在事件发生之前必须满足这两种条件。即使工作簿中没有工作表数据，也可以引发该事件，例如，无论何时完成整个工作簿的计算，并且没有查询正在运行。 外接程序开发人员使用AfterCalculate事件来知道工作簿中的所有数据何时已经通过任何可能正在进行的查询和/或计算完全更新。 此事件发生在所有工作表之后。算一算，图表。计算、AfterRefresh和SheetChange事件。它是在所有刷新处理和所有calc处理完成之后，以及在Application之后发生的最后一个事件。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 SubFYI，调试结果如下：
按下按钮:2017年10月25日下午4:49:20。 加州大学洛杉矶分校于2017年10月25日下午4:49:22结束。 电话:2017年10月25日下午4:49:22。
Stack Overflow用户
发布于 2017-10-24 14:23:21
以下解决方案满足了我的需要：
按下重新计算按钮时，vba将检查当前Excel计算状态。在进行计算的情况下，直接启动计算Recalculate的VBA过程.如果计算模式挂起或正在计算，则只有本地工作表变量p_RecalcButtonClicked设置为true。在完成excel计算时，每个工作表在计算Worksheet_Calculate事件后触发事件。这样我们就可以指导Excel到Recalculate。
作为一种安全措施，我使用函数the related two questions from the above comment在子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 Functionhttps://stackoverflow.com/questions/46908493
复制相似问题
腾讯云开发者
