excel vba import system.collections.hashmap into a

Question

From the inside of my MS Excel 2010 installation I have opened the Visual Basic Editor. (Tab Developer Tools -> Visual Basic)

Inside the Visual Basic Editor i right-clicked into the Project Window and created a module. (Insert -> Module)

Then i typed the following code into the Code Window:

Sub myFunction()
    'do some stuff to my table cells, not important
End Sub

After coding a lot of things inside the function body, i thought i was in need for a Hashmap, which needs to be accessed inside that given function body. Unfortunately, i could not import the class needed by doing this:

Imports System.Collections
Sub myFunction()
    'do some stuff to my table cells, not important
End Sub

The error message appears when launching the module by pressing F5. And it says (translated from geman to english by me): "Error while Compiling: illegal outside of a procedure".

How is it possible to have something imported in VBA, when the code is structured like above? I'am usually a java or python person. You can also re-structure the code, if the function still executes.

Answer 1

In this case you either use a

Collection class

or

Dictionary class

---

Collection class is built-in into VBA so you do not need to add external references to be able to use it. You can simply declare it

Dim c as Collection
Set c = new Collection

Collection exposes 4 methods: add, count, item, remove so it may not be sufficient for you.

Customizing, expanding Collection class

---

If you want to use something like a HashTable/HashMap then add references to Microsoft Scripting Runtime by clicking Tools and References in the VBE window

then you can use early-binding and intellisense with the Dictionary class

Dim d as Dictionary
Set d = new Dictionary

Or use late-binding

Dim d as Object
set d = CreateObject("Scripting.Dictionary")

I would go for the early-binding (first example - adding references to VBA project) so you can use VBA Intellisense.

You can view Dictionary or Collection class using the Object Browser - simply hit F2 in the VBE window and type in Dictionary or Collection

Useful read about the Dictionary class