speed up macro Excel VBA

crmpicco · December 16th, 2005, 08:41 AM

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