文件已关闭时，Excel VBA自动运行

张俊|

我有一个Excel文件，当用户打开文件时，该文件应每30分钟自动运行一次功能并关闭文件。

具体来说，我在ThisWorkbook对象下有以下代码：

Private Sub Workbook_Open()
TimeOpened = Now
Application.OnTime TimeOpened + TimeValue("00:30:00"), "Check_Average_NA"
Application.OnTime TimeOpened + TimeValue("01:00:00"), "Check_Average_NA"
Application.OnTime TimeOpened + TimeValue("01:30:00"), "Check_Average_NA"
Application.OnTime TimeOpened + TimeValue("02:00:00"), "Check_Average_NA"
Application.OnTime TimeOpened + TimeValue("02:30:00"), "Check_Average_NA"
Application.OnTime TimeOpened + TimeValue("03:00:00"), "Check_Average_NA"
End Sub

该功能Check_Average_NA另存Module1为

Sub Check_Average_NA()

Dim Avg As Double, Na As Long
Dim LastAvgRow As Integer, LastNaRow As Integer, LastTimeRow As Integer


Avg = Application.WorksheetFunction.Average(Workbooks("Holdings_Pricing - Dec").Worksheets("Missing dates").Range("A1:XFD10000"))
Na = Application.WorksheetFunction.CountIf(Workbooks("Holdings_Pricing - Dec").Worksheets("Missing dates").Range("A1:XFD10000"), "#N/A N/A")

LastAvgRow = Workbooks("Holdings_Pricing - Dec").Worksheets("Input, Average, NA").Cells(1000, 1).End(xlUp).Row
LastNaRow = Workbooks("Holdings_Pricing - Dec").Worksheets("Input, Average, NA").Cells(1000, 2).End(xlUp).Row
LastTimeRow = Workbooks("Holdings_Pricing - Dec").Worksheets("Input, Average, NA").Cells(1000, 3).End(xlUp).Row

Workbooks("Holdings_Pricing - Dec").Worksheets("Input, Average, NA").Cells(LastAvgRow + 1, 1) = Avg
Workbooks("Holdings_Pricing - Dec").Worksheets("Input, Average, NA").Cells(LastNaRow + 1, 2) = Na
Workbooks("Holdings_Pricing - Dec").Worksheets("Input, Average, NA").Cells(LastTimeRow + 1, 3) = Now

If LastAvgRow = 10 And LastNaRow = 10 Then

Else
    If Workbooks("Holdings_Pricing - Dec").Worksheets("Input, Average, NA").Cells(LastAvgRow, 1) = Workbooks("Holdings_Pricing - Dec").Worksheets("Input, Average, NA").Cells(LastAvgRow + 1, 1) And Workbooks("Holdings_Pricing - Dec").Worksheets("Input, Average, NA").Cells(LastNaRow, 2) = Workbooks("Holdings_Pricing - Dec").Worksheets("Input, Average, NA").Cells(LastNaRow + 1, 2) Then
        With Workbooks("Holdings_Pricing - Dec").Worksheets("Missing dates").UsedRange
            .Value = .Value
        End With
    Workbooks("Holdings_Pricing - Dec").Save
    Application.Quit
End If

End If
End Sub

所以我要做的是每30分钟检查一次平均值并计算“＃N / AN / A”的数量，将其记录在工作Input, Average, NA表中，如果值与值相同，则保存并关闭文件30分钟前录制的

问题是..当我不需要打开此文件并因此将其关闭时，该文件会随机打开自身并尝试运行代码。有谁知道为什么会发生以及如何预防呢？

谢谢。

蒂姆·威廉姆斯

像这样（未经测试）

在本工作簿中：

Private Sub Workbook_Open()
    StartTimer
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    StopTimer
End Sub

在常规模块中：

Const MACRO_NAME As String = "Check_Average_NA"

Dim colTimes As Collection 'global to store scheduled times

Sub StartTimer()
    Dim arrTimes, e, t
    arrTimes = Array("01:00:00", "01:30:00", "02:00:00", "02:30:00", "03:00:00")
    Set colTimes = New Collection
    For Each e In arrTimes
        t = Now + TimeValue(e)
        Application.OnTime t, MACRO_NAME
        colTimes.Add t
    Next e
End Sub

Sub StopTimer()
    Dim t
    If Not colTimes Is Nothing Then
        For Each t In colTimes
            Application.OnTime t, MACRO_NAME, schedule:=False '<< cancel the timer
        Next t
    End If
End Sub

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。