VBA中解决同名公有宏歧义并通过标记触发私有子过程

问题拆解 #

1. 歧义报错原因：当标准模块与工作表模块存在同名的PublicMacro时，VBA无法识别目标宏的作用域，触发Ambiguous Name错误。
2. 需求冲突：原有Worksheet_Change事件会在数据录入后自动锁定单元格，需要改为仅在指定公有宏执行后才触发锁定逻辑。

修正后的完整代码实现 #

1. 标准模块（如Module1） #

将全局变量和公有宏放在标准模块，明确作用域避免冲突：

' 全局标记变量，仅在标准模块定义
Public flagMacroExecuted As Boolean

' 公有宏：仅执行此宏后才会触发锁定
Sub PublicMacro()
    ' 先解锁工作表（保护状态下无法修改单元格锁定属性）
    ActiveSheet.Unprotect Password:="123"
    
    ' 设置标记，告知后续操作是宏触发的
    flagMacroExecuted = True
    
    ' 明确调用目标工作表的私有过程，避免歧义
    Sheet1.PrivateMacro Selection
    
    ' 重置标记，防止后续误触发
    flagMacroExecuted = False
End Sub

2. 目标工作表模块（如Sheet1） #

修改变更事件并存放私有过程，控制锁定逻辑的触发条件：

' 私有子过程：负责锁定指定列并保护工作表
Private Sub PrivateMacro(ByVal Target As Range)
    Dim columnRange As Range
    Set columnRange = Target.EntireColumn
    columnRange.Locked = True
    
    ' 启用UserInterfaceOnly，后续VBA操作无需反复解锁
    Me.Protect Password:="123", UserInterfaceOnly:=True
End Sub

' 修改原有变更事件：仅当公有宏触发时才执行锁定
Private Sub Worksheet_Change(ByVal Target As Range)
    If flagMacroExecuted Then
        Me.Unprotect Password:="123"
        Target.Locked = True
        Me.Protect Password:="123", UserInterfaceOnly:=True
    End If
End Sub

核心优化点 #

• 解决歧义：通过标准模块存放公有宏，调用私有过程时指定工作表对象（Sheet1.PrivateMacro），彻底消除同名宏的作用域冲突。
• 标记控制：利用全局变量flagMacroExecuted作为开关，仅当公有宏执行时才允许锁定逻辑触发，屏蔽Worksheet_Change的自动锁定。
• 保护优化：UserInterfaceOnly:=True参数让工作表在保持用户界面保护的同时，允许VBA直接修改单元格属性，无需反复执行解锁/保护操作。

内容的提问来源于stack exchange，提问作者cupcake