Announcement

Collapse
No announcement yet.

MS Excel 2007 - Performance Improvement Issues with VB Code

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • MS Excel 2007 - Performance Improvement Issues with VB Code

    Hello Members,

    This is my first post on this website and I am looking here for some help. I would like to have certain suggestions for the Excel tool that I have developed.

    I have about 5000 records in a sheet called "Errors" and Then about 50,000 records in a sheet called "Raw-Data".

    So for each of the record in sheet "Errors", I am counting the number of records in sheet "Raw-Data". So basically, I am browisng through the set of 50,000 records for about 5000 times.

    This is where I am going wrong and need some suggestion to improve the performance.

    As of now it is taking about 15 hours to finish(Based on the sample data set). I am not doing all the transactions in Memory and right now it is combination of Array and read/write from sheets.

    Do you think that doing all the validations using an array would improve the perfromance? Any sugegstions are welcomed.

    Cheers B
    Last edited by Born; 10th August 2010, 13:32. Reason: Spelling mistake in subject

  • #2
    Re: MS Excel 2007 - Performance Improvement Issues with VB Code

    Could you post your code to give us a better idea of what is happening?
    Tom Jones
    MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
    PhD, MSc, FIAP, MIITT
    IT Trainer / Consultant
    Ossian Ltd
    Scotland

    ** Remember to give credit where credit is due and leave reputation points where appropriate **

    Comment


    • #3
      Re: MS Excel 2007 - Performance Improvement Issues with VB Code

      Hi Ossian,
      PFA.

      Thanks for your prompt response!

      Cheers B
      Attached Files

      Comment


      • #4
        Re: MS Excel 2007 - Performance Improvement Issues with VB Code

        Could you give a basic example of each sheet?

        Basically though, just go through the 50K record sheet once and count the errors and slap the results into an array, e.g.

        Error 1 | 5
        Error 6 | 2000
        etc.

        Then compare that array to the 5K record sheet. No need to parse that one sheet 5000 times. Theoretically that should take it from 15 hours to 15 seconds.
        ** Remember to give credit where credit is due and leave reputation points where appropriate **

        Comment

        Working...
        X