I'm continuing to get a "CellExceptions" thrown by the component when I'm pushing a string form a forumla. This is only happening when the string is very long or has many arguments for the function that I'm trying to call.
I've chased this down to the number of arguments. In Excel 2003, the limit of number of arguments is 30, while in Excel 2007 it is 255. My question is how does the system know which format you are working to when you create a blank workbook in memory? How are the different versions being handled?
In my application, if I capture the string that forms the formula and handle the exception to not place the summary ( =SUM(A1,B2,C3, ..... AB31)) and apply this same formula to the final spreadsheet which is saved to disk (Excel 2007), it works just fine. But if the file format is 2003, it does not work fine, due to the limitiation of 30 arguments.
It seems as if the component is not handling the versions properly, or I simply just don't know how to get it into one mode or another depending on desired format level.
The following is the method that I'm creating the new workbook.
' create the basic workbook
Dim workbook As Workbook = New Workbook()
workbook.Worksheets.Add()
Dim worksheet As Worksheet = workbook.Worksheets(0)
Thank you for the fix, I have not tried it as of yet. I do however have a question related to this fix prior to my use of this. I submitted my question on 11/30/2009 in the early morning here in the US. However, I note that your fix is dated 11/27/2009, three days before my query. How is this possible? Was this fix already incorporated for someone else, if so, I would be very interested in the link to the posting where they describe their problem, to see if it is simlar.
Back to my original question, is the object model agnostic with respect to the workbook being of a certain type (97,2000, 2003, etc...) and subject to their limitations? Just seems to me that when we open a workbook or create a new workbook, we should be limited to those bounds as found in Excel for that particular version, where the only release of these constraints is in the case that we decide to save in a later version.
a)There are some formulas which contains more than 30 Arguments in the template file of <A href="</A>. So we removed limits in this fix.</P>
<P>b)In this fix, we just simply removed this limits to work as MS Excel 2007.We will look into how to add limits for particular Excel version.</P>
When you set a formula with more than 30 arguments at run time, we don't know if you want to create an Excel file in Excel2007 or Excel97-Excel2003 format. So we just accept them. When you save the file and specify the file format, we can use two approaches to accomodate this issue:
1. Just write the formula to file and ignore the version.
2. Check the number of arguments depending on the version and throw exception when the number of arguments exceeds the limits of Excel2003.
Second approach is elegant but it degrades the peformance because we have to check all formula again to verify if its arguments exceeds 30. In most cases there is no need to check it because most of formulas won't exceed it and I think our users can handle it when they know the limits.
What's your suggestion and preferred way to hand this limits?
Firstly, this discussion is not limited to that of the formula arguments, it is also effective on other areas within Excel that are affected by the significant changes in 2007, such as formula length, sorting levels, formula nesting levels, unique cell sytles in a workbook, row and column limits, etc....
I prefer option 3 which is when you create the object, you tell it the version that is being targeted and as you go along, the system takes care of the limits internally as you do things. This would also need to apply to opening files as well, where you read the file and determine the compatibility level.
I know that this is more complicated, but think you'll have to do this, as this is going to be a common thing moving forward.
If I can't have 3, I'd prefer to have 2, as this would allow me to trap it, rather than let the user handle it, which is what I think will be very important to all developers.