VBA Performance in Access vs Excel

[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

 

[mscertified]

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

 

[Klatuu]

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.

 

[mscertified]

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

 

[david]

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)

 

[Michel Walsh]

Ideally, you won't loop on a recordset to delete records, but a single
query.


To say that some code runs slower of faster in Access or in Excel, it has to
be the same code, exactly the same. Clearly, you use DIFFERENT code to do
something SIMILAR, but that does not mean that it was the most EFFICIENT
(neither the EASIER) way to do what you did.


Vanderghast, Access MVP

 

[Ray C]

The goal is to iterate all the lines in an Excel worksheet and delete lines
that have certain projects.

Here is the code that I have in an MS Access function (the projects that
need to be deleted are in a recordset):

strSQL = "SELECT ProjectNumber FROM tblProjects WHERE TypeID=" & mTypeID
Set rs = CurrentDb.OpenRecordset(strSQL)
If Not rs.EOF Then
rs.MoveLast
NbProjets = rs.RecordCount
rs.MoveFirst
mLastRow = objExcel.ActiveSheet.Range("B65536").End(xlUp).Offset(0,
1).Row
mFirstRow = objExcel.Sheets("Rapport").Range("A65536").End(xlUp).Row + 2
For j = mLastRow To 2 Step -1
rs.MoveFirst
Do While Not rs.EOF
If objExcel.ActiveSheet.Range("D" & j) = rs![ProjetNumber]
Then
objExcel.ActiveSheet.Rows(j).Delete
End If
rs.MoveNext
Loop
Next j
End If
rs.Close
Set rs = Nothing

Now here is the code that the client has in Excel (the projects that need to
be deleted are in a listbox):

mLastRow = Range("B65536").End(xlUp).Offset(0, 1).Row
mStartRow = Sheets("Rapport").Range("A65536").End(xlUp).Row + 2

For j = 2 To mLastRow
For i = 0 To MyListBox.ListCount - 1
project = MyListBox.List(i)
If Range("D" & j) = project Then
Rows(j).Delete
j = j - 1
End If
Next i
Next j

The code is slightly different but the idea is the same, yet there is a big
difference in performance.

Thanks in advance.

Ray

 

[Graham Mandeno]

Hi Ray

It often improves performance drastically if you turn off automatic
calculation in the worksheet before performing a large amount of
manipulation by automation.

From memory, the property is EnableCalculation:
objExcel.ActiveSheet.EnableCalculation = False

It will also help to assign the sheet you are working with to a variable of
type Excel.Worksheet:
Set objXlSheet = objExcel.ActiveSheet

At the moment, every time you are referring to anything on
objExcel.ActiveSheet, you need to ask Excel to tell you what its current
active sheet is, and return a reference to it.

Thirdly, instead of looping through your entire recordset to see if any
record matches the value in the current Excel row, why not use FindFirst?

For j = mLastRow To 2 Step -1
rs.FindFirst "[ProjetNumber]=" & objXlSheet.Range("D" & j)
If Not rs.NoMatch Then
objXlSheet.Rows(j).Delete
End If
Next j

(you might need quotes in the FindFirst line if [ProjetNumber] is a text
field)