Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring a Public variable as a Range and its lifetime
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring a Public variable as a Range and its lifetime
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring a Public variable as a Range and its lifetime
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" wrote 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 "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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring a Public variable as a Range and its lifetime
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" wrote 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 "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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring a Public variable as a Range and its lifetime
Hey, great! It works. Thank you.
I did not know about the .HasFormula and .Formula properties. John Wirt "keepITcool" wrote in message ft.com... 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" wrote 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 "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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring a Public variable as a Range and its lifetime
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" wrote in message ft.com... 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" wrote 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 "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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring a Public variable as a Range and its lifetime
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" wrote in message ft.com... 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" wrote 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 "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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring a Public variable as a Range and its lifetime
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Declaring variables in Module vs. Public | Excel Discussion (Misc queries) | |||
Declaring Variables Public | Excel Programming | |||
Declaring Variables Public | Excel Programming | |||
declaring a public array | Excel Programming | |||
Declaring Public Procedure | Excel Programming |