Excel Global Variable Setting

[John Baker]

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

 

[Bob Phillips]

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]

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

 

[Chip Pearson]

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]

Chip

Would that be:

Send = ActiveWorkbook.Name
Public Send As String

?

Thanks

John

 

[Chip Pearson]

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]

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]

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 Autpen()
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]

Chip;


Ahhhhhhhhhhhh. I see thanks very much!



John Baker