How to lookup value in VBA
Dictionary object in VBA is a substitute of excel vlookup function. In this post i will explain how you implement dictionary in your code. How you can add keys and values in dictionary object.
What is Dictionary Object?
Consider dictionary as two columns array, 1. Keys 2. Items. where you can save your data against unique key. Once you save you keys and item in dictionary, you can use it extract items providing the keys. it works like Vlookup in excel. So, you can save your vlookup table array in dictionary and then later provide lookup value to extract the information.
How to create dictionary object?
In my last post Excel Outlook Automation i explained Late Binding and Early Binding. We can use either way to get the dictionary reference as dictionary is not VBA native object. this is how you create a dictionary object.
Dim objDic as Object
Set objDic = CreateObject(“Scripting.Dictionary”)
How to add keys and Items in Dictionary?
There are two ways you add keys and items in dictionary. most of the time i use second method but sometime i have to use first method. have a look at first method below.
Dim strKey as string
Dim strValue as string
objDic.Add strKey, strValue
You added a key which is “City” and its item as “Delhi”. Now, it will throw an error if you try to add this key again. There is one method to check if the key is already available in dictionary or not. So, this would be the proper syntax to add a key in dictionary.
If not objDic.Exists(strKey) then objDic.Add strKey, strItem
Here is second method –
objDic.item(strKey) = strItem
This second method is awesome, you don’t need to check if key is already added or not, if key is available this method will replace item on that key. if key is not available it will add it. so this is what i prefer to use.
How to Remove item from dictionary?
You can remove one item, or you can clear all items from dictionary by using these two methods
Is Dictionary Case-Sensitive?
Let’s say you have keys like “city” and “City”. Both are same but not technically same. both are in different case. if you add these in dictionary. they both will get added without any problem. Because by default dictionary is case sensitive. it will assume “city” and “City” are two different keys. you can change this behaviour by using CompareMode property.
objDic.CompareMode=vbTextCompare ‘– This will make dictionary non-case sensitive
objDic.CompareMode=vbBinaryCompare‘– This will make dictionary case sensitive
Now, lets get back to our topic “How to lookup value in VBA”. Let’s me show you a real example of dictionary where it can be used to lookup value and will reduce the number of loops. Main purpose of dictionary is to reduce the number of loops to make your code faster.
You can find examples in attached file. there are three examples
- Lookup First value
- Lookup Last value
- Lookup All values.
I would love to see your program to lookup the second value. please share your code in comments. download file from the link below and please share the post if you learn something great today.Dictionary-Example.xlsm (153 downloads)
See more here :