VBA list reference to

Question

I would like to store a multi-dimenension list into memory in excel VBA to preform a look up function. Using an vendor id, which can be alpha numeric, return three other values associate with that vendor. Use a mailing address as an example. I have a list of all vendors that I do business with along with their mailing address. When creating an invoice for these vendors, I want to be able to lookup/ refer to their mailing address line 1 , line 2 , zip code etc based upon their name.

The data is stored as

"Abc Corportation", 123 main st, New York, New York, 12345  
"My Company", 456 not here, Buffalo , Newy York, 12367  
etc.

When creating the bill, I have "Abc Corportation" and I want to return the 123 Main st, new york new york.

I would prefer not having to create an loop to scan through the list each time I want to lookup a vendor address.

What have you tried so far? We aren't here to write code for you, but to help correct problematic code. — Adam, Aug 18 '14 at 16:12
You can store these pairs in an array, a collection, or a dictionary. While you may prefer to avoid iteration over an array, [it is actually up to 10x faster than using the `Application.Match` function](http://stackoverflow.com/questions/18754096/matching-values-in-string-array). — David Zemens, Aug 18 '14 at 16:29
Take a look at storing this information in a `Dictionary` object, where the `Key` is your vendor name, and the `Value` is the comma-delimited address. [Using Dictionary Object in VBA](http://stackoverflow.com/questions/915317/does-vba-have-dictionary-structure) — StoriKnow, Aug 18 '14 at 16:30
But the broader question is how you intend to implement this, i.e., how you will *get* these values in to memory. If they're coming from the workbook itself, seems easier (although maybe not faster) to just use VLOOKUP. — David Zemens, Aug 18 '14 at 16:32
@DavidZemens Good point. The assumption I have is `in-memory` really means `in-memory`, not necessarily stored in the workbook itself. If this is the case then native Excel `VLOOKUP` may be ideal for simplicity sake. — StoriKnow, Aug 18 '14 at 16:35
But where are you *getting* the values from? You'll have to instantiate this `in-memory` list somehow. You can't **save** the list in memory to persist indefinitely... And when it is out of scope, you'll have to re-instantiate it (from a file source). — David Zemens, Aug 18 '14 at 16:40
To Adam: I can load it into an array and loop through it with each pass but worst case scario, 1,000 vendors * 1,000 invoices = 1,000,000 passes. Where if I can use a string as the key such as PHP does, it would reduce it down to 1,000 iterations. — Kevin, Aug 18 '14 at 20:07
@DavidZemens With regard to the speed difference, I agree about using Match against a VBA array; but it seems to run faster than iteration when using it against a range object. Does that agree with your findings? — Ron Rosenfeld, Aug 19 '14 at 01:20
@RonRosenfeld I haven't tested it. It's normally the case that performing operations in memory are less expensive than the same/similar operations carried out on objects. It may seem cumbersome, but it's probably faster to store the range `.Value` in an array (that can typically be done in one statement like: arrayValues = Range("A1:D10000").Value`) and iterate the array, rather than using the worksheet function methds. But I have not tested it for performance and it may be the case that the sheet functions are optimized. Let me know what you find out if you do test it!! — David Zemens, Aug 19 '14 at 02:45
@DavidZemens When I tested it here (and I used worksheetfunction.match; not application.match), it ran about twice as fast using .match on the range compared with iterating through the array (using a Do loop and incrementing the pointer inside the loop. (Using .match on a 2D array was much slower, as you found). If your findings were different, I'd want to compare the specifics of how we each tested. That's why I asked. — Ron Rosenfeld, Aug 19 '14 at 03:54

score 0 · Answer 1 · answered Aug 18 '14 at 21:04

Props to David Zemens for his answer / question, vlookup is the way to go as long as the data is format with the index in the left most column.

 aValue = "ABC Company"

 answer = ""

 Dim myRange As Range
 On Error Resume Next
 Set myRange = Worksheets("Vendor Lookup").Range("A1:D99")
 answer = Application.WorksheetFunction.VLookup(aValue, myRange, 2, False)
 If answer <> "" Then MsgBox (answer)

Thanks all for the quick repsonses.

score 0 · Answer 2 · answered Aug 19 '14 at 02:47

Kevin -- First: Congrats on finding your own solution! I hope you don't mind if I show you a few things here, based on your original solution.

With something like this problem, there are going to be more than a few ways to do it. Rather than show you other ways, I just wanted to show you a few ways to improve your existing solution.

First and foremost, declare all variables, and type them as strongly as allows. This helps avoid hard-to-find errors. On a related note, using Option Explicit at the top of each module will help enforce this. While it sometimes seems a PITA to declare everything, it's much more inconvenient to spend hours debugging simply typographical errors.

NOTE: In some other languages like python, a line like this implies that answer is of type string.

answer = ""

However, in vba, any undeclared variable is of type Variant, which can essentially be anything. The following two lines will not raise any errors in VBA:

answer = "hello, world!"
Set answer = CreateObject("Wscript.Shell")

If you must use On Error Resume Next make sure you also do On Error Goto 0 at the earliest possible line to resume normal error-handling, but it is generally preferable to trap and program around anticipated/known error conditions.

I declare answer as Variant (not as String) in order to use the Application.Vlookup function which differs slightly from the WorksheetFunction.Vlookup. The latter will raise error a 1004 error if the value is not found, whereas Application.Vlookup will return an error-type value, which can be more aptly tested for than using the clunky On Error Resume Next.

Dim answer as Variant
Dim aValue as String
Dim myRange as Range

aValue = "ABC Company"
Set myRange = Worksheets("Vendor Lookup").Range("A1:D99")
answer = Application.Vlookup(aValue, myRange, 2, False)
If Not IsError(answer) Then MsgBox answer

VBA list reference to

2 Answers2