Performance Comparison - Arrays versus Workbooks - Which is Faster?

  • Thread starter Thread starter james.igoe
  • Start date Start date
J

james.igoe

I am working on Excel VBA code that parses a CSV into tab1, then reads
through the lines to compare each line against a list of terms, from
which the code builds a reduced list of records showing only those with
the search terms, into tab2. It then creates a pivot table on tab3,
after which it creates another tab with reordered columns. The first
tab is created directly, while tabs 2, 3, and 4 are done in arrays.

I was under the assumption that doing this in arrays would be faster
than manipulating worksheets, but the process seems to be taking
inordinately long. Would performance be better using worksheets?
 
James,

while VBA isn't slow, compared to .e.g excel lookup formulas VBA
looping isn't very speedy either. BTW what you are doing is a "database
join", so a good database would beat 'em all.

DM Unseen
 
I also code Access databases, but the choice was limited by the
business, although your BTW is a great idea. I can import the new CSV,
run it against existing tables, then export imported table to tab1,
query result to tab2, crosstab query to tab3 and reordered query to
tab4.
 
PS,

when going from Access back to excel you can alwasy choose in using a
"push" model withe.e.g Access.transferspreadsheet out, or use a "pull"
model using Excel->get External Data. You can maybe even skip all
tables except the Excel pivottable, which you can fill directly from
Access.

DM Unseen
 
I understand the transferspreadsheet method, but it isn't sufficient
for this exercise, since I would create multi-tabbed workshhets, which
I currently do through createcobject, and adding worksheets.
 
When I redid the spreadsheet, using a larger array to hold the data,
row and column as opposed to a single column, the routing ran much
faster. Very spedy. The speed issue was caused by something else,
e.g., using TextToColumns, or searching a log strings of text as
oppposed to cells. Not usre.
 
Back
Top