vba - 变更表代码名称

由于某些原因，以下代码仅在vbe中运行，在事件处理代码中运行时或通过宏列表运行时，代号仍为sheet1。

请有人可以调查吗？

Sub changesheetcodename()

Dim ws,tsst as worksheet

For Each Ws In ActiveWorkbook.Worksheets
    If Ws.Name <> "Instructions"  then ws.delete
Next Ws

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Sheet1"

set tsst = Sheets("Sheet1")
With tsst
    .Parent.VBProject.VBComponents(.CodeName) _
    .Properties("_CodeName") = "test"
End With

End Sub

最佳答案

Sub change_code_name()
  Dim wbk As Object, sheet As Object
  ActiveWorkbook.VBProject.Name = "VBAProject"
  Set wbk = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.CodeName)
  wbk.Name = "wbk_code_name"
  Set sheet = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.Sheets(1).CodeName)
  sheet.Name = "sheet_code_name"
End Sub

此外，还需要访问VBAProject:请参阅“宏设置”并设置"Trust access to the VBA object model"。要以编程方式更改设置，请查看here。