Coding Technique Question Regarding Varibles

RunTime91

Active Member
Joined
Aug 30, 2012
Messages
262
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...
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

BarryL

Well-known Member
Joined
Jan 20, 2014
Messages
1,436
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,976
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

Active Member
Joined
Aug 30, 2012
Messages
262
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

Active Member
Joined
Aug 30, 2012
Messages
262

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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.
 
Last edited:

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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.

Code:
'--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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,531
Messages
5,659,360
Members
418,499
Latest member
mbcmel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top