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