//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); Excel.Workbook.AddCustomFunction(new MyFunctionWithAcceptErrors("MyFuncCanAcceptErrors", true)); Excel.Workbook.AddCustomFunction(new MyFunctionWithAcceptErrors("MyFuncNotAcceptErrors", false)); IWorksheet worksheet = workbook.ActiveSheet; worksheet.Range["A1:B1"].Merge(); worksheet.Range["A7:B7"].Merge(); worksheet.Range["A1:B1"].Value = "Custom function that accepts errors"; worksheet.Range["A7:B7"].Value = "Custom function that does not accept errors"; worksheet.Range["A2, A8"].Value = "Formula"; worksheet.Range["B2, B8"].Value = "Result"; worksheet.Range["A1:B1, A7:B7, A2, A8, B2, B8"].Font.Bold = true; worksheet.Range["A2, A8, B2, B8"].HorizontalAlignment = HorizontalAlignment.Center; // B3 cell's value is "Exist errors: #Div0". worksheet.Range["A3"].Value = "=MyFuncCanAcceptErrors(1, 2, 1/0)"; worksheet.Range["B3"].Formula = "=MyFuncCanAcceptErrors(1, 2, 1/0)"; // B4 cell's value is "Exist errors: #Value". worksheet.Range["A4"].Value = "=MyFuncCanAcceptErrors(1, \"test\" + 1, 3)"; worksheet.Range["B4"].Formula = "=MyFuncCanAcceptErrors(1, \"test\" + 1, 3)"; // B5 cell's value is "Exist errors: #Name, #Num". worksheet.Range["A5"].Value = "=MyFuncCanAcceptErrors(SUME(1), 2, SQRT(-1), 4)"; worksheet.Range["B5"].Formula = "=MyFuncCanAcceptErrors(SUME(1), 2, SQRT(-1), 4)"; // B9 cell's value is error of #DIV/0!. worksheet.Range["A9"].Value = "=MyFuncNotAcceptErrors(1, 2, 1/0)"; worksheet.Range["B9"].Formula = "=MyFuncNotAcceptErrors(1, 2, 1/0)"; // B10 cell's value is error of #VALUE!. worksheet.Range["A10"].Value = "=MyFuncNotAcceptErrors(1, \"test\" + 1, 3)"; worksheet.Range["B10"].Formula = "=MyFuncNotAcceptErrors(1, \"test\" + 1, 3)"; // B11 cell's value is error #NAME?. worksheet.Range["A11"].Value = "=MyFuncNotAcceptErrors(SUME(1), 2, SQRT(-1), 4)"; worksheet.Range["B11"].Formula = "=MyFuncNotAcceptErrors(SUME(1), 2, SQRT(-1), 4)"; worksheet.Range["A:B"].AutoFit(); worksheet.PageSetup.PrintHeadings = true; worksheet.PageSetup.PrintGridlines = true; worksheet.PageSetup.Orientation = PageOrientation.Landscape; worksheet.PageSetup.PrintArea = "$A$1:$G$20"; /* public class MyFunctionWithAcceptErrors : CustomFunction { public MyFunctionWithAcceptErrors(string funcName, bool acceptError) : base(funcName, FunctionValueType.Text) { this._acceptErrors = acceptError; } private bool _acceptErrors = false; override public bool AcceptErrors { get => _acceptErrors; set => _acceptErrors = value; } public override object Evaluate(object[] arguments, ICalcContext context) { if (arguments == null || arguments.Length == 0) { return null; } StringBuilder result = new StringBuilder(); for (int i = 0; i < arguments.Length; i++) { if (arguments[i] is CalcError calcError) { result.Append(result.Length > 0 ? $", #{calcError}" : $"Exist errors: #{calcError}"); } } return result.Length > 0 ? result.ToString() : "No error"; } } */ // Save to a pdf file workbook.Save("AcceptErrorsInCustomFunction.pdf");
' Create a new Workbook Dim workbook As New Workbook Excel.Workbook.AddCustomFunction(New MyFunctionWithAcceptErrors("MyFuncCanAcceptErrors", True)) Excel.Workbook.AddCustomFunction(New MyFunctionWithAcceptErrors("MyFuncNotAcceptErrors", False)) Dim worksheet As IWorksheet = workbook.ActiveSheet worksheet.Range("A1:B1").Merge() worksheet.Range("A7:B7").Merge() worksheet.Range("A1:B1").Value = "Custom function that accepts errors" worksheet.Range("A7:B7").Value = "Custom function that does not accept errors" worksheet.Range("A2, A8").Value = "Formula" worksheet.Range("B2, B8").Value = "Result" worksheet.Range("A1:B1, A7:B7, A2, A8, B2, B8").Font.Bold = True worksheet.Range("A2, A8, B2, B8").HorizontalAlignment = HorizontalAlignment.Center ' B3 cell's value is "Exist errors: #Div0" worksheet.Range("A3").Value = "=MyFuncCanAcceptErrors(1, 2, 1/0)" worksheet.Range("B3").Formula = "=MyFuncCanAcceptErrors(1, 2, 1/0)" ' B4 cell's value is "Exist errors: #Value" worksheet.Range("A4").Value = "=MyFuncCanAcceptErrors(1, ""test"" + 1, 3)" worksheet.Range("B4").Formula = "=MyFuncCanAcceptErrors(1, ""test"" + 1, 3)" ' B5 cell's value is "Exist errors: #Name, #Num" worksheet.Range("A5").Value = "=MyFuncCanAcceptErrors(SUME(1), 2, SQRT(-1), 4)" worksheet.Range("B5").Formula = "=MyFuncCanAcceptErrors(SUME(1), 2, SQRT(-1), 4)" ' B9 cell's value is error of #DIV/0! worksheet.Range("A9").Value = "=MyFuncNotAcceptErrors(1, 2, 1/0)" worksheet.Range("B9").Formula = "=MyFuncNotAcceptErrors(1, 2, 1/0)" ' B10 cell's value is error of #VALUE! worksheet.Range("A10").Value = "=MyFuncNotAcceptErrors(1, ""test"" + 1, 3)" worksheet.Range("B10").Formula = "=MyFuncNotAcceptErrors(1, ""test"" + 1, 3)" ' B11 cell's value is error #NAME? worksheet.Range("A11").Value = "=MyFuncNotAcceptErrors(SUME(1), 2, SQRT(-1), 4)" worksheet.Range("B11").Formula = "=MyFuncNotAcceptErrors(SUME(1), 2, SQRT(-1), 4)" worksheet.Range("A:B").AutoFit() worksheet.PageSetup.PrintHeadings = True worksheet.PageSetup.PrintGridlines = True worksheet.PageSetup.Orientation = PageOrientation.Landscape worksheet.PageSetup.PrintArea = "$A$1:$G$20" 'Public Class MyFunctionWithAcceptErrors ' Inherits CustomFunction ' Public Sub New(funcName As String, acceptError As Boolean) ' MyBase.New(funcName, FunctionValueType.Text) ' Me._acceptErrors = acceptError ' End Sub ' Private _acceptErrors As Boolean = False ' Public Overrides Property AcceptErrors As Boolean ' Get ' Return _acceptErrors ' End Get ' Set(value As Boolean) ' _acceptErrors = value ' End Set ' End Property ' Public Overrides Function Evaluate(arguments() As Object, context As ICalcContext) As Object ' If arguments Is Nothing OrElse arguments.Length = 0 Then ' Return Nothing ' End If ' Dim result As New StringBuilder() ' For i As Integer = 0 To arguments.Length - 1 ' If TypeOf arguments(i) Is CalcError Then ' Dim calcError As CalcError = DirectCast(arguments(i), CalcError) ' If result.Length > 0 Then ' result.Append($", #{calcError}") ' Else ' result.Append($"Exist errors: #{calcError}") ' End If ' End If ' Next ' Return If(result.Length > 0, result.ToString(), "No error") ' End Function 'End Class ' save to a pdf file workbook.Save("AcceptErrorsInCustomFunction.pdf")