Declaring a Public variable as a Range and its lifetime

I want to store a range in a public variable that lasts as long as the
workbook is open.

Will this declaration in a Module header accomplish this:

Public RA as Range

Sub XXX...
Set RA=Range("A1")
....

The scope of this variable is the project. What is its lifetime?

John Wirt

That should work for you... The only thing to look out for is that it will
potentially unload depending how errors are handled...

HTH

"John Wirt" wrote:

> I want to store a range in a public variable that lasts as long as the
> workbook is open.
>
> Will this declaration in a Module header accomplish this:
>
> Public RA as Range
>
> Sub XXX...
> Set RA=Range("A1")
> ....
>
> The scope of this variable is the project. What is its lifetime?
>
> John Wirt
>
>
>

That should do it.

Did you try it and come across a problem?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Wirt" <[email protected]> wrote in message
news:[email protected]...
> I want to store a range in a public variable that lasts as long as the
> workbook is open.
>
> Will this declaration in a Module header accomplish this:
>
> Public RA as Range
>
> Sub XXX...
> Set RA=Range("A1")
> ....
>
> The scope of this variable is the project. What is its lifetime?
>
> John Wirt
>
>

The answer is, no, it doesn't work. The first procedure works. If I select a
linked cell, the procedure chanages to the worksheet with the destination
cell and selects that cell. Colors it dark orange even.

The Public variable declaration does not seem to work. The first procedure
stores the original range in the Public variable, RAorig.

The second procedure calls that variable and attempts to reference it. The
result of the Set RA= statment is "nothing." RA is set to nothing.

So something is wrong. John Wirt

Here is the code:
- - - - - - - - - - - - - - - - - - -
Public RAorig As Range

Sub FindLinkedCell()
Dim CellFormula As String
Dim OK As VbMsgBoxResult
Dim WshName As String
Dim RAdest As Range
Dim RAorig As Range

Set RAorig = Selection
WshName = ActiveSheet.Name

CellFormula = RAorig.Formula

Select Case Left(CellFormula, 1)
Case ""
OK = MsgBox("Cell is empty.", vbOKOnly)
Exit Sub
Case "="
Case Else
OK = MsgBox("Cell value is not a link.", vbOKOnly)
Exit Sub
End Select

CellAddress = Mid(CellFormula, 2, Len(CellFormula) - 1)
Set RAdest = Range(CellAddress)
Application.Goto Reference:=RAdest, Scroll:=True
RAdest.Interior.ColorIndex = 40
End Sub

Public Sub ReturntoCell()
Dim RAaddress As String
Dim RA As Range

Set RA = RAorig
Application.Goto Reference:=RAorig

End Sub
- - - - - - - - - - - - - - - - - - - - - -
"Jim Thomlinson" <[email protected]> wrote in message
news:[email protected]...
> That should work for you... The only thing to look out for is that it will
> potentially unload depending how errors are handled...
>
> HTH
>
> "John Wirt" wrote:
>
> > I want to store a range in a public variable that lasts as long as the
> > workbook is open.
> >
> > Will this declaration in a Module header accomplish this:
> >
> > Public RA as Range
> >
> > Sub XXX...
> > Set RA=Range("A1")
> > ....
> >
> > The scope of this variable is the project. What is its lifetime?
> >
> > John Wirt
> >
> >
> >

This should work.

If both procedures are in the SAME module the rngOri variable can be
private, as long as it's declared at module level.

You only need to declare it public if you want to call it:
a.from a procedure in another workbook.
b.from a procedure in another module in the same workbook without
qualifying the module name.

e.g. from a procedure in module2 rngOri will fail if it's not public,
but module1.rngOri will work.



I've reduced the code to the bare minimum but added an error handler
for formulas like =sum(a1:a2) In your original many of the variables
aren't truely needed and I happened to know that Excel can handle
strings like
Range("=[book1.xls]sheet1!a3") without using Mid$ to strip the "="


Option Explicit

Dim rngOri As Range


Sub FindLinkedCell()
Dim rngDst As Range

Set rngOri = Nothing

