0

I'm writing a simple app using vb.net that takes a user selected text file, populates a usoft excel worksheet, and creates a scatter plot chart of the data. The code compiles without error and when executing, produces the desired results up to the point when attempting to add a text box to the chart. That's when it produces a:

NullReferenceException was unhandled error, An unhandled exception of type 'System.NullReferenceException' occurred in Graphing_Tool.exe. Additional information: Object reference not set to an instance of an object.

Included is an excerpt of my code. The highlited line is where it stops executing and produces the described error. All of my attempts to date at correcting this error have failed. I'm beginning to think that text boxes cannot be added to excel charts using vb.net. My OS is win10 and my IDE is visual studio 2012. Any help on resolving my issue would be greatly appreciated! Thanks.

            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing
            Dim xlCells As Excel.Range = Nothing
            Dim SheetName As String = ""


            'New instance of Excel and start a new workbook.
            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Add
            xlWorkSheets = xlWorkBook.Worksheets
            xlWorkSheet = xlWorkSheets(1)
            xlApp.Visible = True


            Dim maxTemp As Single = 0.0
            Dim minTemp As Single = 10000.0

            'Write data to excel sheet
            With xlWorkSheet
                tempCount = 0
                Do While tempCount <= lineCount - 1
                    If (data(tempCount).PixelValue > maxTemp) Then
                        maxTemp = data(tempCount).PixelValue
                    End If
                    If (data(tempCount).PixelValue < minTemp) Then
                        minTemp = data(tempCount).PixelValue
                    End If
                    .Cells(tempCount + 1, 1) = data(tempCount).PixelNum
                    .Cells(tempCount + 1, 2) = data(tempCount).PixelValue
                    tempCount += 1
                Loop
                tempCount -= 1
            End With
            Erase data

            'Calculate Thermal Resistance (Junction-Case)
            Dim thermalResistance As Single = 0.0
            thermalResistance = (maxTemp - CStr(CaseTemp)) / maxPower
            thermalResistance = Math.Round(thermalResistance, 3, MidpointRounding.AwayFromZero)

            Dim chartArea As Excel.Chart
            Dim xlCharts As Excel.ChartObjects
            Dim myChart As Excel.ChartObject
            Dim chartRange As Excel.Range

            xlCharts = xlWorkSheet.ChartObjects
            myChart = xlCharts.Add(300, 80, 500, 250)
            chartArea = myChart.Chart
            With chartArea
                chartRange = xlWorkSheet.Range("A:B")
                .SetSourceData(Source:=chartRange)
                .ChartType = Excel.XlChartType.xlXYScatterLinesNoMarkers
                .HasTitle = True
                .HasLegend = False
                .ChartTitle.Text = "Temperature Data Trace SN= " & PN
                .SeriesCollection(1).delete
                .SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(255, 0, 0)
                Dim xlAxisCategory, xlAxisValue As Excel.Axes
                xlAxisCategory = CType(chartArea.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)
                xlAxisCategory.Item(Excel.XlAxisType.xlCategory).HasTitle = True
                xlAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Characters.Text = "Pixel Count"

                xlAxisValue = CType(chartArea.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)
                xlAxisValue.Item(Excel.XlAxisType.xlValue).HasTitle = True
                xlAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Characters.Text = "Temperature (Degrees C)"
                xlAxisValue.Item(Excel.XlAxisType.xlValue).MinimumScale = Int(0.9 * minTemp)

            End With


            **Dim oShape As Excel.Shape = xlApp.ActiveChart.Shapes.AddLabel(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, 320, 100, 500, 250)**
            oShape.TextFrame2.TextRange.Text = "some text"
Brian M Stafford
  • 8,483
  • 2
  • 16
  • 25
Your Magesty
  • 1
  • Does this answer your question? [What is a NullReferenceException, and how do I fix it?](https://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) – Andrew Morton Jan 31 '20 at 15:16
  • What happens when you change xlApp.ActiveChart to `xlWorksheet.ActiveChart`? – Jimmy Smith Jan 31 '20 at 17:45
  • Thank-you for your suggestions. Changing xlApp.ActiveChart to xlWorksheet.ActiveChart produces the same error. – Your Magesty Jan 31 '20 at 19:02
  • I may have "up" a level in your class hierarchy. The gist of the issue is we're referencing an item that does not exist, albeit part of another one, if this makes sense. Are you trying to add the label to "myChart"? – Jimmy Smith Feb 01 '20 at 13:55
  • Hello Sir, Thank-you for taking a look at my code and helping solve this problem. Yes, I'm trying to add a label to myChart. – Your Magesty Feb 04 '20 at 20:03

0 Answers0