VBA variables that exist over all modules? And file saving.

[groundhog1]

1.) Is there a way to create a variable that lasts forever and is usable over all modules?

My code is in several modules and I want several things to be constant, like the serial number of my unit under test and maybe the partnumber. Stuff like that.

Right now I am just reading it off of the excel spreadsheet. But if I move the contents of the cell, i will have to go through the code and adjust all of the places i call the cell out.

2.) I am saving each file using the the serial number in the filename. Problem is that the file size is like half a Meg each. Is there a way to just save the Excel workbooks and not the all of the VBA code and stuff? I think the code is causing the filesize to be big. There is a giant module that has to be used to communicate with the equipment.



Thankyou,
groundhog

 

[johnwm]

A variable declared as Public at the head of a Module is visible to all code in the Workbook.

If you have code that you want to access from many places, store it in Personal.xls which is loaded by default when you open Excel. Stuff in there is available to all other open worksheets

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

Steam Engine enthusiasts:

http://www.essexsteam.co.uk

 

[joerd]

Re. 1) You should give a name to the range tbat has your serial number etc. Then, you can call it like Range("Serial") or something similar, and you can move it if you want without redoing all the code.
Re. 2) You can write some procedure that copies all the data (or the worksheets) to a new workbook and then save that workbook. Then, you only have to keep one "master" workbook with the code, the other ones are simple "straight" spreadsheets.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[groundhog1]

joerd,
do you have a sample of the code you are describing to put a name to a cell location? Is it as simple as:
Variablename = Cells(1,1)

thanks,
groundhog

 

[johnwm]

Insert Menu - Name - Define

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

Steam Engine enthusiasts:

http://www.essexsteam.co.uk

 

[joerd]

The simplest way is what johnwm suggested. Select the cell with your serial number, choose from the menu Insert/Name/Define (or press Ctrl-F3), and type the name (e.g. "serial"). Then, in your code, you can use
[tt]Dim strSerial as String
strSerial = Range("serial")[/tt]
You can add names using VBA, but then you'll lose what you're looking for since you will have to specify the exact location of the cell with the serial when you define the name: [tt]ActiveSheet.Names.Add "serial", Cells(1, 1)[/tt], and also, you only have to define the name once, so coding is not really worthwile.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.