Performance Comparison - Arrays versus Workbooks - Which is Faster?

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?
 
D

DM Unseen

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
 
J

james.igoe

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.
 
D

DM Unseen

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
 
J

james.igoe

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.
 
J

james.igoe

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.
 
Top