Blog: Working with Excel Workbooks and Worksheets

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

Blog: Working with Excel Workbooks and Worksheets
"Get By Code Name" does not work. 
Leon Lai Kan




Posts: 200
 Joined: 2018-12-20
Hi,

I am studying this blog download:
Working with Excel workbooks and worksheets: VB.NET examples
ADX

In particular, I am studying the section entitled:
Get worksheet name


Here are the Original Codes: 
Private Sub btnCodeName_OnClick(sender As Object,control As IRibbonControl, _
pressed As Boolean) Handles btnCodeName.OnClick

   Dim text As String = GetWorksheetCodeName(ExcelApp.ActiveSheet)
End Sub

Private Function GetWorksheetCodeName(sheet As Excel.Worksheet) As String
   Return sheet.CodeName
End Function


When I click the Ribbon Button, nothing happens.

So, I modified the codes as follows:
1. I added a MsgBox
2. I modified the codes so as to be compatible with "Option Strict On"


Here are my amended codes: 
Private Sub btnCodeName_OnClick(ByVal sender As Object, _
     ByVal control As IRibbonControl, ByVal pressed As Boolean) _ 
     Handles btnCodeName.OnClick

  'Dim text As String = GetWorksheetCodeName(ExcelApp.ActiveSheet)            '// Original
   Dim text As String = CStr(GetWorksheetCodeName(TryCast(ExcelApp.ActiveSheet, Excel.Worksheet)))

    MsgBox(text)                               '// missing in original

End Sub

Private Function GetWorksheetCodeName(ByVal sheet As Excel.Worksheet) As String
   Return sheet.CodeName
End Function



At Runtime

When I run the program (debug), a MsgBox now pops up, but it is empty.
The CodeName does not display in the MsgBox.


What could be the problem?

Thanks
Leon
----

PS:
Both CodeName and Name are Strings.
If I replace CodeName by Name, the Name of the worksheet correctly displays in the MsgBox. 
Return sheet.Name

But if I use CodeName, the CodeName does not display in the MsgBox. 
Return sheet.CodeName


.
Posted 13 Mar, 2019 05:48:52 Top
Andrei Smolin


Add-in Express team


Posts: 17227
 Joined: 2006-05-11
Hello Leon,

I suggest that you set a breakpoint on the codeline below and check if it is called and if it returns a non-empty string.

Return sheet.CodeName

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 13 Mar, 2019 07:19:55 Top
Leon Lai Kan




Posts: 200
 Joined: 2018-12-20
Hi, Andrei

Thanks for your reply.

I did as you explained.

I set a breakpoint on this line: 
' Return sheet.CodeName

I see it is highlighted in RED.

Then I do: Step Into
The line is highlighted YELLOW. Does this mean it is called?

Press Continue:
The line is highlighted RED again, and a MsgBox pops up.
But it is empty. It just has a Title and an OK button.


-----

I did another test.
I commented out this line: 
'   Return sheet.CodeName

and replaced it with: 
MsgBox("CodeName = " & sheet.CodeName)


When I run the program (debug), the MsgBox pops up, and displays:
"CodeName = "

So, the line is executed, but sheet.CodeName returns an EMPTY STRING.

Hope this can help.

Leon
Posted 13 Mar, 2019 08:07:51 Top
Andrei Smolin


Add-in Express team


Posts: 17227
 Joined: 2006-05-11
Leon Lai Kan writes:
So, the line is executed, but sheet.CodeName returns an EMPTY STRING.


It looks like sheet.Codename is an empty string or Noithing.

I suggest that you use https://docs.microsoft.com/en-us/visualstudio/debugger/debugger-feature-tour?view=vs-2017 to find out how to stop at a break point (you do this correctly) and inspect the sheet variable (is it Nothing?) and its properties (specifically CodeName).

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 13 Mar, 2019 08:20:58 Top
Leon Lai Kan




Posts: 200
 Joined: 2018-12-20
Andrei,

Thanks for your long article. Will go through it and try to find out what the variables return.

Meanwhile, what puzzles me is that both Name and CodeName are similarly defined (As String).

In Visual Studio's Object Browser, they are defined as follows:

ReadOnly Property CodeName As String
Member of Microsoft.Office.Interop.Excel._Worksheet


Property Name As String
Member of Microsoft.Office.Interop.Excel._Worksheet


The only difference (probably not relevant) is that one is ReadOnly.

Why does code work with Name, but not with CodeName?

Very puzzling indeed!



Leon
Posted 13 Mar, 2019 09:20:00 Top
Andrei Smolin


Add-in Express team


Posts: 17227
 Joined: 2006-05-11
ReadOnly is irrelevant. Either something is wrong with the sheet variable (it is Nothing or it contains something incomprehensible) or that worksheet doesn't have codename (totally unexpected. In that specific document maybe?)

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 13 Mar, 2019 10:39:24 Top
Leon Lai Kan




Posts: 200
 Joined: 2018-12-20
Hi, Andrei

I found an answer to my question.

It's funny, but it was yourself who answered it correctly more than 4 years ago, when somebody got the same problem as me.

See here


It seems that there is a bug in the CodeName function or in Visual Studio.
If not, why should we need to open the VBA IDE for it to work?

I read somewhere that VSTO requires that the developer should make some kind of reference to VBA for VSTO to work correctly.
Is it the case similarly for ADX?

Anyway, thanks for your time, and Best Regards.

Leon
Posted 14 Mar, 2019 07:16:42 Top
Andrei Smolin


Add-in Express team


Posts: 17227
 Joined: 2006-05-11
Hello Leon,

I believe the issue is somewhere in Office.

There's no VBA reference to be used with VSTO or Add-in Express. There's a rarely used VBIDE reference that helps you create VBA code.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 14 Mar, 2019 09:23:32 Top