Atomicity in Excel VBA

Question

I have the following code in a Form Module in an xlsm:

Public Sub DoModal() ' Note: Called by LaunchHelper which is associated with F12 in Workbook_Open
    Dim ewsActive As Worksheet: Set ewsActive = ThisWorkbook.ActiveSheet
    If ewsActive Is Overview Then ' Overview is the CodeName of one of the Worksheets
        Set ewsOverview = ewsActive ' This is a Private variable in the Form Module
        ShowSafe
    End If
End Sub

Private Sub ShowSafe() ' Calling Show directly caused random crashes, I concluded that these were caused by that the Form was not loaded into memory, this function makes sure, it is in memory before calling Show
    Dim blnLoaded As Boolean: blnLoaded = False
    Dim objForm As Object: For Each objForm In UserForms
        If objForm.name = Me.name Then
            blnLoaded = True
            Exit For
        End If
    Next objForm
    If blnLoaded = False Then
        Load Me
    End If
    Show
End Sub

As far as I know:

VBA is single-threaded, it can never happen that two Subs or Functions are executed in parallel (e.g. while processing Worksheet_Activate, Worksheet_SelectionChange is also called, and the order of executing the statements inside those two functions is undetermined).
However, if we look at Excel+VBA as a system, then it is multi-threaded because Excel and VBA run in parallel (and Excel is multi-threaded itself, in addition).

My problem is:

I see a race condition in DoModal, because checking and acting is not atomic, and the following scenario is possible:

(1) The condition ewsActive Is Overview is evaluated as true, so we start executing the branch inside the If block.

(2) The execution is taken over by Excel, where the user switches to a different Worksheet.

(3) ShowSafe is called while we the ActiveSheet is not Overview but something else.

Do you agree with my reasoning? Is it correct that in spite of the checking, the Form might show up on a different Worksheet? So far, I have not succeded in making this error happen but I would like to know if it was possible in theory, even if it has only a low chance.

Where can I find more information about problems and guarantees related to multi-threading in VBA?

AFAIK, a loaded form is not bound to a specific worksheet, so it is entirely possible for the worksheet to be switched under the form. Exactly when that happens (and when it becomes visible to the user) is another story. Even if Excel does not process user events during your code above, it could/will as soon as your code returns to get any user input/actions. — RBarryYoung
– RBarryYoung, Commented Jun 19, 2017 at 16:57
@RBarryYoung: Once the Form appears, the user cannot click on anything in Excel's UI, including Sheet Tabs. Activating from VBA code is possible but that is under my control. My problem is the period from pressing F12 until the Form actually appears (unfortunatelly, we have very weak computers at my workplace, so this can be quite long). — z32a7ul
– z32a7ul, Commented Jun 24, 2017 at 10:06

A.S.H · Accepted Answer · 2017-06-19 16:26:39Z

2

For the user to switch the ActiveSheet, Excel needs to process User Events. Experienced VBA programmers usually let Excel process user events by explicitly calling DoEvents, because Excel's GUI freezes/starves for CPU while VBA code is running. This suggests that the race condition you describe is extremely unlikely, if possible at all.

However, this non-concurrency between Excel's GUI and its VBA runtime is only know by practice. I didn't find official documentation that formally confirms that there's absolutely zero chance for Excel to process user events while VBA code is running (without explicitly yielding). We only know by practice that this chance is "almost zero".

For this reason I would say that your suggested race condition is, though extremely unlikely, theoretically possible until we have an official statement or documentation that rules out that concurrency.

p.s. I consider this question as of academic interest only, and the code of illustrative purpose only, because obviously there no relevance for a form to "check" whether it is actually loaded.

edited Jun 19, 2017 at 16:26

answered Jun 19, 2017 at 16:19

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

z32a7ul Over a year ago

Thanks for your answer. What I was looking for is whether I can remove the 'almost' from before the 'zero'.

z32a7ul Over a year ago

BTW, I use ShowSafe because Safe produced random crashes, then I added a Load that was called always, that way it crashed always, then I wrote this workaround. I suppose these crashes were due to internal errors in Office 2007 (the earliest version can crash when I simply try to open an xlsm). I was not able to determine which update is required to remove this glitch, and I have no control over Microsoft update at my workplace (not to speak about business partners' computers). Besides, ShowSafe has no drawback, so I retained it in my template.

Harassed Dad · Accepted Answer · 2017-06-19 14:12:15Z

1

I'm not seeing what you're trying to do. Why does it matter what sheet is active when you show your user form? If it needs to be Worksheets("overview") then just Activate it on form Load event? It won't hurt if it's already active.

Similarly if you aren't sure if it's loaded then call Load just before your Show

Finally you're referring to the user form as Me - which implies that you're running this from the user form itself. So this code will not run unless the form is loaded into memory, so ShowSafe is pointless code.

answered Jun 19, 2017 at 14:12

Harassed Dad

4,7141 gold badge12 silver badges12 bronze badges

2 Comments

z32a7ul Over a year ago

Purpose: This Form is a Helper to a complex Worksheet: it helps in navigating, and it makes possible to change the value of some key assumptions quickly. The Form has no meaning without the Overview sheet, so I would like to check that it is the Active one. For efficiency and simplicity, I assume that if at the beginning this is the Active one, it won't be change until Hide is called, so one check at the beginning is sufficient.

z32a7ul Over a year ago

Regarding ShowSafe, see my comment to A.S.H.'s answer. When I read about it that time, I think that the thing missing from memory is not the code of the form but its resources (dialog resource).

Collectives™ on Stack Overflow

Atomicity in Excel VBA

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related