VBA复制模块代码：如何通过工作表名称而非Sheet代号访问Excel Objects？

你希望在VBA代码中用自定义的工作表显示名称（例如"Akquise2024"和"Akquise2023"）代替工作表代号（如"Sheet1"、"Sheet10"）来访问对应的VB模块，但直接使用VBComponents("工作表显示名称")无法成功。

问题原因 #

ThisWorkbook.VBProject.VBComponents接受的参数是工作表的代码名称（即VBA编辑器工程窗口中显示的名称，比如Sheet1），而非Excel界面底部标签显示的自定义名称。

解决方案 #

先通过工作表显示名称获取对应的工作表对象，再利用工作表的CodeName属性来访问其VB组件。

修改后的代码 #

Sub CopyCodeBetweenModules()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim sourceModule As Object
    Dim targetModule As Object
    Dim sourceCode As String
    
    ' 通过工作表显示名称获取工作表对象
    Set sourceSheet = ThisWorkbook.Worksheets("Akquise2023")
    Set targetSheet = ThisWorkbook.Worksheets("Akquise2024")
    
    ' 利用工作表的CodeName属性访问对应的VB组件
    Set sourceModule = ThisWorkbook.VBProject.VBComponents(sourceSheet.CodeName)
    Set targetModule = ThisWorkbook.VBProject.VBComponents(targetSheet.CodeName)
    
    ' 获取源模块的全部代码
    sourceCode = sourceModule.CodeModule.Lines(1, sourceModule.CodeModule.CountOfLines)
    
    ' 将代码插入目标模块的末尾
    targetModule.CodeModule.InsertLines targetModule.CodeModule.CountOfLines + 1, sourceCode
    
    MsgBox "代码复制成功！"
End Sub

可选：添加错误处理 #

如果需要避免因工作表名称错误导致的运行时错误，可以加入错误判断：

Sub CopyCodeBetweenModules()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim sourceModule As Object
    Dim targetModule As Object
    Dim sourceCode As String
    
    ' 检查源工作表是否存在
    On Error Resume Next
    Set sourceSheet = ThisWorkbook.Worksheets("Akquise2023")
    If Err.Number <> 0 Then
        MsgBox "找不到名为「Akquise2023」的工作表"
        Exit Sub
    End If
    
    ' 检查目标工作表是否存在
    Set targetSheet = ThisWorkbook.Worksheets("Akquise2024")
    If Err.Number <> 0 Then
        MsgBox "找不到名为「Akquise2024」的工作表"
        Exit Sub
    End If
    On Error GoTo 0
    
    ' 利用工作表的CodeName属性访问对应的VB组件
    Set sourceModule = ThisWorkbook.VBProject.VBComponents(sourceSheet.CodeName)
    Set targetModule = ThisWorkbook.VBProject.VBComponents(targetSheet.CodeName)
    
    ' 获取源模块的全部代码
    sourceCode = sourceModule.CodeModule.Lines(1, sourceModule.CodeModule.CountOfLines)
    
    ' 将代码插入目标模块的末尾
    targetModule.CodeModule.InsertLines targetModule.CodeModule.CountOfLines + 1, sourceCode
    
    MsgBox "代码复制成功！"
End Sub

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