VBA Performance in Access vs Excel

  • Thread starter Thread starter Ray C
  • Start date Start date
R

Ray C

I have a client that wants me to automate some manual Excel file
manipulation. The solution is to use an Access database to completely
automate the opening, editing and saving of an Excel worksheet. I placed all
the necessary data and parameters into Access tables (normalized).

Some of the VBA code used for the "editing" of data was copied and pasted
from Excel to an Access module. After all, both products use VBA. I looked at
some of his code and it is as efficient as it can get...

However, I noticed that when the same function runs within Access, the code
takes a lot longer to execute. But when the same code runs inside Excel, it's
a lot faster. The function simply iterates all the lines in the worksheet and
deletes the lines that have a certain value.

When I manipulate the Excel worksheet from within Access (using the same
code to iterate the lines) it takes a lot longer. Here are the results:

Iterating 30,000 lines:
Within Excel , less than a minute.
Within Access, 17 minutes!

Am I to conclude that iterating lines is done faster in Excel? I would
assume this to be true since it's the worksheet being edited, but to see a
difference of this magnitude? it's shocking.

Ray
 
Ray C said:
I have a client that wants me to automate some manual Excel file
manipulation. The solution is to use an Access database to completely
automate the opening, editing and saving of an Excel worksheet. I placed all
the necessary data and parameters into Access tables (normalized).

Some of the VBA code used for the "editing" of data was copied and pasted
from Excel to an Access module. After all, both products use VBA. I looked at
some of his code and it is as efficient as it can get...

However, I noticed that when the same function runs within Access, the code
takes a lot longer to execute. But when the same code runs inside Excel, it's
a lot faster. The function simply iterates all the lines in the worksheet and
deletes the lines that have a certain value.

When I manipulate the Excel worksheet from within Access (using the same
code to iterate the lines) it takes a lot longer. Here are the results:

Iterating 30,000 lines:
Within Excel , less than a minute.
Within Access, 17 minutes!

Am I to conclude that iterating lines is done faster in Excel? I would
assume this to be true since it's the worksheet being edited, but to see a
difference of this magnitude? it's shocking.

Ray
Click to expand...
 
There should not be that much difference in the execution of the code. It is
more likely how the data are structured and/or how you are manipulating the
data.

It would be helpful if you would post your Access version of the code and we
can look for something that may be causing the problems.
 
There are many many things that could cause this. Access tables and code can
be optimized (or screwed up) in many ways.
Something is definitely wrong with your code. I routinely process hundreds
of thousands of records in Acccess in a few seconds.

-Dorian
 
VBA runs the same in Access as in Excel.

But Excel runs slower in Access than in Excel,
And Access runs slower in Excel than in Access,
and Word runs slower in Excel than in Word,
etc.

Show us your code.

(david)
 
Back
Top