vba 将自定义错误消息添加到 excel 宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22095245/
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
Adding custom error message to excel macro
提问by OO_Learner
The macro below opens up an excel workbook and builds a chart on the provided data. I would like to add some error handling so that in the event an excel workbook named differently to the one specified (Employee_source_data) is provided then a message box is displayed e.g Please ensure spreadsheet name provided is "Employee_source_data". Any ideas how to accomplish this? Thanks in advance!
下面的宏打开一个 excel 工作簿并在提供的数据上构建一个图表。我想添加一些错误处理，以便在提供与指定名称不同的 Excel 工作簿 (Employee_source_data) 的情况下，会显示一个消息框，例如请确保提供的电子表格名称为“Employee_source_data”。任何想法如何实现这一点？提前致谢！
Sub GenerateEmployeeReport()
Workbooks.Open Filename:=ThisWorkbook.Path & "\Employee_source_data"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
Selection.AutoFill Destination:=Range("E2:E7"), Type:=xlFillDefault
Range("E2:E7").Select
Range("A1:A7,E1:E7").Select
Range("E1").Activate
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range( _
"'Sheet2'!$A:$A,'Sheet2'!$E:$E")
ActiveChart.ChartType = xl3DColumnClustered
End Sub
回答by Jimmy Smith
The Dir() function will come in handy.
Try something like
Dir() 函数会派上用场。
尝试类似的东西
if dir(ThisWorkbook.path & "Employee_source_data*") = "" then
msgbox "Please ensure spreadsheet name provided is Employee_source_data"
end if
Another alternative is to use the FileDialog control if you're in a situation where it'd be easier to have them select it. In my experience, no one sticks to the naming convention less it is enforced.
另一种选择是使用 FileDialog 控件，如果您处于让他们选择它更容易的情况。根据我的经验，除非强制执行，否则没有人会坚持命名约定。
回答by user2140261
Sub GenerateEmployeeReport()
Dim strWorkbookName As String
Dim Answer As String
Start:
strWorkbookName = InputBox("Enter Workbook", "Open Workbook")
On Error GoTo BadWorkbook
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & strWorkbookName
Range("E2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
Selection.AutoFill Destination:=Range("E2:E7"), Type:=xlFillDefault
Range("E2:E7").Select
Range("A1:A7,E1:E7").Select
Range("E1").Activate
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range( _
"'Sheet2'!$A:$A,'Sheet2'!$E:$E")
ActiveChart.ChartType = xl3DColumnClustered
Exit Sub
BadWorkbook:
Answer = MsgBox("Please ensure spreadsheet name provided is Employee_source_data", vbRetryCancel)
If Answer = 4 Then
GoTo Start
Else: Exit Sub
End If
End Sub
The goto command jumps the code to The line title WrongWorkbookand gives your user the chance to exit or continue with the msgbox rety cancel buttons. Then If they retry it restart the sub with goto start, other wise it exits
goto 命令将代码跳转到行标题，WrongWorkbook并让您的用户有机会退出或继续使用 msgbox rety 取消按钮。然后，如果他们重试，则使用 goto start 重新启动 sub，否则它将退出
