Save a variable value outside a macro
 

Hi, good night (at last in Mexico), There is a way to set a variable value
with a macro so that value exist all the time, I mean, all the time the
program, it doesn´t metter if the macro ends or not??????????

Save a variable value outside a macro
 

A global variable will hold its value as long as your code is in
memory, ie
In the general declarations section type:
Public MyVar as Single

If you want the value to be stored even after your workbook has closed
then you will need to store it either in a cell in the workbook itself
or in a text file (from which you can read it again when your workbook
reopens). There are many approaches to achieve the latter - search for
'Open Statement' in vba help for one of the simpler ones.

Good luck,
Andrew

Save a variable value outside a macro
 

A global variable will hold its value as long as your code is in
memory, ie
In the general declarations section type:
Public MyVar as Single

If you want the value to be stored even after your workbook has closed
then you will need to store it either in a cell in the workbook itself
or in a text file (from which you can read it again when your workbook
reopens). There are many approaches to achieve the latter - search for
'Open Statement' in vba help for one of the simpler ones.

Good luck,
Andrew

Save a variable value outside a macro
 

Global variables persist for the duration as do static varaibles. I think you
are familiar with globals so try this code and run it twice...

Sub test()
Static x As Integer

MsgBox x
x = 10
MsgBox x
End Sub

the first msgbox is a zero and the rest of the time they will be 10.

--
HTH...

Jim Thomlinson


"filo666" wrote:

Hi, good night (at last in Mexico), There is a way to set a variable value
with a macro so that value exist all the time, I mean, all the time the
program, it doesn´t metter if the macro ends or not??????????

Save a variable value outside a macro
 

Try this example in a new workbook:

Put the below VBA code into a general module:

'------Start of Code------
Option Explicit

'Creates a hidden workbook-level name that can be used as a variable
Sub CreateMyPermanentVariable()
ThisWorkbook.Names.Add _
Name:="MyVariable", _
Visible:=False, _
RefersTo:="MySetting"
End Sub

'Changes the RefersTo value of the hidden wkbk-level name
Sub SetMyVariableValue()
Names("MyVariable").RefersTo = "MyNewSetting"
End Sub
'------End of Code------

Then run the CreateMyPermanentVariable code

Next, put this formula into any cell in a worksheet:
=MyVariable
(It will return "MySetting")

Now run the SetMyVariableValue code

The formula will now return "MyNewSetting"

Experiment with settings.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"filo666" wrote:

Hi, good night (at last in Mexico), There is a way to set a variable value
with a macro so that value exist all the time, I mean, all the time the
program, it doesn´t metter if the macro ends or not??????????