Hi all

i need to have a global 'path' that all of my functions and subs have access
to, so that if i want to change the path, i need to change it only in one
place

i have

const docPath="C:\Temp\"

defined in ThisWorkbook, but when i open the workbook and run any of my
functions trying to reference docPath, it is empty

is defining it as a const in ThisWorkbook not setting it public?
or do i need to do

public docPath

Private Sub Workbook_Open()
docPath = "C:\Temp\"
End Sub

?

doesn't that allow the path to be changed from other functions?

any suggestions greatly appreciated!

thanks!

J

Don't declare it in ThisWorkbook. Declare the constant in a
regular code module. If you do declare it in ThisWorkbook, you
need to reference the variable as ThisWorkbook.docPath.




--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Gixxer_J_97" wrote in
message
...
Hi all

i need to have a global 'path' that all of my functions and
subs have access
to, so that if i want to change the path, i need to change it
only in one
place

i have

const docPath="C:\Temp\"

defined in ThisWorkbook, but when i open the workbook and run
any of my
functions trying to reference docPath, it is empty

is defining it as a const in ThisWorkbook not setting it
public?
or do i need to do

public docPath

Private Sub Workbook_Open()
docPath = "C:\Temp\"
End Sub

?

doesn't that allow the path to be changed from other functions?

any suggestions greatly appreciated!

thanks!

J

thanks Chip!

just for clarification - can the const be defined in any code module, or
does it need to be defined in the module that it will be used? is there a
way to make it "global" or does defining it as const take care of that?


"Chip Pearson" wrote:

Don't declare it in ThisWorkbook. Declare the constant in a
regular code module. If you do declare it in ThisWorkbook, you
need to reference the variable as ThisWorkbook.docPath.




--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Gixxer_J_97" wrote in
message
...
Hi all

i need to have a global 'path' that all of my functions and
subs have access
to, so that if i want to change the path, i need to change it
only in one
place

i have

const docPath="C:\Temp\"

defined in ThisWorkbook, but when i open the workbook and run
any of my
functions trying to reference docPath, it is empty

is defining it as a const in ThisWorkbook not setting it
public?
or do i need to do

public docPath

Private Sub Workbook_Open()
docPath = "C:\Temp\"
End Sub

?

doesn't that allow the path to be changed from other functions?

any suggestions greatly appreciated!

thanks!

J

Declare it in a general module if you want easily see it in all parts of
your project

Public Const DocPath As String = "C:\Temp\"

Modules that are not general modules don't permit PUBLIC constants so
constants declared there are local/private to that module/class.

--
Regards,
Tom Ogilvy





"Gixxer_J_97" wrote in message
...
Hi all

i need to have a global 'path' that all of my functions and subs have
access
to, so that if i want to change the path, i need to change it only in one
place

i have

const docPath="C:\Temp\"

defined in ThisWorkbook, but when i open the workbook and run any of my
functions trying to reference docPath, it is empty

is defining it as a const in ThisWorkbook not setting it public?
or do i need to do

public docPath

Private Sub Workbook_Open()
docPath = "C:\Temp\"
End Sub

?

doesn't that allow the path to be changed from other functions?

any suggestions greatly appreciated!

thanks!

J

If you declare it as Public, it can be in any code module, and
will be accessible to all code modules. If you omit the Public
declaration, the constant will be accessible only to the module
in which it is declared.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Gixxer_J_97" wrote in
message
...
thanks Chip!

just for clarification - can the const be defined in any code
module, or
does it need to be defined in the module that it will be used?
is there a
way to make it "global" or does defining it as const take care
of that?


"Chip Pearson" wrote:

Don't declare it in ThisWorkbook. Declare the constant in a
regular code module. If you do declare it in ThisWorkbook, you
need to reference the variable as ThisWorkbook.docPath.




--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Gixxer_J_97" wrote in
message
...
Hi all

i need to have a global 'path' that all of my functions and
subs have access
to, so that if i want to change the path, i need to change
it
only in one
place

i have

const docPath="C:\Temp\"

defined in ThisWorkbook, but when i open the workbook and
run
any of my
functions trying to reference docPath, it is empty

is defining it as a const in ThisWorkbook not setting it
public?
or do i need to do

public docPath

Private Sub Workbook_Open()
docPath = "C:\Temp\"
End Sub

?

doesn't that allow the path to be changed from other
functions?

