VBA to Activate & Recalculate Workbooks Causing Physical Memory Usage Growth

[Drewf2000]

Background: I have an add-in from a third party that allows me to collect values from a historian server. The workbook is filled with these values and uses them to display various graphics and values. I have two workbooks like this that rotate every 5 minutes on a monitor 24/7. The vba below calculates the workbook every five minutes, and then activates each workbook in rotation.

Issue: As the vba code runs, the physical memory usage of excel grows constantly until the graphics are unable to be displayed. It does not give an error or crash. Excel just stops updating the graphics and the pc becomes very slow to respond. This usually takes about 3 days. At that point I can call up task manager and see an excessive amount of physical memory in use and end the excel.exe task and restart excel, it will then run smooth for another 3 days.

The code is seen below, are there any inherent issues with the code that may cause this?

Public RunWhen As Double
Public Const cRunIntervalSeconds = 300 ' Timer Interval
Public Const cRunWhat = "TheSub"  ' the name of the procedure to run
Public whichsht As Variant

--------------------

Sub StartTimer()
    RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
        Schedule:=True
End Sub

--------------------

Sub TheSub()

Dim sht1 As Worksheet
Dim sht2 As Worksheet

Set sht1 = Workbooks("Workbook1.xlsm").Worksheets("Display")
Set sht2 = Workbooks("Workbook2.xlsm").Worksheets("Display")

Application.CalculateFull


If sht1 Is ActiveSheet Then
    sht2.Activate
    Else
    sht1.Activate
End If

StartTimer  ' Reschedule the procedure

End Sub

-------------------------

Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
        Schedule:=False
End Sub

 

[dchaney]

Is this third part software copying this data to the clipboard? if so it may be causing the clipboard to fill up and dragging the system down. If it is using copy to get the data try to add the following;

Application.CutCopyMode = False

 

[Drewf2000]

It's proprietary so I can't actually see, but it's worth a try.

Also, the increase in memory usage seems to be correlated with the graphics, displays with more graphs seem to suffer from this issue more than others.