Scope & Persistence

[CaliKidd]

I'm a novice VBA programmer and I am working on a workbook that is designed to be run over and over again. To make it more user-friendly, the first time the workbook is opened, a userform is displayed and gathers information from the user and then stores it to a hidden worksheet. For example:

Worksheets("SETUP").Range("A1").Value = txtUserName

Once the workbook is saved and run again, the saved information is retrieved, like this:

txtUserName = Worksheets("SETUP").Range("A1").Value

I use this "retrieval" code in various forms and subs that use the information saved in this hidden worksheet.

The whole point of doing this is to prevent the user from having to re-enter the same information each time the workbook is run. They enter it once and then don't have to bother with it again from that point forward.

There are a couple of problems with this approach, however:
1. For every form or sub that uses stored entries, I have to put the code in to retrieve the value (see second code example). This seems redundant.
2. While I am able to "get away" with storing and retrieving values this way in Excel (thanks to having a worksheet I can read/write to), I won't be able to use this approach when using other Office programs that have an entirely different object model.
3. Finally, while this approach works, my gut tells me that this method of retrieving values by object reference is inefficient and slow because there are about 30 values that I store in different cells, many of which are retrieved multiple times by various forms and subs.

I'm hoping there is a better way!

I've been reading up on variable scope and experimenting with it, but I'm still having some problems. I created a basic module (Module1) and declared these variables in the General Declarations section, like this:

Public txtUserName as String

My understanding is that this allocates memory for this variable and will make the txtUserName global to my project.

My question, however, is where should I put the code that retrieves the stored entries and stores them in these global variables?

I tried putting it in the Workbook_Open sub, thinking that I could load in all global variables up front when the workbook is opened, but when the sub ends the variables are erased from memory. I believe this is the case because after the workbook is opened, I have a command button that launches a form that uses the txtUserName variable, but the value it is retrieving is "". I also checked for the value in the Immediate Window by typing ? txtUserName and it showed no value.

I also tried declaring the variables in the General Declarations section of Module1 as Static, but I received a compile error saying that I couldn't use Static outside of a procedure.

What I am trying to accomplish is:
1. Declare global variables once and have them accessible from anywhere in my project.
2. Store values and be able to retrieve/transfer them to these global variables so I can use them as needed throughout my project.
3. Ensure these variables are not accessible from outside my project.
4. Make this is a self-contained, standalone workbook. In other words, not use a separate database to read/write to.

Is this possible? Is there a better approach that what I'm attempting to use?

 

[Ralajer]

Variables defined outside a sub or function using the Public keyword should have scope across all the modules in the workbook and persistence until the the workbook is closed. In my experience runtime/compile errors can invalidate the values.

Based on your needs it might be useful to use a Class (Class module).
Example:
Class Module (Name) = CPerson

' Public Class Variables Accessed through '.' operator
Public Name As String
Public age As Integer
Public hair As String

' Class Constructor called when
'       Set VarName = New ClassName
'   is called in another module
'
Private Sub Class_Initialize()
    SetValuesFromSheet
End Sub

' Private Class Helper Method(s) cannot be accessed outside the class module

''' Set values in memory to values on sheet
Private Sub SetValuesFromSheet()
    Name = shtHidden.Range("B2").Value
    age = shtHidden.Range("B3").Value
    hair = shtHidden.Range("B4").Value
End Sub

' Public Methods accessible outside the class module

''' Reset values in memory to values on sheet
Public Sub ResetValuesFromSheet()
    SetValuesFromSheet
End Sub

