Can I call a spreadsheet function from a custom function in SpreadsheetGear

Question

I am using SpreadsheetGear and have been given a spreadsheet with a VBA macro that I need to convert to a custom function (as SSG can't handle VBA code). The macro calls Excel functions (Match, Index) and I can't see how to call these functions from within my custom function. Any ideas if this is possible and how to do it?

Thanks

Answer 1

You can evaluate Excel formula strings by using the ISheet.EvaluateValue(...) method. Example:

// A1:A3 on "Sheet1" will be summed up.
object val = workbook.Worksheets["Sheet1"].EvaluateValue("SUM(A1:A3)");

// Ensure we actually get a numeric value back
if(val != null && val is double)
{
    double sum = (double)val;
    Console.WriteLine(sum);
}

However, it sounds like you will be using this method inside your custom function, so I should point out that you cannot use this method for any sheet that belongs to any workbook in the IWorkbookSet that is currently being calculated. Please review the rules that are laid out in the Remarks section for the Function.Evaluate(...) method, pasted below, particularly the ones I bolded:

---

The implementation of this method must follow a number of rules:

• The method must be thread safe. Some versions of SpreadsheetGear call this method from multiple threads at the same time.
• The method must not use any API in the workbook set which is being calculated except for IArguments.CurrentWorksheet.Name, IArguments.CurrentWorksheet.Index or IArguments.CurrentWorksheet.Workbook.Name.
• All access to cells must be through arguments.
• Accessing the IArguments indexer has the side effect of converting references to ranges or arrays to a single simple value. Use GetArrayDimensions and GetArrayValue to access the individual values of a range or array. For example, if the first custom function argument is the range A1:C3, setting a watch on arguments[0] in a debugger will convert this range to a single simple value.
• No references to interfaces or objects passed to, or acquired during the execution of this method should be used after this method completes execution.

---

So if you call ISheet.EvaluateValue(...) within your custom function, it would have to be done on some sheet that belongs to an IWorkbookSet other than the one currently being calculated. It would also need to be done in a thread-safe manner, as per the first rule mentioned above.