Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

Storing variables in VBA

I am new to VBA and am coding in Excel 2007.

I have learned about events and writing sub-routines that I can execute fromthe events. I need to have a few global variables though.

How do I do that?

Thanks.
0
newbieweb
Asked:
newbieweb
  • 5
  • 3
  • 2
  • +2
10 Solutions
 
zorvek (Kevin Jones)ConsultantCommented:
Define the variables in a general code module in the top declaration section as:

Public MyPublicVariable As Variant

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
Any public (AKA global) variable defined this way is available to the entire project.

Kevin
0
 
Martin LissOlder than dirtCommented:
And you add a module by going to the 'Insert' menu item and selecting 'Module'.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Martin LissOlder than dirtCommented:
BTW I would 99.9% of the time not define the variable as Variant, but rather as String, Integer or whatever was appropriate.
0
 
Bill PrewCommented:
Somewhat useful discussion here:

http://www.ozgrid.com/VBA/variable-scope-lifetime.htm

~bp
0
 
NorieVBA ExpertCommented:
Why do you need global variables?
0
 
newbiewebSr. Software EngineerAuthor Commented:
> Define the variables in a general code module in the top declaration section as:

In which sheet?

> Why do you need global variables?

I may need to limit the frequency with which I download data from the database. Maybe every 10 minutes, for a given table.

I am not sure but want to have the ability to do it if I need to...
0
 
zorvek (Kevin Jones)ConsultantCommented:
A general code module is not the same as a sheet module.

Add a general code module by choosing "Module" from the "Insert" menu.

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
A global variable (or constant) is an excellent choice for doing what you want. I frequently do the same.

A global constant looks like:

Public Const DatabaseCheckInterval As Long = 10 ' Minutes

Kevin
0
 
NorieVBA ExpertCommented:
I'm not sure global variables will limit that.

If you are populating something, let's say comboboxes, with the data it can be a one off.

You open the connection, get the data, populate the combobox, close the connection, that's it.
0
 
zorvek (Kevin Jones)ConsultantCommented:
What the author is asking is reasonable if the workbook contains code that has a loop that pulls from the database every so many minutes and he wants the interval time to be easily editable.

Kevin
0
 
NorieVBA ExpertCommented:
Kevin

I know, but I'm still not sure what the author has/wants.

This question isn't totally clear and some of the others aren't either.
0
 
newbiewebSr. Software EngineerAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now