Ask a new question

How to reset variables?

How to reset/release variables at the conclusion of an excel VBA loop procedure or before exit sub?

For instance,

Dim Report2Sheet As Worksheet 

Dim ptSheet As String

Dim i As Long

Dim PatientExist As Boolean
Dim LabDate As Date

At end:

set Report2Sheet = Nothing

ptSheet = vbNullString

How about the Long, Boolean, and Date variables?

Thanks

 

Answer
Answer

If I understand correctly, then before statement 'end sub', to clear memory and improve speed, I only need to say:


And not bother about clearing the other variable types at end?


How do I clear the LabDate value that was picked up in first loop so that it would not be carried to next loop?

 

The answers for this 3 questions are:

1.) No, there is no need to set a local variable to Nothing.
2.) Yes, do not bother about clearing any local variable.
3.) If a sub starts any local variable is cleared by VBA, unless you have it declared as STATIC.

And your loops should look like this:

  Dim LabDate As Date
  For i = 1 To 10
    If Expired Then
      LabDate = Sheets(i).Range("A1").Value
    End If
    If LabDate > 0 Then MsgBox "Last LabDate in First Loop = " & LabDate
  Next
 
  LabDate = 0
  For i = 1 To 10
    If Expired Then
      LabDate = Sheets(i).Range("A1").Value
    End If
    If LabDate > 0 Then MsgBox "Last LabDate in First Loop = " & LabDate
  Next

BTW, did Sub CopySheet work?

Andreas.

4 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Answer
Answer
There is no need to set any local variable to Nothing at the end of a sub, because the memory manager does this automatically.

If you call a sub the (VBA-) stack is increased automatically, any local variable is place inside this memory structure and the stack shrinks automatically if the sub ends and any local variable is removed.

Only global object variables in normal modules should be set to Nothing to release the object from memory. Forget anything else.

Andreas.

4 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated March 19, 2024 Views 128,318 Applies to: