Need Help Speeding Up a Subroutine

  • As a starting disclaimer I think I qualify as a script kiddie...


    We run a continuing education school in a retirement community. I adapted the dormant project, J-Walk Enhanced Data Form, that allows us to enter course registration information derived from paper forms into an Excel workbook we are using as a database. It's an entry form that allows me to go over the 32 field limit. Everything works on the form. There are four buttons which fill or clear large subsets of checkboxes that indicating the registration state of the resident for the the 30 or so offerings we have every semester. The subsets are for all the evening events, just the evening lectures, just the evening musical performances or a fourth button to remove all the checkmarks previously checked on the form. There is a third category of daytime classes but unlike the evening events, practically none of our residents commit to all of the day courses, so it doesn't need a button to speed recording of registrations.


    Anyway execution upon clicking on one of the buttons mentioned above takes between 5-10 seconds. I did some class, module and routine profiling and unless I misinterpreted the results, almost half the time comes from taking -1s (minus ones) written or cleared by the buttons in the worksheet and writing them to the form and the other half, comes from the checkmark making class itself, so the bottleneck might be the making or clearing of checks on the form. I also gleaned from here (a DoneEx owned website–see below) that maybe the button subroutines are not as efficient as they could be, because they use sheet activations and cell selection commands.


    I am calling an optimization sub recommended on the page linked for turning off automatic calculation, screenupdating, enabling events and the like and I think that helps. I'm aware that turning off the optimization routine is not couched within error catching code and that is a real problem that needs fixing. If anyone wants to help with that, I would appreciate it mightily too!


    Anyway below is the clear button code and the form update routine. The attached checkmark class I'm guessing is professionally written and may not be improvable, but I don't know . Any suggestions on how to speed up the code would be greatly appreciated. DoneEx XCell Compiler cuts the 10 second button executions to 2 seconds. I'm highly tempted to buy it, but it is about $50 more at $150 than I can justify to our treasurer.


    Thanks in advance for any help anybody can offer.

    Here's the probably offending form update sub:

  • Thanks! I have an optimize subroutine, see below:


    Code
    Public Sub OptimizedMode(ByVal enable As Boolean)
        Application.EnableEvents = Not enable
        Application.Calculation = IIf(enable, xlCalculationManual, xlCalculationAutomatic)
        Application.ScreenUpdating = Not enable
        Application.EnableAnimations = Not enable
        Application.DisplayStatusBar = Not enable
        Application.PrintCommunication = Not enable
    End Sub


    I call it at the beginning and turn it off at the end of the subs, everything else in the link is more or less difficult for me to understand and put into practice. I studied the page and many similar pages. I've tried to do some of the tips with mixed success. I think I know for instance how to stop activating sheet36 and selecting a cell there, but doing the same with sheet5 in the clear button sub, doesn't work for me and my code keeps landing me on worksheet 36 without doing any work on sheet5.

  • Great ...!!! :)


    The next simple check point is about empty blanks rows and columns in your database ...


    Have you already deleted all the useless Rows and Columns ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Quote

    I think I know for instance how to stop activating sheet36 and selecting a cell there, but doing the same with sheet5 in the clear button sub, doesn't work for me and my code keeps landing me on worksheet 36 without doing any work on sheet5.


    Do you mean that your performance problem is only related to your ClearButton_Click() ...???


    If that is the case, it could just be a matter of not Selecting the ActiveCell ... :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • I accessed his code first by bypassing the password. I felt guilty so I wrote him asking him if I could pay for the code. He told me the project was no longer active so I guess I was free to do what I wanted. I insisted I wanted to pay for the VBA, so he finally took the money.

  • Great ...!!! :)


    The next simple check point is about empty blanks rows and columns in your database ...


    Have you already deleted all the useless Rows and Columns ...?

    As far as I can tell, we have to keep all the names of the 1000 or so residents,

    with all of their blank columns, because they might later register for a course. Are you suggesting we should only enter a row for a resident if they actually do register? That is an interesting idea and may be the way the form was originally designed. In the end only 250 or so sign up in any one semester and I suppose I could make a combo box and select them from a named range which would then and only then insert them into the database.

  • As far as I can tell, we have to keep all the names of the 1000 or so residents, with all of their blank columns, because they might later register for a course.


    Are you suggesting we should only enter a row for a resident if they actually do register? That is an interesting idea and may be the way the form was originally designed. In the end only 250 or so sign up in any one semester and I suppose I could make a combo box and select them from a named range which would then and only then insert them into the database.


    I tested the idea on the final semester database. I removed the rows for residents who didn't register. I ended up with 265 residents instead of 1035. It did cut the execution time down about 15–20% for each of the buttons. So I'm going to see if that is a feasible idea.


    Early in the semester it would maybe even faster as there would be far fewer residents in the database. I tried removing all the residents but 50, and that last experiment didn't add any speed, it still only shave about 10% (it seem to a half second longer actually according to the benchmarking MsgBoxes).


    However thanks for the idea. That is a start on restructuring the database.

  • There are three other buttons like ClearButton_Click(). In ClearButton_Click() there are two situations where I activate a sheet and select a cell.

    Changing this:

    Code
        Sheet36.Activate    lastRowNumber = LastRowColumn(Sheet36, "r")      lastCell = "C" & lastRowNumber     Set lastCellRange = Range(lastCell)

    To this:

    Code
        'Sheet36.Activate    lastRowNumber = LastRowColumn(Sheet36, "r")      lastCell = "C" & lastRowNumber     Set lastCellRange = Sheet36.Range(lastCell)

    Works, but changing this:

    Code
        Sheet5.Activate    ActiveCell.Offset(0, 10).Range("A1").Select    Set xRg = ActiveCell.Range(Cells(1, 1), Cells(1, numberOfOfferings))    Application.DisplayAlerts = False    For Each rg In xRg        With rg            Select Case .Value            Case Is = -1                .Value = 0            Case Is = 0                .Value = 0            End Select        End With    Next

    To this:

    Code
        'Sheet5.Activate     Set xRg = Sheet5.Range("A1")    xRg = ActiveCell.Offset(0, 10)    xRg = ActiveCell.Range(Cells(1, 1), Cells(1, numberOfOfferings))    Application.DisplayAlerts = False    For Each rg In xRg        With rg            Select Case .Value            Case Is = -1                .Value = 0            Case Is = 0                .Value = 0            End Select        End With    Next

    Doesn't work. Instead it sends me back to Sheet36 and when I go back and check the row I tried to clear, the -1s (negative ones) are still where I don't want them to be.

  • Quote

    I adapted the dormant project, J-Walk Enhanced Data Form, that allows us to enter course registration information derived from paper forms into an Excel workbook we are using as a database.

    This is a protected tool and this question seems to be breaking copyright rules. I have asked how you obtained access to John's code. Please respond otherwise I will close this question.

  • OK thanks for the heads up. I guess I will have to spring for the DoneEx XCell compiler.


    In the end for the clear button code, after trying all kinds of things I ended up with:

    Code
    Private Sub ClearButton_Click()
        OptimizedMode True
        Dim benchmark As Double
        benchmark = Timer
        ActiveCell.Range(Cells(1, 11), Cells(1, 43)).Value = vbNullString
        OptimizedMode False
        Call UpdateForm
        MsgBox Timer - benchmark
    End Sub

    It shaves 2–3 seconds off and is funny how simple it is, but it gets the job done. I will go back and do some programming so that the hard coded constants 11 and 43 won't break the form when a semester has a different amount of courses.


    Thanks for your help and patience.

Participate now!

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