创建Excel工作表时能否指定CodeName？VBA宏代码报错求助

你直接用NewWs.CodeName = NameVar赋值没用，是因为CodeName是只读属性，不能通过工作表对象直接修改，必须通过VBA项目组件或者API来实现。

方法一：通过VBProject组件修改（推荐） #

这是最直接稳定的方式，核心是修改工作表对应的VB组件名称：

If sheetExists Then
    MsgBox "The sheet exists."
Else
    Set NewWs = Worksheets.Add(After:=Sheets(Sheets.Count))
    NewWs.Name = SheetName
    
    ' 关键：修改VB组件的名称来设置CodeName
    ThisWorkbook.VBProject.VBComponents(NewWs.CodeName).Name = NameVar
End If

注意事项： #

• 必须先启用「信任对VBA项目对象模型的访问」：打开Excel的「文件>选项>信任中心>信任中心设置>宏设置」，勾选该选项，否则代码会报错权限不足。
• 可以添加错误处理，避免因权限或其他问题导致宏中断。

方法二：用Windows API修改（无需启用VBProject权限） #

如果不想开启VBA项目权限，可以通过API模拟VB编辑器的操作来修改CodeName，不过兼容性略差：

' 声明API函数（64位Excel用PtrSafe，32位去掉）
Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
    (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" _
    (ByVal hWnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr

Private Const WM_SETTEXT = &HC
Private Const WM_KEYDOWN = &H10

' 自定义修改CodeName的函数
Sub SetSheetCodeName(ByVal ws As Worksheet, ByVal newCodeName As String)
    Dim hWndVBEditor As LongPtr
    Dim hWndProperties As LongPtr
    Dim hWndCodeNameInput As LongPtr
    
    ' 确保VB编辑器可见
    If Not VBE.MainWindow.Visible Then VBE.MainWindow.Visible = True
    
    ' 获取VB编辑器相关窗口句柄
    hWndVBEditor = FindWindowEx(0&, 0&, "wndclass_desked_gsk", vbNullString)
    hWndProperties = FindWindowEx(hWndVBEditor, 0&, "PROPSHEET", vbNullString)
    hWndCodeNameInput = FindWindowEx(hWndProperties, 0&, "Edit", vbNullString)
    
    ' 选中目标工作表对应的VB组件
    VBE.ActiveVBComponent = ThisWorkbook.VBComponents(ws.CodeName)
    
    ' 输入新的CodeName并回车确认
    SendMessage hWndCodeNameInput, WM_SETTEXT, 0&, ByVal newCodeName
    SendMessage hWndCodeNameInput, WM_KEYDOWN, vbKeyReturn, 0&
End Sub

' 调用示例
If sheetExists Then
    MsgBox "The sheet exists."
Else
    Set NewWs = Worksheets.Add(After:=Sheets(Sheets.Count))
    NewWs.Name = SheetName
    ' 调用自定义函数设置CodeName
    SetSheetCodeName NewWs, NameVar
End If

注意事项： #

• 该方法依赖VB编辑器的窗口结构，不同Excel版本可能出现兼容性问题。
• 不需要开启VBA项目对象模型权限，但VB编辑器必须能正常启动。

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