any suggestions greatly appreciated!

thanks!

J

If docpath is declared as a constant in ThisWorkbook, you can't declare it
as public and thus I don't believe you can use ThisWorkbook.docPath to refer
to it. At least it doesn't work in xl2003 in my tests.

--
Regards,
Tom Ogilvy

"Chip Pearson" wrote in message
...
Don't declare it in ThisWorkbook. Declare the constant in a
regular code module. If you do declare it in ThisWorkbook, you
need to reference the variable as ThisWorkbook.docPath.




--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Gixxer_J_97" wrote in
message
...
Hi all

i need to have a global 'path' that all of my functions and
subs have access
to, so that if i want to change the path, i need to change it
only in one
place

i have

const docPath="C:\Temp\"

defined in ThisWorkbook, but when i open the workbook and run
any of my
functions trying to reference docPath, it is empty

is defining it as a const in ThisWorkbook not setting it
public?
or do i need to do

public docPath

Private Sub Workbook_Open()
docPath = "C:\Temp\"
End Sub

?

doesn't that allow the path to be changed from other functions?

any suggestions greatly appreciated!

thanks!

J

If it is defined in any module other than the one it is to be used, use

Public Const DocPath As String= "C:\Temp\"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gixxer_J_97" wrote in message
...
thanks Chip!

just for clarification - can the const be defined in any code module, or
does it need to be defined in the module that it will be used? is there a
way to make it "global" or does defining it as const take care of that?


"Chip Pearson" wrote:

Don't declare it in ThisWorkbook. Declare the constant in a
regular code module. If you do declare it in ThisWorkbook, you
need to reference the variable as ThisWorkbook.docPath.




--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Gixxer_J_97" wrote in
message
...
Hi all

i need to have a global 'path' that all of my functions and
subs have access
to, so that if i want to change the path, i need to change it
only in one
place

i have

const docPath="C:\Temp\"

defined in ThisWorkbook, but when i open the workbook and run
any of my
functions trying to reference docPath, it is empty

is defining it as a const in ThisWorkbook not setting it
public?
or do i need to do

public docPath

Private Sub Workbook_Open()
docPath = "C:\Temp\"
End Sub

?

doesn't that allow the path to be changed from other functions?

any suggestions greatly appreciated!

thanks!

J

Yeah, you're right. I botched the answer.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Tom Ogilvy" wrote in message
...
If docpath is declared as a constant in ThisWorkbook, you can't
declare it
as public and thus I don't believe you can use
ThisWorkbook.docPath to refer
to it. At least it doesn't work in xl2003 in my tests.

--
Regards,
Tom Ogilvy

"Chip Pearson" wrote in message
...
Don't declare it in ThisWorkbook. Declare the constant in a
regular code module. If you do declare it in ThisWorkbook, you
need to reference the variable as ThisWorkbook.docPath.




--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Gixxer_J_97" wrote in
message
...
Hi all

i need to have a global 'path' that all of my functions and
subs have access
to, so that if i want to change the path, i need to change
it
only in one
place

i have

const docPath="C:\Temp\"

defined in ThisWorkbook, but when i open the workbook and
run
any of my
functions trying to reference docPath, it is empty

is defining it as a const in ThisWorkbook not setting it
public?
or do i need to do

public docPath

Private Sub Workbook_Open()
docPath = "C:\Temp\"
End Sub

?

doesn't that allow the path to be changed from other
functions?

any suggestions greatly appreciated!

thanks!

J

thats what i was looking for! thanks Tom!

J

"Tom Ogilvy" wrote:

Declare it in a general module if you want easily see it in all parts of
your project

Public Const DocPath As String = "C:\Temp\"

Modules that are not general modules don't permit PUBLIC constants so
constants declared there are local/private to that module/class.

--
Regards,
Tom Ogilvy





"Gixxer_J_97" wrote in message
...
Hi all

i need to have a global 'path' that all of my functions and subs have
access
to, so that if i want to change the path, i need to change it only in one
place

i have

const docPath="C:\Temp\"

defined in ThisWorkbook, but when i open the workbook and run any of my
functions trying to reference docPath, it is empty

is defining it as a const in ThisWorkbook not setting it public?
or do i need to do

public docPath

Private Sub Workbook_Open()
docPath = "C:\Temp\"
End Sub

?

doesn't that allow the path to be changed from other functions?

any suggestions greatly appreciated!

thanks!

J