Coding Technique Question Regarding Varibles

[RunTime91]

Greeting All!

The Excel program I work with uses many worksheets and many ranges; most of these ranges are used repetitively, thus I am setting these sheets & ranges as variables throughout my code.

I recently read where a coder, in addition to declaring his variables as public also 'Set' them in the Workbook Open event.

I as well have mine set as 'Public' but I am wondering from a technique and/or speed perspective is it a good idea to 'Set' your variables in the Workbook Open Event or just set them at the beginning of each event and then unset them at the end of each event?

Or...is there a better way altogether not mentioned here.

Thanks...

 

[BarryL]

I doubt you could see the difference if you did declare them when the workbook opened, declaring variables works in the memory so it's not labour intensive for the sheet AFAIK.

while you mention it, "Set" is only for Objects, we "Let" for values although we often drop the "Let".

 

[Eric W]

I'd recommend defining the ranges as Names. No need to put that in the Open event. There's a couple ways to do that. Select the range and put the name in the name box, or go to the Name manager on the Formula tab. Then you can use the name in your VBA: Range("MyRange").

Hope this helps.

 

[RunTime91]

Barry/Eric...

Thank you both for chiming in...Your responses are what I was hoping for...just some general guidance from those who know more about this stuff than I do.

At some point, this Workbook program I'm working with is going to become rather verbose with data, so speed is my near #1 concern.

Currently I invoke the practice of declaring Public then Setting and Unsetting (Set to = nothing) at the event level.

But I do like to idea of Setting at the Workbook Open Event just wasn't sure if it was better than how I do it now...n

Thanks again Barry/Eric

 

[RunTime91]

Here is an update...

I adopted the idea to pre-load my variables in the Workbook Open Event

All worked great until I closed/unloaded my UserForm.

When I tried to re-load my UserForm I would get Object Variable Not Set.

It seems closing the UserForm removes all the Set variables from memory and in order to

re-load (re-initialize) the UserForm without an error I have to actually close and re-open the workbook.

So I'm thinking of moving my pre-set variables from the Workbook Open Event to the Userform Initialize Event


Any thoughts on this?

Thanks All

 

[Jerry Sullivan]

Hi RunTime91,

One of the more widely accepted best practices in programming is to limit the scope of variables as much as practical. Doing so provides more control and clarity.

There's certainly a value in using Public variables for some purposes, such as establishing Constants, or variables that take some time to calculate initially, but need to be used through your project at runtime. Assigning Range objects upon opening the workbook, IMO has downsides that outweigh potential benefits.

The unexpected problem you encountered when working with a Userform shows that it's undesirable to have your code assume that variables were assigned and have maintained the correct assignments made by other procedures or code modules within your project during an earlier runtime. A similar problem could occur because VBA loses all its variable assignments when it encounters an unhandled error. It's also possible that the referenced object has changed since being assigned (e.g. through rows being inserted or a sheet being deleted).

I agree with Eric's recommendation to use Named Ranges as a convenient and effective way to have VBA reference worksheet ranges. To your broader question, I'd discourage the setting of Public variables at start up to be used in procedure calls later in the user's Excel session.

 

[Jerry Sullivan]

When I tried to re-load my UserForm I would get Object Variable Not Set.

It seems closing the UserForm removes all the Set variables from memory and in order to

re-load (re-initialize) the UserForm without an error I have to actually close and re-open the workbook.

This isn't the typical case when opening of UserForm. The Public variables may have lost their assignments due to some other reason.

You can run the code below in a blank workbook (with a UserForm1 object added) to confirm opening a UserForm alone doesn't cause the variable assignments to be cleared.

'--run this code from a Standard Code Module (like Module1), not a Userform code Module.

Public gsMyString As String
Public grMyRange As Range

Sub InitilizePublic()
   gsMyString = "Stored String"
   Set grMyRange = Sheet1.Range("Z8")
End Sub

Sub OpenUserFormTest()
'--code to test whether public variables are retained after
'  userform is opened

 InitilizePublic
 UserForm1.Show
 MsgBox "gsMyString: " & gsMyString & vbCr _
   & "grMyRange: " & grMyRange.Address
 
End Sub