Solved: Macro alternates fast & slow execution time

[David]

Hello,

First, let me say that I'm new to this forum, so please forgive me if I make any mistakes...

The problem with my Excel macro is that sometimes it runs fast and sometimes it runs slow. Specifically, it alternates among taking 15 seconds and 45 seconds--of course, I want it always to take 15 seconds! The execution times alternate predictably: first 15 seconds, then 45, then 15, then 45, etc. It doesn't matter if I save the file between runs; it still follows the pattern until I close & reopen Excel.

The part of the macro that causes the variation is a copy-paste action. The macro copies a large range (16,845 R x 66 C), and then does "paste formulas" with "skip blanks." I need to use "skip blanks," which is why I can't simply use Range("new").Formula = Range("old").Formula.

During this action, the activity bar in the lower-left corner of the Excel window says "Cell:" and shows a FULL progress bar. It seems to hang at this point (when the progress bar appears full).

Before this action, the macro performs about 80 smaller copy-paste actions. I have tried using VBA to clear the clipboard before and after the problematic code, but it doesn't seem to do anything. I don't think it's a clipboard problem anyway, because the fast/slow variation occurs when running the exact same code on the exact same data.

I'm not going to attach the file, because it's over 40 MB!

Any help would be tremendously appreciated...

Thanks,
David

[Norie]

David

It might help to see the code.

Have you tried turning off automatic calculation?

Application.Calculation = xlManual
' your code
Application.Calculation = clAutomatic

[David]

Thanks for the quick response!

Yes, it does use manual calculation. (You can't see it in the code because this sub is called from another one.) I'm going to paste in the code below, but it probably won't look right. I checked the FAQs, but didn't see any tips on how to keep the VBA formatting for the code... I'd be happy to repost the code if you can suggest a way to make it look better!

The purpose of this sub is to detect whether to use the default data or override (OVR) data for each of about 80 countries. Most of the code is dedicated to testing whether to use the default or OVR data, and then moving the appropriate data set to the reporting range.

Moving this data requires pasting over some formulas. When all the data movement is finished, the sub replaces the formulas, which is where the problem occurs. To replace the formulas, it copies from a big range which contains only the formulas, and pastes with skip blanks. (The skip blanks prevents pasting over the data, which is interspersed with the formulas.)

I marked the problem area with rows of asterisks. It's near the end.

The part that really stumps me is the pattern of the execution times. Why would it be fast, then slow, then fast, then slow???

-David

[vba]
Sub OVRcalcsPatientsVALUECountry()

'Application.ScreenUpdating = False

'this cell will be used as the anchor which runs down the column
Set WorkingCell = ThisWorkbook.Worksheets("Patients").Range("A11")

'Identify last row
intPatientsLastRow = WorkingCell.SpecialCells(xlCellTypeLastCell).Row
ThisWorkbook.Worksheets("Patients").Range("C1") = intPatientsLastRow

Do
varRowType = WorkingCell.Offset(0, 4)
'load in the countryname

If WorkingCell.Offset(0, 4) = "CountryName" Then
varCountryNameNoSpaces = WorkingCell
'find OVR status
'load this country's OVR status
With ThisWorkbook.Worksheets("SourceControls").Range("A3:A110")
Set countrycell = .Find(varCountryNameNoSpaces, LookIn:=xlValues)

If Not countrycell Is Nothing Then
varThisCountrySource = countrycell.Offset(0, 6)
End If

End With
End If

'Test if this is a row that qualifies for OVR and perform necessary calcs


If varThisCountrySource = "CM" Then 'that is, if the country is set to CM as a datasource,
'transfer each timeperiod's data from the Country Manager area
WorkingCell.Offset(3, 8).Range("A1:AE202").Value = WorkingCell.Offset(3, 78). _
Range("A1:AE202").Value 'move the whole country
Set WorkingCell = WorkingCell.Offset(208, 0) 'move down to the next Country
Else 'do this if the source is OVRLoc, but change to accomodate OVRReg!!!
'first copy the CM data into the report
WorkingCell.Offset(3, 8).Range("A1:AE202").Value = WorkingCell. _
Offset(3, 78).Range("A1:AE202").Value 'move the whole country
'then override with any data in the OVRLoc section
WorkingCell.Offset(3, 43).Range("A1:AE202").Copy 'copy the whole country;
'must use the "copy" function so that we have access to the "skip blanks" function
WorkingCell.Offset(3, 8).PasteSpecial Paste:=xlPasteValues, SkipBlanks:=True
Call ClearClipboard
Set WorkingCell = WorkingCell.Offset(208, 0) 'move down to the next genzyme drug
End If

Loop Until WorkingCell.Row >= intPatientsLastRow + 1

'*****************************************
'paste in vial% formulas
ThisWorkbook.Worksheets("PatientsFormulas").Range("I11:AM" & intPatientsLastRow).Copy
ThisWorkbook.Worksheets("Patients").Range("I11"). _
PasteSpecial Paste:=xlPasteFormulas, SkipBlanks:=True
'******************************************

Call ClearClipboard
ThisWorkbook.Worksheets("Patients").UsedRange.Columns("I:AM").Calculate
Beep
End Sub
[/vba]

[johnske]

Hi David, welcome to VBAX. Hope you don't mind, but I've added VBA tags and some line breaks to make your code more readable

[johnske]

40 MB? That will slow things down straight away...

First off, where are your variables declared? Have you declared public variables perhaps? If Public, it chews up memory and also causes unintended results unless they're handled properly...

If they're not public and you're not using Option Explicit, then undeclared variables are declared as type Variant by default - very slow, and may also cause unintended results.

Do you have another workbook open at the same time? If not, "ThisWorkbook" is the default and its use is redundant.

Do you have any ChartSheets in the workbook? If not, "WorkSheets()" can be replaced with just "Sheets()"

When you use Set on an Object variable you should set the object to nothing at the end of the code to release it from memory. It's supposed to be released automatically and I've never had any issues, but I've read that it can sometimes cause problems...

Try the code below in a copy of your workbook and see if it does what's intended (got no workbook to test it on, but I think it's right). If this doesn't speed it up, it could be a PC issue, read this article and see if that helps any - it may just need a 'clean-up' to get rid of your Temp files and redundant registry entries.

