Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Efficient Macro
Group, What are examples of VBA macros that are efficient and no efficient. I'm learning now that as I do more and more error detectio on spreadsheets that have less than 2000 rows and less than 52 columns speed is an issue. Presently my test spreadsheet with all th comparing and looking for errors takes about 6 minutes to completel run. I'm a beginner at VBA and therefore believe many of my bits o code are probably poorly written. Examples of code optimization I' sure can reduce my macro cycle-time significantly. A budding VBA programmer....... Ton -- ajociu ----------------------------------------------------------------------- ajocius's Profile: http://www.excelforum.com/member.php...fo&userid=1769 View this thread: http://www.excelforum.com/showthread.php?threadid=39211 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Efficient Macro
Good afternoon Ajocius Make this your first line of your code: Application.ScreenUpdating=False and this your last: Application.ScreenUpdating=True If you haven't already used this technique, you'll find the increase i speed staggering. If you have recorded a macro, bear in mind that yo don't have to select a cell to do something to it, for example: Range("A1").Select ActiveCell.FormulaR1C1 = "hello" is what the macro recorder will give you but can be achieved with : Range("A1").FormulaR1C1 = "hello" Also, if you are manipulating a single object across successive line of code, use the With .. End With construct. Also, any variables should be declared (use Option Explicit at th start of your code to force you to do so) and typed (eg, Dim i A Integer). Hopefully, following these should lead to slicker, quicker code. HTH Dominic -- dominic ----------------------------------------------------------------------- dominicb's Profile: http://www.excelforum.com/member.php...fo&userid=1893 View this thread: http://www.excelforum.com/showthread.php?threadid=39211 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Efficient Macro
Macros that work on the worksheet cells are slow.
You can greatly increase speed if you set up an array of the variant type from you worksheet cells then use code that works with the array. eg Dim MySheetArray as variant (in the declarations) then MySheetArray = Range(A1:AZ2000) then code that works with the elements of this 2 dimensional array. mySheetArray(1,1) is the value in A1 on the worksheet, mySheetArray(2,2) is the value in B2 on the worksheet etc. One book I use suggests this results in code that runs up to 50 times faster! Ken Johnson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Efficient Macro
DominicB and Hanjohn, Whoa, I can't believe how much faster my very large macro executes. Thanks for the advice. Take care and God Bless. Tony -- ajocius ------------------------------------------------------------------------ ajocius's Profile: http://www.excelforum.com/member.php...o&userid=17695 View this thread: http://www.excelforum.com/showthread...hreadid=392117 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF and VLOOKUP - how efficient? | Excel Worksheet Functions | |||
What is more efficient | Excel Discussion (Misc queries) | |||
More efficient way? | Excel Programming | |||
Which is more efficient? | Excel Programming | |||
More efficient code | Excel Programming |