vba 打开excel文件时如何将计算模式设置为手动？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17106544/
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 set calculation mode to manual when opening an excel file?
提问by user1187571
My excel file contains a lot of formulas and I therefore want it to set calculation mode to manual as soon as it is opened. Otherwise calculation starts automatically and I have to wait for hours. I found this page:
我的 excel 文件包含很多公式，因此我希望它在打开后立即将计算模式设置为手动。否则计算会自动开始，我必须等待几个小时。我找到了这个页面：
http://excel.tips.net/T001988_Forcing_Manual_Calculation_For_a_Workbook.html
http://excel.tips.net/T001988_Forcing_Manual_Calculation_For_a_Workbook.html
which should be able to do the trick. However it's not working for my excel-file. It states that in the VBA-code, in the section "ThisWorkbook", the following code should be entered:
这应该能够做到这一点。但是它不适用于我的 excel 文件。它指出，在 VBA 代码中，在“ThisWorkbook”部分，应输入以下代码：
Private Sub Workbook_Open()
Application.Calculation = xlManual
Application.CalculateBeforeSave = False
End Sub
As pointed it out, it doesn't work in my case. Does someone have an alternative solution?
正如所指出的，它在我的情况下不起作用。有人有替代解决方案吗？
回答by timonippon
The best way around this would be to create an Excel called 'launcher.xlsm' in the same folder as the file you wish to open. In the 'launcher' file put the following code in the 'Workbook' object, but set the constant TargetWBNameto be the name of the file you wish to open.
解决此问题的最佳方法是在与您要打开的文件相同的文件夹中创建一个名为“launcher.xlsm”的 Excel。在“启动器”文件中，将以下代码放在“工作簿”对象中，但将常量设置为TargetWBName要打开的文件的名称。
Private Const TargetWBName As String = "myworkbook.xlsx"
'// First, a function to tell us if the workbook is already open...
Function WorkbookOpen(WorkBookName As String) As Boolean
' returns TRUE if the workbook is open
WorkbookOpen = False
On Error GoTo WorkBookNotOpen
If Len(Application.Workbooks(WorkBookName).Name) > 0 Then
WorkbookOpen = True
Exit Function
End If
WorkBookNotOpen:
End Function
Private Sub Workbook_Open()
'Check if our target workbook is open
If WorkbookOpen(TargetWBName) = False Then
'set calculation to manual
Application.Calculation = xlCalculationManual
Workbooks.Open ThisWorkbook.Path & "\" & TargetWBName
DoEvents
Me.Close False
End If
End Sub
Set the constant 'TargetWBName' to be the name of the workbook that you wish to open.
This code will simply switch calculation to manual, then open the file. The launcher file will then automatically close itself.
*NOTE: If you do not wish to be prompted to 'Enable Content' every time you open this file (depending on your security settings) you should temporarily remove the 'me.close' to prevent it from closing itself, save the file and set it to be trusted, and then re-enable the 'me.close' call before saving again. Alternatively, you could just set the False to Trueafter Me.Close
将常量“TargetWBName”设置为要打开的工作簿的名称。此代码将简单地将计算切换为手动，然后打开文件。然后启动器文件将自动关闭。*注意：如果您不希望每次打开此文件时都提示“启用内容”（取决于您的安全设置），您应该暂时删除“me.close”以防止其自行关闭，保存文件并将其设置为受信任，然后在再次保存之前重新启用“me.close”调用。或者，您可以将 False 设置为 TrueafterMe.Close
