CODEWITHSUNDEEP
vbaexcel
AndrewK
AndrewK

Reputation: 293

Excel using too much memory when repeatedly running macro with application.ontime

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

Upvotes: 1

Views: 1889

Answers (2)

paul bica
paul bica

Reputation: 10715

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

Try these 2 versions:

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 (Edited - Non recursive)

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 is using less CPU as well

Upvotes: 3

user8221156
user8221156

Reputation:

I assume, based on you explicit identification of the objects involved that the Sub Store() is in a general module. If not, make sure it is declared Public and mod the call in Sub b() below appropriately.

You need to separate the action from the recall. Place two subs in a general module (EDITED - I mixed them up earlier on):

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

 Public Sub b()
    Call Store
 End Sub

Replace the Application.OnTime Now + TimeValue("00:15:00"), "Store" in your Sub Store() with Call a. That's it.

The OnTime works when it is in a General module (not in an Object module, e.g. a Worksheet module). Also, it should call a sub in a General module (Insert --> Module). I just know this works (took me a while to figure this out long time ago). In addition, this arrangement allows the "Store" routine to finish running and VBA engine to clear out the memory it used to run the "Store".

Still, do not just trust me (or yourself either): always test!

Upvotes: 1

Related Questions