如何基于用户权限优雅实现Excel VBA模块代码执行限制？

一、封装通用权限验证函数（最易落地） #

把重复的权限判断逻辑抽成全局函数，所有受限宏只需调用该函数，避免代码冗余，同时权限规则集中维护。

实现代码 #

' 放在公共标准模块（如Module_Common）
Public Function CheckUserPermission(requiredLevel As String) As Boolean
    ' 可将用户列表移至隐藏工作表/名称管理器，方便无需改代码即可更新
    Dim adminUsers As Variant, level1Users As Variant
    adminUsers = Array("admin", "myname")
    level1Users = Array("userA", "userB")
    
    Dim currentUser As String
    currentUser = Environ("Username") ' 优先用Windows登录名，比Application.UserName更可靠
    
    Select Case requiredLevel
        Case "admin"
            CheckUserPermission = Not IsError(Application.Match(currentUser, adminUsers, 0))
        Case "level1"
            ' admin自动继承level1权限
            CheckUserPermission = Not IsError(Application.Match(currentUser, level1Users, 0)) Or _
                                  Not IsError(Application.Match(currentUser, adminUsers, 0))
        Case Else
            CheckUserPermission = True ' 默认开放给所有用户
    End Select
    
    If Not CheckUserPermission Then
        MsgBox "用户 " & currentUser & vbCrLf & "无权限执行此操作", vbExclamation, "权限不足"
    End If
End Function

受限宏调用示例 #

Sub AdminOnlyBackup()
    If Not CheckUserPermission("admin") Then Exit Sub
    
    ' 这里写admin专属的备份逻辑
    MsgBox "正在执行管理员级备份..."
End Sub

Sub Level1DataEdit()
    If Not CheckUserPermission("level1") Then Exit Sub
    
    ' 这里写level1权限的数据编辑逻辑
    MsgBox "正在执行Level1级数据编辑..."
End Sub

二、类模块统一管理权限与宏映射（适合复杂场景） #

用类封装权限规则与对应宏的关联，结构化管理多按钮、多权限的场景，扩展和维护更清晰。

1. 创建类模块clsPermission #

Private m_requiredLevel As String
Private m_targetMacro As String

Public Property Let RequiredLevel(level As String)
    m_requiredLevel = level
End Property

Public Property Let TargetMacro(macroName As String)
    m_targetMacro = macroName
End Property

Public Sub Execute()
    If CheckUserPermission(m_requiredLevel) Then
        Application.Run m_targetMacro
    End If
End Sub

' 内嵌权限验证逻辑（或复用全局函数）
Private Function CheckUserPermission(requiredLevel As String) As Boolean
    ' 同全局函数的验证逻辑，略
End Function

2. 初始化权限映射 #

Public permissionCollection As Collection

Sub InitPermissions()
    Set permissionCollection = New Collection
    
    ' 绑定admin按钮与对应宏
    Dim adminMacro As New clsPermission
    adminMacro.RequiredLevel = "admin"
    adminMacro.TargetMacro = "AdminOnlyBackup"
    permissionCollection.Add adminMacro, "AdminBackupBtn"
    
    ' 绑定level1按钮与对应宏
    Dim level1Macro As New clsPermission
    level1Macro.RequiredLevel = "level1"
    level1Macro.TargetMacro = "Level1DataEdit"
    permissionCollection.Add level1Macro, "Level1EditBtn"
End Sub

3. 按钮点击事件调用 #

Sub AdminBackupBtn_Click()
    permissionCollection("AdminBackupBtn").Execute
End Sub

Sub Level1EditBtn_Click()
    permissionCollection("Level1EditBtn").Execute
End Sub

三、隐藏受限模块+统一入口（提升安全性） #

将受限代码放在隐藏模块中，仅通过带权限验证的入口宏调用，避免普通用户查看或修改受限逻辑。

实现步骤 #

1. 新建模块Module_Restricted，右键模块→属性→将「可见性」设为「隐藏」
2. 在隐藏模块中编写受限代码：

Public Sub AdminCleanup()
    ' 管理员专属清理逻辑
End Sub

Public Sub Level1ReportGenerate()
    ' Level1专属报表生成逻辑
End Sub

3. 公共模块写统一入口：

Sub RunRestrictedOperation(opName As String, requiredLevel As String)
    If Not CheckUserPermission(requiredLevel) Then Exit Sub
    Application.Run "Module_Restricted." & opName
End Sub

4. 按钮绑定宏：

Sub AdminCleanupBtn_Click()
    RunRestrictedOperation "AdminCleanup", "admin"
End Sub

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