Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Const or Variable that doesn't change
Hi,
I'm still learning all this VBA stuff, so apologies if this is simplistic, but... I have a set of macros embedded into a worksheet which all work fine. Several of these use the same variable. The value of this variable is the content of a single defined cell on a specific sheet. the user inputs this value, so it can change, but not between iterations of the macros (hope this makes sense so far). What I would like to do is declare the variable once across all the macros, rather than having to declare it each time and send the code off to look for it. I don't seem to be able to declare a Const as a Range, so I tried to do it as a String, like this: Const Trigram As String = Sheets("User Data").Range("b2") but the compiler doesn't like that at all (the error is "Constant expression required") I also tried: Dim ReadOnly Trigram As Range = Sheets("User Data").Range("b2") this time the error highlighted Trigram and is "Expected: end of statement" Questions: a) I'm putting these immediately below the Option Explicit (ie above the line delimiting the first sub() ) - is this the correct place for them? b) What is the correct way of achieving my intent? TIA Dave |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Const or Variable that doesn't change
A constant is a constant so you can not declare it by pointing it to
something that might change. A variable needs to be initialized in a sub or function. It can not be outside of that. If it was me I would be inclide to just use... Sheets("User Data").Range("b2") when I need the value. If you want though you could use a function something like this... public function Trigram () as string Trigram = Sheets("User Data").Range("b2").value end function Now you just use it the same way you would a variable. msgbox Trigram -- HTH... Jim Thomlinson "Risky Dave" wrote: Hi, I'm still learning all this VBA stuff, so apologies if this is simplistic, but... I have a set of macros embedded into a worksheet which all work fine. Several of these use the same variable. The value of this variable is the content of a single defined cell on a specific sheet. the user inputs this value, so it can change, but not between iterations of the macros (hope this makes sense so far). What I would like to do is declare the variable once across all the macros, rather than having to declare it each time and send the code off to look for it. I don't seem to be able to declare a Const as a Range, so I tried to do it as a String, like this: Const Trigram As String = Sheets("User Data").Range("b2") but the compiler doesn't like that at all (the error is "Constant expression required") I also tried: Dim ReadOnly Trigram As Range = Sheets("User Data").Range("b2") this time the error highlighted Trigram and is "Expected: end of statement" Questions: a) I'm putting these immediately below the Option Explicit (ie above the line delimiting the first sub() ) - is this the correct place for them? b) What is the correct way of achieving my intent? TIA Dave |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Const or Variable that doesn't change
What about
Const Trigram As String = Sheets("User Data").Range("b2").Value a) Yes but I believe you add the word "Public" before the word "Const" b) Looks like you figured it out already :-) HTH, JP On Aug 13, 4:56*pm, Risky Dave wrote: Hi, I'm still learning all this VBA stuff, so apologies if this is simplistic, but... I have a set of macros embedded into a worksheet which all work fine. Several of these use the same variable. The value of this variable is the content of a single defined cell on a specific sheet. the user inputs this value, so it can change, but not between iterations of the macros (hope this makes sense so far). What I would like to do is declare the variable once across all the macros, rather than having to declare it each time and send the code off to look for it. I don't seem to be able to declare a Const as a Range, so I tried to do it as a String, like this: Const Trigram As String = Sheets("User Data").Range("b2") but the compiler doesn't like that at all (the error is "Constant expression required") I also tried: Dim ReadOnly Trigram As Range = Sheets("User Data").Range("b2") this time the error highlighted Trigram and is "Expected: end of statement" Questions: a) I'm putting these immediately below the Option Explicit (ie above the line delimiting the first sub() ) - is this the correct place for them? b) What is the correct way of achieving my intent? TIA Dave |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Const or Variable that doesn't change
Jim,
Thanks for the prompt response - exactly what I needed. Dave "Jim Thomlinson" wrote: A constant is a constant so you can not declare it by pointing it to something that might change. A variable needs to be initialized in a sub or function. It can not be outside of that. If it was me I would be inclide to just use... Sheets("User Data").Range("b2") when I need the value. If you want though you could use a function something like this... public function Trigram () as string Trigram = Sheets("User Data").Range("b2").value end function Now you just use it the same way you would a variable. msgbox Trigram -- HTH... Jim Thomlinson "Risky Dave" wrote: Hi, I'm still learning all this VBA stuff, so apologies if this is simplistic, but... I have a set of macros embedded into a worksheet which all work fine. Several of these use the same variable. The value of this variable is the content of a single defined cell on a specific sheet. the user inputs this value, so it can change, but not between iterations of the macros (hope this makes sense so far). What I would like to do is declare the variable once across all the macros, rather than having to declare it each time and send the code off to look for it. I don't seem to be able to declare a Const as a Range, so I tried to do it as a String, like this: Const Trigram As String = Sheets("User Data").Range("b2") but the compiler doesn't like that at all (the error is "Constant expression required") I also tried: Dim ReadOnly Trigram As Range = Sheets("User Data").Range("b2") this time the error highlighted Trigram and is "Expected: end of statement" Questions: a) I'm putting these immediately below the Option Explicit (ie above the line delimiting the first sub() ) - is this the correct place for them? b) What is the correct way of achieving my intent? TIA Dave |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Const or Variable that doesn't change
Have you tried this or anything similar...
Const Trigram As String = Sheets("User Data").Range("b2").Value it won't compile at my end. The only thing you added was the .value and value is the default property of a range so it is there by default. Additionally declaring Const or Public Const is exactly the same thing since the default scope is public. -- HTH... Jim Thomlinson "JP" wrote: What about Const Trigram As String = Sheets("User Data").Range("b2").Value a) Yes but I believe you add the word "Public" before the word "Const" b) Looks like you figured it out already :-) HTH, JP On Aug 13, 4:56 pm, Risky Dave wrote: Hi, I'm still learning all this VBA stuff, so apologies if this is simplistic, but... I have a set of macros embedded into a worksheet which all work fine. Several of these use the same variable. The value of this variable is the content of a single defined cell on a specific sheet. the user inputs this value, so it can change, but not between iterations of the macros (hope this makes sense so far). What I would like to do is declare the variable once across all the macros, rather than having to declare it each time and send the code off to look for it. I don't seem to be able to declare a Const as a Range, so I tried to do it as a String, like this: Const Trigram As String = Sheets("User Data").Range("b2") but the compiler doesn't like that at all (the error is "Constant expression required") I also tried: Dim ReadOnly Trigram As Range = Sheets("User Data").Range("b2") this time the error highlighted Trigram and is "Expected: end of statement" Questions: a) I'm putting these immediately below the Option Explicit (ie above the line delimiting the first sub() ) - is this the correct place for them? b) What is the correct way of achieving my intent? TIA Dave |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Const or Variable that doesn't change
Searching into old posts helps...
This is what I needed. Thanks "Jim Thomlinson" wrote: A constant is a constant so you can not declare it by pointing it to something that might change. A variable needs to be initialized in a sub or function. It can not be outside of that. If it was me I would be inclide to just use... Sheets("User Data").Range("b2") when I need the value. If you want though you could use a function something like this... public function Trigram () as string Trigram = Sheets("User Data").Range("b2").value end function Now you just use it the same way you would a variable. msgbox Trigram -- HTH... Jim Thomlinson "Risky Dave" wrote: Hi, I'm still learning all this VBA stuff, so apologies if this is simplistic, but... I have a set of macros embedded into a worksheet which all work fine. Several of these use the same variable. The value of this variable is the content of a single defined cell on a specific sheet. the user inputs this value, so it can change, but not between iterations of the macros (hope this makes sense so far). What I would like to do is declare the variable once across all the macros, rather than having to declare it each time and send the code off to look for it. I don't seem to be able to declare a Const as a Range, so I tried to do it as a String, like this: Const Trigram As String = Sheets("User Data").Range("b2") but the compiler doesn't like that at all (the error is "Constant expression required") I also tried: Dim ReadOnly Trigram As Range = Sheets("User Data").Range("b2") this time the error highlighted Trigram and is "Expected: end of statement" Questions: a) I'm putting these immediately below the Option Explicit (ie above the line delimiting the first sub() ) - is this the correct place for them? b) What is the correct way of achieving my intent? TIA Dave |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Const and VBA values | Excel Programming | |||
Macro to change macro or module level const that is calculated | Excel Programming | |||
Changing the CONST Variable | Excel Programming | |||
Const Array | Excel Programming | |||
crash changing const to public const | Excel Programming |