CODEWITHSUNDEEP
f#excel-interopexcel-dna
Janthelme
Janthelme

Reputation: 999

Set some of the Excel Chart Series values to #N/A using Excel-DNA or Excel.Interop

I am using ExcelDNA and Microsoft.Office.Interop.Excel to set some of the y values of a given Excel chart series to #N/A. (*)

This is what I am trying to achieve in VBA. This works as expected:

Sub test()
  Dim xdata As Variant, ydata As Variant
  Dim chrt As Chart
  
  Set chrt = ActiveWorkbook.Worksheets("Sheet1").ChartObjects("Chart 1").Chart

  With chrt
    xdata = Array(0, 1, 2, 3, 4, 5)
    ydata = Array(0, 10, 20, CVErr(xlErrNA), 40, 50)  'Works as expected
    .SeriesCollection(1).XValues = xdata
    .SeriesCollection(1).Values = ydata
  End With

End Sub

In F# I have tried the 2 following approaches, and none worked:

module TEST =
    open Microsoft.Office.Interop.Excel
    open ExcelDna.Integration

    let series : Series = 
        let app = ExcelDnaUtil.Application :?> Application
        let wks   = (app.Sheets.Item "Sheet1") :?> Worksheet
        let cho = wks.ChartObjects("Chart 1") :?> ChartObject
        let ch = cho.Chart
        let s = ch.SeriesCollection(1) :?> Series
        s

    [<ExcelFunction(Category="Chart", Description="")>]
    let setYSeries1() : obj = 
        let s = series
        s.XValues <- [| 0.0; 1.0; 2.0; 3.0; 4.0; 5.0 |]
        s.Values <- [| 0.0; 10.0; 20.0; 30.0; 40.0; 50.0 |] |> Array.map box // Works as expected.
        box "Done."

    [<ExcelFunction(Category="Chart", Description="")>]
    let setYSeries2() : obj = 
        let s = series
        s.XValues <- [| 0.0; 1.0; 2.0; 3.0; 4.0; 5.0 |]
        s.Values <- [| box 0.0; box 10.0; box 20.0; ExcelError.ExcelErrorNA |> box; box 40.0; box 50.0 |] |> Array.map box // y = 42 for x = 3, instead of y = #N/A
        box "Done."

    [<ExcelFunction(Category="Chart", Description="")>]
    let setYSeries3() : obj = 
        let s = series
        s.XValues <- [| 0.0; 1.0; 2.0; 3.0; 4.0; 5.0 |]
        s.Values <- [| box 0.0; box 10.0; box 20.0; ((int32) -2146826246) |> box; box 40.0; box 50.0 |]  // y = -2146826246 for x = 3, instead of y = #N/A
        box "Done."

setYSeries1 is the base case without #N/A values. It works fine.

setYSeries2 was the natural way, using ExcelDNA's ExcelError.ExcelErrorNA enum, but #N/A is replaced by its enum value in the chart (y = 42).

I tried setYSeries3 after I read in this article that, internally, Excel uses integers to represent errors like #N/A (while Excel uses doubles to represent numbers), substituting (int) -2146826246 for #N/A value. No luck either.

My question: what should I do to pass #N/A values to the series' .Values array?

(*) I need to set the series' .Values properties via an array, rather than via a sheet range.

Upvotes: 1

Views: 156

Answers (1)

Govert
Govert

Reputation: 16907

You need to convert the error value to a type that .NET will understand as a COM error type (like CVErr in VBA).

There is an Excel-DNA helper that maps the C API error enums to COM error:

    ExcelDna.Integration.ExcelErrorUtil.ToComError(ExcelError.ExcelErrorNA)

Internally this will do

     new System.Runtime.InteropServices.ErrorWrapper(-2146826246)

Upvotes: 2

Related Questions