How to make a public dictionary in an Excel

Question

I have something like this:

Private Sub ComboBox1_Change()
'(...do something related to alpha...)
End Sub

Private Sub UserForm_Activate()
Set alpha = CreateObject("Scripting.Dictionary")
'(...do something like add keys and values to the alpha dictionary...)
End Sub

I'm really new at VBA and I noticed that alpha dictionary can only be used inside UserForm_Activate, so I want to make a global variable in order to use it also in ComboBox1_Change.

But I can't.

I tried with something more simple, like an integer.

Public x as Integer

Private Sub ComboBox1_Change()
MsgBox(x)
End Sub

Private Sub UserForm_Activate()
x = 5
End Sub

And this work, but if I do something similar but with a dictionary (Public alpha as Scripting.Dictionary), it raise (from spanish) "Compilation error: It wasn't defined the type defined by the user". I don't know what to do.

Set your variable outside sub `Public alpha As Scripting.Dictionary` — 0m3r, Dec 09 '17 at 02:35
See [get unique values from array](https://stackoverflow.com/a/3017950/4408538) for an example in action. — Joseph Wood, Dec 09 '17 at 02:36
@0m3r Error: "Compilation error: It wasn't defined the type defined by the user". — Ender Look, Dec 09 '17 at 02:38
To add the reference to your VBA project, go to the VB Editor, and select Tools --> References from the menu. Select Microsoft Scripting Runtime from the list of installed libraries, and click OK. — 0m3r, Dec 09 '17 at 03:08
@0m3r, sorry, but what are you talking about "set a reference"? — Ender Look, Dec 09 '17 at 03:11
Read something about `scope of variables` [here](https://support.microsoft.com/en-us/help/141693/scope-of-variables-in-visual-basic-for-applications) or [here](http://www.cpearson.com/excel/scope.aspx). And read about `scripting dictionary` e.g. [here](https://excelmacromastery.com/vba-dictionary/). And in case you didn't use `Option Explicit` then try to use it. HTH — Daniel Dušek, Dec 09 '17 at 08:56

score 2 · Answer 1 · answered Dec 09 '17 at 02:35

2

Using Public Alpha As Object above everything. Then Alpha can be change to a dictionary using Set alpha = CreateObject("Scripting.Dictionary").

answered Dec 09 '17 at 02:35

Ender Look

2,303
2
17
41

Kostas K. · Accepted Answer · 2017-12-09T07:45:06.190

Having a Public object withing a Form's Class module is a bad idea. Unless the object should be accessible only within the class, thus the accessibility should be changed to Private, consider having it in a Standard module.

You could also expose it by a Public property so you don't have to worry about the object's construction.

Private objAlpha As Object

Public Property Get Alpha() As Object
    If objAlpha Is Nothing Then Set objAlpha = CreateObject("Scripting.Dictionary")
    Set Alpha = objAlpha
End Property

To call it:

If Alpha.Exists("SomeText") Then
    '...
End If

This way it can be referenced anywhere in your project.

How to make a public dictionary in an Excel

2 Answers2