Can soemone reocmmend the best way to build a stack for pushing and pulling
ranges?

I am thinking of using a collection object called, "stack," and defining
push, pull, and empty methods.

John Wirt

Sounds like a candidate for a Stack class.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"John Wirt" wrote in message
...
Can soemone reocmmend the best way to build a stack for pushing and
pulling
ranges?

I am thinking of using a collection object called, "stack," and defining
push, pull, and empty methods.

John Wirt

Here's a very simple example that I just knocked up

Option Explicit

Private mStack As Object
Private mIndex As Long

Public Property Get Address(Optional idx As Long)
Dim oItems
If idx = 0 Then
If mIndex 0 Then
idx = mIndex
End If
End If
If idx < 0 Then
oItems = mStack.items
Address = oItems(idx - 1).Address
Else
Address = "Stack is empty"
End If
End Property

Public Function Push(rng As Range)
mIndex = mIndex + 1
mStack.Add CStr(mIndex), rng
End Function

Public Function Pull()
mIndex = mIndex - 1
If mIndex < 0 Then mIndex = 0
End Function

Public Function IsEmpty() As Boolean
IsEmpty = mIndex = 0
End Function

Private Sub Class_Initialize()
Set mStack = CreateObject("Scripting.Dictionary")
End Sub

Private Sub Class_Terminate()
Set mStack = Nothing
End Sub


and it could be used like this

Dim stack As clsStack

Set stack = New clsStack
stack.Push Range("A1:B5")
stack.Push Range("M1:P20")
MsgBox stack.Address
stack.Pull
MsgBox stack.Address
stack.Pull
MsgBox stack.Address
MsgBox stack.IsEmpty


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Phillips" wrote in message
...
Sounds like a candidate for a Stack class.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"John Wirt" wrote in message
...
Can soemone reocmmend the best way to build a stack for pushing and
pulling
ranges?

I am thinking of using a collection object called, "stack," and defining
push, pull, and empty methods.

John Wirt

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

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

'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

You might want to consider a simpler approach to your stack. I note from
this thread that you do not intend to store the actual data/cell contents of
the range in your stack - just references to ranges. So why not create a
stack of string labels,eg, txtLABEL instead, when you need to use them use
RANGE(txtLABEL).
If you have a fancy range then assign to a label first, and use that label
name.

Your push/pop can just be a simple function,eg:

call push("A1:B10")
lab = pop()
range(lab).clear

This way you can even implement your push/pull/pop stack using Personal.xls
so that it will be global to your session and even survive when you close
Excel.
Eg, use Column IV as the stack array, cell IU1 as the stack pointer.

Happy to show you push/pop code (in LIFO) for this if you want.

Regards,
Antonio Elinon

"John Wirt" wrote:

Can soemone reocmmend the best way to build a stack for pushing and pulling
ranges?

I am thinking of using a collection object called, "stack," and defining
push, pull, and empty methods.

John Wirt

Antonio..

Using a range a the stack is not a bad idea.

however
did you note my class can handle strings as well as range objects?

Dim oStack as CStack

Sub foo()
Set oStck = New CStack
With oStack
.Push Range("A1:B5").address
.Push Range("M1:P20").address

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




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


Antonio Elinon wrote in


Eg, use Column IV as the stack array, cell IU1 as the stack pointer.

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

Here is the code to implement a string stack in personal.xls

Public Sub push(txt As String)
Dim ptr As Integer
' STACK OVERFLOW: item not pushed but pointer incremented
On Error Resume Next
ptr = 1 + Workbooks("personal.xls").Worksheets("Stack").Rang e("A1")
Workbooks("personal.xls").Worksheets("Stack").Rang e("B" & ptr) = txt
Range("A1") = ptr
End Sub

Public Function pop() As String
Dim ptr As Integer
' STACK OVERFLOW: return blanks, decrement pointer
' STACK EMPTY: Range("A1") = not positive
On Error Resume Next
ptr = Val(Workbooks("personal.xls").Worksheets("Stack"). Range("A1"))
If ptr 0 Then
pop = Workbooks("personal.xls").Worksheets("Stack").Rang e("B" & ptr)
Workbooks("personal.xls").Worksheets("Stack").Rang e("B" & ptr) = ""
Workbooks("personal.xls").Worksheets("Stack").Rang e("A1") = ptr - 1
Else
pop = ""
End If
End Function

Public Sub initialise()
Workbooks("personal.xls").Worksheets("Stack").Rang e("A:B").Clear
End Sub

to use:
call push("c1:d10")
lab = pop()
range(lab).clear

Regards,
Antonio Elinon

"keepITcool" wrote:

Antonio..

Using a range a the stack is not a bad idea.

however
did you note my class can handle strings as well as range objects?

Dim oStack as CStack

Sub foo()
Set oStck = New CStack
With oStack
.Push Range("A1:B5").address
.Push Range("M1:P20").address

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




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


Antonio Elinon wrote in


Eg, use Column IV as the stack array, cell IU1 as the stack pointer.

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