Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Global Variable Setting
Hi:
I have encountered a slight difficulty (or maybe irritation) in Excel. I executed a macro line thus: send = ActiveWorkbook.Name and kind of assumed that send would always represent the active workbook name until I changed it. Imagine my disappointment when I found that the next macro I executed has no clue what "send" represented. Is there some way in Excel that I can set a GLOBAL Variable that all macros will know and understand? Thanks John Baker |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Global Variable Setting
John,
Declare the variable outside of a macro, in a general code module. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "John Baker" wrote in message ... Hi: I have encountered a slight difficulty (or maybe irritation) in Excel. I executed a macro line thus: send = ActiveWorkbook.Name and kind of assumed that send would always represent the active workbook name until I changed it. Imagine my disappointment when I found that the next macro I executed has no clue what "send" represented. Is there some way in Excel that I can set a GLOBAL Variable that all macros will know and understand? Thanks John Baker |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Global Variable Setting
Bob:
Thanks, but I am not quite certain how to do that. GIven the specific variable I wish to set, can you give me the code. I am sorry, but I am a neophyte when it comes to complex VB things. Regards John Baker "Bob Phillips" wrote: John, Declare the variable outside of a macro, in a general code module. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Global Variable Setting
John,
Declare the variable, as below, before and outside of any procedure. Public Send As String -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "John Baker" wrote in message ... Bob: Thanks, but I am not quite certain how to do that. GIven the specific variable I wish to set, can you give me the code. I am sorry, but I am a neophyte when it comes to complex VB things. Regards John Baker "Bob Phillips" wrote: John, Declare the variable outside of a macro, in a general code module. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Global Variable Setting
Chip
Would that be: Send = ActiveWorkbook.Name Public Send As String ? Thanks John "Chip Pearson" wrote: John, Declare the variable, as below, before and outside of any procedure. Public Send As String |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Global Variable Setting
John,
You can declare variable as public, so that they will be available to all procedures in the project, but you can't assign them a value outside of a procedure. For example, Public Send As String Sub Setup() Send = ActiveWorkbook.Name End Sub Note that Send will contain the name of the ActiveWorkbook when the value is assigned to Send, but will not automatically update if another workbook in made active. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "John Baker" wrote in message ... Chip Would that be: Send = ActiveWorkbook.Name Public Send As String ? Thanks John "Chip Pearson" wrote: John, Declare the variable, as below, before and outside of any procedure. Public Send As String |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Global Variable Setting
Chip I set it up thus:
Sub auto_open() ' ' auto_open Macro ' Macro recorded 11/25/2003 by John H Baker ' WBpath = ActiveWorkbook.Path timesheet = ActiveWorkbook.Name ' Public WBpath As String Public timesheet As String cd WBpath End Sub It does not like the Public statement. I have no idea what I have done wrong. Can you point me in he right direction? Regards John Baker "Chip Pearson" wrote: John, Declare the variable, as below, before and outside of any procedure. Public Send As String |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Global Variable Setting
John,
The Public declaration must appear outside of and before any Sub or Function procedure in the module. So your code would look something like Option Explicit Public WBpath As String Public timesheet As String Sub Auto_Open() WBpath = ActiveWorkbook.Path timesheet = ActiveWorkbook.Name CD WbPath End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "John Baker" wrote in message ... Chip I set it up thus: Sub auto_open() ' ' auto_open Macro ' Macro recorded 11/25/2003 by John H Baker ' WBpath = ActiveWorkbook.Path timesheet = ActiveWorkbook.Name ' Public WBpath As String Public timesheet As String cd WBpath End Sub It does not like the Public statement. I have no idea what I have done wrong. Can you point me in he right direction? Regards John Baker "Chip Pearson" wrote: John, Declare the variable, as below, before and outside of any procedure. Public Send As String |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Global Variable Setting
Chip;
Ahhhhhhhhhhhh. I see thanks very much! John Baker "Chip Pearson" wrote: John, You can declare variable as public, so that they will be available to all procedures in the project, but you can't assign them a value outside of a procedure. For example, Public Send As String Sub Setup() Send = ActiveWorkbook.Name End Sub Note that Send will contain the name of the ActiveWorkbook when the value is assigned to Send, but will not automatically update if another workbook in made active. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Global Header Setting | Excel Discussion (Misc queries) | |||
Excel global setting for all workbooks? Calculation Automatic | Excel Discussion (Misc queries) | |||
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions | |||
Global Variable -- Excel / VBA | Excel Discussion (Misc queries) | |||
Global variable | Excel Programming |