If you want to store ActiveWorkbook.path & "\" to a variable and access it
everywhere why not declare a string variable as public and in assign the path
in the beginning of the macro or @ workbook open...

Public fPath as String
Sub Macro()
fpath = application.ActiveWorkbook.path & "\"
'rest of your code
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Richard" wrote:

Jacob

that works well, until I try the following

Public Const fpath = application.ActiveWorkbook.path & "\"

I then get a compile error: Constant expression required with the .path
section of the string highlighted


"Jacob Skaria" wrote:

Try Const statement..

Public Const MySheet = "Sheet1"
Sub Macro()
MsgBox MySheet
End Sub

Take a look at the below link
http://msdn.microsoft.com/en-us/libr...ed(VS.85).aspx

If this post helps click Yes
---------------
Jacob Skaria


"Richard" wrote:

I think I must be stupid, as I have read lots of information and am not
getting the point.

I am very used to declaring variables as follows:

sub whatever()

dim strSht4Name As String

strSht4Name = "nameofsheet4"

end sub

I have 2 questions:

1) Can I combine the 2 rows i.e.
dim strSht4Name As String = "nameofsheet4"

2) As I now also better understand going "public" I would like to do the
following:
Public strSht4Name As String
strSht4Name = "nameofsheet4"

But am informed by excel that the second line where I pass a value to the
variable is not correct outside a sub. Is this correct or am I doing
something wrong?

Thanks for the help and advice (It would be good if this got better coverage
in the books us lesser mortals purchase)

Richard