Enlighten me - Mac vs. PC processing speeds

[c1802362]

Can anyone enlighten me on the fundamental difference why the same VBA
code runs at such different speeds on Macs vs. PCs?

Here's an example. I wrote some code months ago on my PC at work (Dell
1.73GHz/504 mB RAM/Windows XP). The task involved transposing 4000+
data records from a columnar listing to a tabulated format. The source
data was comprised of column A which listed the label name of each
field in the record. Column B carried the corresponding data for each
field. Between each record there was a blank cell, so each record's
field labels were repeated in column A. The only other nuance was that
each record did not carry the same number of fields, so I had to code
the macro to read to the end of each record in column B before copying
and transposing it to the table. I accomplished this by creating a
loop that read the data in each field into an array until it reached a
blank cell, then created another loop to write out the array to the
table. On the PC it took about 2 minutes 30 seconds to complete the
task.

I took the code home to run the same file on my Mac (iMac G5 1.8 GHz/
1GB RAM/OS X 10.5). It took more than 8 hours to run the same code!

A few weeks ago, I recoded the macro to make the task more efficient.
This time around I used 'Range(ActiveCell,
ActiveCell.End(xlDown)).Select' to grab the entire record at once
before transposing it to the table. Now the PC version runs in under 3
seconds. The Mac takes 2 minutes and 20 seconds.

Can someone explain what is inherent in the Mac that doesn't allow it
to process code as fast as the PC?

Art

 

[Jim Gordon MVP]

Can anyone enlighten me on the fundamental difference why the same VBA
code runs at such different speeds on Macs vs. PCs?

Here's an example. I wrote some code months ago on my PC at work (Dell
1.73GHz/504 mB RAM/Windows XP). The task involved transposing 4000+
data records from a columnar listing to a tabulated format. The source
data was comprised of column A which listed the label name of each
field in the record. Column B carried the corresponding data for each
field. Between each record there was a blank cell, so each record's
field labels were repeated in column A. The only other nuance was that
each record did not carry the same number of fields, so I had to code
the macro to read to the end of each record in column B before copying
and transposing it to the table. I accomplished this by creating a
loop that read the data in each field into an array until it reached a
blank cell, then created another loop to write out the array to the
table. On the PC it took about 2 minutes 30 seconds to complete the
task.

I took the code home to run the same file on my Mac (iMac G5 1.8 GHz/
1GB RAM/OS X 10.5). It took more than 8 hours to run the same code!

A few weeks ago, I recoded the macro to make the task more efficient.
This time around I used 'Range(ActiveCell,
ActiveCell.End(xlDown)).Select' to grab the entire record at once
before transposing it to the table. Now the PC version runs in under 3
seconds. The Mac takes 2 minutes and 20 seconds.

Can someone explain what is inherent in the Mac that doesn't allow it
to process code as fast as the PC?

Art

Hi Art,

If you go into the Visual Basic Editor (VBE) and step through the code
by pressing F8, are is there a particular command or operation that
takes a long time to execute?

If so, can you post the offending code snippet here? Sometimes people
know alternative methods that can be faster and they can post their
suggestions in the newsgroup.

-Jim

 

[c1802362]

Hi Art,

If you go into the Visual Basic Editor (VBE) and step through the code
by pressing F8, are is there a particular command or operation that
takes a long time to execute?

If so, can you post the offending code snippet here? Sometimes people
know alternative methods that can be faster and they can post their
suggestions in the newsgroup.

-Jim

--
Jim Gordon
Mac MVP

MVPs are independent experts who are not affiliated with Microsoft.

Jim,

Yes, I've done the step through, including using the watch window, and
putting msgbox pop ups where appropriate (that's generally how I
confirm my code is doing what I want)

there was nothing that was obvious to me. If you want, I'll post the
code tomorrow after I bring it home from work,

Art

 

[Bob Greenblatt]

Without looking at your code, it's pretty hard to tell, but here are a few
things:
- 1. it's just slower on a Mac. (how much slower, depends on what you are
doing.)
- 2. displayupdating=false isn't quite the same thing on the Mac as windows,
the Mac display is still repainted under certain conditions.
- 3. on either platform, NEVER activate, and/or select items, ranges, etc.
It REALLY REALLY slows things down.

 

[c1802362]

Without looking at your code, it's pretty hard to tell, but here are a few
things:
- 1. it's just slower on a Mac. (how much slower, depends on what you are
doing.)
- 2. displayupdating=false isn't quite the same thing on the Mac as windows,
the Mac display is still repainted under certain conditions.
- 3. on either platform, NEVER activate, and/or select items, ranges, etc.
It REALLY REALLY slows things down.

I probably broke the do not select rule, but if you can give me any
guidance it would be greatly appreciated:

