Error Exporting to Excel from VB 2008: Chart Axis Error

[E.J.GUN]

Imports Excel = Microsoft.Office.Interop.Excel
...
Public WithEvents xlApp As Excel.Application For creating Excel worksheets
Public xlBook As Excel.Workbook
Public xlSheet As Excel.Worksheet
Public xlChart As Excel.Chart
Public xlSeriesCol As Excel.SeriesCollection
Public xlSeries As Excel.Series
Public xlAxes As Excel.Axes
Public xlAxis As Excel.Axis
...
If either axis is supposed to be log scale, let Excel know
xlAxes = CType(xlChart.Axes, Excel.Axes)
If (frmMain.CheckBox1.CheckState = CheckState.Checked) Then
xlAxis = CType(xlAxes(Excel.XlAxisType.xlCategory), Excel.Axis)
xlAxis.ScaleType = Excel.XlScaleType.xlScaleLogarithmic
End If
If (frmMain.CheckBox2.CheckState = CheckState.Checked) Then
xlAxis = CType(xlAxes(Excel.XlAxisType.xlValue), Excel.Axis)
xlAxis.ScaleType = Excel.XlScaleType.xlScaleLogarithmic
End If

The code above is part of a VB 2008 project that generates some data and exports it to Excel, creating a chart in the new Excel workbook in the process. Everything works great except the part about setting either the Category or Value axis to be a logarithmic scale. Setting the Category ("X") axis to logarithmic works just fine. However, setting the Value ("Y") axis to logarithmic generates this error:

Object reference not set to an instance of the object.

on the line (from the second "If" statement above dealing with the Value axis):

xlAxis.ScaleType = Excel.XlScaleType.xlScaleLogarithmic

I dont understand why the error is happening. Any suggestions will be greatly appreciated.

Eric

Continue reading...