在Excel中播放声音，具体取决于使用按钮的值

Question

VBA不是我的强项，但我想得到一个Excel工作表，其中A1可能包含单词"Hello"或"World"或数字1，并且根据该内容，单击旁边的按钮时应播放声音文件。wav 文件位于 excel 文件所在的特定子文件夹中。

我使用的是 Excel(64 位(版本。我已经在检查此页面： 如何使用VBA播放波形文件或声音文件

并尝试了以下方法(64位兼容(：

Option Explicit
#If VBA7 Then
Public Declare PtrSafe Function sndPlaySound32 Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long
#Else
Public Declare Function sndPlaySound32 Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long
#End If
Sub PlayTheSound(ByVal WhatSound As String)
If Dir(WhatSound, vbNormal) = "" Then
' WhatSound is not a file. Get the file named by
' WhatSound from the WindowsMedia directory.
WhatSound = Environ("SystemRoot") & "Media" & 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.
Exit Sub
End If
Else
' WhatSound is a file. Use it.
End If
sndPlaySound32 WhatSound, 0&    ' Finally, play the sound.
End Sub
Sub PlayIt()
Select Case Range("A1").Value
Case "Hello"
PlayTheSound "chimes.wav"
Case "World"
PlayTheSound "chord.wav"
Case 1
PlayTheSound "tada.wav"
End Select
End Sub

然而，我收到一个错误

参数不是可选的

谁能帮我？

Answer 1

导致此错误的原因是，当函数需要 3 个参数时，您使用 2 个参数调用函数。如果你想这样称呼sndPlaySound32

sndPlaySound32 WhatSound, 0&    ' Finally, play the sound.

然后，您需要更改函数签名，以便每个引用的问题只有 2 个参数 - 如下所示：

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

所以完整的工作代码是：

Option Explicit
#If VBA7 Then
Public Declare PtrSafe Function sndPlaySound32 Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal lpszName As String, ByVal dwFlags As Long) As Long
#Else
Public Declare Function sndPlaySound32 Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal lpszName As String, ByVal dwFlags As Long) As Long
#End If
Sub PlayTheSound(ByVal WhatSound As String)
If Dir(WhatSound, vbNormal) = "" Then
' WhatSound is not a file. Get the file named by
' WhatSound from the WindowsMedia directory.
WhatSound = Environ("SystemRoot") & "Media" & 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.
Exit Sub
End If
Else
' WhatSound is a file. Use it.
End If
sndPlaySound32 WhatSound, 0&    ' Finally, play the sound.
End Sub
Sub PlayIt()
Select Case Range("A1").Value
Case "Hello"
PlayTheSound "chimes.wav"
'PlayTheSound "chime.wav"
'Case "World"
'PlayTheSound "chord.wav"
'Case 1
'PlayTheSound "tada.wav"
End Select
End Sub