Passing null, System.DBNull, between Excel, c# and VBA

[Josh]

Hi,

I have the following 2 c# functions:

[ExcelFunction(Description = "SQL select (ADODB connection)")]

public static object[,] Sql_Select(string connectionStr, string sqlStr)

{...}

[ExcelFunction(Description = "Write data to range on given worksheet")]

public static int Vba_WriteToRange(object[,] data, string workSheet, string range)

{

    if (ExcelDnaUtil.IsInFunctionWizard()) return 0;

    Excel.Worksheet wSheet = CommonExcel.GetWorksheet(workSheet);

    Excel.Range rRange = CommonExcel.GetNamedRange(wSheet, range);

    int rows = data.GetLength(0);

    int cols = data.GetLength(1);

    int startRow = rRange.Row;

    int startColumn = rRange.Column;

    

    Excel.Range outRange = wSheet.get_Range(

        (Excel.Range)wSheet.Cells[startRow, startColumn],

        (Excel.Range)wSheet.Cells[startRow + rows - 1, startColumn + cols - 1]);

    outRange.Value2 = data;

    return 0;

}

Sql_Select() function returns an array of objects.

Now, some queries return System.DBNull and i'm trying to find a good way to pass them to Excel, VBA and pass back from VBA to C#.

I want to use the Sql_Select()  in 2 ways:

1. directly from Excel, eg: =Sql_Select(A9,A4) 

2. (because i have to replicate some old VBA code)  through the 2 step approach to write Excel spreadsheet with database data by first querying with Sql_Select() then writing it to spreadsheet by Vba_WriteToRange() function via the following VBA snippet:

Sub Button1_Click()

    

    ret = Application.Run("RmSql_Select", conn, sql)

    RaiseIfError (ret)

    

    ret1 = Application.Run("RmVba_WriteToRange", ret, "Sql", "rgSqlOut")

    RaiseIfError (ret1)

    

End Sub

I tried different ways to handle DBNull and replaced them with different values.

The following listing shows the replacement value and their results in Excel, VBA and how it is passed back from VBA.

1. ExcelMissing

Excel: 0

VBA: Variant/Null

Vba_WriteToRange: call doesn't reach c#: Run-time error 13: type mismatch

2. ExcelEmpty

Excel: 0

VBA: Variant/Empty

Vba_WriteToRange: outRange.Value2 = data fails: 

    Error: System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC

    System.RuntimeType.ForwardCallToInvokeMember()

    Microsoft.Office.Interop.Excel.Range.set_Value2(Object value)

3. ExcelErrorNull

Excel: #NULL!

VBA: Variant/Error  Error 2000

Vba_WriteToRange: Gets back the ExcelErrorNull that results in Excel showing 0

4. ExcelErrorValue

Excel: #VALUE!

VBA: Variant/Error  Error 2015

Vba_WriteToRange: Gets back the ExcelErrorValue that results in Excel showing 15

5. ExcelErrorNA

Excel: #N/A

VBA: Variant/Error  Error2042

Vba_WriteToRange: Gets back the ExcelErrorNA that results in Excel showing 42

6. DBNull

Excel: #VALUE!

VBA: Variant/Error  Error 2015

Vba_WriteToRange: Gets back ExcelErrorValue that results in Excel showing 15

7. null

Excel: 0

VBA: Variant/Empty

Vba_WriteToRange: outRange.Value2 = data fails: 

    Error: System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC

    System.RuntimeType.ForwardCallToInvokeMember()

    Microsoft.Office.Interop.Excel.Range.set_Value2(Object value)

8. Empty string: ""

Excel: nothing visible in cells

VBA: Variant/String ""

Vba_WriteToRange: Gets back the empty strings that results in Excel showing nothing

So far the best results is given by the empty string, but i would like to know if there are any better ways. 

What i would probably like is a mixture of the results, eg Excel showing nothing, VBA shoving Variant/Null and c# to write Excel as nothing.

[FastExcel]

Excel does not currently support a Null datatype, so you have to decide how you want to represent Null using one of the existing datatypes (floating point number, boolean, string, error).

The decision depends on how you want to process the NULL in subsequent Excel Formulas and rendering/formatting. Probably one of either an error (use CVERR(xlErrNull) or CVERR(xlErrValue)) or an empty string or a zero