excel VBA 代码不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19222217/
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
excel VBA code not working
提问by udaya726
I have a excel sheet with a VBA code as follows
我有一个带有 VBA 代码的 Excel 表，如下所示
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 And Target.Column = 5 Then
Dim iRet As Integer
If Not IsEmpty(Range("AZ1").Value) Then
iRet = MsgBox("You have already selectd a Size Template", _
vbOKOnly, "Select Size Template")
Exit Sub
End If
Dim arr As Variant
arr = Split(Target, ",")
Range("R14:AZ14").ClearContents
Range("R14:AZ14").NumberFormat = "@"
Range("R14", Cells(14, UBound(arr) + 18)) = WorksheetFunction.Transpose( _
WorksheetFunction.Transpose(arr))
Range("AZ1").Value2 = Target
End If
End Sub
I saved the excel file as .xlsm(macro enabled excel file) and opend in another.This code works really fine in my machine. BUt not in any other machine. I enabled the marco and allowed the Trust acess to the VBA ojbect model. CAn anybody figure out the issue here . Excel versions are also same in both mahcines
我将 excel 文件另存为 .xlsm（启用宏的 excel 文件）并在另一个中打开。这段代码在我的机器上工作得很好。但不是在任何其他机器上。我启用了 marco 并允许 Trust 访问 VBA ojbect 模型。任何人都可以在这里找出问题。两种机器中的 Excel 版本也相同
回答by Kazimierz Jawor
I want to elaborate a bit the comment of mine which was correct suggestion.
我想详细说明一下我的评论，这是正确的建议。
First,let me repeat that- you need to switch on events in this way
首先，让我重复一遍——你需要以这种方式开启事件
Application.EnableEvents = true
which you can be run once in Immediate Window in VBA/IDE Editor. Now we know that was it!
您可以在 VBA/IDE 编辑器的立即窗口中运行一次。现在我们知道就是这样！
Second,if you decided to switch on events using any other subroutine (or event, which is however strange) please keep in mind that there could be some other subroutines, functions or add-ins which require events to be switched off. As long as you are not sure why events are not working you should keep them not working right after your macro doesn't need them any more. Therefore, my suggestion is to switch events off each time you will close your file. Therefore you could add this event to ThisWorkbook module:
其次，如果您决定使用任何其他子程序（或事件，无论多么奇怪）打开事件，请记住可能有一些其他子程序、函数或插件需要关闭事件。只要您不确定事件为何不起作用，您就应该在您的宏不再需要它们之后让它们立即停止工作。因此，我的建议是每次关闭文件时都关闭事件。因此，您可以将此事件添加到ThisWorkbook module：
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.EnableEvents = False
End Sub
Extra tip. The best option would be to read events status at the beginning, keep this information until you close your file. You could do it in the following steps:
额外的提示。最好的选择是在开始时读取事件状态，保留此信息直到您关闭文件。您可以按照以下步骤进行操作：
A)declare public variables in your file
A)在文件中声明公共变量
Public boEventsStatus as Boolean
B)read status when opening file (you need to figure it out where put this line of code)
B）打开文件时读取状态（你需要弄清楚这行代码放在哪里）
boEventsStatus = Application.EnableEvents
C)switch on events as described at the beginning
C)如开头所述开启事件
D)use this BeforeCloseevent:
D)使用这个BeforeClose事件：
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.EnableEvents = boEventsStatus
End Sub
