VBA Macro Speed Slowing Down

[deluxes]

I am using EXCEL V on OS 10.3.7 on 550mhz titanium powerbook. I have a
large sheet (2500 rows x 90 columns). I am copying values from one
column to another.

Here is the VBA macro

Sub Testput2()
'
Dim AFrom As Range
Dim Time1 As Long, Time2 As Long
Dim Result As Variant
Time1 = Timer
Application.ScreenUpdating = False
Application.Calculation = xlManual
Set AFrom = Range("CE4:CE2541")
With AFrom
.Offset(0, -64).ClearContents
For Each Cell In .Cells
If (Cell.Value <> 0) Then _
Cell.Offset(0, -64).Value = Cell.Value
Next Cell
End With
Application.Calculate
Time2 = Timer
Application.ScreenUpdating = True
MsgBox "Elapsed Time " & Format((Time2 - Time1), "0.00") & _
" Seconds " _
, , "Input Calculations Complete"
End Sub

When I change the line
If (Cell.Value <> 0) Then _
to
If (IsNumeric(Cell.Value)) Then _

the time to execute goes from less than a second to 37 seconds!!
Anybody know a way around this problem.

 

[Paul Berkowitz]

I am using EXCEL V

You've said this many times. What do you mean by it? There is no Excel V. Do
you mean Excel X?

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.

 

[deluxes]

Sorry, I meant to type EXCEL X!!!

You've said this many times. What do you mean by it? There is no Excel V. Do
you mean Excel X?

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.

 

[Bob Greenblatt]

Sub Testput2()
'
Dim AFrom As Range
Dim Time1 As Long, Time2 As Long
Dim Result As Variant
Time1 = Timer
Application.ScreenUpdating = False
Application.Calculation = xlManual
Set AFrom = Range("CE4:CE2541")
With AFrom
.Offset(0, -64).ClearContents
For Each Cell In .Cells
If (Cell.Value <> 0) Then _
Cell.Offset(0, -64).Value = Cell.Value
Next Cell
End With
Application.Calculate
Time2 = Timer
Application.ScreenUpdating = True
MsgBox "Elapsed Time " & Format((Time2 - Time1), "0.00") & _
" Seconds " _
, , "Input Calculations Complete"
End Sub

I played with this and don't see any difference in execution time. I
formatted column S with funny fonts and borders and it still does not make
any difference. Remember timer only has a resolution of 1 second on the
Macintosh. What differences do you get if you place time2 before the
calculate?

 

[J Laroche]

Bob Greenblatt wrote on 2005/02/10 08:56:

I played with this and don't see any difference in execution time. I
formatted column S with funny fonts and borders and it still does not make
any difference. Remember timer only has a resolution of 1 second on the
Macintosh. What differences do you get if you place time2 before the
calculate?

The only condition that for me produces such a large disparity (1 s vs 42 s)
is when Afrom is empty. Somehow VBA is very slow to test IsNumeric on an
empty cell. It's proven by the fact that if only half of Afrom is empty the
time is cut in half.

By the way have you noticed the funny ghosts that appear around the cursor
when the execution is slow? I wonder what it's trying to show.

JL
Mac OS X 10.3.8, Office v.X 10.1.6

 

[J Laroche]

J Laroche wrote on 2005/02/10 19:34:

Bob Greenblatt wrote on 2005/02/10 08:56:



The only condition that for me produces such a large disparity (1 s vs 42 s)
is when Afrom is empty. Somehow VBA is very slow to test IsNumeric on an
empty cell. It's proven by the fact that if only half of Afrom is empty the
time is cut in half.

By the way have you noticed the funny ghosts that appear around the cursor
when the execution is slow? I wonder what it's trying to show.

JL
Mac OS X 10.3.8, Office v.X 10.1.6

Actually my experiment was flawed and my conclusion false because you sent
us on the wrong track.
For an empty cell, (Cell.Value <> 0) returns FALSE, whereas
(IsNumeric(Cell.Value)) returns TRUE. So right from the bat one test was
skipping the interior of the IF-THEN entirely.

The part that takes long to execute is therefore
Cell.Offset(0, -64).Value = Cell.Value

Interestingly, Cell.Offset(0, -64).Value = 89 by itself doesn't take long,
and neither does Variable = Cell.Value by itself.
But Variable = Cell.Value followed by Cell.Offset(0, -64).Value = Variable
takes long.

If the next column after AFrom contains values and I do
Cell.Offset(0, -64).Value = Cell.Offset(0,1).Value
then execution is short.

My conclusion: It's very long to copy emptiness, just like in your previous
thread it was very long to assign an empty string to a cell
(FactCol.Offset(Count, 0).Value = "") vs (FactCol.Offset(Count,
0).ClearContents)

JL
Mac OS X 10.3.8, Office v.X 10.1.6

 

[deluxes]

