vb.netudfexcel-dna

Set Cell Formula in ExcelDNA UDF


I am working on a UDF(User defined function) for Excel user, the task seems easy with the help of ExcelDNA. But While I test it in a cell of Excel with following two formula, both show #VALUE!. Need help to get this solved, thanks.

=mySetCellFormula("Test", "")
=mySetCellFormula("Test", "A1")

Imports System.Net
Imports System.Drawing
Imports System.Windows.Forms
Imports Microsoft.Office.Core
Imports Excel = Microsoft.Office.Interop.Excel
Imports ExcelDna.Integration
Imports ExcelDna.Integration.XlCall

Public Module MyFunctions
    Public Function mySetCellFormula(ByVal sFormuaR1C1 As String, ByVal cellAddress As String) As String
        ' Get the correct application instance
        Dim xlApp As Excel.Application
        Dim xlSheet As Excel.Worksheet
        Dim xlCell As Excel.Range
        xlApp = CType(ExcelDnaUtil.Application, Excel.Application)
        xlSheet = CType(xlApp.ActiveSheet(), Excel.Worksheet)
        If cellAddress = "" Then
            xlCell = xlApp.ActiveCell()
        Else
            xlCell = xlSheet.Range(cellAddress)
        End If
        'xlCell.FormulaR1C1 = "=" & Chr(34) & sFormuaR1C1 & Chr(34)
        xlCell.FormulaR1C1 = "=" & sFormuaR1C1
        mySetCellFormula = ""
    End Function
End Module

Solution

  • Thank Govert for enlightening me on Excel Calculation Model and Macro concept, finally I work out a solution as follow:

    Imports System.Net
    Imports System.Drawing
    Imports System.Windows.Forms
    Imports Microsoft.Office.Core
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports ExcelDna.Integration
    Imports ExcelDna.Integration.XlCall
    
    Public Module MyFunctions
        Public Function mySetCellFormula(ByVal sFormuaR1C1 As String, ByVal cellAddress As String) As String
            ' Get the correct application instance
            Dim xlApp As Excel.Application
            Dim xlSheet As Excel.Worksheet
            Dim xlCell As Excel.Range
            xlApp = CType(ExcelDnaUtil.Application, Excel.Application)
            xlSheet = CType(xlApp.ActiveSheet(), Excel.Worksheet)
            If cellAddress = "" Then
                xlCell = xlApp.ActiveCell()
            Else
                xlCell = xlSheet.Range(cellAddress)
            End If
    
            ExcelAsyncUtil.QueueAsMacro( _
                Sub()
                    xlCell.FormulaR1C1 = "=" & sFormuaR1C1
                End Sub)
    
            mySetCellFormula = ""
        End Function
    End Module