Why do I have to convert ‘Cells’ here?

Question

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))

The answer to your second question can be found at https://learn.microsoft.com/en-us/dotnet/api/system.timespan?view=net-5.0#formatting-a-timespan-value Just `TimeSpan.ToString("...")` — Mary, Sep 07 '21 at 20:29
@Mary Thanks! `Duration_of_the_runtime.ToString("ddd\:hh\:mm")` — Daniel, Sep 07 '21 at 20:36
Side note on th US values. Just use Integer (Int32). See https://stackoverflow.com/questions/530690/net-optimized-int32 — Mary, Sep 07 '21 at 21:04
I think I would create and fill a DataTable. Then export the DataTable to Excel. There are several ways to do this and it is searchable with Google "datatable to excel in vb.net". I would use 2 methods, one to build the DataTable and one to export to Excel. — Mary, Sep 07 '21 at 21:09
I assume that when you say "`Cells` is returning a `Range`" you're going by documentation or some kind of runtime inspection? Because the error suggests that even though the *runtime type* of the return value is `Range`, the *static type* is `Object`. `Option Strict On` disallows implicit conversions, including conversions from `Object`, so you have to use `CType` or `DirectCast`. (The latter would avoid some minor overhead of trying to find a conversion when the runtime type doesn't match.) — Craig, Sep 07 '21 at 21:10
@Craig _you're going by documentation or some kind of runtime inspection?_ Yes. — Daniel, Sep 07 '21 at 21:14
So you can have confidence that casting the result to `Range` would be expected to work. This unfortunate design might be related to this being the Excel Automation object model, which to my recollection isn't the friendliest to work with because of a lot of legacy baggage. If you find that the amount of casting is too much of a PITA, you can always turn `Option Strict Off`, which I wouldn't normally recommend, but there are exceptions. (I think I did so on some of my own Excel automation code because of the loose typing of the interface.) — Craig, Sep 07 '21 at 21:18

score 1 · Accepted Answer · answered Sep 07 '21 at 21:35

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)...

Why do I have to convert ‘Cells’ here?

1 Answers1