Why Does VB6 execute Excel Worksheet so Slowly?

  • Thread starter Thread starter Dennis
  • Start date Start date
D

Dennis

Below is a snippet of an Excel VBA Macro that I converted to VB6. The code
executes almost instantly in Excel while it takes VB6 36seconds. I've excluded
all the Dim statements for brevity. There are no variant variables. All the
Cell indices are defined in the sub ColumnNumber. I've added the "Microsoft
Excel 9.0 Object Library" to the project References.

Any Ideas?

Thanks.

Dennis

==========VB6 Code ========================================
Option Explicit
Public XL As Excel.Application 'Make Global for other subroutines to use
Sub main()

Set XL = New Excel.Application
XL.Workbooks.Open Filename:="c:\myExcelFile.csv"
With XL.ActiveWorkbook
With .Sheets(1)
'Find Column numbers for given column labels
ColumnNumber
If (iont = 0 Or iosnp = 0) Then
MsgBox (" iont=" & iont & " iosnp=" & iosnp )
Exit Sub
End If
LastRow = .Cells(Rows.Count, "a").End(xlUp).Row
LastCol = .Range("A1").End(xlToRight).Column
For irow = 2 To LastRow 'first row is labels
avOSNP = 0
osnp = .Cells(irow, iosnp).Value
ont = .Cells(irow, iont).Value
If ont = 0 Then ont = 1
If ont = -1 Then ont = 1
If ont > 0 Then avOSNP = osnp / ont
'replace osnp with avosnp
.Cells(irow, iosnp).Value = avOSNP
'Adjust r22 and r2 for negative slopes
slope = .Cells(irow, ib0).Value
r2 = .Cells(irow, ir2).Value
r22 = .Cells(irow, ir22).Value
If slope < 0 Then
If r2 > 0 Then .Cells(irow, ir2).Value = -r2
If r22 > 0 Then .Cells(irow, ir22).Value = -r22
End If
Next irow
End With '.Sheets(1)
End With 'XL.ActiveWorkbook

End Sub
 
The most likely cause is that the code results in multiple
instantiations of XL. All that is needed is an unqualified reference
such as Rows.Count as in LastRow = .Cells(Rows.Count, "a").End(xlUp).Row


--
Regards,

Tushar Mehta
MS MVP Excel 2000-2004
www.tushar-mehta.com
Excel, PowerPoint, and VBA tutorials and add-ins
Custom Productivity Solutions leveraging MS Office
 
Tushar,

Thanks for the response.

Are you saying I don't need the "With" statements?

Does VB6 create a new object for the ".Cells" request when I loop within the
"With" statements? LastRow is 3000. Does VB6 release object memory with the
"End With" statements? If it has to create a new ".Cells" object within the
"With" statements on every loop this could cause the significant slowdown.

With XL.ActiveWorkbook
With .Sheets(1)
For irow = 2 To LastRow 'first row is labels
osnp = .Cells(irow, iosnp).Value
ont = .Cells(irow, iont).Value
 
No, I did not mean you don't need to quality the Cells reference.
Rather that the problem is caused by the *unqualified* use of the
Rows.Count part.

I don't know how to explain this since I don't know the technical
details of how ActiveX objects are implemented. However, the code below
documents the problem -- and, I don't know if MS considers it a bug or a
feature.

Keeping this in VBA, I put the code below in a PowerPoint code module.

Now, ensure that no copy of Excel is in memory. Do so by getting to the
Task Manager (CTRL+ALT+DEL), and checking that it doesn't appear in the
Application or the Process list.

Step through the code below. It will instantiate a new copy of Excel
and you will get the message $A$1.

However, even after the code finishes execution, there will be a copy of
Excel in the Process list of the Task Manager!

It will go away when PowerPoint is quit.

Apparently, the unqualified use of Rows.Count caused VB/OS/whatever to
link PP to XL in a 'behind the scenes' connection.

No such problem will occur if you use XL.Rows.Count or if you use late
binding because Rows.Count will result in a 'variable not defined'
compile error.

Option Explicit

Sub testIt()
Dim XL As Excel.Application, InitiatedXL As Boolean
On Error Resume Next
Set XL = GetObject(, "excel.application")
On Error GoTo ErrHandler
If XL Is Nothing Then
Set XL = CreateObject("excel.application")
XL.Workbooks.Add
InitiatedXL = True
End If
MsgBox XL.Cells(Rows.Count, "a").End(xlUp).Address
If InitiatedXL Then XL.Quit
Set XL = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description & " (" & Err.Number & ")"
If InitiatedXL Then XL.Quit
Set XL = Nothing
End Sub


--
Regards,

Tushar Mehta
MS MVP Excel 2000-2004
www.tushar-mehta.com
Excel, PowerPoint, and VBA tutorials and add-ins
Custom Productivity Solutions leveraging MS Office
 
Tushar,

I did the following "Sets"

Set XL = CreateObject("Excel.Application")
Set XLWB = XL.Workbooks.Open(FileName:=dir & Fil1)

