vba - 使用按钮根据值播放Excel中的声音

Question

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

我使用的是Excel(64位)版本。我已经在检查此页面:
How to play a wave file or sound file using 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 Windows\Media 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

但是，我得到一个错误

Argument not optional

有人可以帮我吗？

Answer 1

引起该错误的原因是，当函数期望值为3时，您正在使用2个参数调用该函数。如果要这样调用sndPlaySound32，

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

然后，您需要将函数签名更改为 referenced每个问题只有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 Windows\Media 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