sbalhara
09-10-2021, 04:37 AM
I would like to find out how to release Excel RAM using VBA - I have tried closing and reopening workbook with VBA- but that does not release RAM used by Excel.
I would like to find out any ideas to try-I setup my vba macro to run every few minutes , and the RAM in my Excel keeps creeping up and eventually causes memory or hang /freeze. I am using Excel 2016.
---
If I use Application.Quit, it would release RAM as it exits the application altogether, but is it possible to restart Excel Application and load workbook using VBA
Is there any other way to release RAM.
Thank you. This code below is not needed for this question on how to release RAM, but just in case, here is the code I am using.
Sub HarryPotter()
With Worksheets("Data")
Stop_macro_timer
'do stuff
MainMacro
End With
End Sub
Public Sub MainMacro()
With Worksheets("Data")
httpqueryfordata
'do stuff
Start_macro_timer
End With
End Sub
Sub httpqueryfordata()
With Worksheets("activetick")
Dim Qy As QueryTable
Set Qy = Worksheets("activetick").QueryTables.Add(Connection:="URL;htp://localhost:5000/barData?symbol=" & sk & "&historyType=1&intradayMinutes=0&beginTime=" & DAY & x1 & "&endTime=" & DAY & x2 & "" _
, Destination:=Worksheets("activetick").Cells(m, location))
With Qy
.Name = "Qy"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
.Delete
End With
DoEvents
Set Qy = Nothing
End With
End Sub
Sub Start_macro_timer()
MacroTimerWhen = Now + TimeSerial(0, 0, 15)
Application.OnTime EarliestTime:=MacroTimerWhen, Procedure:="HarryPotter", _
Schedule:=True
macrotimerrun = MacroTimerWhen
DoEvents
End Sub
Sub Stop_macro_timer()
On Error Resume Next
MacroTimerWhen = macrotimerrun
Application.OnTime EarliestTime:=MacroTimerWhen, Procedure:="HarryPotter", _
Schedule:=False
End Sub
I would like to find out any ideas to try-I setup my vba macro to run every few minutes , and the RAM in my Excel keeps creeping up and eventually causes memory or hang /freeze. I am using Excel 2016.
---
If I use Application.Quit, it would release RAM as it exits the application altogether, but is it possible to restart Excel Application and load workbook using VBA
Is there any other way to release RAM.
Thank you. This code below is not needed for this question on how to release RAM, but just in case, here is the code I am using.
Sub HarryPotter()
With Worksheets("Data")
Stop_macro_timer
'do stuff
MainMacro
End With
End Sub
Public Sub MainMacro()
With Worksheets("Data")
httpqueryfordata
'do stuff
Start_macro_timer
End With
End Sub
Sub httpqueryfordata()
With Worksheets("activetick")
Dim Qy As QueryTable
Set Qy = Worksheets("activetick").QueryTables.Add(Connection:="URL;htp://localhost:5000/barData?symbol=" & sk & "&historyType=1&intradayMinutes=0&beginTime=" & DAY & x1 & "&endTime=" & DAY & x2 & "" _
, Destination:=Worksheets("activetick").Cells(m, location))
With Qy
.Name = "Qy"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
.Delete
End With
DoEvents
Set Qy = Nothing
End With
End Sub
Sub Start_macro_timer()
MacroTimerWhen = Now + TimeSerial(0, 0, 15)
Application.OnTime EarliestTime:=MacroTimerWhen, Procedure:="HarryPotter", _
Schedule:=True
macrotimerrun = MacroTimerWhen
DoEvents
End Sub
Sub Stop_macro_timer()
On Error Resume Next
MacroTimerWhen = macrotimerrun
Application.OnTime EarliestTime:=MacroTimerWhen, Procedure:="HarryPotter", _
Schedule:=False
End Sub