Passing Global variable between Subroutines in Macros

[TeddyBear]

I have a repeatitative process which I would like to call from an Excel
subroutine. The process use variables calculated in another subroutine.

How can I pass the variable information into the call subroutine so that the
value is passed to it.

 

[Og]

You are more likely to received informed responses to your Excel programming
queries if you post where the Excel programming gurus hang out:
microsoft.public.excel.programming
Steve

 

[Harlan Grove]

I have a repeatitative process which I would like to call from an Excel
subroutine. The process use variables calculated in another subroutine.

How can I pass the variable information into the call subroutine so that
the value is passed to it.

Better to ask in Excel-specific newsgroups.

The standard approach is to use either global variables or defined names.
Global variables can lose their values if the VBA project resets (such as by
aborting a procedure while debugging or running an End statement in any
procedure). Defined names are more reliable, and they're also available to
worksheet formulas.

Names.Add Name:=somename, RefersTo:="something"

then use it by wrapping it inside Evaluate calls, e.g.,

MsgBox Title:=Names("somename"), Prompt:=Evaluate("somename")

 

[TeddyBear]

Hi,

I've found the answer...
Answer: All that is required for variables to be available to ALL
subroutines within a Module is - you need to define the variables as Public
before any Subrotines. Then they become available to All sub-routines as
follows:.

E.g. Public vName As String

Thanks.

 

[TeddyBear]

Found the answer in the public.excel.programming site amongst one of the
answers provided.

All that is required for variables to be available to ALL
subroutines within a Module is - define the variables as Public
before any Subrotines. Then they become available to All sub-routines that
follow in the module. The statement to define the variables should be as
follows:

E.g. Public vName As String

Thanks.