时间戳

首页 > 解决方案 > 调用模块到工作表时未定义子或函数

excel - 调用模块到工作表时未定义子或函数

问题描述

我有一个 Worksheet_Change 脚本，我想将其解析为模块。注释掉的部分已移至带有子程序“Alluma_Change”的模块。调用时出现“未定义子或函数”错误。我需要将它分发给我的团队，所以我认为将代码分解为模块将允许它们在有更新时导入，但也许这是不可能的。谢谢！

Option Explicit

Private Sub Worksheet_Change(ByVal target As Range)

    If Not Intersect(target, Range("F1")) Is Nothing Then

        If Range("F1").Value = "Both PT and Trad" Then
            Rows("37:72").EntireRow.Hidden = False
        End If

        If Range("F1").Value = "Pass-Through Only" Then
            Rows("37:53").EntireRow.Hidden = False
            Rows("54:72").EntireRow.Hidden = True
        End If

        If Range("F1").Value = "Traditional Only" Then
            Rows("37:53").EntireRow.Hidden = True
            Rows("54:72").EntireRow.Hidden = False
        End If

    End If

'Dim mail_margin As Range, specialty_margin As Range, x() As String
'Set mail_margin = Range("D15"): Set specialty_margin = Range("D16")
'
'    If Not Intersect(target, Range("H3")) Is Nothing Then
'
'        If Range("H3").Value = "Alluma" Then
'            mail_margin.Formula = mail_margin.Formula & "*0"
'            specialty_margin.Formula = specialty_margin.Formula & "*0"
'        Else
'            x() = Split(Range("d15").Formula, "*")
'            mail_margin.Formula = x(0) & "*" & x(1)
'            x() = Split(Range("d16").Formula, "*")
'            specialty_margin.Formula = x(0) & "*" & x(1)
'        End If
'
'   End If

Call Alluma_Change

End Sub

参考模块：

Option Explicit

Private Sub Alluma_Change()

Dim mail_margin As Range, specialty_margin As Range, x() As String
Set mail_margin = Range("D15"): Set specialty_margin = Range("D16")

    If Not Intersect(target, Range("H3")) Is Nothing Then

        If Range("H3").Value = "Alluma" Then
            mail_margin.Formula = mail_margin.Formula & "*0"
            specialty_margin.Formula = specialty_margin.Formula & "*0"
        Else
            x() = Split(Range("d15").Formula, "*")
            mail_margin.Formula = x(0) & "*" & x(1)
            x() = Split(Range("d16").Formula, "*")
            specialty_margin.Formula = x(0) & "*" & x(1)
        End If

End Sub

标签： excelvba

解决方案

你应该：

  1. 移动Alluma_Change()到标准模块
  2. 做Public而不是Private
  3. 放在Application.EnableEvents=False事件代码的开头
  4. 放在Application.EnableEvents=True事件代码的底部
  5. 修复任何未定义的变量Alluma_Change()

推荐阅读