Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old December 16th, 2005, 08:41 AM
Friend of Wrox
Points: 8,019, Level: 38
Points: 8,019, Level: 38 Points: 8,019, Level: 38 Points: 8,019, Level: 38
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2005
Location: Mauchline, East Ayrshire, Scotland
Posts: 1,525
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to crmpicco Send a message via AIM to crmpicco Send a message via MSN to crmpicco Send a message via Yahoo to crmpicco
Default 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
__________________
_______________________
Ayrshire Minis - a Mini E-Community
http://www.ayrshireminis.com
http://www.crmpicco.co.uk
  #2 (permalink)  
Old December 16th, 2005, 09:28 AM
Authorized User
 
Join Date: Oct 2004
Location: , , .
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to shattered Send a message via Yahoo to shattered
Default

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..

  #3 (permalink)  
Old December 16th, 2005, 10:59 AM
Friend of Wrox
Points: 8,019, Level: 38
Points: 8,019, Level: 38 Points: 8,019, Level: 38 Points: 8,019, Level: 38
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2005
Location: Mauchline, East Ayrshire, Scotland
Posts: 1,525
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to crmpicco Send a message via AIM to crmpicco Send a message via MSN to crmpicco Send a message via Yahoo to crmpicco
Default

Code:
Workbooks.Open Filename:=gsContractFilename
Worksheets(i).Activate
are there any quicker ways to execute these lines?

www.crmpicco.co.uk
  #4 (permalink)  
Old December 19th, 2005, 01:27 AM
Registered User
 
Join Date: Oct 2005
Location: :), :), Malaysia.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to ix_ly Send a message via MSN to ix_ly
Default

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
  #5 (permalink)  
Old December 19th, 2005, 11:29 AM
Friend of Wrox
Points: 8,019, Level: 38
Points: 8,019, Level: 38 Points: 8,019, Level: 38 Points: 8,019, Level: 38
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2005
Location: Mauchline, East Ayrshire, Scotland
Posts: 1,525
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to crmpicco Send a message via AIM to crmpicco Send a message via MSN to crmpicco Send a message via Yahoo to crmpicco
Default

thanks, i removed the range references. anything else?

www.crmpicco.co.uk
  #6 (permalink)  
Old December 19th, 2005, 12:45 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Bournemouth, Dorset, United Kingdom.
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #7 (permalink)  
Old December 20th, 2005, 08:49 AM
Friend of Wrox
Points: 8,019, Level: 38
Points: 8,019, Level: 38 Points: 8,019, Level: 38 Points: 8,019, Level: 38
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2005
Location: Mauchline, East Ayrshire, Scotland
Posts: 1,525
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to crmpicco Send a message via AIM to crmpicco Send a message via MSN to crmpicco Send a message via Yahoo to crmpicco
Default

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
  #8 (permalink)  
Old December 21st, 2005, 10:37 AM
Friend of Wrox
Points: 8,019, Level: 38
Points: 8,019, Level: 38 Points: 8,019, Level: 38 Points: 8,019, Level: 38
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2005
Location: Mauchline, East Ayrshire, Scotland
Posts: 1,525
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to crmpicco Send a message via AIM to crmpicco Send a message via MSN to crmpicco Send a message via Yahoo to crmpicco
Default

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
  #9 (permalink)  
Old December 21st, 2005, 11:17 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Bournemouth, Dorset, United Kingdom.
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



Similar Threads
Thread Thread Starter Forum Replies Last Post
Removing a VBA macro password in Excel utalwalk Excel VBA 2 December 22nd, 2013 11:38 AM
Save a file in CSV format thru Excel VBA macro roli2712 Excel VBA 1 July 11th, 2007 03:05 AM
control Excel VBA macro with a VB 05-designed UI dgr7 Visual Basic 2005 Basics 1 January 17th, 2007 11:29 AM
control Excel VBA macro with a VB 05-designed UI dgr7 VB How-To 0 December 29th, 2006 05:59 PM
control Excel VBA macro with a VB 05-designed UI dgr7 Excel VBA 0 December 29th, 2006 05:58 PM





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