Is it possible to access collection which is at the bottom of stack?

Question

The problem that I face is, I have a object/collection which will be created on the bottom of the stack and I have a program 3 levels deep, the collection will be only used at the 3(top) level, the first item will be used and removed from the collection, but I want to keep it for the entirety of the program as the next step needs to use the next item in the collection until the whole thing is done.

The best way I would like to do it is the create that collection in the bottom layer which is where the collection will be used, and keep the collection even if the collection is out of scope.

The way I am doing it right now is to create the collection at the bottom most level and pass it down the chain, cause if I create it in the top layer it will be deleted after it goes out of scope

I feel like there must be a better way solve my problem, but I just can't find it. Does anyone knows the answer?

I just set up some text in excel as follows

(A) (1)

(B) (2)

(C) (3)

(D) (4)

(E) (5)

'The Code works, but what I am asking is it possible to dont pass dict through all those sub

Sub Main()

Static dict As New Dictionary
Dim x As Integer

Set dict = readrange

Do While x < 3
    somesub dict
    x = x + 1
Loop


End Sub

'----------------------- Next Module ----------------------------------------------------

Sub somesub(dict As Dictionary) '<----------------------- Dont want this dict
    'some code which doesnt not use the dict
    Dictchange dict

End Sub

'----------------------- Next Module ----------------------------------------------------

Sub Dictchange(dict As Dictionary)  '<----------------------- Dont want this dict too

    Cells(dict(dict.Keys(0)), 4) = "Done"

    'Is it possible to call dict in Main without pass the chain
    'I cant use public as in the real code, "somesub" and "Dictchange" are in different module
    'I could use Global, but i always feel like it just a "Dirty" way to fix thing

    dict.Remove dict.Keys(0)

End Sub

'----------------------- Next Module ----------------------------------------------------

'In the real code, this is one function in a class Module
Function readrange() As Dictionary

Dim temp As New Dictionary
For i = 1 To 5

    temp.Add Cells(i, 1).Value, Cells(i, 2).Value

Next i

Set readrange = temp

End Function

I hope this would help

A [global variable](https://stackoverflow.com/questions/2722146/how-do-i-declare-a-global-variable-in-vba) might solve your issue? Otherwise please provide a [mcve] to illustrate your issue. — Pᴇʜ, Jan 29 '19 at 14:48
Your comment on `Dictchange` is "Dont want this dict too", yet you do use that dict in the Sub? It's kind of unclear exactly what the issue is here. — Tim Williams, Jan 29 '19 at 17:26

score 0 · Answer 1 · answered Jan 30 '19 at 08:09

As I already told in my comment: Make your dict a global variable.

Option Explicit

Public dict As Dictionary 'this is globally defined in a module

Sub Main()
    Dim x As Long

    Set dict = ReadRangeToDict

    Do While x < 3
        SomeProcedure
        x = x + 1
    Loop
End Sub

Function ReadRangeToDict() As Dictionary
    Dim TempDict As New Dictionary

    Dim iRow As Long
    For iRow = 1 To 5
        If Not TempDict.Exists(Cells(iRow, 1).Value) Then 'check if key already exists to prevent errors!
            TempDict.Add Cells(iRow, 1).Value, Cells(iRow, 2).Value
        End If
    Next iRow

    Set ReadRangeToDict = TempDict
End Function

So you can access it in any other procedure/function without giving it as a parameter.

Sub SomeProcedure()
    'output the dict in the immediate window

    Dim itm As Variant
    For Each itm In dict
        Debug.Print itm, dict(itm)
    Next itm
End Sub

Is it possible to access collection which is at the bottom of stack?

1 Answers1