Stop VBA to block Excel sheet when executing a function

Question

I’ve got following Excel scenario:

A1 has the value of "A", B2 = "B", C3 = "C"

but they are interchangeable.

Within the workbook path there are two subfolders containing wav files named A.wav, B.wav and C.wav

The code at the bottom allows me to playback the wav files with a button click firing the macro PlayIt().

My Problem is, while the function is executing I’m unable to edit cells in Excel which I really need to! It kinda looks like this

https://gifyu.com/images/GIF8d5e1.gif

Thank you for any help!

Code for Audioplayback:

Option Explicit

#If VBA7 Then
Public Declare PtrSafe Function PlaySound Lib "winmm.dll" _
  Alias "sndPlaySoundA" (ByVal lpszName As String, ByVal hModule As Long, ByVal dwFlags As Long) As Long
#Else
Public Declare Function PlaySound Lib "winmm.dll" _
  Alias "sndPlaySoundA" (ByVal lpszName As String, ByVal hModule As Long, ByVal dwFlags As Long) As Long
#End If

Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000

Sub PlayTheSound(ByVal WhatSound As String)
    If Dir(WhatSound, vbNormal) = "" Then
        ' WhatSound is not a file. Get the file named by
        ' WhatSound from the Windows\Media directory.
        WhatSound = ThisWorkbook.Path & "\stimuli\" & "\1second\" & WhatSound
        If InStr(1, WhatSound, ".") = 0 Then
            ' if WhatSound does not have a .wav extension,
            ' add one.
            WhatSound = WhatSound & ".wav"
        End If
        If Dir(WhatSound, vbNormal) = vbNullString Then
            Beep            ' Can't find the file. Do a simple Beep.
            MsgBox "Could not find the file in the Path: " & WhatSound
            Exit Sub
        End If
    Else
        ' WhatSound is a file. Use it.
    End If

    PlaySound WhatSound, 0&, SND_ASYNC Or SND_FILENAME    ' Finally, play the sound.
End Sub

Sub PlayIt()
    PlayTheSound (Range("A1").Value)
    PlayTheSound (Range("B1").Value)
    PlayTheSound (Range("C1").Value & "e")
End Sub

`ByVal hModule As LongPtr`. If your office is x64 that might be the reason. — GSerg, Jul 13 '17 at 16:05
@GSerg If I delete `ByVal hModule As LongPtr` and only call `PlaySound WhatSound, SND_ASYNC Or SND_FILENAME` I only hear the last wav file `PlayTheSound (Range("C1").Value & "e")` — Kalaschnik, Jul 13 '17 at 16:21
You have `ByVal hModule As Long`. The correct thing is `ByVal hModule As LongPtr`. — GSerg, Jul 13 '17 at 20:17
try doevents. more details at this [link](https://www.excelforum.com/tips-and-tutorials/875335-vba-doevents-function-magic.html), and you can download [this file](https://www.excelforum.com/attachments/tips-and-tutorials/213929d1360690023-vba-doevents-function-magic-2012.11.15...ms-excel-vba-doevents-function-demo-v.xls) with some working macros that you can still interact with the worksheet while they run. — luckyguy73, Jul 14 '17 at 00:16
@AshtonMorgan That is a really bad advice in general, it is especially bad for functions that can natively run asynchronously, and it is impossible to implement in this case because you would need to inject `DoEvents` call *inside* the call to `PlaySound`. — GSerg, Jul 14 '17 at 06:51
@GSerg, changed it to `Alias "sndPlaySoundA" (ByVal lpszName As String, ByVal hModule As LongPtr, ByVal dwFlags As LongPtr) As LongPtr` but this doesn’t help (StringPtr prompts an error btw and having only `ByVal hModule As LongPtr` is not working as well... — Kalaschnik, Jul 14 '17 at 08:39
I shared the file here https://1drv.ms/f/s!Ak19sOo9DVGCqv4QdqA6cnOGMuhQSg with the code above and the wav files. Maybe this helps... thanks so far for your input @gserg and @ashton-morgan! — Kalaschnik, Jul 14 '17 at 08:49
Please don't invent data types. Only `hModule` is `LongPtr`, all others, including the function return type, you had right from the beginning. [That last version](https://stackoverflow.com/questions/45085827/stop-vba-to-block-excel-sheet-when-executing-a-function?noredirect=1#comment77168578_45085827) is *very* wrong. — GSerg, Jul 14 '17 at 11:39
While all that is true, what I did not notice is that you are calling the wrong function. You are providing parameters for [PlaySound](https://msdn.microsoft.com/en-us/library/windows/desktop/dd743680(v=vs.85).aspx) but actually you're calling [sndPlaySound](https://msdn.microsoft.com/en-us/library/windows/desktop/dd798676(v=vs.85).aspx). — GSerg, Jul 14 '17 at 11:46

score 0 · Answer 1 · answered Jul 14 '17 at 12:01

0

You have your declaration wrong in two ways: hModule should be LongPtr, and you are using parameters from PlaySound while actually calling sndPlaySound.

Fix your declaration:

#If VBA7 Then
Public Declare PtrSafe Function PlaySound Lib "winmm.dll" _
  Alias "PlaySoundA" (ByVal lpszName As String, ByVal hModule As LongPtr, ByVal dwFlags As Long) As Long
#Else
Public Declare Function PlaySound Lib "winmm.dll" _
  Alias "PlaySoundA" (ByVal lpszName As String, ByVal hModule As Long, ByVal dwFlags As Long) As Long
#End If

answered Jul 14 '17 at 12:01

GSerg

76,472
17
159
346

This rises another issue, now only the last line from PlayIt() will be played `PlayTheSound (Range("C1").Value & "e")`, the first du lines are "skipped"... – Kalaschnik Jul 14 '17 at 15:00
Sorry but VBA noob question; what does the Alias actually do? I seems safe to delete it, and only have this: `Public Declare PtrSafe Function PlaySound Lib "winmm.dll" _ (ByVal lpszName As String, ByVal hModule As LongPtr, ByVal dwFlags As Long) As Long` The file I provide is updated now. Thanks alot! – Kalaschnik Jul 14 '17 at 15:03
@McTell Like it is stated in the documentation, it will stop a currently played sound when you start a new one, unless you also pass `SND_NOSTOP` (which you do not), in which case it will not play anything if another sound is in progress. `Alias` is used when the name as exported from the library is different to the name you want to use for that function in your code. The actual function name is `PlaySoundA`, and unless you are willing to declare it as `PlaySoundA`, you have to provide an alias. If you provide an alias that matches the declared name, the IDE will delete it. – GSerg Jul 14 '17 at 15:53
@McTell There is no easy way with this function to queue sounds so that they play in order. You might want to look into some other media features for that, or merge the wav files before playing them, which is [not difficult](https://stackoverflow.com/q/340646/11683). You can even do that in memory and pass the pointer to the byte array to `PlaySound` with `SND_MEMORY`. – GSerg Jul 14 '17 at 15:58
Sorry, I got confused using SND_NOSTOP won’t fix my problem here, rather I should go for the merge approach? – Kalaschnik Jul 14 '17 at 17:15
@McTell Correct. Or look into other sound playing components. – GSerg Jul 14 '17 at 18:48
But is merging possible within Excel VBA functionality? The Link you provided is using C#.. – Kalaschnik Jul 14 '17 at 21:12
@McTell The process is about reading the file into a byte array and ignoring some of the bytes. Yes, you can do that with Excel VBA. – GSerg Jul 15 '17 at 09:44

Stop VBA to block Excel sheet when executing a function

1 Answers1