excel VBA代码不工作 [英] excel VBA code not working

问题描述

我有一个带有VBA代码的excel表格，如下所示：

  Private Sub Worksheet_Change（ByVal Target As Range）
如果Target.Row = 1和Target.Column = 5然后
 Dim iRet As Integer 
如果不是IsEmpty（范围（AZ1）。值）然后
 iRet = MsgBox（你已经选择了一个大小模板，_ 
 vbOKOnly，选择大小模板）
退出子
结束如果
 
 Dim arr As Variant 
 arr = Split（Target，，）
 Range（R14：AZ14）。ClearContents 
范围（R14：AZ14）。NumberFormat =@
范围（R14 ，Cells（14，UBound（arr）+ 18））= WorksheetFunction.Transpose（_ 
 WorksheetFunction.Transpose（arr））
 Range（AZ1）。Value2 = Target 
 End If 
 End Sub 
  

我将excel文件保存为.xlsm（启用了宏的excel文件）和在另一个在我的机器里很好不要在任何其他机器。我启用了marco，并允许信任访问VBA ojbect模型。有人在这里找出问题。 Excel的版本在mahcines中也是一样的

解决方案

我想详细说明一下我的评论是正确的建议。

首先，让我重复一遍，你需要以这种方式开启事件

  Application.EnableEvents = true 
  

您可以在VBA / IDE编辑器的立即窗口中运行一次。现在我们知道是这样的！

第二，如果您决定使用任何其他子例程（或事件，这是奇怪的事情） ）请记住，可能会有一些其他子例程，函数或加载项需要关闭事件。只要你不确定为什么事件不工作，你应该保持它们不能正常工作，你的宏不再需要它们了。因此，我的建议是每次关闭文件时切换事件。因此，您可以将此事件添加到 ThisWorkbook模块：

  Private Sub Workbook_BeforeClose （Cancel As Boolean）
 Application.EnableEvents = False 
 End Sub 
  

额外提示。最好的选择是在开始时读取事件状态，保留此信息，直到关闭文件。您可以按照以下步骤进行操作：

A）声明文件中的公共变量

 公共boEventsStatus为布尔
  

B ）在打开文件时读取状态（您需要确定哪里放这行代码）

  boEventsStatus = Application.EnableEvents 
  

C）开启事件，如开始时所述

D）使用此 BeforeClose 事件：

  Private Sub Workbook_BeforeClose（Cancel As Boolean）
 Application.EnableEvents = boEventsStatus 
 End Sub 
  / pre> 
I have a excel sheet with a VBA code as follows
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
解决方案
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!

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:
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
Public boEventsStatus as Boolean
B) read status when opening file (you need to figure it out where put this line of code)
boEventsStatus = Application.EnableEvents
C) switch on events as described at the beginning

D) use this BeforeClose event:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.EnableEvents = boEventsStatus
End Sub


                        
这篇关于excel VBA代码不工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！