personally i'll stick with my class...

further:
I'd always use Long and never Integer when working with rows.
In fact I almost never use integers. It may save 2 bytes, but slows
down the code (fractionally;) and you always have to think about it's
32k limit... In your worksheet stack it doesnt really matter if you
bounce at 32k or 65k...

I'd stay away from "personal.xls" as I think it should be reserved for
exclusive use of the user.


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


Antonio Elinon wrote in


kIC,

Thanks. I did notice your code can handle multiple types. My point
was to suggest that personal.xls can implement the stack and thus it
is limited to strings only, therefore the need to use Range(label)
after popping. On exit from Excel, personal.xls can be saved and the
stack will still be operational with other macros in personal.xls

To Cool,

Unfortunately, I do not understand the reason for the "Sub Init()"
formulation in your example code.

Following your instructions, here is the code I've written:

'NORMAL MODULE==============
Option Explicit
Public rngStack As Cstack
Dim lnkColor As Integer
Public Const lnkWhite = 0
Public Const lnkYellow = 36
Public Const lnkOrange = 40

Sub CellStack_Initialize()
Set rngStack = New Cstack
End Sub

Sub AddCellStack()
Dim Wsh As Worksheet, Wshtmp As Worksheet
Dim Flag As Boolean
Dim rngDst As Range, rngOri As Range
Dim MarkCellsQ As String
Set rngOri = Nothing
MarkCellsQ = "ON"
lnkColor = lnkOrange
If Not rngDst Is Nothing Then
Set rngDst = Nothing
End If

With ActiveCell
If Not .HasFormula Then
MsgBox "Selected cell has no formula"
Else
On Error Resume Next
Set rngDst = Range(.Formula)
On Error GoTo 0
If rngDst Is Nothing Then
MsgBox "Formula in starting cell is linked to more than" & vbLf & _
"one cell or destination workbook is not open.", vbOKOnly
Else
'Store originally selected cell
If rngStack.Count = 0 Then
rngStack.Push .Cells(1)
End If
Set rngOri = .Cells(1)
Set Wsh = rngDst.Worksheet
For Each Wshtmp In Sheets
If Wshtmp.Visible = xlSheetHidden Then
If Wshtmp Is Wsh Then
Flag = True
End If
End If
Next
If Flag Then
MsgBox "Worksheet containing the cell linked to" & vbLf & _
"is hidden. Unhide it.", vbOKOnly
Exit Sub
End If
Wsh.Activate
rngStack.Push rngDst
If MarkCellsQ = "ON" Then
rngDst.Interior.ColorIndex = 40
rngOri.Interior.ColorIndex = 40
End If
rngDst.Select
End If
End If
End With
End Sub

Sub ReturnCellStack()
Dim Wsh As Worksheet
Dim rngDst As Range, rngOri As Range
If rngStack.Count < 2 Then
MsgBox "No cell to return to."
Else
Set rngDst = rngStack.Pop <==ERROR HERE
rngDst.Interior.ColorIndex = 36
Set rngOri = rngStack.Pop
Set Wsh = rngOri.Worksheet
Wsh.Activate
rngStack.Push rngOri
End If
End Sub

'THISWORKBOOK module=================
Option Explicit
Private Sub Workbook_Open()
Dim M As Variant
Application.OnKey "^{[}", "AddCellStack"
Application.OnKey "^{]}", "ReturnCellStack"
Call CellStack_Initialize
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnKey "^{[}"
Application.OnKey "^{]}"
End Sub
'======================================
So, this code is intended to map a lookup function to the Ctrl-[ key
sequence and Ctrl-] to undo the lookup. The functions are to be used to
start from a worksheet that has formulas in it that are linked to a formulas
in another worksheet, which are linked to numerical values in another
worksheet. There can be one initial worksheet with formulas linked to
another worksheet with numbers in it, or multiple worksheets with formulas
in them with the last in this sequence of linked worksheets containing
numerical values. This code will be used in a government statistical agency,
where statistical program output is pasted into the last owrksheet in a
workbook and then finished tables are built from it by linking the numbers
to appear in the finished table (which are rounded to a certain number of
digits) to certain numbers on the output worksheet (or the output may be in
a separate workbook). There can be multiple levels of llinking from one
worksheet to another and across workbooks. My initial goal is to get the two
key sequences to work with simple lookup formulas, such as :
='Worksheet3'!G34. Once this works I will try to figure out a way of looking
up more complicated formulas. Even this simple first step will be useful,
though.

