I’ve got some custom formulas that I want to use in my designer spreadsheet/workbook. However, Aspose.Excel is apparently stripping out my VBA module containing the custom formulas from the designer workbook while populating data into the designer spreadsheet. I see the VBA module in my designer source workbook, but when the designer workbook is pushed down to the user over the intranet, the user’s version of the workbook results in #NAME?. The #NAME? error is a result of Excel not being able to resolve the function name back to anything because the VBA module has been removed!
Also, as a follow-on to my previous post regarding problems with the SUBTOTAL formula, my custom formula is behaving similarly to the SUBTOTAL formula, except that no value whatsoever shows in the cell where the formula is supposed to be.
Something interesting I discovered while experimenting: if I assign the formula string via Cell.PutValue instead of Cell.Formula, then I see the text of the formula show up in the cell. I can then put my cursor in the cell where the formula text is, click in the Formula Bar of the Excel UI, press the Enter key, and Excel then transforms the formula text into a value. Subsequently, the SUBTOTAL formula gets transformed into a value in those cells that have been assigned the SUBTOTAL formula. Likewise, the cells with custom formulas get transformed, but result in the #NAME? error due to the reason I stated above.
I hope I’m not wearing out my welcome in this forum, but the Aspose.Excel component has so much potential for meeting my requirements that I do not want to give up. There is no other .NET component on the market that I’m aware of that is so flexible and powerful and works. I’ve tried other “.NET to Excel” components from your competition, but they are either not true .NET managed components, I can’t get them to work at all in my environment, and/or they are too feature poor to meet my requirements. Yes, I may be exposing either shortcomings in Aspose.Excel, or shortcomings in my knowledge. But, if these issues can be worked out, then this is one product that I would not mind evangelising to anyone who will listen.
Thank you for sticking with this customer feedback process and keep up the good work!
I need to elaborate on the terminology I used in my previous post. When I say custom formulas, I mean that I’ve got functions written in VBA that I’m using as custom formulas in my designer workbook.
Sorry guys, this is a non-issue now. In order to support custom formulas in both dynamically generated spreadsheets and designer workbooks, I need to make my library of custom functions/formulas available as an Excel Add-In (.xla). This is a better and more elegant solution than trying to embed the module holding the VBA for the custom functions/formulas in the designer spreadsheet, and/or having an Aspose.Excel API for adding in custom functions. Don’t waste your time pursuing this issue on my behalf. This was a matter of informing myself better about Excel’s features.
Thanks again!
Clarification on requirements!
I still do need to be able to assign a reference to a custom formula/function via Cell.Formula and have it work.
What I’m not concerned with is Aspose.Excel’s apparent behavior of stripping a module of custom VBA code from a designer workbook. This may be of grave concern to others, but it is not an issue currently for anything I’m working on.
I hope I haven’t confused the issue further…
Dear Nathan,
Thanks for your so many contributions to this forum!
We hope the forums can be really active so any post is welcome. The more the better!
Sorry Nathan, currently Aspose.Excel doesn’t allow you to read or write designer files with VBA and Addin.
So based this fact, would you like to reconsider your needs and then tell me where the problem is.
By the way, we’re working on VBA and Addin and hopefully these features will be available within 4-8 weeks.
I did some homework and determined that I will distribute my custom functions through an Excel Add-In file (.XLA) that the user will enable in their copy of Excel. This way, the functions will always be available regardless of whether the ASP.NET application is pushing a designer-based workbook or a dynamically generated workbook down to the user.
The problem I am still facing, however, is that I cannot get Aspose.Excel to properly handle formulas that involve either the SUBTOTAL function or a custom function. If I assign the formula string to Cell.Formula, the formula nor the value that should result from the formula appear in the rendered spreadsheet. If I assign the formula string via Cell.PutValue, then the formula string shows up as a text value in the cell it was assigned to in the rendered spreadsheet. I can put the cursor in the cell, then click the mouse pointer in the Formula Bar, press the Enter key, and Excel then treats the formula string as a real formula, calculates the value, and shows the numeric value in the cell. This proves to me that the formula is correct; Aspose.Excel is just doing something inexplicable with the formula string when it is assigned to the Cell.Formula property.
I detailed this problem in another thread,“Problem with SUBTOTAL formula”, so to avoid further confusion, I should probably drop this discussion about the SUBTOTAL/custom function problem in this thread and let it play out in the original thread in which I raised the issue.
I apologize for any confusion I may have caused.
Dear Nathan,
Yes we’re investigating the thread,“Problem with SUBTOTAL formula”.
We will send you a fixed dll if it is a bug.
Dear Nathan,
Good news!
We add a new Cell.SetAddInFormula to allow you to porgammatically use custom formulas from an Excel Add-in. Please download the latest hot fix and then replace installed Aspose.Excel.dll with it.
This is the declaration of Cell.SetAddInFormula:
///
/// Sets an Add-In formula to the cell.
///
/// Add-In file name.
/// Add-In function name.
///
/// Add-In file should be placed in the directory or sub-directory of Excel Add-In library.
/// For example, file name can be “Eurotool.xla” or “solver\solver.xla”.
///
public void SetAddInFormula(string addInFileName, string addInFunction)
This the example to use Cell.SetAddInFormula:
[Visual Basic]
Dim excel As Excel = New Excel()
excel.Worksheets.Add()
excel.Worksheets.Add()
Dim cell As Cell = excel.Worksheets.GetAt(0).Cells.GetAt(“C2”)
cell.SetAddInFormula(“eurotool.xla”, “=euroconvert(1.20,"DEM","EUR")”)
cell = excel.Worksheets.GetAt(0).Cells.GetAt(“d3”)
cell.SetAddInFormula(“eurotool.xla”, “=euroconvert(1.9,"DEM","EUR")”)
cell = excel.Worksheets.GetAt(0).Cells.GetAt(“e4”)
cell.SetAddInFormula(“HRVSTTRK.xla”, “=pct_overcut(A10:A12)”)
cell = excel.Worksheets.GetAt(2).Cells.GetAt(“e5”)
cell.SetAddInFormula(“eurotool.xla”, “=euroconvert(120,"DEM","EUR")”)
cell = excel.Worksheets.GetAt(2).Cells.GetAt(“e6”)
cell.SetAddInFormula(“solver\solver.xla”, “=Formname()”)
excel.Save(“AddinAPI.xls”, SaveType.OpenInExcel, FileFormatType.Default, Me.Response)
[C#]
Excel excel = new Excel();
excel.Worksheets.Add();
excel.Worksheets.Add();
Cell cell = excel.Worksheets.GetAt(0).Cells.GetAt(“C2”);
cell.SetAddInFormula(“eurotool.xla”, “=euroconvert(1.20,"DEM","EUR")”);
cell = excel.Worksheets.GetAt(0).Cells.GetAt(“d3”);
cell.SetAddInFormula(“eurotool.xla”, “=euroconvert(1.9,"DEM","EUR")”);
cell = excel.Worksheets.GetAt(0).Cells.GetAt(“e4”);
cell.SetAddInFormula(“HRVSTTRK.xla”, “=pct_overcut(A10:A12)”);
cell = excel.Worksheets.GetAt(2).Cells.GetAt(“e5”);
cell.SetAddInFormula(“eurotool.xla”, “=euroconvert(120,"DEM","EUR")”);
cell = excel.Worksheets.GetAt(2).Cells.GetAt(“e6”);
cell.SetAddInFormula(@“solver\solver.xla”, “=Formname()”);
excel.Save(“AddinAPI.xls”, SaveType.OpenInExcel, FileFormatType.Default, this.Response);
Bravo!
It works!
Thank you for the great work you have done to make this feature available.
Dear Nathan,
As a reminder, please consult the following posts:
Enterprise Edition
and
Licensing question
if you decide to buy Aspose.Excel as License Types and Edition Types have been updated recently.
Hi,
I tried the same procedure. I need to use ANALYSIS\ATPVBAEN.XLA Add-in to use the Yield formula.
I added the add in C:\Program Files\Microsoft Office\Office\Library directory
And used the following code. But it does not populate the formula at all. Instead, the cell is empty. pls help!!!
cell.SetAddInFormula("ANALYSIS\ATPVBAEN.XLA", "=YIELD("05/09/2006","03/12/2007",0.0539000000,101.2945148936170213,100,4,0)")
Thanks in advance,
Pradeepac
Please try this attached version.
Hi,
Thanks for your help. I tried with the latest dll.
It creates the excel file but when I try to open it, I get the error as atatched.
Thanks
Pradeepac
Hi Pradeepac,
Thanks for considering Aspose.
Yes, there looks some problem with the AddInFormula. We will resolve your issue soon.
Thanks for your patience.
Regards
Amjad Sahi
Aspose Nanjing Team
Hi,
This formula works if I use it in a seperate excel sheet.
It does not work when it has to be generated using aspose.
Thanks
Pradeepac
Hi Pradeepac,
Could you please post the output file here generated by Aspose.Cells, so that we may find and resolve the problem, Thank you.
Hi,
Here it is. I am not even able to open the file.
Thanks
Pradeepac
Hi Pradeepac,
Thanks for the file.
We will figure out and resolve the your issue soon.
Thank you
Dear Pradeepac,
Please try the attached fix.
Thank you.
Hi,
I tried with the new dll. It still does not work and gives the same problem as before.
Thanks
Pradeepac