?
Solved

[excel] - autofill speed?

Posted on 2003-03-20
13
Medium Priority
?
1,936 Views
Last Modified: 2011-09-20
I have a macro contains the following lines for autofilling:


    Range("D2:I2").AutoFill Destination:=Range("D2:I9"), Type:=xlFillDefault
    Range("D22:I22").AutoFill Destination:=Range("D22:I29"), Type:=xlFillDefault
    Range("D42:I42").AutoFill Destination:=Range("D42:I49"), Type:=xlFillDefault
    Range("D62:I62").AutoFill Destination:=Range("D62:I69"), Type:=xlFillDefault
    Range("D82:I82").AutoFill Destination:=Range("D82:I89"), Type:=xlFillDefault


The macro itself is in a loop of running around 1000 times.
The macro is basically like this, everytime, the D2:I2, D22:I22..etc data are changed, and then autofilled... and then the data changed again, and then autofilled again...

My question is, why does my macro run @ a very fast speed @ the beginning, however, after around 200 times, it slows down dramatically. Why is that??
my cpu is duron 1ghz.
excel 2000

any ideas why?? can i make it run consistently fast??
because the speeds that it slows down to is very pathetic.. it takes like several seconds to autofill few lines..



   
0
Comment
Question by:c_kent301
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 4
13 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 8178423
From past experience, when VBA programs run too slowly, it's usually time to change the algorithm to something more efficient. Why do you need to loop through the AutoFill 1000 times?
0
 

Author Comment

by:c_kent301
ID: 8179050
but why does it start out so fast? and then gets slower & slower??
I thought if the algorithm isn't efficient, it should be slow all the way...
0
 
LVL 3

Expert Comment

by:ehout
ID: 8179669
Well,
It may have to do with your screenupdating? or that excel holds the undo information and thus is using more and more resources?
Try putting this:
application.screenupdating=false
before the loop and
application.screenupdating=true
after it and in your errorhandling.

Also you might consider
the following before your code

dim lngCalc&
dim blnautoComp as boolean
with application
  lngcalc& = .Calculation
  blnautoComp = .AutoComplete
  .Calculation = xlManual
  .AutoComplete = False
end with

again, after your code (and in your errorhandling) you should reset this, so put there:

with application
  .Calculation = lngcalc&
  .AutoComplete = blnautoComp
end with

Kind regards
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:c_kent301
ID: 8185779
ehout,
thanx for your reply..
I have already disabled the screenupdat...

however, i didn't have this
==============
dim lngCalc&
dim blnautoComp as boolean
with application
 lngcalc& = .Calculation
 blnautoComp = .AutoComplete
 .Calculation = xlManual
 .AutoComplete = False
end with
===============
can you tell me what this is for??
and..

I copied/pasted it in my macro,
but there's something wrong with:

 blnautoComp = .AutoComplete



 .AutoComplete = False


Something wrong also happens for these lines:
 
 .Calculation = lngCalc&
 .AutoComplete = blnautoComp


0
 

Author Comment

by:c_kent301
ID: 8185797
"that excel holds the undo information and thus is using more and more resources?"

I think this could be it..
but don't know how to clear it..
can you help me w/ it?
thanx a lot
0
 
LVL 81

Expert Comment

by:byundt
ID: 8186725
The extra code turned your calculation from automatic to manual. When you change a cell with manual calculation, it will resolve its own formula, but not those of any cells that refer to it. With automatic (or semi-automatic) calculation, excel will resolve the entire spreadsheet after each change.

If all you are doing is pasting data, then it makes no difference. On the other hand, if your spreadsheet formulas refer to the range being filled--then it could slow things down considerably.

The .Calculation=IngCalc& trys to restore whichever calculation mode you had before changing it to manual. Unless you are using the Table feature (one input or two input), then there is no difference between automatic & semi-automatic. Rather than figure out the error, you might change the line to:

application.calculation=xlAutomatic

This latter line is more reliable if your code blows up (thus trapping you in manual calculation mode).
0
 
LVL 3

Expert Comment

by:ehout
ID: 8187201
byundt is right about the code.
However, I'm a bit surprised you got errors. What errors did you get?

If often use the way above to change any setting in an Office program and later on restore it to the previous one. (create VBA for more people than just me, and cannot rely on people not having changed some settings).

If the code keeps erroneous, you can achieve the same thing by switching autocalculation and autocomplete off in the options dialog.

I'm not sure if the undo stuff can be switched off in VBA.
But I think the easiest way is just not to use the workbook functions, but create a loop yourself to achieve what you want.

Cry out loud if you need an example for this.

Kind regards.
0
 

Author Comment

by:c_kent301
ID: 8187760
Hey guys,

thanx for both of your repllies...

however, it still slows down after around 200 cycles...


I've uploaded an example of my file to

http://www.geocities.com/c_kent301/data.xls


can you guys check it out and see if the same thing happens to you guys? like the whole thing begins to slow down after few hundreds of cycles?? and if there's anything that i did wrong or can be improved, please help me to correct it.. thanx.


the macro I run is

replace_autofill

(the code is following)


======================
Sub replace_autofill()
   Dim wshSheet2           As Worksheet
   Dim intInitialValue     As Integer
   Dim intFinalValue       As Integer
   Dim i                   As Integer 'counter
   Dim int6P1Row        As Integer
   Dim btest   As Boolean
   Set wshSheet2 = ThisWorkbook.Sheets("Sheet2")
   
   intInitialValue = Sheet2.Range("K10")
   intFinalValue = Sheet2.Range("L10")
   int6P1Row = 2
   
   For i = intInitialValue To intFinalValue
       
    '   Application.Run "AutoClear"
       
    j = i + 1
   
    Range( _
        "D2:I2,D22:I22,D42:I42,D62:I62,D82:I82,D102:I102,D122:I122,D142:I142,D162:I162,D182:I182,D202:I202,D222:I222" _
        ).Select

    Selection.Replace i, _
        Replacement:=j, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        MatchCase:=False
       
       
       
       'autofill
        Application.Run "AutoFill"

 
   









