excel - save global variables in spreadsheet -

i have module load of global variables. name of global variables in module saved in first column, , global variable value saved in second column

i.e

public variable1 string public variable2 string public variable3 string  variable1 = david variable2 = chicken variable3 = apple 

column a: variable1, variable2, variable3 column b: david,chicken,apple

i can think of 3 ways go this.

you set global variables properties , have thier values stored in cells on worksheet. execution of code slower, values persist , viewable/editable on spreadsheet.

for example, define string property 'foo' acts global variable put in global module:

public property foo() string   foo = debugsheet.[b2] end property  public property let foo(value string)   debugsheet.[b2] = value end property 

you use named ranges globals, eg debugsheet.[foo]

a third way have hardcoded save , load routines explicitly save each global specific cell in spreadsheet. works if save routine has run since last changed global variable.

edit

as side note i'd recommend properties. takes more write it, later on if want switch vba variables (for speed) requires fewer code changes using named ranges.

edit 2

if want crazy , create self modifying code workbooks vbproject, component module contains globals, lines codemodule.

set module = thisworkbook.vbproject.vbcomponents("globalsmodule").codemodule code = module.lines(1, module.countoflines) 

you'd have module code in string variable code, you'd parse write results back. not easy or recommended.