vba - 使用 application.ontime 重复运行宏时 Excel 使用过多内存

Question

我对 VBA 知之甚少，但我制作了一个每 15 分钟运行一次的宏，它只是在工作表中添加一个新行，其中包含第一行的值(它们是 rtd 函数)。我运行的时间越长，excel 使用的内存就越多，24 小时后它使用 1gb+ 的 RAM。无论如何我可以改进或阻止这种情况的发生，以便我可以运行代码几天？谢谢

Sub Store()
currow = Workbooks("Store data.xlsm").Worksheets("Sheet1").Range("A65536").End(xlUp).Row

Workbooks("Store data.xlsm").Worksheets("Sheet1").Cells(currow + 1, 1) = Format(Now, "dd/mm/yyyy HH:nn:ss")

Workbooks("Store data.xlsm").Worksheets("Sheet1"). _
   Range(Workbooks("Store data.xlsm").Worksheets("Sheet1").Cells(currow + 1, 2), _
    Workbooks("Store data.xlsm").Worksheets("Sheet1").Cells(currow + 1, 47)) = _
    Workbooks("Store data.xlsm").Worksheets("Sheet1"). _
    Range(Workbooks("Store data.xlsm").Worksheets("Sheet1").Cells(2, 2), _
    Workbooks("Store data.xlsm").Worksheets("Sheet1").Cells(2, 47)).Value

Application.OnTime Now + TimeValue("00:15:00"), "Store"
End Sub

Answer 1

从早期的实验中，我还发现 Application.OnTime不应该调用自己(它需要调用单独的过程)，并且递归以不同的方式处理

试试这两个版本:

V1 - Application.OnTime

Option Explicit

Public Sub UpdateStore()
    Application.OnTime Now + TimeValue("00:15:00"), "Store"
End Sub

Public Sub Store()
    Dim curRow As Long, firstRow As Range, lastRow As Range

    With Workbooks("Store data.xlsm").Worksheets("Sheet1")
        curRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
        .Cells(curRow, 1) = Format(Now, "dd/mm/yyyy HH:nn:ss")

        Set firstRow = .Range(.Cells(2, 2), .Cells(2, 47))
        Set lastRow = .Range(.Cells(curRow, 2), .Cells(curRow, 47))
    End With
    lastRow = firstRow.Value
    UpdateStore    'recursive call
End Sub

V2 - sleep API(已编辑 - 非递归)

Option Explicit

#If Win64 Then      'Win64=true, Win32=true, Win16= false
    Private Declare PtrSafe Sub Sleep Lib "Kernel32" (ByVal dwMilliseconds As Long)
#ElseIf Win32 Then  'Win32=true, Win16=false
    Private Declare Sub Sleep Lib "Kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
#End If

Public Sub StoreSleepAPI()
    Dim curRow As Long, firstRow As Range, lastRow As Range, counter As Long

    For counter = 1 To 400    '<-- adjust this to "how many hours" * "4" ( times / hour)

        With Workbooks("Store data.xlsm").Worksheets("Sheet1")
            curRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
            .Cells(curRow, 1) = Format(Now, "dd/mm/yyyy HH:nn:ss")

            Set firstRow = .Range(.Cells(2, 2), .Cells(2, 47))
            Set lastRow = .Range(.Cells(curRow, 2), .Cells(curRow, 47))
        End With

        lastRow = firstRow.Value
        Sleep 900000    '1000 = 1 second, 900000 = 15 minutes
        DoEvents
    Next
End Sub

sleep 也使用更少的 CPU