PC to Mac macro speed issues

[robotman]

I'm writing a macro that needs to run on a PC and Mac.

I have the code working on both platforms, but the Mac is incredibly
slower. What takes 20 seconds on a PC takes over 12 minutes on the
Mac! Another thing that takes 1 second on the PC, takes over 2
minutes on the Mac. The Mac is a new MacPro so it's not the processor
speed.

Most of the macro is dealing with transfering information from one
spreadsheet to another and formatting cells.

* Can anyone help me understand the speed difference and if there are
any tricks to speed up the Mac? (any webpages dedicated to this
issue?)

Thanks.

John

 

[JE McGimpsey]

I'm writing a macro that needs to run on a PC and Mac.

I have the code working on both platforms, but the Mac is incredibly
slower. What takes 20 seconds on a PC takes over 12 minutes on the
Mac! Another thing that takes 1 second on the PC, takes over 2
minutes on the Mac. The Mac is a new MacPro so it's not the processor
speed.

Most of the macro is dealing with transfering information from one
spreadsheet to another and formatting cells.

* Can anyone help me understand the speed difference and if there are
any tricks to speed up the Mac? (any webpages dedicated to this
issue?)

First, VBA is version 5 on Macs, so some functions may not have been
updated. Nor, except in a very few areas, does it appear to have been
optimized since Office98.

Second, VBA was a kludge in MacOffice from the beginning - having to
emulate some features that use optimized system routines on the Win side.

That's one reason that VBA will go away in Office2008. The effort
required to update VBA for a universal binary would have been too great.

So many, if not most, VBA routines will take somewhat longer on Macs
than Wintel machines.

That said, the differences you're seeing are *very* much bigger than
what I see in my work, especially for simple transfers of information.

Are you selecting/activating worksheets and cells in your transfer and
formatting, rather than using range objects directly? That would easily
explain *some* of the difference.

Can't really hazard a guess unless you describe/post your code.

 

[robotman]

Thanks for your reply.

I think most of the timing difference is stemming from a custom sub I
wrote to enter text with specified formatting into the cells. It
seems like the Mac is MUCH slower when it continually calls this
function (see below). Even though I have the For/Next set up for a
range of cells, usually I just use the function on one cell at a time.

I've heard a little bit about VBA going away in Mac Excel 2008. What
is the proposed alternative? Applescript? Is there any talk of a VBA
-> AppleScript convertor? (someone could make big $$$ on that!).

How are people dealing with workbooks that need to be cross-platform?

Is there a link someone can point me to discussing what people are
planning to do when VBA goes away? Macs and cross-platform VBA macros
are used heavily in my company!

Thanks.

John

___ ENTERTEXT function ____

Public Function EnterText(CellAddress As Range, Optional TextString As
String, Optional TextSize As Integer, Optional TextColor As String,
Optional TextBold As Boolean, Optional TextItalic As Boolean, Optional
TextAlignment As Integer, Optional TextFormat As String)
' Quick Public Function to enter text with formatting

Dim TextCell As Range
' With Range(TextAddress)

For Each TextCell In CellAddress

If TextString <> "" Then TextCell.Value = TextString
' leave existing text blank if just formatting existing text

If Val(TextSize) <> 0 Then TextCell.Font.Size = TextSize
If TextColor <> "" Then TextCell.Font.ColorIndex =
GetColorIndex(TextColor)
If Val(TextAlignment) <> 0 Then TextCell.HorizontalAlignment =
TextAlignment
If TextFormat <> "" Then TextCell.NumberFormat = TextFormat
TextCell.Font.Bold = TextBold
TextCell.Font.Italic = TextItalic

Next

End Function