Release all object variables from Memory

Re: Release all object variables from Memory

I can't think of a way other than using an array of objects then cycling through them with a loop.

But doing it that way you would lose names for your objects which I imagine could be awkward when writing code.

Re: Release all object variables from Memory

It depends on where you put your dim statement. Put it inside your sub so it will be release once your macro goes out of the sub.

Re: Release all object variables from Memory

In Visual Basic / VBA the memorys are dropped atteh end of procedure they are whats know as dead, but are still in momory as public avalible memory spaces and can / will be over written next time around. Taking into account Windows / Excel memory managemenst are a little bit differently from most other applications ie excel manages a memory allocation given by windows and windows passes over controil to excel only stepping in at times of crisis, it is good practive to manually code kill the variables as you have outlines

ie
use VBNullstring, “” amd NOTHING to clear these so they do not stay in memory at all.

It is good programming to declair variables in side their procedure and if needs be inside more if they are in more than one prodecure, CONST are very good but cautions I would only really use strings as constants and only if they are called many times, many programmes enjoy setting many const to save code lines, can be good but not always. see how it pains out.

I use valKillA_xx for each procedure to take care of this ie..

Option Explicit


Sub Test1_NotSoGood()
' written by: Jack in the UK
' J & R Excel Solution - [url]www.excel-it.com[/url]
' Excel xP +


Dim T1Mess As String
Dim J_R As String
T1Mess = "Jacks Test Message"
J_R = "J & R Excel Solitions - www.excel-it.com"
MsgBox T1Mess, vbInformation, J_R
Exit Sub
End Sub

Option Explicit


Sub Test2_Better()
' written by: Jack in the UK
' J & R Excel Solution - [url]www.excel-it.com[/url]
' Excel xP +


' ValKill added at end of Procedure
Dim T1Mess As String
Dim J_R As String
T1Mess = "Jacks Test Message"
J_R = "J & R Excel Solitions - www.excel-it.com"
MsgBox T1Mess, vbInformation, J_R
theEnd:
' jiuk - do something --
theEnd2:
T1Mess = vbNullString
J_R = vbNullString
Exit Sub
End Sub

Option Explicit


Sub Test3_Best()
' written by: Jack in the UK
' J & R Excel Solution - [url]www.excel-it.com[/url]
' Excel xP +


' ValKill added at end of Procedure
Dim T1Mess As String
Dim J_R As String
T1Mess = "Jacks Test Message"
J_R = "J & R Excel Solitions - www.excel-it.com"
MsgBox T1Mess, vbInformation, J_R
theEnd:
' jiuk - do something --
theEnd2:
Call valKillA_Test3_Best
Exit Sub
End Sub

Sub valKillA_Test3_Best()
' written by: Jack in the UK
' J & R Excel Solution - [url]www.excel-it.com[/url]
' Excel xP +


theEnd:
' now these will not really be required as dropped on procedure exit and will fail
' no longer held as variable


T1Mess = vbNullString
J_R = vbNullString
' jiuk - will bug as not defined
' jiuk - REM these two line code to fix


Exit Sub
End Sub

Re: Release all object variables from Memory

Ryan

You don't really need to do this especially with variables like strin, integer etc.

The memory the variables used will be available, but it will still contain the information for the variable.

The only time you really need it is when you are working with other applications.

For example say you have created a new instance of Access and run your code on it.

Unless you set that object to Nothing the Access instance will still exist.

Re: Release all object variables from Memory

The "End" statement may be relevant to this discussion. (Learned of it while researching variable scope/lifetime on OzGrid :)) But not sure if it's going overboard.

Microsoft info here:

http://msdn.microsoft.com/en-u…ry/0wt87xba(v=vs.71).aspx

Re: Release all object variables from Memory

i have soloved this before myexcel.quit use

MyExcel.DisplayAlerts = False
            MyExcel.SaveWorkspace()