With a series of Ctrl[, Ctrl[, Ctrl[, Ctrl], Ctrl], Ctrl]s, the analyst will
then be able to check that all the links in the workbook are to the correct
numbers from worksheet to worksheet. You may know that the foward key
sequence, Ctrl[. is built into Excel but the return key sequence, Ctrl], is
not.

The Ctrl [ code above, which runs AddCellStack, works. The return, Ctrl ],
does not. An error at the point shown above occurs. The error is, "block
variable is not set." Obviously, the range added to the public "stack"
variable, rngStack, by the "_Initialize" procudure, does not survive as a
global" variable.

Why not?

Thanks.

John Wirt


"keepITcool" wrote in message
.com...
'normal module
Dim oStack as CStack

Sub Init()
'initialize the stack
Set oStack = new CStack
'rest of your code..
End sub

the instance of the cstack class resides where you dim the variable
oStack. (The collection resides in each instance of the class)

BTW: you could use Dim oStack as New CStack.. BUT the compiler will
wrap each call to oStack with a construct like if ostack is nothing
then set oStack = new Cstack, degrading performance.



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


John Wirt wrote in

OK, the "With New CStack" syntax is cool but I want the stack to be a
"global" variable that lasts for the current session of Personal.xls.
I want to push ranges onto the stack from multiple runs of a macro.

A global variable is defined by a Dimension statement in the
declaration section of a module:

Dim oStack as CStack

But what about the assignment statement,

Set oSTack = New CStack?

That won't work in the declaration statement. What can I do instead?

Where does the stack that is created by the "New" statement reside?
In the class module? With your syntax ('With New CStack') does the
stack that is created survive the instance of creation and store all
ranges pushed sequentially on it by separate executions of the foo
procedure?

Thanks.

John Wirt


"keepITcool" wrote in message
. com...
Bob,

Nice! ... But..

A collection maintains an ordered list and the dictionary does not.
imo this makes a collection better suited for a stack then a
dictionary.

so i knocked up following:
note: mIndex removed
note: Returns the actual range iso it's address

'CLASS MODULE CStack====================================
Option Explicit

Private mStack As Collection

Public Property Get Item(Optional ByVal idx As Long = -1)
With mStack
If Not IsEmpty Then
If idx = -1 Then
idx = .Count
End If
If IsObject(.Item(idx)) Then
Set Item = .Item(idx)
Else
Item = .Item(idx)
End If
End If
End With
End Property
Public Function Push(rng As Range)
mStack.Add rng
End Function
Public Function Pop()
mStack.Remove mStack.Count
End Function
Public Function Count() As Long
Count = mStack.Count
End Function
Public Function IsEmpty() As Boolean
IsEmpty = (Count = 0)
End Function

Private Sub Class_Initialize()
Set mStack = New Collection
End Sub

'NORMAL MODULE===========================================
Sub foo()
'Dim oStack as CStack
'Set oStck = New CStack
'With oStack

With New CStack 'cool syntax?!
.Push Range("A1:B5")
.Push Range("M1:P20")

MsgBox .Item.Address & vbLf & _
"Stack contains:" & vbLf & _
.Item(2).Address & vbTab & _
.Item(1).Address
.Pop
MsgBox .Item.Address
.Pop
MsgBox .IsEmpty
End With
End Sub


"keepITcool" wrote in message
.com...

personally i'll stick with my class...

further:
I'd always use Long and never Integer when working with rows.
In fact I almost never use integers. It may save 2 bytes, but slows
down the code (fractionally;) and you always have to think about it's
32k limit... In your worksheet stack it doesnt really matter if you
bounce at 32k or 65k...

I'd stay away from "personal.xls" as I think it should be reserved for
exclusive use of the user.


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


Antonio Elinon wrote in


kIC,

Thanks. I did notice your code can handle multiple types. My point
was to suggest that personal.xls can implement the stack and thus it
is limited to strings only, therefore the need to use Range(label)
after popping. On exit from Excel, personal.xls can be saved and the
stack will still be operational with other macros in personal.xls

John see my answer elsewhere in the thread
--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


John Wirt wrote in

To Cool,

Unfortunately, I do not understand the reason for the "Sub Init()"
formulation in your example code.