storing variable globally?

Excel has issues with this. I have had the best luck
storing the variable in a hidden worksheet. This lets
you close and open the workbook and still be able to
use the variable value.

This would work, although it'd get a bit awkward unhiding and rehiding the sheet each time the module uses it, wouldn't it?

I found that passing the variable between modules as a parameter worked the best, for example:

sub first_module()
...
foo = 100
call second_module(foo)
...
end sub

sub second_module(parameter1)
whatever_you_want = parameter1
...
end sub

so in essence, "foo" is assigned the value of 100 in the first_module, and is then passed as "parameter1" into second_module.
You can then assign, or use the value of the parameters in the second_module.

Hope it helps!

Why wouldn't you just set "Visible" to FALSE when
you added the name that's globally know to the
workbook?

No need to unhide a sheet to pass a cell value

Sub Tester()
'assumes HiddenSheet exists
'assumes value in activecell

Dim MyNum
MyNum = ActiveCell
Sheets("HiddenSheet").Range("E4") = MyNum
Selection.Offset(5, 5) = MyNum
End Sub