当使用application.ontime重复运行宏时，Excel使用过多的内存 [英] Excel using too much memory when repeatedly running macro with application.ontime

问题描述

我对VBA知之甚少，但是我制作了一个每15分钟运行一次的宏，该宏仅向工作表中添加一个新行，其中包含第一行(即rtd函数)中的值.我运行的时间越长，excel使用的内存就越多，并且24小时后它正在使用1gb +的RAM.无论如何，有什么我可以改善或阻止这种情况的发生，因此我可以将代码运行几天?谢谢

I know very little about VBA but I made a macro which runs every 15 minutes that just adds a new row to the worksheet which contains the values from the first row (which are rtd functions). The longer I have this running, the more memory excel uses and after 24 hours it is using 1gb+ of RAM. Is there anyway I can improve this or stop this from happening so I can run the code for days? Thanks

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

推荐答案

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

From earlier experiments I also found that the Application.OnTime is not supposed to call itself (it needs to call a separate procedure), and the recursion to be handled in a different way

尝试以下两个版本:

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-睡眠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

---

睡眠也使用更少的CPU

---

Sleep is using less CPU as well

这篇关于当使用application.ontime重复运行宏时，Excel使用过多的内存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！