Hello all,

I have about 40 variables that I currently declare before running
about 5 macros. All of these are the same across the macros because
they set things like column numbers and start and end rows for the
spreadsheets I work with. The macros all reside in the same sheet.
How can I make it so that the variables all sit in one spot and are
referenced by the 5 different macros? Can I write a function that
declares the variables and then call that function in each of my 5
macros? I tried doing that but the macro did not recognize the
variables even after making them Public. Thanks!

Here is some sample code showing the variables I want to share across
the macros:
SP_Index = 3
SP_CA = 4
SP_WP = 5
etc...

I'd create a new module that has this in it:

Option Explicit
Public VarsAreInitialized as Boolean
Public SP_Index as long 'or double or whatever
Public SP_CA as long
....

Sub InitializeVariables()
VarsAreInitialized = true
sp_index = 3
sp_ca = 4
...
End sub

Then in each of the routines that depend on those variables:

Option Explicit
Sub FirstSub()

if varsareinitilized then
'do nothing
else
call initializeVariables
end if

...rest of code
End sub



Michael wrote:
>
> Hello all,
>
> I have about 40 variables that I currently declare before running
> about 5 macros. All of these are the same across the macros because
> they set things like column numbers and start and end rows for the
> spreadsheets I work with. The macros all reside in the same sheet.
> How can I make it so that the variables all sit in one spot and are
> referenced by the 5 different macros? Can I write a function that
> declares the variables and then call that function in each of my 5
> macros? I tried doing that but the macro did not recognize the
> variables even after making them Public. Thanks!
>
> Here is some sample code showing the variables I want to share across
> the macros:
> SP_Index = 3
> SP_CA = 4
> SP_WP = 5
> etc...

--

Dave Peterson

On Aug 12, 1:52*pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> I'd create a new module that has this in it:
>
> Option Explicit
> Public VarsAreInitialized as Boolean
> Public SP_Index as long 'or double or whatever
> Public SP_CA as long
> ...
>
> Sub InitializeVariables()
> * VarsAreInitialized = true
> * sp_index = 3
> * sp_ca = 4
> * ...
> End sub
>
> Then in each of the routines that depend on those variables:
>
> Option Explicit
> Sub FirstSub()
>
> * *if varsareinitilized then
> * * * 'do nothing
> * *else
> * * * call initializeVariables
> * *end if
>
> * *...rest of code
> End sub
>
>
>
>
>
> Michael wrote:
>
> > Hello all,
>
> > I have about 40 variables that I currently declare before running
> > about 5 macros. *All of these are the same across the macros because
> > they set things like column numbers and start and end rows for the
> > spreadsheets I work with. *The macros all reside in the same sheet.
> > How can I make it so that the variables all sit in one spot and are
> > referenced by the 5 different macros? *Can I write a function that
> > declares the variables and then call that function in each of my 5
> > macros? *I tried doing that but the macro did not recognize the
> > variables even after making them Public. *Thanks!
>
> > Here is some sample code showing the variables I want to share across
> > the macros:
> > * * SP_Index = 3
> > * * SP_CA = 4
> > * * SP_WP = 5
> > etc...
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

that worked great! thanks a bunch!!!