Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 29-08-2009
Member
 
Join Date: Aug 2009
Posts: 98
VBA macro optimization tips

I need some help with my Excel 2007 macro. I have created a small macro in VBA coding and found that takes a lot of time. I have used a FOR EACH loop in this rather than a regular FOR loop. I think this is taking a lot of my time and also system resources. Once I start executing this, my computer slows down. In Windows Task Manager I have seen CPU usage history goes to 70%.

I have tried various method to optimize my VBA macro but nothing fixed. Can anyone provide me some VBA macro optimization tips?
Reply With Quote
  #2  
Old 29-08-2009
Member
 
Join Date: May 2008
Posts: 685
Re: VBA macro optimization tips

To conserve memory resources, always declare all your variables with proper data types. When a variable is undeclared, system considered it as Variant which consumes more memory than that is required. Always keep track of how much memory each data type requires. If you think, a variable will not be able to handle certain value and will call implicit function then it is better to predefine it. Avoid using double data type if you think float will do the operation.
Reply With Quote
  #3  
Old 29-08-2009
Member
 
Join Date: May 2008
Posts: 2,293
Re: VBA macro optimization tips

Another point of speeding up your VBA MACRO is when you want to do division on integers, use the integer division operator (\) rather than the floating point division operator (/). This is because the latter one always returns a Double value regardless of the types of the numbers. Also keep a note that although you are using a Single or Double value in an arithmetic expression with integer values, the integers are still converted to Single or Double values.
Reply With Quote
  #4  
Old 29-08-2009
Member
 
Join Date: May 2008
Posts: 2,008
Re: VBA macro optimization tips

There are many things that you can do to optimize your macros. The main reason why coding go slow is running loops. Revisit your loops to see if you are repeatedly calling memory resources. If possible then move unnecessary variables that you do not need inside the loop. If you think you can move some expressions outside the loop.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "VBA macro optimization tips"
Thread Thread Starter Forum Replies Last Post
Real world tips for Windows 7 SSD tweaks and optimization Anyone-4-CS Operating Systems 5 13-05-2010 12:33 PM
Beginners tips for Search Engine Optimization Computer_Freak Tips & Tweaks 2 08-05-2010 05:01 PM
Best PC Optimization Tools Maq.H Reviews 2 21-01-2010 01:57 AM
SSE Optimization on AMD CPU Tobius Software Development 2 14-01-2009 07:54 PM
Search Engine Optimization Tips Praxey Tips & Tweaks 2 06-01-2009 03:30 PM


All times are GMT +5.5. The time now is 01:48 PM.