基于单元格结果运行的 VBA 宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21022026/
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
VBA macro to run based on the outcome of a cell
提问by JamieB
I want to be able to run macros based on the outcome of the cell, for example.
例如，我希望能够根据单元格的结果运行宏。
If A1 is 1111100 then run X macro If its 1000000 then run this macro etc. I have had a look at "Case Select" but my lack of knowledge in this matter makes me thing that might not be what I want.
如果 A1 是 1111100 然后运行 X 宏如果它是 1000000 然后运行这个宏等等。我看过“案例选择”，但我在这方面缺乏知识使我的事情可能不是我想要的。
Any ideas? :/
有任何想法吗？：/
Thank you in advanced.
先谢谢了。
JB
JB
采纳答案by SeanC
you can combine the two types, and yes, a Case Selectis the easiest to read and maintain.
你可以结合这两种类型，是的，aCase Select是最容易阅读和维护的。
Here's example code that runs different routines depending on what is in A1:
下面是根据 A1 中的内容运行不同例程的示例代码：
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A1"), Target) Is Nothing Then
Application.EnableEvents = False
Select Case Target.Value
Case "A"
FunctionWhenA
Case 1
ThasIsAnotherFunction
End Select
Application.EnableEvents = True
End If
End Sub
note that I also disable/enable events so this isn't triggered every time a cell is changed
请注意，我还禁用/启用事件，因此每次更改单元格时都不会触发
回答by Robbert Koppenol
Yes would need to loop through them.
是的，需要遍历它们。
You could replace the "cas" subs in following with your implementation of VBA for that case.
对于这种情况，您可以用 VBA 的实现替换下面的“cas”子项。
Function Strange(myVal)
Select Case myVal
Case 1
Cas1
Case 2
Cas2
Case 3
Cas3
End Select
End Function
Sub Cas1()
MsgBox ("hi")
End Sub
Sub Cas2()
MsgBox ("ha")
End Sub
Sub Cas3()
MsgBox ("ho")
End Sub
Sub LoopThem()
Do While ActiveCell.Value <> ""
Strange (ActiveCell.Value)
ActiveCell.Offset(1, 0).Activate
Loop
End Sub
So cell A1 to A3 with values 1,2,3 would consecutively pop up msgboxes "hi" "ha" "ho".
因此，值为 1,2,3 的单元格 A1 到 A3 将连续弹出 msgboxes "hi" "ha" "ho"。
回答by JamieB
Fixed it.
修复。
Done this via using Intergers to stop when I have no further cells with data to stop the macro, and detect on many '1's are within the code and copy the data as neededs using "For" commands.
当我没有更多带有数据的单元格来停止宏时，通过使用 Intergers 停止来完成此操作，并检测代码中的许多“1”并根据需要使用“For”命令复制数据。
回答by Ahmad
There are two main types of Excel macros
Excel 宏有两种主要类型
1- Those that are written to perform actions, change data, etc.
1- 为执行操作、更改数据等而编写的那些。
2- And those that are written to perform calculations and return values to certain cells (used as customized formulas that are not built in to excel)
2- 以及那些为执行计算并将值返回到某些单元格而编写的（用作未内置于 excel 的自定义公式）
The first type can only be triggered to start execution by clicking a button on a form, invoking the Macros window within Excel and selecting the name of the macro to be run
第一种类型只能通过单击窗体上的按钮、调用 Excel 中的宏窗口并选择要运行的宏的名称来触发以开始执行
The second type can be run as soon as a value of a certain cell changes (that cell must be an input for the said macro function to work with, calculate and return a certain output), and thus the returned value will be stored on another cell.
第二种类型可以在某个单元格的值发生变化时立即运行（该单元格必须是所述宏函数的输入才能使用，计算并返回某个输出），因此返回的值将存储在另一个细胞。
In the second type, Excel will ignore any code that tries to modify the content of other cells, perform actions on the worksheet, workbook, or any other action that is not limited to the cell contanining the formula for the custom macro.
在第二种类型中，Excel 将忽略任何试图修改其他单元格内容的代码、对工作表、工作簿执行操作或不限于包含自定义宏的公式的单元格的任何其他操作。
If you intend to run a macro of the first type, and want it to be executed right after a certain value changes, then that is not possible.
如果您打算运行第一种类型的宏，并希望它在某个值更改后立即执行，那么这是不可能的。
If you want to write a macro of the second type, then that is possible but code will only be limited to a single cell only.
如果您想编写第二种类型的宏，那么这是可能的，但代码仅限于单个单元格。
