Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables
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. "Richard" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum if 2 variables | Excel Discussion (Misc queries) | |||
Not at all clear on use of variables and/or object variables | Excel Discussion (Misc queries) | |||
Storing variables in a macro and using those variables to performcalculations. | Excel Programming | |||
Variables in VBA | Excel Programming | |||
SUM IF and two variables | Excel Worksheet Functions |