Wow!! A very impressive examination of the problem. For my situation,
you gave me the solution. I first test to see if it is numeric, the
make sure it is <>0, then copy. No what I want is fast!! I never
would have solved it without your help. Thank you, thank you, thank
you!

 

[J Laroche]

OK, final word on this subject. As deluxes initially observed, VBA might be
slower on the Mac than on a PC. Actually, it seems especially concentrated
on the code to blank a cell. For proof I ran the following code on Win
XL2000 and Mac XL v.X:

Sub CopyCell()
T1 = Timer
For Each c In Range("A1:A2000").Cells
c.Offset(1, 0).Value = c.Value
Next
T2 = Timer
MsgBox "The operation took " & (T2 - T1) & " seconds"
End Sub

And the results are:
cell A1 XL2000 XL v.X
date 0.609375 s 1 s
62 characters 0.40625 < 1 s
999999999 0.328125 s < 1 s
empty 2.21875 s 24 s

On the PC timer results are fractional, not on the Mac. We can see that on
both platforms setting the value of a cell to nothing is noticeably longer
than setting it to any other value. But on the Mac is excruciatingly slow.
Mac BU has some code cleaning to do. Can somebody with XL2004 check if it's
been done already? Maybe not, since the VBA version is the same (VBA 5,
versus VBA 6 on Windows).

JL
Mac OS X 10.3.8, Office v.X 10.1.6

 

[Bob Greenblatt]

Sub CopyCell()
T1 = Timer
For Each c In Range("A1:A2000").Cells
c.Offset(1, 0).Value = c.Value
Next
T2 = Timer
MsgBox "The operation took " & (T2 - T1) & " seconds"
End Sub

Yes, I can confirm the disparity of blank vs. non blank cells still exists
in Excel 2004.

 

[deluxes]

Does anyone know if you can use a similar trick across different
sheets???

 

[Bob Greenblatt]

Does anyone know if you can use a similar trick across different
sheets???

Yes, I can confirm the disparity of blank vs. non blank cells still exists
in Excel 2004.

I do not understand your question. What trick? How do you want to process or
test different sheets?

 

[deluxes]

I was referring to using the "with" statement for one range and
referring to other ranges using the "Offset" to refer to other ranges
in order to cut down on the number of times an object is addressed.

 

[J Laroche]

deluxes wrote on 2005/02/15 01:37:

Does anyone know if you can use a similar trick across different
sheets???

Do you mean something like that, copying all data from one sheet to another?

Sub CopySheet()
For Each c In Sheets(1).UsedRange.Cells
Sheets(2).Range(c.Address).Value = c.Value
Next
End Sub

But if you want the simple duplication of a sheet, better simply do:
Sheets("Sheet1").Copy _
After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)


JL
Mac OS X 10.3.8, Office v.X 10.1.6

 

[deluxes]

I have sheets with about 2500 rows and 40 columns. I am usin g a a
"For Next" loop to go through the cells in a column in one sheet, do
some calculations (based on the value in the cell, which may require
the values from other cells on other sheets)) and write the result to a
cell in another sheet.

deluxes

 

[J Laroche]

deluxes wrote on 2005/02/16 08:19:

I have sheets with about 2500 rows and 40 columns. I am usin g a a
"For Next" loop to go through the cells in a column in one sheet, do
some calculations (based on the value in the cell, which may require
the values from other cells on other sheets)) and write the result to a
cell in another sheet.

deluxes

Well, that's almost what I posted before. Just adapt from this very simple
modified example (a, b, d, e and f are variables that you replace with what
you need). Play and experiment.

You notice below that some properties, such as .Offset, are shown without a
formal object preceding them. That's because c (from With c) is the default
object. Use this structure when the same object is referred to often in a
short portion of the code, to ease reading (here it's only c, but it could
be Range("MySource"), or ThisWorkbook.Sheets(3), etc.).

Sub BigCalculation()
a = 2
b = 1
d = 1
e = 3
f = 10
For Each c In Sheets(a).UsedRange.Columns(b).Cells
With c
Sheets(d).Range(.Address).Value = _
.Value * (.Offset(0, e) + .Offset(0, f))
End With
Next
End Sub


JL
Mac OS X 10.3.8, Office v.X 10.1.6

 

[deluxes]

Doesn't "sheets(d)" cause the sheet object to be loaded with each cell
and slow the process way down? Is ther any way to avoid this by some
property or method like an "Offset" to help speed this up?

 

[J Laroche]

deluxes wrote on 2005/02/17 23:49:

Doesn't "sheets(d)" cause the sheet object to be loaded with each cell
and slow the process way down? Is ther any way to avoid this by some
property or method like an "Offset" to help speed this up?

Not at all. As long as you don't do a Sheet(x).Activate, there's no change
of the active worksheet. Excel can manipulate via VBA any sheet without
making it the active (displayed) sheet.

JL
Mac OS X 10.3.8, Office v.X 10.1.6