MsgBox Required depends on selection

[Marc L]

As I never met any issue with arrays, even huge, but only with Object variables.​

But I erase only global variables whatever array or not …​

​

 

[BobBridges]

Oh, global. I use them occasionally, but not much. Wait, is "global" the same as "public at the module level", ie available to all other modules in a project? That's what I use occasionally. And that's what you're careful to set to Nothing at the end of execution?

 

[Marc L]

Before the end of a VBA procedure :​

• any object variable set to Nothing whatever where it stands
• reset any variable at module level whatever just via Dim, Private or Public : via Erase if array, Empty if Variant, 0 if numeric, "" if String …

 

[rlv01]

"Set owb = Workbooks("MyBook.xlsx").

I know @Marc L disagrees with me (as he has explained above) , but I don't think that kind of a standard object needs to have a Set owb = Nothing to close it out UNLESS it is a module level global variable or you are using the assignment statement within a looping structure.

In terms of variable memory management, VBA does not use true garbage collection. Instead it's got a mechanism called "reference counting" so something like Set owb = Workbooks("MyBook.xlsx" increments the counter. Set owb = Nothing is a way to explicity decrement the reference counter. But the same thing happens if the object is declared in a Function or Sub and the Function or Sub terminates*. It's why I don't worry to much about not including a Set WB = Nothing statement in this kind of subroutine structure.

Sub Test()
    Dim WB As Workbook
    
    Set WB = ActiveWorkbook

    Debug.Print WB.Name
End Sub

That said, there nothing wrong with being thorough and doing it Marc's way. Also are some 3rd party libraries (the old JetEngine DAO database lib springs to mind) where the overwhelming consensus was that those objects absolutely needed to be de referenced manually.

* (though I suppose there could be an issue if the variable was declared as Static. I'll have to think about that).

 

[Marc L]

Yes, like I wrote « Most of the time this is not a concern to not free objects variables »​

in particular when playing on VBA forums but, when it's for a pro concern,​

according to what I met, as Excel loads all in memory but badly manages it,​

as data leak is common with Excel so the better is to free the object variables.​

​

Back to post #10 there is no sense to create an object variable to just use it once instead of working directly with the object …​

​