Why do I have to convert ‘Cells’ here?-CodePudding

Brief concern: I noticed that I have to convert using CType here, even though Cells is already returning a Range. What am I doing wrong? Cells is ReadOnly and does not take any parameters. Well, I was shown it like that once.

If I'm doing this

xlRange = myWorksheet.Cells(Line   1US, 2US)

the error is

Option Strict On disallows implicit conversions from 'Object' to 'Range'

I would like to mention—if that's because of that—that I leave Option Infer Off so that I force myself to always write down the type.

The source code should write measured values in an Excel sheet for a certain period of time.

Example:

    A          B        C
1  07.09.2021  08:00    25,0
2              12:00    30,0
3              16:00    35,0
4  08.09.2021  08:00    26,0
5              12:00    28,0
6              16:00    26,0

class-wide variables:

Private Time_of_the_program_start As Date
Private Duration_of_the_runtime As TimeSpan
Private xlApp As Excel.Application
Private myWorkbook As Excel.Workbook
Private running As Boolean = False
Private Line As UInt16 = 0US
Private ReadOnly Deu As New System.Globalization.CultureInfo("de-DE")

running is set to True within a button procedure.

Private Function read_and_write_data(ByVal file_path As String) As Boolean
        xlApp = New Excel.Application With {
            .Visible = True
        }
        myWorkbook = xlApp.Workbooks.Add()
        Dim myWorksheet As Excel.Worksheet = CType(myWorkbook.Sheets("Tabelle1"), Excel.Worksheet)
        Dim xlRange As Excel.Range
        Dim Temperatur As Single

        While running
            Duration_of_the_runtime = Date.Now - Time_of_the_program_start
            If Duration_of_the_runtime.Days = 365 Then Exit While

            Me.Invoke(Sub() Label_Duration.Text =
                          Duration_of_the_runtime.Days.ToString(Deu).PadLeft(3, "0"c) & ":" &
                          Duration_of_the_runtime.Hours.ToString(Deu).PadLeft(2, "0"c) & ":" &
                          Duration_of_the_runtime.Minutes.ToString(Deu).PadLeft(2, "0"c))

            'System.Threading.Thread.Sleep(10000)
            'myWorksheet.Cells(Line, column)
            '–––––––––––––––––––––––––––––––––––––––
            'Spalte A: Datum
            '–––––––––––––––––––––––––––––––––––––––
            xlRange = CType(myWorksheet.Cells(Line   1US, 1US), Excel.Range)
            xlRange.Value = Date.Now.ToString("d", Deu)
            '–––––––––––––––––––––––––––––––––––––––
            'Spalte B: Uhrzeiten
            'Spalte C: Messwerte
            '–––––––––––––––––––––––––––––––––––––––
            Temperatur = Get_Temperature_from_Pt100()

            xlRange = CType(myWorksheet.Cells(Line   1US, 2US), Excel.Range)
            xlRange.Value = Date.Now.ToString("t", Deu)

            xlRange = CType(myWorksheet.Cells(Line   1US, 3US), Excel.Range)
            xlRange.Value = Temperatur
            '‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
            Temperatur = Get_Temperature_from_Pt100()
            xlRange = CType(myWorksheet.Cells(Line   2US, 2US), Excel.Range)
            xlRange.Value = Date.Now.ToString("t", Deu)

            xlRange = CType(myWorksheet.Cells(Line   2US, 3US), Excel.Range)
            xlRange.Value = Temperatur
            '‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
            Temperatur = Get_Temperature_from_Pt100()
            xlRange = CType(myWorksheet.Cells(Line   3US, 2US), Excel.Range)
            xlRange.Value = Date.Now.ToString("t", Deu)

            xlRange = CType(myWorksheet.Cells(Line   3US, 3US), Excel.Range)
            xlRange.Value = Temperatur
            '–––––––––––––––––––––––––––––––––––––––
            'inkrementieren
            '–––––––––––––––––––––––––––––––––––––––
            Line  = 3US
        End While

        myWorksheet.SaveAs(file_path, Excel.XlFileFormat.xlWorkbookDefault)
        myWorkbook.Close()
        xlApp.Quit()
        If myWorksheet IsNot Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(myWorksheet)
        If myWorkbook IsNot Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(myWorkbook)
        If xlApp IsNot Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
        xlApp = Nothing
        myWorkbook = Nothing
        myWorksheet = Nothing

        Return True
    End Function

By the way: is there a more elegant solution for this?

Me.Invoke(Sub() Label_Duration.Text =
                          Duration_of_the_runtime.Days.ToString(Deu).PadLeft(3, "0"c) & ":" &
                          Duration_of_the_runtime.Hours.ToString(Deu).PadLeft(2, "0"c) & ":" &
                          Duration_of_the_runtime.Minutes.ToString(Deu).PadLeft(2, "0"c))

CodePudding user response：

To your main question, no, you're not doing anything wrong, that's just how the Excel automation interface behaves. Specifically, the collection types are all the classic VBA Collection (or the functional equivalent) meaning that index access returns an Object rather than something that is strongly-typed.

As you've noted, if you try to work with this in an Option Strict On context, you will have to cast the results. My own interop code that has a bunch of collection item accesses is littered with things like DirectCast(.Rows(2), Excel.Range)...