With ActiveCell
If Not .HasFormula Then
MsgBox "cell has no formula"
Else
On Error Resume Next
Set rngDst = Range(.Formula)
On Error GoTo 0
If rngDst Is Nothing Then
MsgBox "cell is not linked to 1 other cell" & vblf & _
"or destination workbook not open"
Else
Set rngOri = .Cells(1)
Application.Goto rngDst, True
End If
End If
End With
End Sub

Sub ReturnToCell()
If rngOri Is Nothing Then
MsgBox "No cell to return to"
Else
Application.Goto rngOri, True
Set rngOri = Nothing
End If
End Sub





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


John Wirt wrote :

> The answer is, no, it doesn't work. The first procedure works. If I
> select a linked cell, the procedure chanages to the worksheet with
> the destination cell and selects that cell. Colors it dark orange
> even.
>
> The Public variable declaration does not seem to work. The first
> procedure stores the original range in the Public variable, RAorig.
>
> The second procedure calls that variable and attempts to reference
> it. The result of the Set RA= statment is "nothing." RA is set to
> nothing.
>
> So something is wrong. John Wirt
>
> Here is the code:
> - - - - - - - - - - - - - - - - - - -
> Public RAorig As Range
>
> Sub FindLinkedCell()
> Dim CellFormula As String
> Dim OK As VbMsgBoxResult
> Dim WshName As String
> Dim RAdest As Range
> Dim RAorig As Range
>
> Set RAorig = Selection
> WshName = ActiveSheet.Name
>
> CellFormula = RAorig.Formula
>
> Select Case Left(CellFormula, 1)
> Case ""
> OK = MsgBox("Cell is empty.", vbOKOnly)
> Exit Sub
> Case "="
> Case Else
> OK = MsgBox("Cell value is not a link.", vbOKOnly)
> Exit Sub
> End Select
>
> CellAddress = Mid(CellFormula, 2, Len(CellFormula) - 1)
> Set RAdest = Range(CellAddress)
> Application.Goto Reference:=RAdest, Scroll:=True
> RAdest.Interior.ColorIndex = 40
> End Sub
>
> Public Sub ReturntoCell()
> Dim RAaddress As String
> Dim RA As Range
>
> Set RA = RAorig
> Application.Goto Reference:=RAorig
>
> End Sub
> - - - - - - - - - - - - - - - - - - - - - -
> "Jim Thomlinson" <[email protected]> wrote in
> message news:[email protected]...
> > That should work for you... The only thing to look out for is that
> > it will potentially unload depending how errors are handled...
> >
> > HTH
> >
> > "John Wirt" wrote:
> >
> > > I want to store a range in a public variable that lasts as long
> > > as the workbook is open.
> > >
> > > Will this declaration in a Module header accomplish this:
> > >
> > > Public RA as Range
> > >
> > > Sub XXX...
> > > Set RA=Range("A1")
> > > ....
> > >
> > > The scope of this variable is the project. What is its lifetime?
> > >
> > > John Wirt
> > >
> > >
> > >

Hey, great! It works. Thank you.
I did not know about the .HasFormula and .Formula properties.
John Wirt


