VBA variables that exist over all modules? And file saving.
VBA variables that exist over all modules? And file saving.
(OP)
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





RE: VBA variables that exist over all modules? And file saving.
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: www.essexsteam.co.uk
RE: VBA variables that exist over all modules? And file saving.
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.
RE: VBA variables that exist over all modules? And file saving.
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
RE: VBA variables that exist over all modules? And file saving.
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
Steam Engine enthusiasts: www.essexsteam.co.uk
RE: VBA variables that exist over all modules? And file saving.
Dim strSerial as String
strSerial = Range("serial")
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: ActiveSheet.Names.Add "serial", Cells(1, 1), 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.