Application.ScreenUpdating = False










   
   
   
   
   
   
   
   
   
   
' 6P1 CHECKING START..

       If Sheet2.Range("U3").Value = "6x" Then
           Sheet2.Select
           Sheet2.Range("D2,L2").Copy
           Sheets("6P1").Select
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
           Application.CutCopyMode = False
           ActiveCell.Offset(1, 0).Select
           Sheets("Sheet2").Select
       End If
   
   
        If Sheet2.Range("V3").Value = "6x" Then
           Sheet2.Select
           Sheet2.Range("E2,L2").Copy
           Sheets("6P1").Select
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
           Application.CutCopyMode = False
           ActiveCell.Offset(1, 0).Select
           Sheets("Sheet2").Select
       End If
   
   
        If Sheet2.Range("W3").Value = "6x" Then
           Sheet2.Select
           Sheet2.Range("F2,L2").Copy
           Sheets("6P1").Select
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
           Application.CutCopyMode = False
           ActiveCell.Offset(1, 0).Select
           Sheets("Sheet2").Select
       End If

        If Sheet2.Range("X3").Value = "6x" Then
           Sheet2.Select
           Sheet2.Range("G2,L2").Copy
           Sheets("6P1").Select
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
           Application.CutCopyMode = False
           ActiveCell.Offset(1, 0).Select
           Sheets("Sheet2").Select
       End If
   
        If Sheet2.Range("Y3").Value = "6x" Then
           Sheet2.Select
           Sheet2.Range("H2,L2").Copy
           Sheets("6P1").Select
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
           Application.CutCopyMode = False
           ActiveCell.Offset(1, 0).Select
           Sheets("Sheet2").Select
       End If

        If Sheet2.Range("Z3").Value = "6x" Then
           Sheet2.Select
           Sheet2.Range("I2,L2").Copy
           Sheets("6P1").Select
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
           Application.CutCopyMode = False
           ActiveCell.Offset(1, 0).Select
           Sheets("Sheet2").Select
       End If
   
' 6P1 CHECKING ENDED..
   
   





Application.ScreenUpdating = True














   Next i
   

Application.Run "AutoClear"

   
    Range( _
        "D2:I2,D22:I22,D42:I42,D62:I62,D82:I82,D102:I102" _
        ).Select

        Selection.Replace j, _
        Replacement:=3, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        MatchCase:=False






   
End Sub






0
 

Author Comment

by:c_kent301
ID: 8188084
ehout,

what do you mean by

"the easiest way is just not to use the workbook functions, but create a loop yourself to achieve what you want." ??

like, what do you mean not to use the workbook functions??



0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 152 total points
ID: 8190239
First, I notice from your sample code that you are using quite a few dot commands (e.g.  .Select). Microsoft suggests minimizing the use of dot commands to improve execution speed. For example, you frequently use:

           Sheets("Sheet2").Select

Each of these references could be eliminated by using

With Sheets("Sheet2")
...
End With

Second, you may want to change your algorithm to minimize the interaction between VBA and the spreadsheet. In one program I wrote, this change resulted in almost ten-fold improvement in execution speed.

There's a fair bit of overhead each time you switch between the Excel spreadsheet and VBA. If you can do all of your manipulation in VBA (without using the spreadsheet to store the intermediate values) then execution speed will increase substantially. The basic approach is to read the data from the spreadsheet into VBA, do the calcs in VBA, then return a single big block of results to the spreadsheet.

To do this, Dim an array Results(222,26)   (I picked those subscripts by looking for the highest row & column references in your sample code). Read as much starting data into VBA as you will need with statements like: Results(i,j)=range("A1").

Once all the data has been entered into VBA, you can then do the manipulations shown in your code above (such as looking for the text string "6X" and then assigning values to the appropriate elements).

When all calcs are complete, paste the results back onto the spreadsheet with a statement like: Range("A1","Z222").value=Results

The above approach assumes that the receiving spreadsheet has been pre-formatted correctly. If need be, however, you can format programmatically--though that will slow things down.
0
 
LVL 3

Accepted Solution

by:
ehout earned 148 total points
ID: 8194347
Hi,

Sorry it took a while.
I'm still looking at your sheets at the moment, but it's not an easy fix. To make things faster, you'll have to rebuild the code. It seems like your pretty experienced in using Excel, but a bit less in programming VBA?

Tip: keep the number of commands to be processed at a minimum. For eaxample, you have a loop iterating hundreds of time. Instead of setting the pplication.screenupdating.false inside this loop. place it outside.
Also byundt has a point with the references to the workbook (sheet and range objects). Worksheet functions often have nice functionality, but each reference to the worksheet, workbook, or any range takes time to process. And since you are iterating hundreds of times...

But all these optimizations will not shorten the running time as much as you want, since you are doing a lot of stuff. So best is to skip running on the worksheet at all and use the method byundt mentioned, namely by use of arrays.

0
 
LVL 3

Expert Comment

by:ehout
ID: 8195793
Hi,

Having seen what your excel sheet looks like, I'd say: optimize it as much as you can, using the remarks given in the comments above. You will gain a little performance, but don't expect miracles.

The stuff is pretty complex, even with help of arrays. If you're not an experienced programmer, just optimize a little be glad with it, and just be patient while it runs.

Kind regards.
0
 
LVL 81

Expert Comment

by:byundt
ID: 8196903
I tried looking for your workbook using the posted link, but got a "This page is not available" message. Could you repost it please?
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question