Solved

Storing variables in VBA

Posted on 2011-09-08
13
277 Views
Last Modified: 2012-06-21
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
Comment
Question by:newbieweb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +2
13 Comments
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 250 total points
ID: 36505942
Define the variables in a general code module in the top declaration section as:

Public MyPublicVariable As Variant

Kevin
0
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 250 total points
ID: 36505950
Any public (AKA global) variable defined this way is available to the entire project.

Kevin
0
 
LVL 47

Assisted Solution

by:Martin Liss
Martin Liss earned 100 total points
ID: 36505961
And you add a module by going to the 'Insert' menu item and selecting 'Module'.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 47

Assisted Solution

by:Martin Liss
Martin Liss earned 100 total points
ID: 36505979
BTW I would 99.9% of the time not define the variable as Variant, but rather as String, Integer or whatever was appropriate.
0
 
LVL 54

Expert Comment

by:Bill Prew
ID: 36505981
Somewhat useful discussion here:

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

~bp
0
 
LVL 33

Assisted Solution

by:Norie
Norie earned 150 total points
ID: 36505994
Why do you need global variables?
0
 

Author Comment

by:newbieweb
ID: 36506071
> 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
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 250 total points
ID: 36506085
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
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 250 total points
ID: 36506108
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
 
LVL 33

Assisted Solution

by:Norie
Norie earned 150 total points
ID: 36506120
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
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 250 total points
ID: 36506174
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
 
LVL 33

Assisted Solution

by:Norie
Norie earned 150 total points
ID: 36506449
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
 

Author Closing Comment

by:newbieweb
ID: 36524131
Thanks!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question