View Full Version : Solved: Cell reference in global declaration

04-16-2007, 06:17 AM

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?

04-16-2007, 06:28 AM
Not a constant, but you can create a variable and the SET the varaibale to that range.

04-16-2007, 06:41 AM

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?

04-16-2007, 08:32 AM

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.

04-17-2007, 07:18 AM
Many thanks

Thats now works perfectly.