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

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

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 <RiskyD...@discussions.microsoft.com>
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

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

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 <RiskyD...@discussions.microsoft.com>
> 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
>
>

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