使用 vba 更改工作表的代号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20818249/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Change codename of worksheet with vba
提问by DaveU
This code works fine with VBE window open, but raises an error Subscript out of rangeat this line: wB.VBProject.VBComponents(wS.CodeName).Properties("_CodeName").Value = "wsData"
when VBE window is closed. Maybe someone can show me what I'm missing here.
此代码在 VBE 窗口打开时工作正常，但Subscript out of range在此行引发错误：wB.VBProject.VBComponents(wS.CodeName).Properties("_CodeName").Value = "wsData"
当 VBE 窗口关闭时。也许有人可以告诉我我在这里缺少什么。
Sub newWorkbook()
Dim wB As Workbook
Dim wS As Worksheet
Dim Proj As Object'<=== added
Set wB = Workbooks.Add
Set wS = wB.Worksheets(1)
wS.Name = "Data"
Set Proj = wB.VBProject '<== added
'wB.VBProject.VBComponents(wS.CodeName).Properties("_CodeName").Value = "wsData" '<==Original line
Proj.VBComponents(wS.CodeName).Properties("_CodeName").Value = "wsData" '<== New
On Error Resume Next
Application.DisplayAlerts = False
wB.SaveAs "C:\dummy.xls", 56
Application.DisplayAlerts = True
If Not wB Is Nothing Then wB.Close False
Set wB = Nothing
End Sub
回答by Doug Glancy
I suspect it's a manifestation of the two.dot rule, or at least a distant relative. I was able to reproduce your problem. I solved it by declaring the whole chain of VBA objects, like this:
我怀疑这是two.dot 规则的体现，或者至少是远亲。我能够重现您的问题。我通过声明整个 VBA 对象链来解决它，如下所示：
Sub newWorkbook()
Dim wB As Workbook
Dim wS As Worksheet
Dim vbProj As VBIDE.VBProject
Dim vbComps As VBIDE.VBComponents
Dim vbComp As VBIDE.VBComponent
Dim vbProps As VBIDE.Properties
Dim CodeNameProp As VBIDE.Property
Set wB = Workbooks.Add
Set wS = wB.Worksheets(1)
wS.Name = "Data"
Set vbProj = wB.VBProject
Set vbComps = vbProj.VBComponents
Set vbComp = vbComps(wS.CodeName)
Set vbProps = vbComp.Properties
Set CodeNameProp = vbProps("_Codename")
CodeNameProp.Value = "wsData"
On Error Resume Next
Application.DisplayAlerts = False
wB.SaveAs "E:\docs\dummy.xls", 56
Application.DisplayAlerts = True
If Not wB Is Nothing Then wB.Close False
Set wB = Nothing
End Sub
I had to set a reference to VBA Extensibility to do this.
我必须设置对 VBA 扩展性的引用才能做到这一点。
Also note that the user has to have allowed access to VBA extensibility, by checking "Trust Access to the VBA Project Model" under Macro Security. You can test whether it's set like this:
另请注意，用户必须通过在宏安全性下选中“信任访问 VBA 项目模型”来允许访问 VBA 可扩展性。你可以测试一下是否是这样设置的：
Function ProgrammaticAccessAllowed() As Boolean
Dim vbTest As VBIDe.vbComponent
On Error Resume Next
Set vbTest = ThisWorkbook.VBProject.VBComponents(1)
If Err.Number = 0 Then
ProgrammaticAccessAllowed = True
End If
End Function
