vba - 在自动计算之前等待外部数据加载？ Excel/VBA

Question

第一次发帖，一直在寻找解决方案，但还没有成功。

解释一下我的问题:条款:OPC = 用于过程控制的对象链接和嵌入 (OLE)(允许其他应用程序使用来自 PLC 标签的数据)PLC = 可编程逻辑 Controller (用于过程控制的计算机SCADA = 监控和数据采集(显示来自 PLC 的值并允许控制的接口(interface))

我有一个 Excel 工作簿，在 PLC 对某些值进行排序后，SCADA 系统(WonderWare > Intouch，软件)会在特定时间自动打开该工作簿。该工作簿使用 OPC 客户端填充其单元格，并使用以下方法访问它们:

=OPCLINK|EAST!'r[BELLWWPOWER]DailyValues_Z3[1,21]'

这很有效，但需要填充很多单元格，因此需要几秒钟的时间。

我想要自动发生的是在所有带有公式的单元格更改为这些公式的值之前，填充这些单元格并完成所有计算。然后，该工作簿将以新工作簿名称(“PowerReports-YesterdaysDate”)保存，并删除 VBA 代码，并关闭两个工作簿(不保存原始工作簿，以保留公式)。

这一切都运行良好，只是发生得太快，并且新保存的副本最终在所有具有 OPC 链接的单元格中只有“#N/A”。当我将第一个工作表的私有(private)子设置为“Worksheet_Activate()”而不是“Worksheet_Calculate()”时，代码不会自动统计并等待鼠标单击工作表的一个单元格(仅供引用:SCADA 系统打开此工作簿自动到工作表 1 以从工作表 1 的代码开始)。在这种情况下，新副本将成功保存，但当代码自动统计时，它太快了。如何在计算完成之前等待外部数据加载？

我尝试过一些事情(我不知道它们实现得有多成功......)，例如:-应用.计算-Application.RefreshAll-定时器- 尝试从 PLC 获取标志-检查剩余的#N/As

似乎如果一个循环或类似的东西立即运行，它不会让外部数据刷新。

Private Sub Worksheet_Calculate()

    ' When first sheet "Main" is activated, all formulas are replaced
    ' with their calculated values. The second sheet, "Monthly Values" is then activated

    Dim rng As Range, r As Range
    Set rng = Range("A1:D52")

    For Each r In rng
        If r.HasFormula Then
            r.Value = r.Value
        End If
    Next r

    Worksheets("Monthly Data").Activate

End Sub

Private Sub Worksheet_Activate()
    ' When second sheet "Monthly Values" is activated, all formulas are replaced
    ' with their calculated values. The sub routine, "SaveWithoutMacros" is then called

    Dim rng As Range, r As Range
    Set rng = Range("A1:BJ84")

    'Worksheets("Monthly Data").Calculate
    'If Not Application.CalculationState = xlDone Then
    'DoEvents
    'End If

    For Each r In rng
        If r.HasFormula Then
            r.Value = r.Value
        End If
    Next r

    Call SaveWithoutMacros
End Sub

Sub SaveWithoutMacros()
    'Purpose : To save a copy of the active workbook without macros

    Dim vFilename As String
    Dim wbActiveBook As Workbook
    '----------------------------------------------------------------------
    'Following two lines causes an error in Excel 97 - comment them out

    Dim VBComp As VBIDE.VBComponent
    Dim VBComps As VBIDE.VBComponents
    '----------------------------------------------------------------------

    ' Save to filename in format (yesterdays date) "PowerReports-DD-MM-YYYY"
    vFilename = ("D:\PowerReports\" & "PowerReport-" _
    & Format(Date - 1, "DD-MMM-YYYY") & ".xls")

    ActiveWorkbook.SaveCopyAs vFilename
    Set wbActiveBook = Workbooks.Open(vFilename)

    'Now strip all VBA, modules, userforms from the copy
    'This code is from Chip Pearson's website http://www.cpearson.com

    Set VBComps = wbActiveBook.VBProject.VBComponents

    For Each VBComp In VBComps
        Select Case VBComp.Type
        Case vbext_ct_StdModule, vbext_ct_MSForm, vbext_ct_ClassModule
            VBComps.Remove VBComp
        Case Else
            With VBComp.CodeModule
                .DeleteLines 1, .CountOfLines
            End With
        End Select
    Next VBComp
    wbActiveBook.Save ' saves new version after code is stripped

    ThisWorkbook.Saved = True ' sets save flag to true, does not actually save
    Application.Quit ' quits entire application, all workbooks
End Sub

抱歉，帖子太长了，看到其他人因为不够详细而被诟病，哈哈。

Answer 1

这现在应该对您有用，通过仅测试最后填写的单元格，然后您将确保在将它们更改为值之前所有数据都已包含

我们基本上告诉 worksheet_calculate 在最后一个单元格有公式之前不执行任何操作

注意:请参阅下面的 OP 答案，了解适用于此 SCADA 情况的修改代码

 Private Sub Worksheet_Calculate()

        Dim rngLastCell As Range
        Set rngLastCell = Range("D52")

        If rngLastCell.HasFormula Then

            Dim rng As Range, r As Range
            Set rng = Range("A1:D52")


            For Each r In rng
                If r.HasFormula Then
                    r.Value = r.Value
                End If
            Next r

            Worksheets("Monthly Data").Activate
       End If
    End Sub