''' Overwrites values on sheet with values in memory
Public Sub SetValuesToSheet()
    shtHidden.Range("B2").Value = Name
    shtHidden.Range("B3").Value = age
    shtHidden.Range("B4").Value = hair
End Sub

Create a instance of the class by using the following
Module1

' Global Variable Person, As CPerson corresponds to Class Module (Name)
Public Person As CPerson

Public Sub setPerson()
    'Checks if Person Class is already set
    If Person Is Nothing Then
       Set Person = New CPerson    'Creates new CPerson Object, Call Class_Initialize
    End If
End Sub

At the beginning of any code that uses the global variables call the setPerson() Sub. That will check to see if the variable is defined redefining it from the sheet's values if it is not.

Module 2 - Code that use global variables

Sub PrintPerson()
    setPerson
    MsgBox (Person.Name & " is " & Person.age & _
            " years old and has " & Person.hair & " hair.")
End Sub

Class variables and methods are accessed by using the '.' operator. To change the name variable of Person and want to write it to the sheet you would do this.

Person.Name = "New Name"
Person.SetValuesToSheet

The advantage to a using a class is that you can encapsulate all the variables and related functions/methods under one object.

 

[xenou]

I've also used methods in standard modules to imitate classes (with a "get name" method, for instance - there's no advantage over real classes but it's a way to do the same thing). So instead of constantly repeating the worksheets("blah").Range("blah").Value stuff you can just write MyGetMyName().

Also not so bad would be to use Excel names to store these values - Chip Pearson has a good article on Excel names in VBA. They are basically easy but take some getting used to.

Keeping everything in public variables (including instantiated classes) *does* have a downside - any VBA crash and you can lose them all.

 

[CaliKidd]

Rob, thanks for your suggested solution. I am not experienced in class modules yet, but I think the basic technique you've put forth is to use a class module to create an encapsulated object that loads the values stored on the hidden worksheet and then use them as properties of that object. Is that correct?

Also, can you elaborate on this, please:

Variables defined outside a sub or function using the Public keyword should have scope across all the modules in the workbook and persistence until the the workbook is closed. In my experience runtime/compile errors can invalidate the values.

Are you saying that the approach I tried should have worked in theory, but doesn't in practice? Or was I barking up the wrong tree to begin with and your comment pertained to something else? The reason I'm asking is your first sentence agrees with what I've read and understood about declaring variables with Public scope. That is why I was declaring them in the general declarations section of a basic module. I was hoping that would make them persistent (as long as the project was open) and accessible from any form or sub in the project. But it didn't work, so I'm just trying to understand why so I can become a better programmer.

Xenou, thanks for your help, too. I googled Chip Pearson and found his site. It seems to have a lot of great info, so I'll make it a point to go through his site to edify my VBA knowledge. I am a little concerned about your "VBA crash" statement, however. Are you suggesting VBA is not stable? What has been your experience? I'm learning to use error handling routines in my code, but if the VBA platform itself is prone to crashing, then that makes me nervous.

 

[mikerickson]

...Keeping everything in public variables (including instantiated classes) *does* have a downside - any VBA crash and you can lose them all.

One way to avoid that problem is to use functions rather than Public variables.
For example, from the OP

Function txtUserName() As String
    txtUserName = ThisWorkbook.Worksheets("SETUP").Range("A1").Value
End Function

The advantage of this approach it that it is robust in the face of the unexpected.
The cost, run time.
(Also the use of code name for the sheet rather than tab name would make it more robust and (I believe) faster)

 

[RoryA]

The method you had should have worked. Are you sure you hadn't declared the variables again in any of the procedures intended to use the public versions?

 

[Ralajer]

Yes, you summed up what I was trying explain about classes.

Sorry for not being clear about Public variables but yes in theory that is how it works. My use and testing of Public variables in practice supports that to a large degree as well. The odd VBA crash will, as mention by xenou, cause the variable to be lost.

I assumed that you could declare a Public variable in the workbook code module with Sub Workbook_Open() but I tested that and it didn't carry over. If you want to load when opening call a sub in standard module from the workbook module.

'Workbook Code Module
Private Sub Workbook_Open()
	StartUP
End Sub

'Module1
Public TESTVAR as String
Sub StartUP()
	TESTVAR = "This is a test"
End Sub

This works for me having tested it to a limited extent. If you are still having problems post your code.

Depending your application it might not matter, performance wise, which of the several sugested approaches you use. Using Public variables will be faster but it has risks that might outweigh speed benefits. Those risk can be handle but it might not be worth the added complexity.

 

[xenou]

I am a little concerned about your "VBA crash" statement, however. Are you suggesting VBA is not stable? What has been your experience? I'm learning to use error handling routines in my code, but if the VBA platform itself is prone to crashing, then that makes me nervous.

In my experience VBA is very stable but I'm also very careful It depends on your tolerance for crashes - if other users are using your application, you should invest more time in data validation and error handling, and test more thoroughly - if tolerance is zero then you may choose not to rely as much on public variables within your vba project (that must persist for the entire session). BTW Mike's post is in a nutshell what I was aiming at too.

 

[CaliKidd]

Thanks, you guys are really smart. I'll be happy when I can get to your level of expertise. Forums like this really help what the books don't cover in detail.

I have a few additional follow-up questions.

One way to avoid that problem is to use functions rather than Public variables.
For example, from the OP

Function txtUserName() As String
    txtUserName = ThisWorkbook.Worksheets("SETUP").Range("A1").Value
End Function

The advantage of this approach it that it is robust in the face of the unexpected.
The cost, run time.
(Also the use of code name for the sheet rather than tab name would make it more robust and (I believe) faster)

Mike, could you provide a brief explanation WHY using the code name for the sheet is more robust than using the tab name? I've not heard that last one before so you've piqued my curiosity.

The method you had should have worked. Are you sure you hadn't declared the variables again in any of the procedures intended to use the public versions?

Well, I am relieved to know that it should have worked and I wasn't barking up the wrong tree. When it didn't work, I had declared the Public variables in more than one place in an effort to try and get it to work across the project, so that's probably the culprit. In any case, I've since modified my code with Rob's class solution and it's working, so I'll stick with it for now.

Xenou and Rob, thanks again. I'm getting a better understanding of the many ways to solve a problem in VBA as well as the trade-offs a programmer often has to make. I want my application to run as fast as possible, but I don't want it to be less stable. I thought using Public variables would be better than referencing cell values on the hidden spreadsheet, but I didn't take into account the possibility that VBA's memory management might be prone to occassional error. I do like the class solution in that it encapaulates things and also enables intellisense when using the object, but I also understand that if that class object bombs then it will be fatal since I've put all of my eggs (stored values) in one basket.

 

[mikerickson]

With an un-hidden sheet, the user can change the tab name. The code name cannot be changed.