Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Can I clear the actions which are stored while a Macro waits

0

I have a Macro which connects to an Oracle PING database to collect an LDAP ID.

It then takes the LDAD ID and connects to LDAP to get the customer ID 

Once it has the Customer ID it looks in 9 other databases to see if the customer is present. 

This is all activated by selecting a the userid cell on the sheet.

I have had to add a 5 sec wait to this to stop users from triggering too frequent hits on the LDAP (Flags a DOS warning to security)

My problem is that while the WAIT is executing the user is able to click all over the place and once the wait finishes excel sets about catching up on all these random clicks.

Is there any way to clear the Stack? Que? Buffer? as the next line after the wait or alternatively some way to stop the mouse activity during the wait?

Answer
Discuss

Answers

0
Selected Answer

Deleting the stack (or whatever) seems like a good idea. The alternative is to block keyboard action during the Wait. I wonder if this effect might be built into the Sleep function. It's a function of Windows and you have to declare the API before you can use it.

In the same vein, showing a Modal form durng the Wait would inhibit the use of the keyboard and you could tell the user to keep her hands to herself as well as how much longer this unhappy state must continue.

But then I found this thread and now feel that there might be an even better solution there.

The code below will wait for 5 seconds without interrupting the code. During this time the keyboard should be locked.

Private Sub Wait5Seconds()
    Dim Tim As Double
    Tim = Timer
    
    Do While Timer < (Tim + 5)
        DoEvents
    Loop
End Sub

In the following you have the above idea fully implemented in a solution. Try this code on a blank worksheet. In your project replace the MsgBox with your LDAP calls.

Option Explicit
Private Waiting As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
    ' 25 May 2018
    If Waiting Then
        With Application
            .EnableEvents = False
            .Undo
            .EnableEvents = True
        End With
    End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' 25 May 2018
    
    Const Loops As Integer = 3              ' number of LDAP calls: change as needed
    Static Cell As Range
    Dim Tim As Double
    Dim i As Integer
    
    If Waiting Then
        Application.EnableEvents = False
        Cell.Select
        Application.EnableEvents = True
    Else
        Set Cell = Target
        Waiting = True
        Do
            i = i + 1
            MsgBox "Making LDAP call No. " & i
            If i = Loops Then Exit Do
            
            Tim = Timer
            Do While Timer < (Tim + 5)
                DoEvents
            Loop
        Loop
        Waiting = False
    End If
End Sub
Discuss

Discussion

Thanks for the reply. I was avoiding "SLEEP" as it looked like it might be a bit more challenging to maintain through the next OS, Hardware, MS builds.  However, using Sleep made no difference to my issue. I think now it is not related to the  WAIT as such but normal XL - Macro behaviour in that any keystrokes or mouse activities occurring during long-running macros will all be carried out, in order, once the macro completes.
k1w1sm (rep: 197) May 22, '18 at 4:42 pm
Sorry my suggestion to try SLEEP didn't work out. Did you try showing a Modal form? Showing such a form would disable the keyboard. I fear however that it might also disable the WAIT function. That's why I like the many ideas put forward in the linked thread.
One of them would be to use a Timer instead of WAIT, running a Do Loop for the duration. If the running code doesn't disable the keyboard DoCmd might enable you to react to anything the user types with Application.Undo immediately.
Variatus (rep: 4889) May 22, '18 at 8:28 pm
Thanks for your efforts - I had a look at the linked item but couldn't find anything of value as I am not working in the WEB or .NET.
I have a progress bar in one of my other workbooks which I could use but it is not that important that I stop the commands buffering. I just thought if I had a single line or two I would put them in. 
k1w1sm (rep: 197) May 22, '18 at 10:16 pm
I have added code to my answer which should have the effect you wish for. It's more than 2 or 3 lines of code. In fact it is 2 + 3 lines but if it solves your problem ... :-)
Variatus (rep: 4889) May 23, '18 at 5:38 am
That was good fun. It did indeed stop the actions queuing up by executing them immediately.  Unfortunately, the overall effect was the exact opposite of what I was trying to achieve. My problem is my macro runs on SelectionChange. This then does a call to LDAP. I need to stop the calls being too frequent as it was looking like a denial of service attack.  The timer solution enables the user to run many processes concurrently whereas the wait forces a single thread.
k1w1sm (rep: 197) May 24, '18 at 3:51 pm
C'mon k1, since you have a command DoEvents in your code you should expect to handle the events that come its way. I have added code to my answer which does that.
Variatus (rep: 4889) May 24, '18 at 10:02 pm
Add to Discussion
1

Thanks for all your efforts. Here is how I implemeted you suggestion. I found exit sub was handy.  

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim lastRow As Long
    Dim ThisSheet As Worksheet
    Dim tim As Single
    Static Cell As Range

        If Target.Cells.Count > 1 Then Exit Sub

        If DoingLdap Then
            Application.EnableEvents = False
            Cell.Select
            Application.EnableEvents = True
            Exit Sub
        End If

        Set ThisSheet = Worksheets("Shared")
        thisRow = Target.Cells.Row
        Thiscol = Target.Cells.Column
        lastRow = ThisSheet.UsedRange.Rows.Count
        Set Cell = Target.Offset(0, 1) ' needed the to show it had finished and until the exit subit ran the same one
        If Thiscol < 2 And thisRow < lastRow + 1 Then
            If thisRow > 1 Then
                DoingLdap = True
                Cells(thisRow, 4) = GET_USERID(ThisSheet.Cells(thisRow, 3))
                Cells(thisRow, 5) = SwnFromLDAPL(ThisSheet.Cells(thisRow, 4))
                Cells(thisRow, 6) = SWNinSAL(ThisSheet.Cells(thisRow, 5))
                Cells(thisRow, 7) = StatusFromLDAPL
                ' it appears our LDAP is held on a production server and excessive hits raise a security flag
                tim = Timer
               Do While Timer < tim + 5
                    DoEvents
               Loop
                DoingLdap = False
            End If
        End If

End Sub
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login