This code works fine with VBE window open, but raises an error Subscript out of range at 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.
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