如果发现错误，用 VBA 播放声音
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14962631/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Play a sound with VBA if an error is found
提问by Siddharth Rout
I currently have this code:
我目前有这个代码：
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
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?
我试图得到它，以便如果 C 列中有错误，则会播放可听见的声音，但它不起作用，有什么想法吗？
回答by Siddharth Rout
You don't need API for this
您不需要为此使用 API
You can use Beepas well.
您也可以使用Beep。
Sub Sample()
Beep
End Sub
Example
例子
WAY 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
WAY 2
方式2
Alternative of above code
上述代码的替代方案
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
Way 3
方式三
If you want the to check Col C only if there is a manual change anywhere in Col C
如果您希望仅在 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
Way 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
Way 5
方式5
Variation of Way 4
方式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
FOLLOWUP (Post Comments)
跟进（发表评论）
The active cell will be in column b, so it should check one right in column d – Sam Cousins 1 min ago
活动单元格将在 b 列中，因此它应该检查 d 列中的一个 – Sam Cousins 1 分钟前
I guess you meant Col C and not Col D. You have to use Worksheet_SelectionChangefor this
我猜你是说 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
回答by Siddharth Rout
Just Paste the below code and run and see if it works...
只需粘贴以下代码并运行，看看它是否有效...
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 PlaySound As Boolean
If Target.Column = 3 Then
For Each Cell In Target
If WorksheetFunction.IsNA(Cell.Value) Then
PlaySound = True
End If
Next
If PlaySound Then
Call sndPlaySound32("C:\windows\media\chord.wav", 1)
End If
End If
End Sub
回答by Ripster
Change Cell.Value to Cell.Text
将 Cell.Value 更改为 Cell.Text
If there is a formula error the cells value will be something along the lines of Error 2042 but your if statement is looking for the text "#N/A"
如果存在公式错误，则单元格值将与错误 2042 类似，但您的 if 语句正在查找文本“#N/A”
I also suggest using only the used range instead of the entire column since this will decrease the time it takes to run.
我还建议仅使用已使用的范围而不是整个列，因为这将减少运行所需的时间。
You can also exit the for immediately if an error is found.
如果发现错误，您也可以立即退出 for。
Using Beep will not allow you to play any sound but it will make an audible sound and does not require an API call or the computer to have the specified audio file.
使用 Beep 将不允许您播放任何声音，但它会发出可听见的声音，并且不需要 API 调用或计算机具有指定的音频文件。
-EDIT- I just tested the code below and it appears to work correctly for me.
- 编辑 - 我刚刚测试了下面的代码，它似乎对我来说工作正常。
-EDIT2- Corrected code
-EDIT2- 更正的代码
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim CheckRange As Range
Set CheckRange = Range(Cells(1, 3), Cells(ActiveSheet.UsedRange.Rows.Count, 3))
For Each Cell In CheckRange
If Cell.Text = "#N/A" Then
Beep
Exit For
End If
Next
End Sub
-EDIT3- Working copy of your original post
-EDIT3- 原始帖子的工作副本
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(Cells(1, 3), Cells(ActiveSheet.UsedRange.Rows.Count, 3))
For Each Cell In CheckRange
If Cell.Text = "#N/A" Then
PlaySound = True
Exit For
End If
Next
If PlaySound Then
Call sndPlaySound32("C:\windows\media\chord.wav", 1)
End If
End Sub
回答by Doug Clarke
I think your If statement which checks the value of PlaySound should be inside your For loop. The way you have it written it will only make a noise if the last cell in CheckRange = "#N/A" because PlaySound will hold the last value assigned to it from the loop.
我认为检查 PlaySound 值的 If 语句应该在 For 循环中。您编写它的方式只会在 CheckRange 中的最后一个单元格 = "#N/A" 时发出噪音，因为 PlaySound 将保存循环中分配给它的最后一个值。
回答by Peter L.
Try to replace with If PlaySound = True Then
尝试替换为 If PlaySound = True Then
