• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Speed up VBA

M

MrBramme

Member
Hi Guys,


I'm working on a VBA code to generate reports. It works great, but it's slow...

I've read the "speeding up" part of this page (and many more) but still, it's slow...


My main obstacle for speed seems to be the lookups over different sheets (the report uses 3 sheets, that are filled in by other employees: a base sheet containing raw data on consumer behaviour, a second sheet containing more info on that customer (link being an ID), and a third sheet containing info on sales personell (linked by their unique id).


The biggest slowdown is matching all data over the 3 sheets. Any tips? i've replaced my vlookups with arrays, seems to have improove speed a bit but not enough :)

I though about just biting the bullet and making step 1: move all data from book 2 to book 1, and book 3 to book 1, so Book 1 contains all data ... but still, that's a lot of matching data :)


any help is welcome!


(note, if you need sample data. i'll try and generate some this evening/weekend)
 
Hi ,


Is it possible to replace sections of the VBA by worksheet formulae ?


Can you give either a proper sample workbook , or copy + paste the code here , so that we can get an idea of the parts where the speeding up can be done ?


Narayan
 
Hi,


I was using worksheetfunctions, but someone told me that array works the fastest of all, hence my swap to arrays :)


The code itself spans over 3 modules with different subs ... i've broken it down in parts you see :)


- module 1: setup --> reads all data, checks for errors, etc etc

- module 2: adding data --> matches all the data and "creates" the report (this is the monster that eats time like crazy)

- module 3: This just does some final checks and saves in xls and pdf.


Tbh, at this point i believe i can only speed it up by rewriting it from scratch, implementing the new VBA tricks i've learned over the past months (i created that one a year ago, when i started taking steps into VBA ... now i'm "that VBA guy" at the office ...)


I'll start by placing timers at each segment so i know where the time takes too long, and i'll copy that portion of code (i'm doing this during my off-work time, so i won't be able to so it before tonight, sorry :))
 
Hi ,


I do not know much about this , but have you gone through the following links ?


1. http://www.excelitems.com/2010/12/optimize-vba-code-for-faster-macros.html


2. http://www.soa.org/news-and-publications/newsletters/compact/2012/january/com-2012-iss42-roper.aspx


3. http://blogs.office.com/b/microsoft-excel/archive/2009/03/12/excel-vba-performance-coding-best-practices.aspx


4. http://fastexcel.wordpress.com/2011/05/25/writing-efficient-vba-udfs-part-1/


Narayan
 
one option to make your life a bit easier you could just whack a few message boxes in to say "do you want to update from [sheet1 name]?" and another for sheet2. At the very least it would mean you don't need to run all of the code unnecessarily. Wouldn't actually speed it up, but could save it some time in the running.
 
You must log in or register to reply here.
Back
Top