XLWB.Activate 'I know this is unnecessary because the "Set XLWB" activates the
Workbook but I did it anyway.
..
..
LastRow = Sheets(1).Cells(Rows.Count, "a").End(xlUp).Row
LastCol = Sheets(1).Range("A1").End(xlToRight).Column
MsgBox ("LastRow=" & LastRow & " LastCol=" & LastCol & _
Sheets(1).Cells(LastRow, itnp).Value)
......
At the message box prompt I checked the Task Manager and there was only one
Excel Object in memory. However the code still ran in 36sec vs <1sec when run
as a macro in Excel.

Enclosed below is an explanation about the slow execution from Norman Yuan in
the ng comp.lang.basic.misc.
==================From Norman Yuan in the ng comp.lang.basic.misc.=============
When using a VB app to automate Excel, Excel is an out-process server
(ActiveX exe, as opposed to ActiveX dll, in-process server). That means,
communication between the two apps must cross process boundary; while
in-process server (DLL) runs in the same process scope as the app, thus,
faster. It is imagineable that if you process spreadsheet cell by cell in
your VB app through ActiveX exe automation, it will be a slow process, that
is of the opposite what you original point. If you want speed, VBA in Excel
is arguably fastest. If you want to hide your code in VBA (VBA's password
protection is not serious option here) and also want to do your job through
your VB app, you can put your most process logic in ActiveX dll project and
write a fairly simple macro in VBA to call your process logic in dll. Then,
as Jo's suggestion, you can still start Excel from your VB app, open
Workbook/Worksheet, and use Excel.Application.Run() method to run the
macro(s). This, your VB app only launch Excel through Active Exe process,
while data process is done in Excel's own process, the same as you run VBA
code in Excel.
=======================End ===================================================
 
For some reason that I am sure makes sense to you, you refuse to remove
the unqualified reference to Rows.Count. Given that, I don't know what
more I can contribute to this discussion.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Tushar,

I referenced the Rows.Count as you asked in:

LastRow = Sheets(1).Cells(XL.Rows.Count, "a").End(xlUp).Row

However, this change made no difference in the execution speed..it still took
36sec vs an execution speed of <1sec as a VBA macro executed in Excel.

Thus other than Norman's method to create an ActiveX DLL as mentioned in my last
post, when Excel is an object in VB6 the execution speed of Excel spreadsheet
functions is very bad.

Is this your experience?

Thanks for your help.

Dennis
 
Dennis,
If you use global Excel objects in your code, VB has to search through the
com interfaces until it finds the global object or fails.
That is why it's running slower.

cells is an Excel global object.
As is sheets, rows, range,.....

xl.cells is not. Nor is xl.range, xl.sheets..............
 
My experience has been automating Office applications from other Office
applications and the performance is fast. The code below executes in
0.0 seconds. Maybe VB6 is slow at automating Office applications,
though I find that hard to believe.

It is possible there is something in the XL workbook that is causing
the delay: intensive recalculation or an automatic refresh of a table
from an external data source, or something else.

If you want to ensure that all the XL references go through the object
you created, a good test to check for unqualified objects (such as
Sheets(1) in your last example) is to remove the reference to the XL
object library and test the code. Of course, you will have to replace
the mnemonics (such as xlUp) with their respective numeric values.

Option Explicit
Sub testXL()
Dim XL As Excel.Application, XLWB As Excel.Workbook, _
InitializedXL As Boolean, _
LastRow As Long, LastCol As Long, AccessTime As Date
On Error Resume Next
AccessTime = Now()
Set XL = GetObject(, "Excel.Application")
On Error GoTo 0
If XL Is Nothing Then
Set XL = CreateObject("excel.application")
InitializedXL = True
End If
Set XLWB = XL.Workbooks.Add
With XLWB
LastRow = .Worksheets(1).Cells(XL.Rows.Count, "a").End(xlUp).Row
LastCol = .Worksheets(1).Range("A1").End(xlToRight).Column
Debug.Print ("LastRow=" & LastRow & " LastCol=" & LastCol & _
.Worksheets(1).Cells(LastRow, LastCol).Value)
End With
XLWB.Close False
If InitializedXL Then XL.Quit
Set XLWB = Nothing
Set XL = Nothing
MsgBox Format(Now() - AccessTime, "hh:mm:ss.s")
End Sub

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Tushar,

Thanks for the neat code! I ran it using VB6 and added the reference "Microsoft
Excel 9.0 Reference Library" to the Project.

When I first ran your code it took 2.2 sec. On the 2nd, 3rd and etc run, your
code took 0.0sec as you observed. I guess the first run must have been the VB6
compile time?

Next as shown below I populated the speadsheet with numbers in rows 1 to 3000
and columns 1 to 3. This run took 50.50sec! This is the slow exe problem I've
been talking about. When you attempt to do something with the cells, BAM, VB6
slows way down. Does this happen with your app?

