Variables

[Richard]

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

 

[Jacob Skaria]

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/library/16twy8ed(VS.85).aspx

If this post helps click Yes

 

[Richard]

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]

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]

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

 

[Richard]

Jacob

thanks for the help. you are a star indeed.

Richard

 

[JLGWhiz]

Just an added note:

The Dim statement is to reserve memory for the data type variable and
therefore cannot be combined with the value assignment to the variable. As
stated, the value assignment must be done inside the Sub procedure on a
separate line from the Dim statement.