Const or Variable that doesn't change

[Risky Dave]

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 Thomlinson]

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

 

[JP]

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

 

[Risky Dave]

Jim,

Thanks for the prompt response - exactly what I needed.

Dave

 

[Jim Thomlinson]

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.

 

[Alberto Ast]

Searching into old posts helps...
This is what I needed.
Thanks