2

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.

Community
  • 1
  • 1
kiltek
  • 3,183
  • 6
  • 47
  • 70

1 Answers1

5

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

enter image description here

Useful read about the Dictionary class

Community
  • 1
  • 1
  • Thanks! There seems to be no "contains" method in both classes, but in the Dictionary class there is something similar: the "exists"-method. Can i use that in the same fashion as a "contains"-method ? – kiltek Oct 17 '13 at 11:53
  • @kiltek check [**`this out`**](http://stackoverflow.com/questions/137845/determining-whether-an-object-is-a-member-of-a-collection-in-vba) - shows how to implement a method to check if a key exists –  Oct 17 '13 at 11:57
  • 1
    The exists function does suit my needs. So when importing something through the excel vba editor i have to do the following "In the vba editor -> Tools -> References -> add needed library". This is not really different from java, but i do not need an extra import. – kiltek Oct 17 '13 at 14:08