Slow EXCEL VBA Macros

[deluxes]

I have written a large amount of VBA code to do calculations for the
spreadsheet. These were written on a Windows machine, but now I am
trying to run them on a Mac using EXCEL X and OS 10.3.7. However,
things that took only 30 seconds on the windows machine is still
running after 8 minutes. I have looked at a number of suggestions at
decisionmodels.com, but none seem to really fit. Is EXCEL X really
THAT slow compared to the windows versions??? Should give up on trying
to get this to run on the Macs that I love???

 

[JE McGimpsey]

I have written a large amount of VBA code to do calculations for the
spreadsheet. These were written on a Windows machine, but now I am
trying to run them on a Mac using EXCEL X and OS 10.3.7. However,
things that took only 30 seconds on the windows machine is still
running after 8 minutes. I have looked at a number of suggestions at
decisionmodels.com, but none seem to really fit. Is EXCEL X really
THAT slow compared to the windows versions??? Should give up on trying
to get this to run on the Macs that I love???

Hyperbole aside, it's impossible to know without giving examples of what
you're code is doing. While I do see slower performance on Macs than
comparable PCs with my cross-platform code, it's nowhere near a 16:1
ratio.

But then, I rarely, if ever, have VBA routines that take 30 seconds on
either machine.

 

[J Laroche]

JE McGimpsey wrote on 2005/02/02 06:44:

Hyperbole aside, it's impossible to know without giving examples of what
you're code is doing. While I do see slower performance on Macs than
comparable PCs with my cross-platform code, it's nowhere near a 16:1
ratio.

But then, I rarely, if ever, have VBA routines that take 30 seconds on
either machine.

If you haven't put a Application.ScreenUpdating = False at the beginning of
the routine it may be an explanation for the lengthy execution (reset to
True at the end). It doesn't explain the difference between the two
platforms but it may be a beginning.

JL
Mac OS X 10.3.7, Office v.X 10.1.6

 

[Fredrik Wahlgren]

I have written a large amount of VBA code to do calculations for the
spreadsheet. These were written on a Windows machine, but now I am
trying to run them on a Mac using EXCEL X and OS 10.3.7. However,
things that took only 30 seconds on the windows machine is still
running after 8 minutes. I have looked at a number of suggestions at
decisionmodels.com, but none seem to really fit. Is EXCEL X really
THAT slow compared to the windows versions??? Should give up on trying
to get this to run on the Macs that I love???

No, I can't imagine Excel for Mac is that much slower. I guess it should be
possible to tweak something to make the Mac version of your VBa code run
about as fast as it does on Windows. It would be nice if you showed some
simple routine so that it can be examined.

I found something that *may* be useful. I don't know if this product runs on
the Mac:
http://www.decisionmodels.com/fastexcel.htm

/Fredrik

 

[deluxes]

Here is a sample that takes 3 seconds on an Windows XP with EXCEL 2003
and 45 seconds with EXCEL V on a Titanium 550 OS 10.3.6

Sub Testput()
'
Dim TLVCol As Variant
Dim TCol As Variant, MWCol As Variant
Dim Count As Integer, Time1 As Variant, Time2 As Variant
Dim FactCol As Variant, Result As Variant
'
' Turn Off Screen Updates and get start time
'
Time1 = Timer
Application.ScreenUpdating = False

TCol = "'Input'!E4:E4"
Set MWCol = Range(TCol)

TCol = "'Input'!BP4:BP4"
Set FactCol = Range(TCol)

TCol = "'Input'!M4:M2541"
Set TLVCol = Range(TCol)
'
Count = 0
For Each Cell In TLVCol
If (MWCol.Offset(Count, 0).Value = 0) Then
FactCol.Offset(Count, 0).Value = ""
Else
FactCol.Offset(Count, 0).Value = MWCol.Offset(Count, 0).Value
/ 24.467
End If
Count = Count + 1
Next Cell
Time2 = Timer
Application.ScreenUpdating = True
MsgBox "Elapsed Time " & Format((Time2 - Time1), "0.00") & _
" Seconds " _
, , "Input Calculations Complete"
'
End Sub

 

[J Laroche]

Hi deluxes

Make this one single change and see how fast it goes. My execution time was
divided by 10.
Instead of
FactCol.Offset(Count, 0).Value = ""
use
FactCol.Offset(Count, 0).ClearContents

JL
Mac OS X 10.3.7, Office v.X 10.1.6



deluxes wrote on 2005/02/06 00:46:

 

[deluxes]

Hi J Laroche,

I tried the clear contents and it dropped from 45 seconds to 43
seconds. The small change is more than I expected since almost all of
the rows have data in them and this statement is seldom executed. Any
other ideas?

 

[J Laroche]

Hi deluxes,

When cells E4 to E2541 (since the test is executed on column MWCol) are
empty or filled with zeroes, the execution on my G4 Quicksilver 733 takes 39
seconds with value="" and 1 second with ClearContents. When all those same
cells are filled with values other than zero, the times are respectively 3
and 1 second.

So what's the content of your E4:E2541 ?

JL
Mac OS X 10.3.7, Office v.X 10.1.6


deluxes wrote on 2005/02/06 11:36:

 

[J Laroche]

Another thought:
Do you have pivot tables in that workbook? These are usually very slow. If
so, add
Application.Calculation = xlManual
at the beginning of the code, and
Application.Calculation = xlAutomatic
at the end to see if it makes a difference.

In the previous post I should have said: "the times are respectively 1 and 1
second." I still had some empty cells left from a previous test.

