none
VBA sub run from ActiveX Command Button behaves differently than when run from Form Control button RRS feed

  • Question

  • I have an Excel 2016 spreadsheet where I manually implement the functionality of Excel's built-in table function in a VBA subroutine.

    It simply consists of a loop where it changes an input cell, calls Application.Calculate, stores the result for each item in the table.

    The problem is if I call this VBA sub from an ActiveX Command button on the sheet (or on a userform) it will not calculate all values correctly IF I move the mouse after clicking the button. The whole loop takes about 20secs and I have a progress indicator message displayed in the status bar that is updated on each iteration. If I move the mouse during the loop I can see the progress speed up from that point on and the values will be incorrect from that point on.

    If I run the VBA sub from a forms control button instead it calculates properly regardless of what I do with the mouse.

    Thanks in advance for any help.

    Friday, April 21, 2017 12:22 PM

All replies

  • Looks a mass. Ask a question in Excel for developers for more help about VBA:

    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev

    Monday, April 24, 2017 1:43 PM
  • Hi,

    This forum focuses on general discussion for Office 2016 and Office 365 PruPlus. I notice your issue is related to the VBA function in Excel 2016.

    To better resolve your issue, I would help to move the thread to Excel for developers forum for further troubleshooting. The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,

    Winnie Liang


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Tuesday, April 25, 2017 2:22 AM
  • Hello,

    To check whether the issue is related to your code or file or your Office application, I suggest you follow the steps below

    1) Create a new workbook and let A1=1, A2=2, A3="=SUM(A1,A2)"

    2) Add an ActiveX button and handle its click event

    Private Sub CommandButton1_Click()
    Range("A1") = 100
    Application.Calculate
    End Sub
    

    3) Add a form button and assign a macro

    Sub Button2_Click()
    Range("A1") = 10
    Application.Calculate
    End Sub

    4) Click the two buttons, would you get correct result for them?

    If the issue could not be reproduced, I suggest you share some detail steps which could reproduce your issue or share your file via OneDrive.

    If the issue exists, what is your detail version number?

    If you have Office 365 subscription, please share the version number from File tab -> Account tab -> the version number shown after Office Updates.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 26, 2017 4:10 AM
    Moderator
  • I think need a file with lots of calculations in it - otherwise it would complete before i could move the mouse after pressing the buttton, which is when the issue happens.

    My office version:

    Version 1702 (Build 7870.2031

    Wednesday, April 26, 2017 3:35 PM
  • The forum won't let me post a link to the file on onedrive until my account has been verified.

    Don't know how long that will take.

    Wednesday, April 26, 2017 3:37 PM
  • Hi,

    You could paste the link as normal string without hyperlink.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 27, 2017 4:32 AM
    Moderator
  • Lets see if this works:

    https://1drv.ms/x/s!Ama9plcoTyX7g2F4oSQpgRD7hbDp

    Thursday, April 27, 2017 11:17 AM
  • Hello,

    I still failed to reproduce the issue.

    I create two buttons, change calculation options into manual, call IterateTables in their click event. I get the same result. And I test several versions, but could not reproduce. Could you reproduce the issue in other computer?

    Since your office version is not latest, I suggest you update your Office.

    Row Start value  Col Start Value  Result
    0.1 0.2 23.6763
    0.2 0.2 22.93661

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 28, 2017 5:42 AM
    Moderator
  • Hi Celeste,

    First of all thanks for looking into this for me.

    For some reason, the file that appears on OneDrive is missing the ActiveX button and the forms button that I placed on the sheet. Don't know why that happened.

    Anyway if you type Ctrl-e a UserForm should appear. If you click the command button and don't move the mouse the table (at cell F8) gives me these results:

    Correct results:

    1.10 2.10 3.10
    1.20 2.20 3.20
    1.30 2.30 3.30
    1.40 2.40 3.40
    1.50 2.50 3.50


    If I move the mouse during the loop I might get something like this:

    Incorrect results:

    1.10 2.10 3.10
    1.20 2.20 2.20
    2.20 2.20 2.20
    2.20 2.20 2.20
    2.20 2.20 2.20


    Here the calculations are correct up to cell G9, but from then on it does not calculate the result properly, and we get 2.2 in the rest of the table.

    When the calculations go wrong depends on when you move the mouse, so in your case the incorrect table may look slightly different.

    Perhaps you could try this?

    Thanks.

    Friday, April 28, 2017 11:20 AM
  • I just updated to

    Version 1703 (Build 7967.2139 Click-to-Run)

    and it still gives me incorrect answer if I move the mouse during calculations.

    Friday, April 28, 2017 11:25 AM
  • I get the same results on a different PC with Windows 7 SP1.

    In an effort to debug I added the following few lines right after Application.Calculate

                        

                        If Not Application.CalculationState = xlDone Then
                            DoEvents
                        End If

                        Range("S30").Offset(j, k) = Application.CalculationState

    The values for Calculation state are

    0 xlCalculating

    1 xlDone

    2 xlPending

    If I don't move the mouse I get 0 printed out for the table.

    If I move the mouse I get 0 and 2.

    I was expecting to get 1 for all entries.

    I tried to change the IF to a Do While loop but it got stuck in an infinite loop.

    How can I tell VBA to wait until Applicaition.Calculate has completed before moving on?

    I didn't think I would need to do this, but it looks like I do.

    Friday, April 28, 2017 2:37 PM
  • Hello,

    Followed your steps, the issue does exist and the result would be different every time if I move the mouse.

    After some researching, I think the issue causes from the screen update. Since the calculation based on the value of Range("F3") and Range("G3"), sometimes, the range value doesn't be updated, then we get wrong result.

    I would suggest you use worksheetfunction to calculate formula with VBA variable instead of range value from UI.

    Please use the line below and it would be faster than calculating based on cell value. 

                        varResults(j, k) = Application.WorksheetFunction.Sum(Range("$C$2:$C$100")) * 0.000000001 + varRowSet(1, k) * 10 + varColSet(j, 1) 'rngFormula.Value2
    

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 1, 2017 7:39 AM
    Moderator
  • Hi Celeste,

    I am glad that you were able to reproduce the issue.

    I don't understand why the ActiveX button has the problem, but the forms control button does not.

    Also, the spreadsheet I uploaded is just a simple example to demonstrate the general problem I am having 

    I have many spreadsheets that have these UserForms and ActiveX command buttons that show this problem.

    They are much more complicated and it would be difficult to find and rewrite whatever cells may be having issues.

    Is this a bug that requires a fix, or is there some better programming methodology that I can use to ensure correct results?

    Monday, May 1, 2017 12:02 PM
  • I think there is no other workaround for the issue. i suggest use form controls or rewrite formula. 

    Hope you could find a better one and we would greatly appreciate if you could share it here.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 2, 2017 11:12 AM
    Moderator