CODEWITHSUNDEEP
vbaoutlook
Martin KS
Martin KS

Reputation: 531

Storing Key-Value pairs in VBA macro

The final stage of a long macro that I've written in outlook is to route an email to the appropriate person, depending on all of the things that the macro has found out. I've done this before using an excel sheet and a vlookup of the "Key" value - but in this case the key-value pairs will be static, and so excel feels like overkill, and will slow things down.

Both the key and value will be strings, less than 50 characters each most likely, but there will probably be around 1500 pairs. Would compiling it into some kind of DLL or something be best?

Upvotes: 0

Views: 1546

Answers (1)

cup
cup

Reputation: 8257

A simple way is to stick all the values into a text file with the key and value separated by a space (or other suitable character if there are spaces in the key or value) and then add them into the dictionary. On my 300MHz P3, this takes less than a second to run.

Dim objDict As Object, objFSO As Object, objFile As Object
Dim strLine As String, strs As Variant

Set objDict = CreateObject("Scripting.Dictionary")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strFile, forReading)
For ii = 1 To 1500
    strs = Split(objFile.ReadLine(), " ")
    objDict.Add strs(0), strs(1)
Next
objFile.Close

Upvotes: 4

Related Questions