How to modify the code when Option Strict is set On?

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

How to modify the code when Option Strict is set On?
 
Leon Lai Kan




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

I am studying this sample:
Working with Excel Workbooks and Worksheets
https://www.add-in-express.com/creating-addins-blog/2013/10/09/excel-workbooks-worksheets-vbnet-examples/


There is a section in that article entitled "Reference a specific sheet"

Here are the codes as provided by the sample:
These codes were written with Option Strict OFF

I have set Option Strict On, and had to modify certain lines of codes in the sample. See the green lines (comments) in the codes. 

Private Sub btnGetSheetByIndex_OnClick(sender As Object, _
        control As IRibbonControl, pressed As Boolean) _
        Handles btnGetSheetByIndex.OnClick

   Dim sheet As Excel.Worksheet = GetSheetByIndex(ExcelApp.ActiveWorkbook, 2)
   '// I modified the above line to:
   '  Dim sheet As Excel.Worksheet = _
   '            GetSheetByIndex(TRYCAST(ExcelApp.ActiveWorkbook, Excel.Workbook), 2) 
   '  but error message: 
   '  "Value of type 'Integer'cannot be converted to Microsoft..Interop.... worksheet"

   Marshal.ReleaseComObject(sheet)
End Sub




Private Function GetSheetByIndex(wb As Excel.Workbook, index As Integer)
        Return wb.Worksheets(index)
        '// I amended the above line to: 
        '   Return CINT(wb.Worksheets(index))
 End Function



Any help to permit me to write the correct code will be much appreciated.

Thanks
Leon
Posted 08 Mar, 2019 06:30:09 Top
Andrei Smolin


Add-in Express team


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

Leon Lai Kan writes:
TRYCAST(ExcelApp.ActiveWorkbook, Excel.Workbook)


There's no need to do this cast: ExcelApp.ActiveWorkbook returns an Excel.Workbook (not any other type such as Object or Variant(VBA)); see https://docs.microsoft.com/en-us/office/vba/api/excel.application.activeworkbook. 

Private Function GetSheetByIndex(wb As Excel.Workbook, index As Integer) 
        Return wb.Worksheets(index) 
        '// I amended the above line to:  
        '   Return CINT(wb.Worksheets(index)) 
 End Function


The compiler generates this error: Option Strict On requires all Function, Property, and Operator declarations to have an 'As' clause.

That is, you should modify it as follows: 

Private Function GetSheetByIndex(wb As Excel.Workbook, index As Integer) As Excel.Worksheet
        Return CType(wb.Worksheets(index), Excel.Worksheet)
 End Function


But... You should also be aware that Excel has these collections: Sheets, Worksheets, and Charts: see three first sentences at https://docs.microsoft.com/en-us/office/vba/api/excel.sheets.

This means the function above will fail if you pass index=2 while the workbook have two sheets: 1) a worksheet, 2) a chart. If the workbook has three sheets: (1) worksheet, (2) chart and (3) worksheet, that function will give you the second worksheet = the third sheet.

That is, depending on your goal, you may also want to have this function: 

Private Function GetSheetByIndex(wb As Excel.Workbook, index As Integer) As Object
    Return wb.Sheets(index)
End Function


When using this function, you'll check the object returned; say, you can do this by TryCast'ing the object to Worksheet or Chart.

Leon Lai Kan writes:
'// I amended the above line to: ' Return CINT(wb.Worksheets(index))


You modified the function so that it returns an integer while the original code returns an object (which represents a worksheet). Returning the integer value causes the "Value of type 'Integer'cannot be converted to..." exception.


Andrei Smolin
Add-in Express Team Leader
Posted 11 Mar, 2019 03:42:53 Top
Leon Lai Kan




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

Thanks a lot for your great explanation.

Unfortunately, I cannot make the sample work.

To try to see what's wrong, I run the ORIGINAL SAMPLE again (where Option Strict is OFF).
Here are the original codes: 

Private Sub btnGetSheetByIndex_OnClick(sender As Object, _
                  control As IRibbonControl, pressed As Boolean) _
                  Handles btnGetSheetByIndex.OnClick

        Dim sheet As Excel.Worksheet = GetSheetByIndex(ExcelApp.ActiveWorkbook, 2)
        Marshal.ReleaseComObject(sheet)

    End Sub

Private Function GetSheetByIndex(wb As Excel.Workbook, index As Integer)
        Return wb.Worksheets(index)

End Function

I build, register, and run debug.
An Excel workbook opens with only Sheet1.
I add a few more sheets: Sheet2, Sheet3, Sheet4.

Then I click the button "Get Sheet by Index" found on the Ribbon.

Nothing happens. I tried many times. I am curious to see what the code does.

red
Once I can make the original code work, I will try to set Option Strict On and modify the code.

Best Regards,
Leon
Posted 11 Mar, 2019 06:38:22 Top
Andrei Smolin


Add-in Express team


Posts: 18830
 Joined: 2006-05-11
Leon Lai Kan writes:
Nothing happens. I tried many times. I am curious to see what the code does.


But that's written in the code itself: 

        Dim sheet As Excel.Worksheet = GetSheetByIndex(ExcelApp.ActiveWorkbook, 2) 
        Marshal.ReleaseComObject(sheet)


It gets the Worksheet object and releases it. Nothing else. You can add a message box showing some info about the sheet.


Andrei Smolin
Add-in Express Team Leader
Posted 11 Mar, 2019 06:45:57 Top
Leon Lai Kan




Posts: 200
 Joined: 2018-12-20
Oh! I see.

I thought it would activate Sheet2, but it's not the case!

Best Regards,
Leon
Posted 11 Mar, 2019 06:55:43 Top
Andrei Smolin


Add-in Express team


Posts: 18830
 Joined: 2006-05-11
To activate a worksheet, you call Worksheet.Activate; in your case, this will be sheet.Activate().


Andrei Smolin
Add-in Express Team Leader
Posted 11 Mar, 2019 07:03:50 Top
Leon Lai Kan




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

Thanks again.

When we add the line sheet.Activate(), at last we SEE something happen when we click on the ribbon button.
Otherwise, this leaves the beginner very puzzled.

Best Regards,
Leon
Posted 11 Mar, 2019 08:03:23 Top
Andrei Smolin


Add-in Express team


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

Yes, that might be an omission or mistake.


Andrei Smolin
Add-in Express Team Leader
Posted 12 Mar, 2019 05:53:39 Top