"keepITcool" <[email protected]> wrote in message
news:[email protected]...
>
> This should work.
>
> If both procedures are in the SAME module the rngOri variable can be
> private, as long as it's declared at module level.
>
> You only need to declare it public if you want to call it:
> a.from a procedure in another workbook.
> b.from a procedure in another module in the same workbook without
> qualifying the module name.
>
> e.g. from a procedure in module2 rngOri will fail if it's not public,
> but module1.rngOri will work.
>
>
>
> I've reduced the code to the bare minimum but added an error handler
> for formulas like =sum(a1:a2) In your original many of the variables
> aren't truely needed and I happened to know that Excel can handle
> strings like
> Range("=[book1.xls]sheet1!a3") without using Mid$ to strip the "="
>
>
> Option Explicit
>
> Dim rngOri As Range
>
>
> Sub FindLinkedCell()
> Dim rngDst As Range
>
> Set rngOri = Nothing
>
> With ActiveCell
> If Not .HasFormula Then
> MsgBox "cell has no formula"
> Else
> On Error Resume Next
> Set rngDst = Range(.Formula)
> On Error GoTo 0
> If rngDst Is Nothing Then
> MsgBox "cell is not linked to 1 other cell" & vblf & _
> "or destination workbook not open"
> Else
> Set rngOri = .Cells(1)
> Application.Goto rngDst, True
> End If
> End If
> End With
> End Sub
>
> Sub ReturnToCell()
> If rngOri Is Nothing Then
> MsgBox "No cell to return to"
> Else
> Application.Goto rngOri, True
> Set rngOri = Nothing
> End If
> End Sub
>
>
>
>
>
> --
> keepITcool
> | www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> John Wirt wrote :
>
> > The answer is, no, it doesn't work. The first procedure works. If I
> > select a linked cell, the procedure chanages to the worksheet with
> > the destination cell and selects that cell. Colors it dark orange
> > even.
> >
> > The Public variable declaration does not seem to work. The first
> > procedure stores the original range in the Public variable, RAorig.
> >
> > The second procedure calls that variable and attempts to reference
> > it. The result of the Set RA= statment is "nothing." RA is set to
> > nothing.
> >
> > So something is wrong. John Wirt
> >
> > Here is the code:
> > - - - - - - - - - - - - - - - - - - -
> > Public RAorig As Range
> >
> > Sub FindLinkedCell()
> > Dim CellFormula As String
> > Dim OK As VbMsgBoxResult
> > Dim WshName As String
> > Dim RAdest As Range
> > Dim RAorig As Range
> >
> > Set RAorig = Selection
> > WshName = ActiveSheet.Name
> >
> > CellFormula = RAorig.Formula
> >
> > Select Case Left(CellFormula, 1)
> > Case ""
> > OK = MsgBox("Cell is empty.", vbOKOnly)
> > Exit Sub
> > Case "="
> > Case Else
> > OK = MsgBox("Cell value is not a link.", vbOKOnly)
> > Exit Sub
> > End Select
> >
> > CellAddress = Mid(CellFormula, 2, Len(CellFormula) - 1)
> > Set RAdest = Range(CellAddress)
> > Application.Goto Reference:=RAdest, Scroll:=True
> > RAdest.Interior.ColorIndex = 40
> > End Sub
> >
> > Public Sub ReturntoCell()
> > Dim RAaddress As String
> > Dim RA As Range
> >
> > Set RA = RAorig
> > Application.Goto Reference:=RAorig
> >
> > End Sub
> > - - - - - - - - - - - - - - - - - - - - - -
> > "Jim Thomlinson" <[email protected]> wrote in
> > message news:[email protected]...
> > > That should work for you... The only thing to look out for is that
> > > it will potentially unload depending how errors are handled...
> > >
> > > HTH
> > >
> > > "John Wirt" wrote:
> > >
> > > > I want to store a range in a public variable that lasts as long
> > > > as the workbook is open.
> > > >
> > > > Will this declaration in a Module header accomplish this:
> > > >
> > > > Public RA as Range
> > > >
> > > > Sub XXX...
> > > > Set RA=Range("A1")
> > > > ....
> > > >
> > > > The scope of this variable is the project. What is its lifetime?
> > > >
> > > > John Wirt
> > > >
> > > >
> > > >

Cool,

You tightened up the code nicely.
I tried adding a feature that the destination cell is colored after the
reverse lookup. Then the color is removed upon returning to the original
cell.
Unfortunately, the rngDst cell is "Nothing" in the reverse lookup. I don't
understand why. Can you check the code and tell me why? Thank you.

John
- - - - - - - -
Option Explicit

Dim rngOri As Range
Dim rngDst As Range
Dim rngColor As Integer

Sub FindLinkedCell()
Dim rngDst As Range

Set rngOri = Nothing

With ActiveCell
If Not .HasFormula Then
MsgBox "cell has no formula"
Else
On Error Resume Next
Set rngDst = Range(.Formula)
On Error GoTo 0
If rngDst Is Nothing Then
MsgBox "cell is not linked to 1 other cell" & vbLf & _
"or destination workbook not open"
Else
Set rngOri = .Cells(1)
Application.Goto rngDst, True
rngColor = Selection.Interior.ColorIndex
Set Dst = Selection '<--tried adding this,too. makes no difference
rngDst.Interior.ColorIndex = 40
End If
End If
End With
End Sub

Sub ReturnToCell()
If rngOri Is Nothing Then
MsgBox "No cell to return to"
Else
If rngDst Is Nothing Then
MsgBox "No destination cell"
Else
rngDst.Interior.ColorIndex = rngColor
End If
Application.Goto rngOri, True
Set rngOri = Nothing
End If
End Sub
- - - - - - - -



"keepITcool" <[email protected]> wrote in message
news:[email protected]...
>
> This should work.
>
> If both procedures are in the SAME module the rngOri variable can be
> private, as long as it's declared at module level.
>
> You only need to declare it public if you want to call it:
> a.from a procedure in another workbook.
> b.from a procedure in another module in the same workbook without
> qualifying the module name.
>
> e.g. from a procedure in module2 rngOri will fail if it's not public,
> but module1.rngOri will work.
>
>
>
> I've reduced the code to the bare minimum but added an error handler
> for formulas like =sum(a1:a2) In your original many of the variables
> aren't truely needed and I happened to know that Excel can handle
> strings like
> Range("=[book1.xls]sheet1!a3") without using Mid$ to strip the "="
>
>
> Option Explicit
>
> Dim rngOri As Range
>
>
> Sub FindLinkedCell()
> Dim rngDst As Range
>
> Set rngOri = Nothing
>
> With ActiveCell
> If Not .HasFormula Then
> MsgBox "cell has no formula"
> Else
> On Error Resume Next
> Set rngDst = Range(.Formula)
> On Error GoTo 0
> If rngDst Is Nothing Then
> MsgBox "cell is not linked to 1 other cell" & vblf & _
> "or destination workbook not open"
> Else
> Set rngOri = .Cells(1)
> Application.Goto rngDst, True
> End If
> End If
> End With
> End Sub
>
> Sub ReturnToCell()
> If rngOri Is Nothing Then
> MsgBox "No cell to return to"
> Else
> Application.Goto rngOri, True
> Set rngOri = Nothing
> End If
> End Sub
>
>
>
>
>
> --
> keepITcool
> | www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> John Wirt wrote :
>
> > The answer is, no, it doesn't work. The first procedure works. If I
> > select a linked cell, the procedure chanages to the worksheet with
> > the destination cell and selects that cell. Colors it dark orange
> > even.
> >
> > The Public variable declaration does not seem to work. The first
> > procedure stores the original range in the Public variable, RAorig.
> >
> > The second procedure calls that variable and attempts to reference
> > it. The result of the Set RA= statment is "nothing." RA is set to
> > nothing.
> >
> > So something is wrong. John Wirt
> >
> > Here is the code:
> > - - - - - - - - - - - - - - - - - - -
> > Public RAorig As Range
> >
> > Sub FindLinkedCell()
> > Dim CellFormula As String
> > Dim OK As VbMsgBoxResult
> > Dim WshName As String
> > Dim RAdest As Range
> > Dim RAorig As Range
> >
> > Set RAorig = Selection
> > WshName = ActiveSheet.Name
> >
> > CellFormula = RAorig.Formula
> >
> > Select Case Left(CellFormula, 1)
> > Case ""
> > OK = MsgBox("Cell is empty.", vbOKOnly)
> > Exit Sub
> > Case "="
> > Case Else
> > OK = MsgBox("Cell value is not a link.", vbOKOnly)
> > Exit Sub
> > End Select
> >
> > CellAddress = Mid(CellFormula, 2, Len(CellFormula) - 1)
> > Set RAdest = Range(CellAddress)
> > Application.Goto Reference:=RAdest, Scroll:=True
> > RAdest.Interior.ColorIndex = 40
> > End Sub
> >
> > Public Sub ReturntoCell()
> > Dim RAaddress As String
> > Dim RA As Range
> >
> > Set RA = RAorig
> > Application.Goto Reference:=RAorig
> >
> > End Sub
> > - - - - - - - - - - - - - - - - - - - - - -
> > "Jim Thomlinson" <[email protected]> wrote in
> > message news:[email protected]...
> > > That should work for you... The only thing to look out for is that
> > > it will potentially unload depending how errors are handled...
> > >
> > > HTH
> > >
> > > "John Wirt" wrote:
> > >
> > > > I want to store a range in a public variable that lasts as long
> > > > as the workbook is open.
> > > >
> > > > Will this declaration in a Module header accomplish this:
> > > >
> > > > Public RA as Range
> > > >
> > > > Sub XXX...
> > > > Set RA=Range("A1")
> > > > ....
> > > >
> > > > The scope of this variable is the project. What is its lifetime?
> > > >
> > > > John Wirt
> > > >
> > > >
> > > >

John,

well.. your adaptations show you still need to study
a bit more.

Q:
is it possible to define a variable with the same name
both as a Module level and at Procediure level?
A:
Yes, but it's confusing.

Q:
Is it possible to name an integer variable rngColor?
A:
Yes, but it's confusing.

Solution:
see below... oops.. it's become a bit messy.




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


John Wirt wrote :

> Cool,
>
> You tightened up the code nicely.
> I tried adding a feature that the destination cell is colored after
> the reverse lookup. Then the color is removed upon returning to the
> original cell.
> Unfortunately, the rngDst cell is "Nothing" in the reverse lookup. I
> don't understand why. Can you check the code and tell me why? Thank
> you.
>
> John
> - - - - - - - -
> Option Explicit
>
> Dim rngOri As Range
> Dim rngDst As Range
RENAME THIS VARIABLE TO intCOLOR or lngCOLOR
> Dim rngColor As Integer

>
> Sub FindLinkedCell()
REMOVE THIS DIM
> Dim rngDst As Range

> Set rngOri = Nothing

ADD IN CASE USER SELECTS FOLLOW BEFORE RETURN
if not rngDst Is nothing then
rngDst.interior.colorindex=lngColor
set rngDst = nothing
end if

>
> With ActiveCell
> If Not .HasFormula Then
> MsgBox "cell has no formula"
> Else
> On Error Resume Next
> Set rngDst = Range(.Formula)
> On Error GoTo 0
> If rngDst Is Nothing Then
> MsgBox "cell is not linked to 1 other cell" & vbLf & _
> "or destination workbook not open"
> Else
> Set rngOri = .Cells(1)
WHY USE SELECTION?
ALSO I PREFER TO SET MY VARS BEFORE THE GOTO

lngColor = rngDst.interior.colorindex
rngDst.interior.colorindex=40

> Application.Goto rngDst, True

FOLLOWING 2 LINES ARE DONE
> rngColor = Selection.Interior.ColorIndex
> Set Dst = Selection '<--tried adding this,too. makes no
> difference rngDst.Interior.ColorIndex = 40
> End If
> End If
> End With
> End Sub
>
> Sub ReturnToCell()
> If rngOri Is Nothing Then
> MsgBox "No cell to return to"
> Else
> If rngDst Is Nothing Then
> MsgBox "No destination cell"
> Else
> rngDst.Interior.ColorIndex = rngColor
ADD FOLLOWING
set rngDst = Nothing

> End If
> Application.Goto rngOri, True
> Set rngOri = Nothing
> End If
> End Sub
> - - - - - - - -
>
>
>
> "keepITcool" <[email protected]> wrote in message
> news:[email protected]...
> >
> > This should work.
> >
> > If both procedures are in the SAME module the rngOri variable can be
> > private, as long as it's declared at module level.
> >
> > You only need to declare it public if you want to call it:
> > a.from a procedure in another workbook.
> > b.from a procedure in another module in the same workbook without
> > qualifying the module name.
> >
> > e.g. from a procedure in module2 rngOri will fail if it's not
> > public, but module1.rngOri will work.
> >
> >
> >
> > I've reduced the code to the bare minimum but added an error handler
> > for formulas like =sum(a1:a2) In your original many of the variables
> > aren't truely needed and I happened to know that Excel can handle
> > strings like
> > Range("=[book1.xls]sheet1!a3") without using Mid$ to strip the "="
> >
> >
> > Option Explicit
> >
> > Dim rngOri As Range
> >
> >
> > Sub FindLinkedCell()
> > Dim rngDst As Range
> >
> > Set rngOri = Nothing
> >
> > With ActiveCell
> > If Not .HasFormula Then
> > MsgBox "cell has no formula"
> > Else
> > On Error Resume Next
> > Set rngDst = Range(.Formula)
> > On Error GoTo 0
> > If rngDst Is Nothing Then
> > MsgBox "cell is not linked to 1 other cell" & vblf & _
> > "or destination workbook not open"
> > Else
> > Set rngOri = .Cells(1)
> > Application.Goto rngDst, True
> > End If
> > End If
> > End With
> > End Sub
> >
> > Sub ReturnToCell()
> > If rngOri Is Nothing Then
> > MsgBox "No cell to return to"
> > Else
> > Application.Goto rngOri, True
> > Set rngOri = Nothing
> > End If
> > End Sub
> >
> >
> >
> >
> >
> > --
> > keepITcool
> > > www.XLsupport.com | keepITcool chello nl | amsterdam
> >
> >
> > John Wirt wrote :
> >
> > > The answer is, no, it doesn't work. The first procedure works. If
> > > I select a linked cell, the procedure chanages to the worksheet
> > > with the destination cell and selects that cell. Colors it dark
> > > orange even.
> > >
> > > The Public variable declaration does not seem to work. The first
> > > procedure stores the original range in the Public variable,
> > > RAorig.
> > >
> > > The second procedure calls that variable and attempts to reference
> > > it. The result of the Set RA= statment is "nothing." RA is set to
> > > nothing.
> > >
> > > So something is wrong. John Wirt
> > >
> > > Here is the code:
> > > - - - - - - - - - - - - - - - - - - -
> > > Public RAorig As Range
> > >
> > > Sub FindLinkedCell()
> > > Dim CellFormula As String
> > > Dim OK As VbMsgBoxResult
> > > Dim WshName As String
> > > Dim RAdest As Range
> > > Dim RAorig As Range
> > >
> > > Set RAorig = Selection
> > > WshName = ActiveSheet.Name
> > >
> > > CellFormula = RAorig.Formula
> > >
> > > Select Case Left(CellFormula, 1)
> > > Case ""
> > > OK = MsgBox("Cell is empty.", vbOKOnly)
> > > Exit Sub
> > > Case "="
> > > Case Else
> > > OK = MsgBox("Cell value is not a link.", vbOKOnly)
> > > Exit Sub
> > > End Select
> > >
> > > CellAddress = Mid(CellFormula, 2, Len(CellFormula) - 1)
> > > Set RAdest = Range(CellAddress)
> > > Application.Goto Reference:=RAdest, Scroll:=True
> > > RAdest.Interior.ColorIndex = 40
> > > End Sub
> > >
> > > Public Sub ReturntoCell()
> > > Dim RAaddress As String
> > > Dim RA As Range
> > >
> > > Set RA = RAorig
> > > Application.Goto Reference:=RAorig
> > >
> > > End Sub
> > > - - - - - - - - - - - - - - - - - - - - - -
> > > "Jim Thomlinson" <[email protected]> wrote
> > > in message
> > > news:[email protected]...
> > > > That should work for you... The only thing to look out for is
> > > > that it will potentially unload depending how errors are
> > > > handled...
> > > >
> > > > HTH
> > > >
> > > > "John Wirt" wrote:
> > > >
> > > > > I want to store a range in a public variable that lasts as
> > > > > long as the workbook is open.
> > > > >
> > > > > Will this declaration in a Module header accomplish this:
> > > > >
> > > > > Public RA as Range
> > > > >
> > > > > Sub XXX...
> > > > > Set RA=Range("A1")
> > > > > ....
> > > > >
> > > > > The scope of this variable is the project. What is its
> > > > > lifetime?
> > > > >
> > > > > John Wirt
> > > > >
> > > > >
> > > > >

Cool,
Thanks again.
Re-declaring the module level variable, rngDst, in the procedure was a
mistake.
OK, using sensible notation (intColor, not rngColor for an interger
variable).is sensible.
Yes, I got lazy and used "Selection." A bit sloppy.
It works.
John