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
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