Jacob

thanks for the help. you are a star indeed.

Richard

"Jacob Skaria" wrote:

You cannot do that.. Few points from the link

--Declares constants for use in place of literal values.

--Unlike variables, constants cannot be inadvertently changed while your
script is running.

--You can't use variables, user-defined functions, or intrinsic VBScript
functions (such as Chr) in constant declarations. By definition, they can't
be constants. You also can't create a constant from any expression that
involves an operator, that is, only simple constants are allowed.

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