vba 如果宏已经运行或特定单元格中有值，如何停止宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3133356/
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
How to stop a Macro either if it has been run already or if a there is a value in a particular cell
提问by Adam
I'm using Auto_Open on a Excel Template (.xlt) to run a macro which imports some data to a sperate sheet, adds some forula and then creates a number of pivot tables and charts. The result of which is a finalised report which can then be saved as .xls.
我在 Excel 模板 (.xlt) 上使用 Auto_Open 来运行一个宏，该宏将一些数据导入到一个单独的工作表中，添加一些公式，然后创建一些数据透视表和图表。其结果是最终报告，然后可以将其保存为 .xls。
I have added the following IF statement to the top of the Auto_Open sub to check cell A2 if it contains "Service Activity Report" (this is a merged cell from A2:N2) and if it does to Exit the Macro. This is so the Macro will not run for second time after the report has been generated.
我已将以下 IF 语句添加到 Auto_Open 子的顶部，以检查单元格 A2 是否包含“服务活动报告”（这是来自 A2:N2 的合并单元格）以及是否包含退出宏。这样宏就不会在生成报告后第二次运行。
If ActiveSheet.Range("A2").Text = "Service Activity Report" Then
Exit Sub
Else
Two Questions:
两个问题：
Is this the best way to stop the macro running for a second time and overwriting the finalised report?
这是第二次停止运行宏并覆盖最终报告的最佳方法吗？
The Macro only works in Excel 2007 due to the nature of the charts yet the code above is being skipped over in Excel 2003
由于图表的性质，宏只能在 Excel 2007 中工作，但上面的代码在 Excel 2003 中被跳过
Any ideas?
有任何想法吗？
回答by MSD
No expert here by any means, but...
无论如何，这里没有专家，但是......
If you're not calling another Sub after the "Auto_Open" Sub, then I would use "End" instead of "Exit Sub". This will ensure that that no further code will execute if "ActiveSheet.Range("A2").Text = "Service Activity Report" = True.
Survey says "Auto_Open" was superceeded by "Workbook_Open" in Excel 2003, so it might not be supported anymore...
如果您没有在“Auto_Open”Sub 之后调用另一个 Sub，那么我将使用“End”而不是“Exit Sub”。这将确保如果 "ActiveSheet.Range("A2").Text = "Service Activity Report" = True，则不会执行进一步的代码。
调查显示 Excel 2003 中的“Auto_Open”已被“Workbook_Open”取代，因此它可能不再受支持...
Cheers!
干杯!
回答by floatingLomas
You might also want to consider being more specific than 'ActiveSheet,' just in case the workbook was saved with a different sheet active. You could give A2 a name - say 'ReportTitle' - which means it doesn't matter what sheet is active:
您可能还想考虑比“ActiveSheet”更具体，以防万一工作簿保存在不同的活动工作表中。你可以给 A2 一个名字——比如“ReportTitle”——这意味着哪个工作表处于活动状态并不重要：
If Range("ReportTitle").Text = "Service Activity Report" Then
Exit Sub
Else
' Code to do whatever you are doing...
End if
And since you actually want to do nothing if your test is true and something if it is false, you might want to consider:
由于如果测试为真，您实际上什么都不做，如果测试为假，您实际上不想做任何事情，因此您可能需要考虑：
If Not (Range("ReportTitle").Text = "Service Activity Report") Then
' Code to do whatever you are doing...
End if
回答by Ben McCormack
- I'm not exactly sure what you are checking. Does
ActiveSheetrefer to the Excel template or the finalized report? If this cell is created for the finalized report and is simply checking to see if it exists, thus indicating the final report has been completed, I wouldn't necessarily change it. It may not be the "best" way to accomplish that task, but when working with VBA and Office, it can often be difficult to find a "best" way to accomplish tricky things. - I've never used
Auto_Openbefore in Excel 2007, so I can't say whether or not it is available in Excel 2003. However, I have usedWorkbook_Open()before to run code when a Workbook starts up. You should be able to see that method if you navigate toThisWorkbookin the Visual Basic explorer window. On the right window, change (General)to be Workbookand you should now see theWorkbook_Open()method.
- 我不确定你在检查什么。是否
ActiveSheet指的是Excel模板或定型的报告？如果这个单元格是为最终报告创建的，只是检查它是否存在，从而表明最终报告已经完成，我不一定要更改它。这可能不是完成该任务的“最佳”方式，但在使用 VBA 和 Office 时，通常很难找到完成棘手事情的“最佳”方式。 - 我之前没有
Auto_Open在Excel 2007中使用过，所以我不能说它是否在Excel 2003中可用。但是，我之前使用Workbook_Open()过在Workbook启动时运行代码。如果您ThisWorkbook在 Visual Basic 资源管理器窗口中导航到，您应该能够看到该方法。在右侧窗口中，将(General)更改为Workbook，您现在应该会看到该Workbook_Open()方法。