Option Explicit
Sub Main()
Dim XL As Excel.Application, XLWB As Excel.Workbook, _
InitializedXL As Boolean, _
LastRow As Long, LastCol As Long, AccessTime As Date
On Error Resume Next
AccessTime = Now()
Set XL = GetObject(, "Excel.Application")
On Error GoTo 0
If XL Is Nothing Then
Set XL = CreateObject("excel.application")
InitializedXL = True
End If
Set XLWB = XL.Workbooks.Add
With XLWB
'Add Data to the work book
Dim icol As Long, irow As Long
For icol = 1 To 3
For irow = 1 To 3000
XL.Sheets(1).Cells(irow, icol).Value = icol
Next irow
Next icol
LastRow = .Worksheets(1).Cells(XL.Rows.Count, "a").End(xlUp).Row
LastCol = .Worksheets(1).Range("A1").End(xlToRight).Column
Debug.Print ("LastRow=" & LastRow & " LastCol=" & LastCol & _
" LastValue" & .Worksheets(1).Cells(LastRow, LastCol).Value)
End With
XLWB.Close False
If InitializedXL Then XL.Quit
Set XLWB = Nothing
Set XL = Nothing
MsgBox ("LastCol=" & LastCol & " LastRow=" & LastRow & _
" XTime=" & Format(Now() - AccessTime, "hh:mm:ss.s"))
End Sub
 
Accessing XL cells is a time consuming process even when the VBA is
within XL itself. Your code ran in about 3 seconds from within XL
itself.

Apparently, the time penalty is worse when automating from another
program. From PowerPoint, it took about 36 seconds.

However, you can transfer the data in one chunk by using an array as in
the code below. It ran from PP in < 1 second. The key is to populate
a VB array, DataArr, and then transfer the contents of the array to the
XL range in one step. There used to be a limit to the size the array
could be (5461 rows if I remember correctly) but with XL2002 (or was it
2000?) MS eliminated the limit.

Sub Main()
Dim XL As Excel.Application, XLWB As Excel.Workbook, _
InitializedXL As Boolean, _
LastRow As Long, LastCol As Long, AccessTime As Date
On Error Resume Next
AccessTime = Now()
Set XL = GetObject(, "Excel.Application")
On Error GoTo 0
If XL Is Nothing Then
Set XL = CreateObject("excel.application")
InitializedXL = True
End If
Set XLWB = XL.Workbooks.Add
With XLWB
'Add Data to the work book
Dim icol As Long, irow As Long, DataArr(1 To 3000, 1 To 3)
For irow = 1 To 3000
For icol = 1 To 3
DataArr(irow, icol) = irow / icol
Next icol
Next irow
.Sheets(1).Cells(1, 1).Resize(3000, 3).Value = DataArr
LastRow = .Worksheets(1).Cells(XL.Rows.Count, "a").End(xlUp).Row
LastCol = .Worksheets(1).Range("A1").End(xlToRight).Column
Debug.Print ("LastRow=" & LastRow & " LastCol=" & LastCol & _
" LastValue=" & .Worksheets(1).Cells(LastRow, LastCol).Value)
End With
XLWB.Close False
If InitializedXL Then XL.Quit
Set XLWB = Nothing
Set XL = Nothing
MsgBox ("LastCol=" & LastCol & " LastRow=" & LastRow & _
" XTime=" & Format(Now() - AccessTime, "hh:mm:ss.s"))
End Sub


--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Thanks Tushar.

I ran your code and it ran in 1sec.

I guess anytime you have to do something outside of XL at the Cell level than
you should export the sheet to an array, do you stuff on the array then copy the
array back to the spreadsheet.

I guess that VB has to construct the XL.cell object on each call from the for
loop and this is what causes the hugh processing time.

Thanks again for your help, I really appreciate the time and work that you put
in to helping me understand this problem. Plus I learned a few things from your
neat code!

Regards,

Dennis
 
You are welcome. Glad to be of help.

Yeah, once one masters a few fundamental 'safety' principles about
automating one app from another, it is not all that difficult to build
more solutions on that basic template.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
After weeks of work on the Excel object called from VB6, I've concluded that if
you want speed then move the spreadsheet into an Array perform *all*
calculations, sorts, filters(the filters can be done quite easily by IF
statements), etc and when done move the Array back into a Worksheet. Any call
to the Excel object from VB6 , especially if these calls are in a loop, will
really slow things down by a factor of 10.

As an example I had a routine that filtered a sheet, copied the filtered sheet
to a new worksheet, transferred the new worksheet to an Array and performed some
calcs on the array. This routine was executed 100 times within a loop and took
50 seconds to complete. Next before I calculated anything, I moved the
spreadsheet into an array. I then did my filtering and calculations on the
filtered Array. This time the routine with loop took 4 seconds to complete!

Someone suggested making the VB6 routine an ActiveX DLL, calling the DLL inside
of Excel with making a new VBA sub and then calling the Excel object new VBA sub
from VB6 and the DLL would execute within the Excel object avoiding all the
Excel object calls.

However when I tried this, I could not get Excel 2000 to recognize the ActiveX
DLL as in ADDIN, I tried Regsrv32, it ran but did not register the ActiveX DLL
so that Excel could see it.

Any suggestions or references I could read to find out what I'm doing wrong and
how to do this?

Thanks

Dennis
 
Back
Top