I'm having an issue with a memory leak while I run some vba code I wrote to look at a source spreadsheet, pull new data, then do some work on it, and save it to other spreadsheets. The code then uses Application.OnTime to call itself again in a few minutes giving me a continually updating dataset. All the excel files involved are under 10MB. The result is after a few hours running, the excel process will be multiple gigabytes as well as the Kernel Memory Paged Pool. Alternatively I've tried controlling the looping from a Word macro so that I'm able to kill the excel process after each run completes. This keeps the excel process memory usage in check but the Kernel Memory Paged Pool still grows seemingly without end - after about two days the paged pool will be about 10GB.
I've seen some advice about being wary of using two dots with COM objects as a source of memory issues.
How do I properly clean up Excel interop objects?
https://www.add-in-express.com/creating-addins-blog/2013/11/05/release-excel-com-objects/
But from what I've seen, these don't address the issue if you're coding within the Microsoft Visual Basic for Applications side of excel? Does the two dot issue remain if my code is all in VBA?
If it does, how far do I need to take idea when dealing with things like ranges, rows, columns etc. For example, a common task is finding the number of used rows in a sheet which I do by:
Dim ws as Excel.Worksheet
Set ws = ThisWorkbook.Worksheets("name")
With ws
rowsize = .Range("A1", .Range("A" & .Rows.Count).End(xlUp)).Rows.Count
End With
Do you need to create variables that hold Worksheet.Range and Range.Rows to avoid double dots? If so what would the above code look like properly written to observe the no two dot rule of thumb?
PS
I've tried debugging the memory leak more directly by using poolmon.exe. This repeatedly shows CMNb as the culprit tag, but I can't seem to get any further down this debug path as I'm unable to locate the tag using strings and findstr as exampled in the below link:
