Declaring a Public variable as a Range and its lifetime

J

John Wirt

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
 
G

Guest

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

HTH
 
B

Bob Phillips

That should do it.

Did you try it and come across a problem?

--

HTH

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

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
- - - - - - - - - - - - - - - - - - - - - -
 
K

keepITcool

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 :
 
J

John Wirt

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


keepITcool said:
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
- - - - - - - - - - - - - - - - - - - - - -
Click to expand...
Click to expand...
 
J

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 said:
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
- - - - - - - - - - - - - - - - - - - - - -
Click to expand...
Click to expand...
 
K

keepITcool

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
Click to expand...
RENAME THIS VARIABLE TO intCOLOR or lngCOLOR
Dim rngColor As Integer
Click to expand...
Sub FindLinkedCell() REMOVE THIS DIM
Dim rngDst As Range
Click to expand...
Set rngOri = Nothing
Click to expand...

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)
Click to expand...
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
Click to expand...

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
Click to expand...
ADD FOLLOWING
set rngDst = Nothing
End If
Application.Goto rngOri, True
Set rngOri = Nothing
End If
End Sub
- - - - - - - -



keepITcool said:
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
Click to expand...


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
- - - - - - - - - - - - - - - - - - - - - -
in message
That should work for you... The only thing to look out for is
that it will potentially unload depending how errors are
handled...

HTH

:

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
Click to expand...
Click to expand...
Click to expand...
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

What is lifetime of public variable? 4
Storing a Range in a Public Variable 8
Need help with FORMULAR1C1 property 3
Public variable 7
Declare a Public Array 5
Sound repeats unending until closing workbook 2
Variable scope and lifetime 1
Save As and new workbook new name used in existing code 8

Top