VBA lookup into large database

[erikhs]

Hello everyone,

My problem is this:

I have a large database 10 columns * 40,000 Rows.
One of the titles is a code for identifying the other entry's.
I often have a different spreadsheet, where i draw information out of
the database with the vlookup function. These sheets can consist of up
to 10,000 of the above mentioned codes.
My problem is that when i write this lookup(vlookup) in VBA code for
automation purposes, excel freezes after a minute or so.
I have turned screenupdate and calculation off, although upon exit i
turn the latter on.
Is there anyway around this, like arrays, or is excel simply to slow to
perform these operations?

Thank you in advance

 

[idyllicabyss]

Erik,
A sample of the code you use for the vlookup could help.

Otherwise, have you tried putting the answer to the lookup into the
spreadsheet rather than a formula?
eg
For each cell in range("B:B")
if cells(cell.row,1).value <> "" then ' Checks row is used by
looking in Col A
cell.value =
worksheetfunction.vlookup(cells(cell.row,1).value,sheets("Sheet2").range("A1:H999"),2,0)
endif
next cell

I haven't checked but I think my sytax is correct

 

[Charles Williams]

sort your database by its identifying code and use approximate match vlookup
(True as the last argument).

Even though you have turned off automatic calculation, Excel still
calculates formulae when they are entered.

If you need to do exact match but can sort the data see
http://www.decisionmodels.com/optspeede.htm#Speed_Lookups


Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

 

[Guest]

Sounds like your requirement is static, so Data=>filter=>Advanced Filter
would be a good way to move a subset of your data to another sheet.

 

[erikhs]

Thank you very much for the quick reply.

I need to make an excact match as the codes can be very similar, and it
is important to get the correct information.
The database is frequently updated by others than mysekf, so it is not
possible to keep it in the same worksheet.
I will perhaps based on you reply, try to import one of the columns
most used into a template of a sort and try to sort, and use avlookup
perhaps. Are you aware of any array functions that might do the trick.
I must add that the lookup is not generally a problem if performed
manually, but in VBA it consumes all available memory.

 

[MH]

It sounds like you should be using a database for this, set-processing is
what they are designed for.

MH

 

[Charles Williams]

Could you post the chunk of code that does the VLookup?
(I dont see why it should be consuming all available memory, perhaps I have
misunderstood what you are doing).

Did you look at the method on
http://www.decisionmodels.com/optspeede.htm#Speed_Lookups
for doing efficient exact match on sorted data?

Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

 

[gimme_this_gimme_that]

I'd recommend filtering the data with where clauses in your SQL
statement.

Don't even use vlookup. Get only the data you need from the database.

 

[erikhs]

Sorry that i have not replied to your suggestions before now.

I will be more specific and post chunck of code.
I receive from the sender, in this case a financial institution, a list
of securities each identified by a code, that can either be a known
standard or an inhouse code. For this list of securities i compare the
codes to the codes i have in the database that are standardized, with
the lookup function. The code could be: USD100100200. Sorting the
datbase would be unpractical as i need to be able to determine the
excactly which securities have been entered when.

Here is the extract:

...Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
....
....
Range("m2", Range("m2").End(xlDown)).Name = "MyRange1"
Range("MyRange1") =
"=VLOOKUP(RC[-1],'[-------database.xls]---BONDS'!C2:C5,3,FALSE)"
....
....
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Thank you again for your time

 

[Charles Williams]

Looks like the database file is not open:
If the database.xls file is not open you will run out of memory because you
will have too many external references (and excel will also be extremely
slow because it will have to repeatedly read the database file): so make
sure you have opened both the xls doing the lookup and the xls (database)
that is being looked up.

When both files are open the time taken to do an exact match lookup is
proportional to the number of rows being scanned before a match is found,
which in your case is large. Approximate match uses binary search which
would be over 1000 times faster on average for 40000 rows.

You dont need to sort the securities list, just the database list. Anyway if
the securities list or the database list is not already time-stamped then
you could add a sequence number before you sort it, if you need it.

Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

 

[Bill_excelforum]

You should be able to query the database to match a group of in-hous
codes to equivalent standard codes. (I'm assuming you need to do tha
as a first step because the rest of the data is organized according t
the standard codes.) Running a database query from Excel is relativel
easy. You can work with the results directly in a recordset or dum
onto a sheet.

Bil