[vba]
Option Explicit
'
Sub OVRcalcsPatientsVALUECountry1()
'
Dim WorkingCell As Range, countrycell As Range
Dim varCountryNameNoSpaces As String, varThisCountrySource As String
Dim intPatientsLastRow As Long
'
Application.ScreenUpdating = False
'
'this cell will be used as the anchor which runs down the column
Set WorkingCell = Sheets("Patients").[A11]
'
With WorkingCell
'Identify last row
intPatientsLastRow = .SpecialCells(xlCellTypeLastCell).Row
Sheets("Patients").[C1] = intPatientsLastRow
'
Do
'varRowType = WorkingCell.Offset(0, 4) < ?this is not used again?
'
'load in the countryname
If .Offset(0, 4) = "CountryName" Then
varCountryNameNoSpaces = WorkingCell
'
'find OVR status
'load this country's OVR status
With Sheets("SourceControls").[A3:A110]
Set countrycell = .Find(varCountryNameNoSpaces, _
LookIn:=xlValues)
If Not countrycell Is Nothing Then
varThisCountrySource = countrycell.Offset(0, 6)
End If
End With
'
End If
'
'Test if this is a row that qualifies for OVR and perform necessary calcs
If varThisCountrySource = "CM" Then
'
'(that is, if the country is set to CM as a datasource, then
'transfer each timeperiod's data from the Country Manager area)
.Offset(3, 8).[A1:AE202] = .Offset(3, 78).[A1:AE202].Value
'
'move down to the next Country
Set WorkingCell = .Offset(208, 0)
'
Else 'do this if the source is OVRLoc,
'but change to accomodate OVRReg!!!
'
'first copy the CM data into the report
'move the whole country
.Offset(3, 8).[A1:AE202] = .Offset(3, 78).[A1:AE202].Value
'
'then override with any data in the OVRLoc section
'copy the whole country
'(must use "copy" so we have access to "skip blanks")
.Offset(3, 43).[A1:AE202].Copy
.Offset(3, 8).PasteSpecial _
Paste:=xlPasteValues, SkipBlanks:=True
'
'clear clipboard
Application.CutCopyMode = False
'
'move down to the next genzyme drug
Set WorkingCell = .Offset(208, 0)
'
End If
'
Loop Until .Row >= intPatientsLastRow + 1
'
'*****************************************
'
'paste in vial% formulas
Sheets("PatientsFormulas").Range("I11:AM" & intPatientsLastRow).Copy
Sheets("Patients").[I11].PasteSpecial _
Paste:=xlPasteFormulas, SkipBlanks:=True
'
'******************************************
'
'clear clipboard
Application.CutCopyMode = False
'
End With
'
'release Object variables
Set WorkingCell = Nothing
Set countrycell = Nothing
'
Sheets("Patients").UsedRange.Columns("I:AM").Calculate
Application.ScreenUpdating = True
'
Beep
'
End Sub[/vba]

[David]

My apologies for not responding earlier!!! Unlike your previous responses, I didn't get an email alert for this one, so I assumed that no one had posted. I will take a look at this shortly.

-David

[David]

I just tried to implement this, and alas, it didn't fix the problem: I still have the fast/slow execution time issue. I've decided to call it quits on this one--the project deadline is approaching, and my clients aren't that concerned about this issue. Thanks for your effort, though!

-David