I agree with cht13er in his comment below about how fast the array populate
should run. I would recommend the following

1. Consider using a multiple dimensional array instead of two separate
arrays; aLookup(1, <totalCount) and use Redim if the data count is not
static.
aLookup(0, #) - Id
aLookup(1, #) - Name

2. Use indirect cell referencing to avoid having to activate worksheets as
you read them.

3. Although you can probably use Integer variable type for your counter, I
use Double instead of Integer when working with numbers over 32K.

I got curious about this so I wrote a test subroutine to populate an array
from a worksheet containing 40k rows with 2 columns of data. I actually
create a 3rd string combining column 1 and column 2 to make it a bit more
complex:

CodeBlock:

Sub testarray()

Dim aTempArr()
Dim iArrCtr, iRowCtr As Double
iArrCtr = -1
iRowCtr = 1

Do While ActiveSheet.Cells(iRowCtr, 1) < ""
iArrCtr = iArrCtr + 1
ReDim Preserve aTempArr(2, iArrCtr)
aTempArr(0, iArrCtr) = ActiveSheet.Cells(iRowCtr, 1)
aTempArr(1, iArrCtr) = ActiveSheet.Cells(iRowCtr, 2)
aTempArr(2, iArrCtr) = Trim(Str(ActiveSheet.Cells(iRowCtr, 1))) + "
" + ActiveSheet.Cells(iRowCtr, 2)
iRowCtr = iRowCtr + 1
Loop

MsgBox "done"
End Sub

This took 3-4 seconds to run on my laptop (1.6Ghz processor with 1Gb RAM
running Excel 2002).

I just completed a project where as part of a form activate event I build
several multi-dimension arrays, populate 5 comboboxes in an admin/report
setup form, after reading a workbook containing 25 worksheets of payroll
data. By using indirect cell referencing and multi-dimension arrays, the
entire form activate event takes 1-2 seconds to complete.

Please feel free to contact me directly.

Ken


" wrote:

Lets say I have a workbook with ID numbers in it. I have a macro
doing a lot of work on this workbook and one of the things I want it
to do is to convert the ID numbers into names. I haven't done this
with VB before and was wondering what the best/easiest way to do this
would be? If I wasn't doing it with a macro, I would just have a
vlookup, but this isn't an option for my case. I have looked into
arrays, but this seems like it would be hard to populate and search
thru. My idea was to create two arrays:

IDs = Array (1111,2222,3333,4444)
Names = Array("Bob","John","Henry","Sue")

The real data would contain about 20 elements. I would rarely if ever
need to add or change IDs/Names. Then as I find the ID value in the
cell, it would look thru the ID array, find the index of the value,
the pull up the corresponding index in the names array. This seems
cumbersome and I am sure there has to be a better way to do this.

Thanks,
Andrew V. Romero