Improve excel VBA speed

    Hi, I'm trying to improve the performance of a spreadsheet I've made that has to work in old pcs. The use of this spreadsheet is as a tables reservation. You can check the seat you want to reservate and then he write the name in the list. The spreadsheet works great but I'm trying to rewrite the code to avoid the use of objects (I think that this is one of the things that slow down my macros). Now I have 3 questions.


    1. Is it true that a lot of object could slow down my excel file (I have more or less 300 checkboxes, and a couple of buttons)?
    2. If I use the event macros (BeforeRightClick, BeforedoubleClick and SelectionChange), instead of objects, could I improve the performance of this file in old pcs?
    3. Once I select the seats in sheet1, I linked the cell from this sheet to sheet2 to give true or false value based on the seat selected (If selected true, empty false). I create a macro using the for function (from bigining of the column to last value), that with a if funcion find the true values and write the name in a cell in sheet1 to the next cell in sheet2. Is there a better way to do this? Avoiding the use of loops? Maybe with a vlook?


    Thanks for you reply!

    I can't promise a solution but it would be easier to help and test a possible solution if you could attach a copy of your file. Explain in detail what you want to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. De-sensitize the data if necessary.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    First file BOOKING


    I select the seats I want to book using the checkboxes, then, I write the name inside the textbox and I press book.
    The program loop throught the line in names sheets and if there is a true value it write the booking's name in the next cell. This file is very slow in old PCs.


    Secondo file testtest


    Here I select the seats I want so book by double click. the value of the seats turn from false to true. Then i write the name od the person ho wantis to book and I select the yellow cell to book. To write the reservation It use the same loop of the old file.

    I'm sorry but the links to your files are actually not links. To attach files, click the paperclip icon in the upper right of the response box.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    Your workbook contains many macros so, to be honest, for me it would be very difficult and time consuming to go through all the code to see how it relates to your data and what you want to do. Also, when designing an application, it is always best to design it from the very beginning to maximize the speed. Once it has been designed and the macros written based on the design, it can be difficult to improve the code without changing the design. I hope that makes sense. I can tell you that using loops can be very slow. Sometimes you don't have to loop through an entire column to find a value. You can use Find...FindNext to look for a value. This would be much faster. I don't know what else I can say at this point. Try to eliminate as many loops as you can. Put your code between

    Code
    Application.ScreenUpdating = False/Application.ScreenUpdating = True

    statements. If you have formulas in your sheet, use also

    Code
    Application.Calculation = xlCalculationManual/Application.Calculation = xlCalculationAutomatic

    statements. This will help to speed things up a bit as well. I hope this helps.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

Participate now!

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

Register Yourself Login