first the code
**************************

Sub SelectDown()
Dim count As Integer, pointer As Integer, num As Integer
Dim lastrow As Long

count = 1 'sets the destination row location
pointer = 3 'locates the source data location's start

' find the last row in the file
lastrow = Range("b65536").End(xlUp).Row

Range("A1").Select

Application.ScreenUpdating = False

Do Until pointer > lastrow

' find the start of the next data record
Cells.Find(What:="STCNumber", After:=ActiveCell, _
SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate
' move over one column to data
ActiveCell.Offset(0, 1).Range("a1").Select

' select this data to the end of the record
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Copy

' count the number of cells in this record
selRange = Range(ActiveCell,
ActiveCell.End(xlDown)).Address(rowabsolute:=True,
columnabsolute:=True)
num = Range(selRange).count
'transpose and paste the selected record a few columns over
Cells(count, 3).PasteSpecial Transpose:=True
'advance the row pointer
count = count + 1

' advance the source data cell pointer
pointer = pointer + num

' go to the next data record
Cells(pointer, 1).Select
pointer = pointer + 1

Loop

Application.ScreenUpdating = True

'add a new sheet and place the data from the source sheet on it

ActiveCell.Cells.Select
Cells.Select
Selection.Copy
Sheets.Add
Range("A1").Select
ActiveSheet.Paste

'delete the first two columns (original data)

ActiveCell.Columns("A:B").EntireColumn.Select
ActiveCell.Offset(0, 1).Range("A1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
ActiveCell.Offset(0, -1).Columns("A:H").EntireColumn.Select
Selection.ColumnWidth = 27.71

Range("A1").Select

End Sub

here's what a typical data record looks like:
*************************************

Number SA00029WI
Manufacturer Acme
MakeModel 180
TCNumber 5A63467
Description uprated model
Status Issued 2/11/93
ACO CE-W
Holder Acme Products
P.O. Box 357
Wellington KANSAS 67152 UNITED STATES

 

[Bob Greenblatt]

I probably broke the do not select rule, but if you can give me any
guidance it would be greatly appreciated:

I have modified your code, and interspersed a few comments and better (in my
opinion) code below yours. I did not go through the whole sub, but you
should get the idea. If not, post again. (I did not specifically test code
below.)

first the code
**************************

Sub SelectDown()
Dim count As Integer, pointer As Integer, num As Integer
Dim lastrow As Long Dim rR as Range
count = 1 'sets the destination row location
pointer = 3 'locates the source data location's start

' find the last row in the file
lastrow = Range("b65536").End(xlUp).Row lastrow=cells.specialcells(xlceltypelastcell).row
Range("A1").Select Don't do this
Application.ScreenUpdating = False

Do Until pointer > lastrow

' find the start of the next data record
Cells.Find(What:="STCNumber", After:=ActiveCell, _
SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate

Set rr= cells.Find(What:="STCNumber", After:=ActiveCell, _
SearchOrder:=xlByRows, SearchDirection:=xlNext)

' move over one column to data
ActiveCell.Offset(0, 1).Range("a1").Select No need for this
' select this data to the end of the record
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Copy

'don't select it, do this instead:
Rr.resize(rr.end(xldown))

' count the number of cells in this record
selRange = Range(ActiveCell,
ActiveCell.End(xlDown)).Address(rowabsolute:=True,
columnabsolute:=True)
num = Range(selRange).count 'Instead,
Num=rr.cells.count
Rr.copy

'transpose and paste the selected record a few columns over
Cells(count, 3).PasteSpecial Transpose:=True
'advance the row pointer
count = count + 1

' advance the source data cell pointer
pointer = pointer + num

' go to the next data record
Cells(pointer, 1).Select
pointer = pointer + 1

Loop

Application.ScreenUpdating = True

'add a new sheet and place the data from the source sheet on it

ActiveCell.Cells.Select
Cells.Select

I don't think you need these 2 lines

Selection.Copy
Sheets.Add
Range("A1").Select 'It's not necessary as it already is selected
ActiveSheet.Paste

'delete the first two columns (original data)

I'm confused now, you still on the new worksheet

ActiveCell.Columns("A:B").EntireColumn.Select
ActiveCell.Offset(0, 1).Range("A1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft

Columns("a:b").delete shift xltoleft

ActiveCell.Offset(0, -1).Columns("A:H").EntireColumn.Select
Selection.ColumnWidth = 27.71 Columns("a:h").columnwidth=27.71
Range("A1").Select

I admit, I might have missed something. You have confused me by referencing
A1 after doing an offset of a range. If this does not work, or not work
faster, email me a sample with some actual data and an example of how you
want the data to end up, and I'll try again.