Custom VBA Function Help Required

Aspose.Cells Product Family
1

Hello,

My client has given me a spreadsheet that makes extensive use of a custom function written in VBA. I need to open the spreadsheet in Aspose, update the values in several worksheets, perform a recalculation and then extract other values and certain charts.

However, I’m not sure how to go about replicating the functionality of the existing custom VBA function. I can easily rewrite it in .NET, but how do I get Aspose to hook into my function when a recalculation is performed?

I’ve read about the SetAddInFunction method but it doesn’t seem to be much help. Since many cells (about one hundred or more) in the spreadsheet are already referencing the custom function I don’t really want to search through the spreadsheet and call SetAddInFunction for each of these cells. I imagine that searching every cell for the occurence of this function will also degrade performance significantly. Is there any way I can register the custom function and associated XLA file across the entire workbook?

I’m also open to any other suggestions that will help me get this working.

Thanks in advance,
Gary

2

Hi Gary,

Thanks for sharing some details.

Please post your template file here, we will check it and may provide you with a better solution.

By the way, since you want to calculate your vba function(s), I think you may try to use Workbook.CalculateFormula(bool ignoreError, ICustomFunction customFunction) method to implement a custom interface function for your requirements.

Thank you.

3

Thanks for your suggestion to use the Workbook.CalculateFormula(bool ignoreError, ICustomFunction customFunction) method. I wasn’t aware that this existed. I’ll give it a try and let you know how I go.

Cheers,
Gary

4

Do I have to implement all logic in c#? or I can only pass range reference or other value to that vba function in Excel and get data return ?

Thanks, Hans

5

Hi,

Well, As we have already told you that Aspose.Cells does not support to process/run macros / VBA, so you need to implement it in C# code.


Thank you.