如果发现错误，请使用VBA播放声音 [英] Play a sound with VBA if an error is found

问题描述

我目前有以下代码:

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列中有错误，则会播放可听见的声音，但是它不起作用，有什么主意吗?

I am trying to get it so that if there is an error in column C, an audible sound is played, however it does not work, any ideas?

推荐答案

您不需要为此使用API​​

You don't need API for this

您也可以使用 Beep .

Sub Sample()
    Beep
End Sub

示例

方法1

如果工作表中的任何地方有更改，此代码将运行

This code will run if there is change anywhere in the sheet

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

If you want the to check Col C only if there is a manual change anywhere in 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

如果您要检查某个特定单元格是否有手动更改，请

If you want the to check a particular cell if there is a manual change in that cell

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

关注(帖子评论)

活动单元格将在b列中，因此它应在d列中检查一个权限– 1分钟前Sam Cousins

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

我猜你是说Col C，而不是ColD.为此，您必须使用 Worksheet_SelectionChange

I guess you meant Col C and not Col D. You have to use Worksheet_SelectionChange for this

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

这篇关于如果发现错误，请使用VBA播放声音的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！