Solved: Cell reference in global declaration

[TonyS]

Hi

I have written some vba using absolute cell addresses

eg - If ['Sheet1'!A1] > 25 Then ...

Is it possible to declare a global const at the top of the module instead

eg - Const AgeCell = Worksheets("Sheet1").cells(1, 1)

and then use this code in the Subroutine

eg - If AgeCell > 25 Then ...

However, I've tried declaring with Const, Set & Public but cannot get it to work.

Any ideas?

[Bob Phillips]

Not a constant, but you can create a variable and the SET the varaibale to that range.

[TonyS]

Thanks


What would the code be to declare a cell variable? Would it be

Dim AgeCell As Variant (Is Variant the right data type?)
Set AgeCell = Worksheets("Sheet1").cells(1, 1)

And can I put both lines in the Global Declaration area of the module so AgeCell can be used by different subroutines?

[Bob Phillips]

No

Dim AgeCell As Range

Variant will work, as it will create a Range subtype, but Range is more efficient.

You can declare the variable in the declaratives, and it will be accessible throughout the module. Wherever it gets changed, that change will be picked up.

[TonyS]

Many thanks

Thats now works perfectly.