从一个独特的 VBA Sub 一次运行多个 Sub
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21254885/
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
Running at once multiple Subs from an unique VBA Sub
提问by Lorenzo Rigamonti
I have three different Subs available in a VBA module and wanted to call those series of Subs from an unique Sub activated through a VBA button.
我在 VBA 模块中有三个不同的 Subs 可用，并且想从通过 VBA 按钮激活的唯一 Sub 调用这些系列的 Subs。
Below the code running:
在运行的代码下面：
Sub Updateworkbook()
Call Unprotectworkbook
Call CopyAndPaste
Call Protectworkbook
End Sub
After the first Sub Unprotectworkbook()is run the other Subare not called and executed. Why this happens?
在第一个Sub Unprotectworkbook()运行后，另一个Sub不会被调用和执行。为什么会发生这种情况？
Below the Unprotectworkbook()Sub code for your reference
在Unprotectworkbook()子代码下方供您参考
Sub Unprotectworkbook()
Dim myCount
Dim i
myCount = Application.Sheets.Count
Sheets(1).Select
For i = 1 To myCount
ActiveSheet.Unprotect "password"
If i = myCount Then
End
End If
ActiveSheet.Next.Select
Next i
End Sub
回答by Dmitry Pavliv
Modify your code as follows (change Endto Exit Sub):
修改您的代码如下（更改End为Exit Sub）：
Sub Unprotectworkbook()
Dim myCount
Dim i
myCount = Application.Sheets.Count
Sheets(1).Select
For i = 1 To myCount
ActiveSheet.Unprotect "password"
If i = myCount Then
Exit Sub
End If
ActiveSheet.Next.Select
Next i
End Sub
or you can simply change it to the next one:
或者您可以简单地将其更改为下一个：
Sub Unprotectworkbook()
Dim sh
For Each sh In Sheets
sh.Unprotect "password"
Next
End Sub
回答by Olle Sj?gren
It is very hard to answer your question without seeing the code in all three subs.
如果没有看到所有三个子程序中的代码，很难回答您的问题。
Some pointers though:
一些指针虽然：
You don't need to select each sheet in order to modify it - just use
Sheet(i).Unprotect "password"in the for loop instead.Also, since you have a for loop you don't need to code when it should end, if you have defined the
For i = 1 To myCountstatement correctly. In other words, remove theIf i = myCount Then Endpart.You could define the For loop like the following:
For i = 1 To Application.Sheets.Countto simplify your code, then you can remove themyCountvariable.You should always define your variables with a datatype in order to minimize errors, e.g use
Dim i As Integerinstead.Alwaysuse
Option Explicitat the top of each module, also to minimize confusion and errors caused by typos etc.
您不需要选择每个工作表来修改它 - 只需
Sheet(i).Unprotect "password"在 for 循环中使用即可。此外，由于您有一个 for 循环，因此如果您
For i = 1 To myCount正确定义了语句，则不需要在它应该结束的时间进行编码。换句话说，移除If i = myCount Then End零件。您可以像下面这样定义 For 循环：
For i = 1 To Application.Sheets.Count为了简化您的代码，然后您可以删除该myCount变量。您应该始终使用数据类型定义变量，以尽量减少错误，例如
Dim i As Integer改为使用。始终
Option Explicit在每个模块的顶部使用，也可以最大程度地减少因拼写错误等引起的混乱和错误。
I strongly advise you to run through a couple of tutorials on VBA, there are lots around. The following is just the first one up when searching, I haven't tried it: Excel VBA Basic Tutorial 1
我强烈建议您阅读一些有关 VBA 的教程，其中有很多。以下只是搜索时第一个上来的，没试过：Excel VBA Basic Tutorial 1
回答by dave
If this helps, I recommend making another set of 3 subs to test blank items first. Otherwise use one of the other answers above.
如果这有帮助，我建议先制作另一组 3 个 subs 来测试空白项目。否则使用上面的其他答案之一。
Sub msgTEST0() 'Call msgTEST0
Call msgTEST1
Call msgTEST2
Call msgTEST3
End Sub
Sub msgTEST1()
MsgBox "MSG1" & Space(10), vbQuestion
End Sub
Sub msgTEST2()
MsgBox "MSG2" & Space(10), vbQuestion
End Sub
Sub msgTEST3()
MsgBox "MSG3" & Space(10), vbQuestion
End Sub
