excel - 如果发现错误，用 VBA 播放声音

Question

我目前有这个代码:

Option Explicit 

Private Declare Function sndPlaySound32 Lib "winmm.dll" _ 
Alias "sndPlaySoundA" (ByVal lpszSoundName _ 
As String, ByVal uFlags As Long) As Long 

Private Sub Worksheet_Change(ByVal Target As Range) 

Dim Cell            As Range 
Dim CheckRange      As Range 
Dim PlaySound       As Boolean 

Set CheckRange = Range("C:C") 
For Each Cell In CheckRange 
    If Cell.Value = "#N/A" Then 
        PlaySound = True 
    End If 
Next 
If PlaySound Then 
    Call sndPlaySound32("C:\windows\media\chord.wav", 1) 
End If      
End Sub  

我正在尝试获取它，以便如果 C 列中出现错误，则会播放可听声音，但它不起作用，有什么想法吗？

Answer 1

您不需要为此使用 API

您也可以使用Beep。

Sub Sample()
    Beep
End Sub

示例

方式1

如果工作表中的任何位置发生更改，此代码将运行

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell            As Range
    Dim CheckRange      As Range

    Set CheckRange = Range("C:C")

    For Each Cell In CheckRange
        If Cell.Text = "#N/A" Then
            Beep
            Exit For
        End If
    Next
End Sub

方式2

上述代码的替代

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell As Range

    For Each Cell In Columns(3)
        On Error Resume Next
        If CVErr(Cell) = CVErr(2042) Then
            Beep
            Exit For
        End If
        On Error GoTo 0
    Next
End Sub

方法3

如果您希望仅在 Col C 中的任何位置进行手动更改时才检查 Col C

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell As Range

    If Not Intersect(Target, Columns(3)) Is Nothing Then
        For Each Cell In Columns(3)
            On Error Resume Next
            If CVErr(Cell) = CVErr(2042) Then
                Beep
                Exit For
            End If
            On Error GoTo 0
        Next
    End If
End Sub

方法4

如果您希望检查特定单元格是否有手动更改

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell As Range

    If Not Intersect(Target, Columns(3)) Is Nothing Then
        On Error Resume Next
        If CVErr(Target) = CVErr(2042) Then
            Beep
            Exit Sub
        End If
        On Error GoTo 0
    End If
End Sub

方法5

方式 4 的变体

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell As Range

    If Not Intersect(Target, Columns(3)) Is Nothing Then
        If Target.Text = "#N/A" Then
            Beep
            Exit Sub
        End If
    End If
End Sub

跟进(发表评论)

The active cell will be in column b, so it should check one right in column d – Sam Cousins 1 min ago

我猜您指的是 Col C 而不是 Col D。为此，您必须使用 Worksheet_SelectionChange

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Columns(2)) Is Nothing Then
        If Target.Offset(, 1).Text = "#N/A" Then
            Beep
        End If
    End If
End Sub