scope and lifetime of vba objects

[whburling]

This question is about scope and lifetime of vba objects

I am unclear as to where I must instantiate collections and their objects


for an example

assume I have a Main program
within it i generate some values
and pass them off to a subroutine to produce data.

Somehow I wish to put the output of the subroutine
into objects which in turn are added to a collection

Can I instantiate the collection and its collected objects within the
subroutine and pass them out?

In the language C, that is a poor approach because of data lifetime issues
and memory management (usually one wishes to create and kill memory
in the same code module so that memory "leaks" (unused memory is not
released and hence one gradually runs out of memory to allocate) do not
occur.

What happens in VBA?

I could instantiate an empty collection and pass it byref to the subroutine
wherein inside of the subroutine I create the objects that will be added to
the collection. But I am worried about the lifetime of those objects added to
the collection when the subroutine passes focus back to the main program.

Please note that I can't instantiate the objects that will be added to the
collection before the subroutine does its calculations as I can not predict
how many objects I will need to create

I am sure multiple solutions exist to solve my problem. In C one might use
global variables. I would like to avoid such a concept if possible.

thank you

[leelnich]

What happens in VBA?

Google 'VBA lifetime scope' to get tons of stuff. Here's a link which might address the quoted question.
https://msdn.microsoft.com/en-us/library/35styb3a.aspx

[xlnitwit]

Hi,

The two simplest solutions are passing the collection into the subroutine ByRef as you mention, or converting the subroutine to a function that returns the Collection as a result.

In VBA you have little real control over memory management- certainly none over when memory is actually reclaimed. For objects, there is a simple reference counter and the object's memory will not be released before that counter reduces to 0, but you cannot control at what point thereafter the release will actually occur.

[whburling]

xlnitwit,

Thank you for taking your valuable time to reply.

though we seem to have a solutino for for collection, how do we adress the object that gets placed within the collection if it was instantiated within the subroutine or function?

Do we care that it was instantiated WITHIN a subroutine or function and passed out?

bil

[Leith Ross]

Hello whburling,

In VBA a Collection object's lifetime depends on how it is created: locally or globally. Once created, the Collection is instantiated using the New keyword. This can only be done within the body of a procedure (Sub or Function). All items added to the collection remain accessible until the procedure ends, i.e. the End Sub or End Function statement is executed. Optionally, the Collection can be destroyed by setting it to the special object Nothing.

[MarvinP]

Hi bil,

Here is my favorite site for this topic:
http://www.cpearson.com/Excel/Scope.aspx

If you look at the index of the site above there are lots of other great topics and explanations to be found.

[xlnitwit]

Do we care that it was instantiated WITHIN a subroutine or function and passed out?

No, we don't. The same rules will apply for that object, as Leith Ross said- its reference will remain until either it is specifically set to Nothing or the containing Collection object is destroyed.

[whburling]

Hi Leith!

Ok....let me share my understanding as it is at the moment. If it disagrees with your view, then don't think I am dead set on defending my point of view. Just help me see more clearly.

As far as I can see, VBA mixes up several concepts.

Declaring....and scope.

Declaring, to me, is an instruction to a VBA compiler to associate a variable with a datastructure. Data structures do not occupy memory. they are merely a scheme that states that if this variable actually existed, then use the scheme to store
data of interest. Declaring indicates nothing about actual memory used. Merely the amount of memory and the way it is
organized if it did exist. Dim A as long tells the compiler that if A is encountered, it needs x bytes and is read in a certain manner. Dim A as SpecialObject is no different.

My interpretation of Chip Pearson explanations suggests that when the compiled code is run and encounters a statement with the variable indicated above as follows: A = ASpecialObject then the run time code (1) copies the data from the datastructure of ASpecialObject and (2) assigns physical memory to A that conforms to the needs of A (not ASpecialObject) and places data in A according to the datastructure assigned to A. In summary: if both data structures are identical (which is checked at compile time). then memory is assigned at run time in the proper manner while executing the above statement.

now scope....(the ability to see a specific variable inside a procedure, or outside of a procedure but still within a module, or outside of a module but still within a project, or outside a project and seen by all workbooks)....is interestingly not assigned at time of assigning memory!!!!! but at compile time. I suspect the compiler looks for special key words (as in private or public) in a declaration and the location of a declaration and at compile time determines the scope of a variable.

Thus in my original example, an object created in its own class module must be declared in my main program at the top of the module that my main procedure is written in ....and that defines its scope (I suspect).

It is actually created in the subroutine that I mentioned but because of the location of the declaration, the compiler ensures it is seen global to all procedures within the module regardless of where memory is actually assigned to the variable.

what do you think?

[MarvinP]

Hey,

My understanding is when you "DIM" a variable, it creates room (memory space) for that variable and points to that space using the name you used in the DIM statement.

Please Login or Register  to view this content.

sets aside memory, within the subroutine or function you put in it that is the size of a "Double" and points to that memory spot using the name "LastRow".

When the subroutine hits an "END" it clears all the memory it was using for the Sub and gives back the memory and variables. This is called "Popped off the stack".

If you declare a Global variable, it can be seen by all the subroutines "within its scope" and you don't need to declare them in the individual subs.

Does that make sense to you???

Short story... In early computer days, there was this guy who liked to write fast code. He would challenge me often to race. I would always declare the variables that I knew would be used the most, first! Then when my code ran it wouldn't need to look through the whole list of variable names to find the spot in memory that held the value. He didn't think it mattered the order of the Dim statements but the computer needs to keep this list and then find the memory location where they are stored. BTW - just declaring the most used variables first kept me winning for a long time.