Create and use a named mutex in VBA

Question

I am doing some intertwining clipboard magic with Word and Excel VBA and I figured clipboard being a shared resource should probably be guarded by a simple mutex.

How would I go about creating and releasing a named mutex in VBA? I couldn't find anything VBA related. As if nobody ever created a mutex from VBA. Is it impossible?

I guess you have to import the corresponding API functions from Kernel32 API... `CreateMutex`, `OpenMutex`, `WaitForSingleObject` etc.. — A.S.H, Oct 27 '15 at 21:31
this old thread might be helpful: http://stackoverflow.com/questions/947/how-to-use-a-mutex-in-visual-basic — A.S.H, Oct 27 '15 at 21:38
Yeah, I figured as much but how do I do that in VBA? I can do that in VB.NET but what's the correct syntax for VBA? — wpfwannabe, Oct 28 '15 at 09:07
plz see my answer below. It should provide some general guideline, you can adjust it to what you want to achieve. Hope this helps :) — A.S.H, Oct 28 '15 at 12:52

A.S.H · Accepted Answer · 2015-10-28T12:51:23.543

This answer should provide you with the general approach to achieve the synchronization between Excel-VBA and word-VBA using a mutex to protect some shared data. The details depend on your target application..

The idea goes like this, you can create this code in the module ThisWorkbook of Excel and similarly in ThisDocument of Word:

' Module ThisWorkbook or similarly ThisDocument

Private myMutex As Long

Private Const ERROR_ALREADY_EXISTS = 183&
Private Const MUTEX_ALL_ACCESS = &H1F0001

Private Declare PtrSafe Function CreateMutex Lib "kernel32" Alias "CreateMutexA" (ByVal lpMutexAttributes As Long, ByVal bInitialOwner As Long, ByVal lpName As String) As Long
Private Declare PtrSafe Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
Private Declare PtrSafe Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long
Private Declare PtrSafe Function OpenMutex Lib "kernel32" Alias "OpenMutexA" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal lpName As String) As Long
Private Declare PtrSafe Function ReleaseMutex Lib "kernel32" (ByVal hMutex As Long) As Long

Private Sub Workbook_Open()
    myMutex = CreateMutex(0, 1, "myMutex")
    Dim er As Long: er = Err.LastDllError
    If er = 0 Then
        MsgBox "myMutex Created"
    ElseIf er = ERROR_ALREADY_EXISTS Then
        MsgBox "mutex previously created"
        myMutex = OpenMutex(MUTEX_ALL_ACCESS, 0, "myMutex")
    Else
        MsgBox "mutex creation failed"
    End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    CloseHandle myMutex
End Sub

Private Sub doSomeCriticalTask()
    WaitForSingleObject myMutex, 20000 ' say we can wait for 20 seconds
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' do critical section code, access shared data safely
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ReleaseMutex myMutex
End Sub

Perfect, thanks! Although I figured out most of it myself after a lot of fiddling and trial and error. You still deserve the credit. — wpfwannabe, Oct 28 '15 at 15:03
@wpfwannabe thanks, glad to give some help in such interesting problem :) — A.S.H, Oct 28 '15 at 15:07

Create and use a named mutex in VBA

1 Answers1

Linked