<3Speed up code? This simple VBA code makes a report but its taking 15 minutes.

  • This macro is for a bus workshop, it reviews the excel DB and makes me a report of all the expenses per bus which ends up giving 2k rows.

    Can you please tell me how to speed up this macro? 15 minutes is a lot of time.

    ||| macro below:

  • Code Tags Added
    Your post does not comply with our Forum RULES. Use code tags around code.

    Posting code between


    tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window.

    (I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

  • Try this.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Try this.

    I know my request is ambitious but i'm perplexed by your code. I thoroughly believe that a human who helps another with beautiful information like this deserves a better future.

    What's the logic behind these three blocks: .
    1) [a4].Resize(lCnt, 11) = z
    2) lCnt = lCnt + 1: ReDim Preserve y(1 To 11, 1 To lCnt)
    3) ReDim z(1 To lCnt, 1 To 11)
    For i = 1 To lCnt
    For ii = 1 To 11
    z(i, ii) = y(ii, i)

    ps it worked! im amazed, perplexed, everyone who helped is k'eng and IDK MY WEEKEND HAS BEEN MADE, I DIDNT KNOW I WOULD FIND SOMETHING AS BEAUTIFUL AS WHAT I LEARNED WITH YOU

  • In a nutshell, there are 2 ways of writing VBA code, object based and array based.

    In the case of object based, as your original code was, for every iteration of the loop data has to be read from the worksheet for the data row concerned, that data then gets written back to the Report sheet in the required order.

    In the case of array based, my code, all the data is read in one go and kept in the computer memory in the form of an array (variable x), then the code loops through that array and for every time item x(i, 8) is not empty the required data is placed into a second array (variable y), the size of array y gets increased by 1 for each time x(i, 8) is not empty.

    Since it is not possible to increase the first dimension of an array, only the second dimension, array y ends up with the data transposed from what is actually required. It is possible to place the data back to a worksheet with the data in the required format by using "Application.Transpose". However, this method has a tendency to fail with large arrays, so the data is first transposed into a third array (variable z).

    All of the above is done within the computer memory using C++ language which is very much faster than VBA, the data is read from the data worksheet just once and placed back to the report sheet just once, as opposed to the thousands of times data gets read and posted back when an object based code is used. This makes the code run much faster (the more data involved the greater the difference between object based and array based run times).

    To answer your specific points:

    1). This is where the contents of array z are placed onto the Report sheet, Range A4 is first resized to the correct number of rows and columns to match the size of array z.

    2). This is where array y gets its second dimension increased by 1 for every iteration of the loop when item x(i, 8) is not empty. The "Preserve" ensures that the contents of the array before re-dimming are not lost.

    3). This is where array z is loaded with all the data contained in array y but with the dimensions reversed (data is transposed).

    I hope that makes it clearer.

    Out of interest how much faster did the code run using my code?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • A

    My code took 15 minutes and your code takes less than 5 seconds, see why I'm perplexed? I couldn't even reply before, I'm just zoning in my mind how good that is, because now I can fix the input/output of iterations and transactions... but I need to understand your code better to be able to do that, it would be similar but I'm not a seasoned coder

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!