.CodeName sometimes empty

Add-in Express™ Support Service
That's what is more important than anything else

.CodeName sometimes empty
 
Albert Siagian


Guest


Using Windows 7 64-bit, Office 2010 and Add-in Express 7.4.4067

I use Advanced Excel Taskpane, has 1 button that do these 2 lines:
MsgBox(TryCast(AddinModule.CurrentInstance.ExcelApp.ActiveWorkbook.ActiveSheet, Excel.Worksheet).Name)
MsgBox(TryCast(AddinModule.CurrentInstance.ExcelApp.ActiveWorkbook.ActiveSheet, Excel.Worksheet).CodeName)

The ".Name" always works, showing active sheet name. However, ".CodeName" sometimes shows blank name for some sheets and shows correct name for other sheets. For new, not saved workbook, ".CodeName" always shows empty.

When I go to Developer, Visual Basic, the CodeName is obviously there, and change it to another name will correctly display .CodeName. So basically, when I do nothing to the workbook, .CodeName sometimes empty, sometimes correct.

Any idea ?

Regards.
Albert
Posted 14 Apr, 2014 20:55:45 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
Hello Albert,

I do the following:
- Start Excel (a new workbook is opened)
- ActiveSheet.CodeName returns an empty string
- Press Alt+F11 to open the VBA IDE
- switch back to Excel
- Now ActiveSheet.CodeName returns a non-empty string

I assume opening the IDE creates the VBA classes and modules accessible through the Excel object model.


Andrei Smolin
Add-in Express Team Leader
Posted 15 Apr, 2014 02:59:15 Top
Albert Siagian


Guest


Hello Andrei,

Thanks, I found same solution. Since I have to do it programmatically, I use following workaround to simulate Alt+F11:

AddinModule.CurrentInstance.ExcelApp.VBE.MainWindow.WindowState = vbext_WindowState.vbext_ws_Minimize
AddinModule.CurrentInstance.ExcelApp.VBE.MainWindow.Visible = True
AddinModule.CurrentInstance.ExcelApp.VBE.MainWindow.Visible = False

Not real solution, but is enough for me.

Regards.
Albert
Posted 15 Apr, 2014 03:14:01 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
Thank you for posting the workaround.


Andrei Smolin
Add-in Express Team Leader
Posted 15 Apr, 2014 04:05:45 Top