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