p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Excel VBA (http://p2p.wrox.com/forumdisplay.php?f=79)
-   -   speed up macro Excel VBA (http://p2p.wrox.com/showthread.php?t=36190)

crmpicco December 16th, 2005 08:41 AM

speed up macro Excel VBA
 

Code:


'... speed up macro
Application.ScreenUpdating = False Application.Calculation = xlCalculationManual


other that the above two lines of code, what can i use to speed up my macro?

www.crmpicco.co.uk

shattered December 16th, 2005 09:28 AM

thats a very open question? Often the best way to speed a macro up (apart from teh above) is to review the macro code itself and look at ways to optimise it.

You could always post the macro here that your trying to speed up..


crmpicco December 16th, 2005 10:59 AM

Code:

Workbooks.Open Filename:=gsContractFilename
Worksheets(i).Activate

are there any quicker ways to execute these lines?

www.crmpicco.co.uk

ix_ly December 19th, 2005 01:27 AM

One way of speeding up the process of macro is process the data without select it.

Example:
'------ this way is quite time & resource consuming
Range("A1").Select
activecell.Value = "test"

'------ this way is quite faster
cells(1,1).value = "test"

ix

crmpicco December 19th, 2005 11:29 AM

thanks, i removed the range references. anything else?

www.crmpicco.co.uk

mjppaba December 19th, 2005 12:45 PM

1. Do you use loops in your code. If so it may be quicker to sort the data using the 3 primary key fields, then conduct a find rather than loop.

2. Do you have lots of calculations/ functions and formula in the workbooks. e.g. Row 1 to 10000 may need the formulae to begin with, but once calculated make a copy of the recordset and paste the formula as values on all but the first line, any subsequent recalculation is only required on line 1. This is a big time saver.

3. Upgrade your PC with more memory

4. reduce the number of lookups in the formula, if you can use arrays to hold data in memory.


cheers

Matt


crmpicco December 20th, 2005 08:49 AM

thanks matt,

i have this loop below in my code:

Code:

For d = (tempCol - 64) To Last_Col

                                        If sPer <> "" Then
                                            sPer = sFarePeriod & "-" & Cells(tempRow, d).Text
                                            sPer = stripPeriods(sPer)
                                            sPer = convertPeriodDates3(sPer)
                                            gbFoundPeriod = True
                                            sFareSeason = "Season 1"
                                            Exit For
                                        End If

                                        '... if the cell has a date inside it then set that as the
                                        '... first part of the period date (01Jan05)
                                        If hasDate(Cells(tempRow, d).Text) Then
                                            sPer= Cells(tempRow, d).Text
                                        End If
                                    Next d

How do you mean using the 3 primary key fields?



www.crmpicco.co.uk

crmpicco December 21st, 2005 10:37 AM

CHEERS for that matt,

just out of interest do you know how to check a checkbox with the keyboard?

:-)

cant find on google.

www.crmpicco.co.uk

mjppaba December 21st, 2005 11:17 AM

Hi crmpicco,

usually it is a process of hitting the [TAB->] key until the checkbox becomes the active object, then press the [space bar] to toggle the on/off.


Sorry about not getting back re the loop, the code is a bit difficult to follow because there is a lot of previous code that is used to define the parameters. Can you give a brief description of the parameters so that we might be can possibly amend the procedure. By "3 primary keys" I am refering to the sort order choices,i.e. when you select a range of data and choose sort in the data menu, you are prompted for 3 sort fields, I have just referred to these as the 3 primary key fields.

Cheers

Matt



All times are GMT -4. The time now is 11:20 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.