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