PDA

View Full Version : Solved: Cell reference in global declaration



TonyS
04-16-2007, 06:17 AM
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
04-16-2007, 06:28 AM
Not a constant, but you can create a variable and the SET the varaibale to that range.

TonyS
04-16-2007, 06:41 AM
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
04-16-2007, 08:32 AM
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
04-17-2007, 07:18 AM
Many thanks

Thats now works perfectly.

:yes