J Laroche wrote on 2005/02/06 11:56:

 

[JE McGimpsey]

Here is a sample that takes 3 seconds on an Windows XP with EXCEL 2003
and 45 seconds with EXCEL V on a Titanium 550 OS 10.3.6

The problem appears to be, at least to a large extent, due to taking
much longer to resolve range references. This takes about one second on
my machine (PB 1.33GHz, XL04), with random values in E4:E2541:

Public Sub Testput()
Dim TLVCol As Range
Dim TCol As String, MWCol As Range
Dim Count As Integer, Time1 As Variant, Time2 As Variant
Dim FactCol As Range, Result As Variant
Dim cell As Range

Time1 = Timer
Application.ScreenUpdating = False
TCol = "'Input'!M4:M2541"
Set TLVCol = Range(TCol)
With TLVCol.Offset(0, -8)
.Offset(0, 63).ClearContents
For Each cell In .Cells
With cell
If .Value <> 0 Then _
.Offset(0, 63).Value = .Value / 24.467
End With
Next cell
End With
Time2 = Timer
Application.ScreenUpdating = True
MsgBox "Elapsed Time " & Format((Time2 - Time1), "0.00") & _
" Seconds ", , "Input Calculations Complete"
End Sub

 

[deluxes]

Public Sub Testput()
Dim TLVCol As Range
Dim TCol As String, MWCol As Range
Dim Count As Integer, Time1 As Variant, Time2 As Variant
Dim FactCol As Range, Result As Variant
Dim cell As Range

Time1 = Timer
Application.ScreenUpdating = False
TCol = "'Input'!M4:M2541"
Set TLVCol = Range(TCol)
With TLVCol.Offset(0, -8)
.Offset(0, 63).ClearContents
For Each cell In .Cells
With cell
If .Value <> 0 Then _
.Offset(0, 63).Value = .Value / 24.467
End With
Next cell
End With
Time2 = Timer
Application.ScreenUpdating = True
MsgBox "Elapsed Time " & Format((Time2 - Time1), "0.00") & _
" Seconds ", , "Input Calculations Complete"
End Sub

I first tried shuting off calculations, but this did not help. Then I
changed it to match the above and the time went up to 83 seconds. Why
does my system work differently? I am using XL V instead of XL04, but
these should be nearly the same.

 

[JE McGimpsey]

I first tried shuting off calculations, but this did not help. Then I
changed it to match the above and the time went up to 83 seconds. Why
does my system work differently? I am using XL V instead of XL04, but
these should be nearly the same.

Hard to tell. It's not the code you posted initially, nor what I posted,
since there's no way that the more efficient code could, by itself,
nearly double the time.

Do you have lots of UDF's or array formulae?

 

[deluxes]

I tried shutting off calculations, but it didn't help. I also tried
the rewrite of the macro, but this caused the time to jump up to 83
seconds!!! I sure don't understand what is going on with XL V???

 

[JE McGimpsey]

I tried shutting off calculations, but it didn't help. I also tried
the rewrite of the macro, but this caused the time to jump up to 83
seconds!!! I sure don't understand what is going on with XL V???

I ran my modification of your code in XL v.X and it took approximately
the same time as XL04 (about 1 second).

So it's not the code or XL v.X.

Rather, it's something else in your workbook. Do you have lots of array
formulas, for instance?

or are you running event macros?

 

[Fredrik Wahlgren]

Hi deluxes,

When cells E4 to E2541 (since the test is executed on column MWCol) are
empty or filled with zeroes, the execution on my G4 Quicksilver 733 takes 39
seconds with value="" and 1 second with ClearContents. When all those same
cells are filled with values other than zero, the times are respectively 3
and 1 second.

So what's the content of your E4:E2541 ?

I think this is an interesting observation. I think deleuxes should try this
and see if the macro runs this fast under these conditions. If they don't,
there's something strange going on in deluxe's computer.

/Fredrik

 

[J Laroche]

deluxes wrote on 2005/02/07 08:18:

I tried shutting off calculations, but it didn't help. I also tried
the rewrite of the macro, but this caused the time to jump up to 83
seconds!!! I sure don't understand what is going on with XL V???

Deluxes, do as everybody here is doing trying to help you, so you can better
identify to source of your problems. In a brand-new workbook only fill cells
E4:E2541 with sequential numbers. Then put in a single module only the code
you posted, and execute it. What result do you get?

If it doesn't improve, disable all Add-ins from the Tools menu (if any is
check-marked), quit Excel and start over with the test.

JL
Mac OS X 10.3.7, Office v.X 10.1.6

 

[deluxes]

I was already working in a workbook that had been reduced to one sheet
and only the test macro. I moved to a new workbook and copied the
macro and put in the needed numbers and the speed was obtained. I went
back to the original sheet and started deleteing things to determine
the cause. It wasn't until I deleted the formatting for the column
that I was writing to that the speed came back. I went back and put
back the formatting and the speed was still at 1 second. How can this
be?

 

[Fredrik Wahlgren]

I was already working in a workbook that had been reduced to one sheet
and only the test macro. I moved to a new workbook and copied the
macro and put in the needed numbers and the speed was obtained. I went
back to the original sheet and started deleteing things to determine
the cause. It wasn't until I deleted the formatting for the column
that I was writing to that the speed came back. I went back and put
back the formatting and the speed was still at 1 second. How can this
be?

I think the sheet may have been corrupt. This happens all the time and nice
formatting seems to cause this quite often. The way I have resolved this in
the past is to open a new document and then do copy&paste from the old file
to the new document.

/Fredrik