Discussion: Persisting variable values---Named range vs hidden sheet

[MichaelSchulz]

Let's say you have a variable, the value of which is determined during one event and you want to use that resulting value during a subsequent event.

I have seen several times the suggestion of writing those values to a hidden sheet, which then can be retrieved during a different event.

What I haven't seen suggested is creating a named range that refers simply to the value rather than to a cell or range of cells.

What are the pros and cons of each approach and is there another approach

 

[StephenCrump]

Your question is a little light on detail, e.g. it's not clear whether you need to store to Excel at all. Do you want the value(s) to persist after you close the workbook/Excel?

It's also not clear what you mean by "event"?

Depending on what you're trying to do, an appropriately scoped variable might suffice: Understanding Scope

Another possibility might be passing the value from one procedure to another.

 

[MichaelSchulz]

An event is an action that can trigger the execution of the specified macro, of which there are many. There are application level events, workbook level events, worksheet level events, UserForm level events, and Chart events.

The main issue I wish to address is the lifetime of a variable. When all code has finished running, the variable loses its scope and its value. There are times, however, when it may be useful for the value of a variable to be utilized again in a separately-run block of code.

Let's say that you have a block of code that is triggered during a worksheet level event—perhaps a Calculate event or Change event. The event itself could be almost anything. The point is that when that event occurs, your block of code runs. During the running of that code, the value of some variable is determined. The value of that variable is needed when some other block of code is run. However, that code is ran only when some other event occurs—perhaps a workbook level event, such as BeforeSave or BeforClose. Again, the exact event isn't important.

What is important is that the value of the variable is lost when at the completion of the first event-triggered code. The code finishes running and the variable loses its scope and value. You cannot simply pass the value to the next procedure because it is not yet time for that code to run.

In searching for a means of dealing with this, I read several posts that suggested having a hidden worksheet and storing the values there. Blocks of code that are run separately can then read values from that sheet. It seems this could be useful for large or amounts of data or complex data. If the variable value(s) are simple—such as a string or numerical value—this approach seems a bit much.

I never saw anyone suggest using VBA to create a Name range and simply assign the value of the variable as the RefersTo. Once the Name is created and the value is set, it is also stored as part of the workbook but without the overhead of storing a Sheet object. The value of the Name range can then be utilized other procedures. The value of the Name range can be modified by other procedures. In short, a Name range could be used a little like a string variable or a numerical variable.

This got me thinking that either this is such a simple approach that no one bothers talking about it or perhaps there is some consideration I haven't yet realized as to why using Name ranges in this way isn't a good idea (or there is something other approach that is better).

I hope to get a discussion going to learn what others think about this.

 

[Joe4]

Interesting concept. It seems like it could work in theory, but there are some things to think about, such as:
- Each procedure would need to know what name to look for. So I am guessing you are probably thinking of using some pre-determined names for your ranges, so as you set them, you know what to look for in your other procedures.
- The order of events and when procedures may be run needs to be taken into consideration also. Otherwise, if a procedure runs that is looking for a certain named range before it has been created, errors will occur.
- You need to be careful if rows may be deleted, as if you delete all the rows from a named range, you will have a named range that has an erroneous range error (the #REF error).

So it seems to me like it should be able to be done, but you might need to put in some tight controls around it, and maybe some error-handling code to account for trying to reference a non-existing named range, or a named range with an error in the range reference.

If you do decide to try it, post back here. I would like to hear how it all turns out.

 

[MichaelSchulz]

Interesting concept. It seems like it could work in theory, but there are some things to think about, such as:
- Each procedure would need to know what name to look for. So I am guessing you are probably thinking of using some pre-determined names for your ranges, so as you set them, you know what to look for in your other procedures.
- The order of events and when procedures may be run needs to be taken into consideration also. Otherwise, if a procedure runs that is looking for a certain named range before it has been created, errors will occur.
- You need to be careful if rows may be deleted, as if you delete all the rows from a named range, you will have a named range that has an erroneous range error (the #REF error).

So it seems to me like it should be able to be done, but you might need to put in some tight controls around it, and maybe some error-handling code to account for trying to reference a non-existing named range, or a named range with an error in the range reference.

If you do decide to try it, post back here. I would like to hear how it all turns out.

To further clarify, the idea of using Name range is that the range would be created by the VBA, e.g. ThisWorbook.Names.Add Name:="whateverNameYouWant", RefersTo:=valueOfVariable
So, yes, the reference to the Name would be known since it's created by your code. Also, since you assign the RefersTo to the value rather than to a cell or range of cells, then it won't matter if a User deletes and rows or columns. Furthermore, if there is a procedure that you know would be the final one ran, you could include a bit of code to delete the Name range that would no longer be needed.

 

[StephenCrump]

No reason why Names won't work just fine. But my preference for long term storage is an Excel worksheet. It's easier to develop/debug/update when everything's on display in the one place. Depending on the user, I may hide it or make it xlVeryHidden.

What is important is that the value of the variable is lost when at the completion of the first event-triggered code. The code finishes running and the variable loses its scope and value. You cannot simply pass the value to the next procedure because it is not yet time for that code to run.

Sorry, I should have read the Pearson article before linking in Post #2. I thought it talked about variable lifetime as well as scope.

Here's a succinct summary of lifetime: Excel VBA Variables Scope and Lifetime. Scope and Lifetime of Excel VBA Variables

Hence, for example:

'Module1 code
Public MyVar As String
'Module2 code
Sub DoSomething()

    MsgBox MyVar

End Sub
'ThisWorkbook
Private Sub Workbook_Open()

    MyVar = "I'm still here"
    
End Sub
'Sheet1 Module
Private Sub Worksheet_Change(ByVal Target As Range)

    MsgBox MyVar
    
End Sub

 

[MichaelSchulz]

No reason why Names won't work just fine. But my preference for long term storage is an Excel worksheet. It's easier to develop/debug/update when everything's on display in the one place. Depending on the user, I may hide it or make it xlVeryHidden.


Sorry, I should have read the Pearson article before linking in Post #2. I thought it talked about variable lifetime as well as scope.

Here's a succinct summary of lifetime: Excel VBA Variables Scope and Lifetime. Scope and Lifetime of Excel VBA Variables

Hence, for example:

'Module1 code
Public MyVar As String
'Module2 code
Sub DoSomething()

    MsgBox MyVar

End Sub
'ThisWorkbook
Private Sub Workbook_Open()

    MyVar = "I'm still here"
   
End Sub
'Sheet1 Module
Private Sub Worksheet_Change(ByVal Target As Range)

    MsgBox MyVar
   
End Sub

So what would happen if the value of MyVar was set or changed during the Worksheet_Change subroutine? When that subroutine ends, would MyVar still have the value?

 

[StephenCrump]

So what would happen if the value of MyVar was set or changed during the Worksheet_Change subroutine? When that subroutine ends, would MyVar still have the value?

Given the scope of MyVar in Post #6, yes, it will retain the new value.

 

[MichaelSchulz]

Given the scope of MyVar in Post #6, yes, it will retain the new value.

Yes, I see. I had once known this but forgotten. I got down a rabbit hole of thinking along a different line and just didn't think about this. Thank you for the reminder and clarification.