It works pretty well but it's slow. I have a feeling if the information was already in an array it would run much faster. Apparently I have a mental block against excel arrays because I just can't figure it out.

mstarr wrote:It works pretty well but it's slow. I have a feeling if the information was already in an array it would run much faster. Apparently I have a mental block against excel arrays because I just can't figure it out.

Here is an example of creating a SafeArray from a range and then getting information from the SafeArray.

Yes, the getting all information and putting into an array has the advantage of Excel not having to remain open. It is probably better if you are just getting the data once and then searching for multiple URL from the data.

If that is the case, I would create a lookup array. It would be a single associative array where the "key" is the State|Type and the "value" is the URL.

This will be a Lookup array where the key is the value from column 1 (State) and column 4 (Type) separated by a | and the elements are the value from column 2 (URL). It is important to realize this is the "columns" of your range used, not columns overall in Excel.

Create your SafeArray with the Range starting on your left most column used (State from what I can tell).

This will allow you to get an URL almost instantly once you create the Lookup array.

Great information! Luckily the list is only about 1500 and it already returns within 1 second using your previous method. In a list over 5k I can see how this would be extremely useful. Really glad this is documented here

You don't need to loop through the columns as you are explicitly referencing the columns you want. And now that it is not a loop within a loop, you do not need the Row and Col variables. You can just use A_Index for the Row which you were already doing.

The first time the function is called it gets the SafeArray from the Excel workbook. The first time will take a few seconds as it has to start Excel and open the file (this is mostly hard drive speed). Later calls it will already have the SafeArray and will be much faster.