Here is my scenario - I write applications that connect to backoffice
solutions, not necessarily in real time. I want to look up the descriptions
for a set of key values as follows.

Column A might have the key values, and column B would have my function called
mylookup(keyvalue). So cell A1 would have 1265 in it and cell B1 would have
=mylookup(A1). This would be repeated for, let's say, 10,000 lines.

Rather than doing 10,000 calls to the backend system, I would rather do
1 call that passes 10,000 lines of data and retrieves 10,000 descriptions.

So I would like the code for mylookup to somehow cache up each keyvalue in
a list or array, and then magically, after it has ALL of them, call the
backend system, get the list of answers, and then put them in the correct
cells.

Is this impossible ? I have a couple of ideas, but I am not very happy
with any of them :)

Thanks,

James Shoffit
(replace with my first name and last name)

James:

We'd need to know if the backend system is capable of receiving,
processing, and returning your descriptions in batch mode. If that software
package cannot collect your list-of-10000 for a bulk lookup, then there isn't
much of an option remaining. If the backend can take a batch request, you'll
need to arrange your myLookUp collective-response to match what the backend
needs. With that, I'd remove the =myLookUp calls in column B, wait until all
of column A is ready, then run a VBA macro to process your requests to the
"backend format." When the backend responds, unload what it sent back to
column B, using column A to make sure that the returning description gets to
its respective source value.

Steve in Ohio

"James Shoffit" wrote:

Here is my scenario - I write applications that connect to backoffice
solutions, not necessarily in real time. I want to look up the descriptions
for a set of key values as follows.

Column A might have the key values, and column B would have my function called
mylookup(keyvalue). So cell A1 would have 1265 in it and cell B1 would have
=mylookup(A1). This would be repeated for, let's say, 10,000 lines.

Rather than doing 10,000 calls to the backend system, I would rather do
1 call that passes 10,000 lines of data and retrieves 10,000 descriptions.

So I would like the code for mylookup to somehow cache up each keyvalue in
a list or array, and then magically, after it has ALL of them, call the
backend system, get the list of answers, and then put them in the correct
cells.

Is this impossible ? I have a couple of ideas, but I am not very happy
with any of them :)

Thanks,

James Shoffit
(replace with my first name and last name)