Well, you may simply loop through the Array and get the values of every index one by one. Please see the following sample code in this regard:
Function CalculateCustomFunction(ByVal functionName As String,
ByVal paramsList As System.Collections.ArrayList,
ByVal contextObjects As System.Collections.ArrayList) As Object Implements Aspose.Cells.ICustomFunction.CalculateCustomFunction
Dim firstParamB1 As Decimal = CType(paramsList.Item(0), Decimal)
Dim secondParamC1C25 As Array = CType(paramsList.Item(1), Array)
Dim thirdParamAnyString As String = paramsList.Item(2).ToString()
' get every item value of second parameter
For Each s As Object In secondParamC1C25
Debug.Print(s(0).ToString())
Next
Return firstParamB1
End Function
Thanks for the reply. I'm also wondering about a few more things that i cant seem to find in the Help guide.
1. Assign all the custom functions to the Workbook when it is opened. So when you enter or edit a cell in the griddesktop it will fire off the custom functions.
2. If one of the parameters is a range name, check to see if the range exists in the Workbook.
3. How to get the address of a range. In Excel you just use Range.Name.Name
4. How to get what sheet a range belongs to. In Excel you use Range.Worksheet.Name
I have over a dozen UDF's in the spreadsheets that I'm opening with your component. Some with SQL server data connections and some are very simple.
If the spreadsheet component loads the document and its macro's properly, then I probably wouldn't need to write custom functions. Would this be correct.
Aspose.Cells supports preserving macros in the template files. This means that it will copy the macros from the template file to the resultant file. Aspose.Cells does not support manipulating the macros in a template file. So, if you only want to preserve the macros as they are, then you may not require writing the UDFs.
So as long as my user-defined functions work in the excel file, they all should work after the file has been loaded into aspose.cells. Would this be correct? I just want to make sure because its kind of hard to verify all the called functions are working properly considering i have no way to view it after being loaded. Keep in mind that these files are huge with a lot of calls to the functions.
As I explained in my previous post, Aspose.Cells only preserves macros / VBA’s. So, if your file has macros / VBA’s, those will be loaded and will be saved (as it is) when you save the file after processing. You will not be able to manipulate / run those macros in the code but they will work fine when you run them using MS Excel.
When you said "manipulate" as of a prior post I didn't realize you meant that the macro's would not run. So I basically have no choice but to re-write the macro's as Custom Functions if I want them to run within the Loaded file?
Also, do you think in a future release we may be able to get the Address and Sheet name from the parameters passed in to a Custom Function? That would be great if you had a work around for accomplishing this.
troy1:
When you said “manipulate” as of a prior post I didn’t realize you meant that the macro’s would not run. So I basically have no choice but to re-write the macro’s as Custom Functions if I want them to run within the Loaded file?
Well, if you want to manipulate the macros / VBAs in your code after loading the template file, you will need to write the custom functions.
troy1:
Also, do you think in a future release we may be able to get the Address and Sheet name from the parameters passed in to a Custom Function? That would be great if you had a work around for accomplishing this.
We have added your requested feature in our internal issue tracking system with issue id: CELLSNET-14928. We will look into the feasibility of this feature after completing some important tasks on hand.
troy1: Also, do you think in a future release we may be able to get the Address and Sheet name from the parameters passed in to a Custom Function? That would be great if you had a work around for accomplishing this.
Well, you may use the third parameter “contextObjects” of the CustomFunction to get the information regarding Workbook, Worksheet and Cell. Please see the API details as below:
/// <summary>
/// Calculates the result of custom function.
/// </summary>
/// <param name="functionName">Custom function name, such as "MyFunc1".</param>
/// <param name="paramsList">A list of parameters value for custom functions.</param>
/// <param name="contextObjects">A list of context objects.</param>
/// <returns>Result of custom function.</returns>
/// <remarks>
/// Currently there are 3 fixed context objects and some variable context objects:
/// 1. Current Workbook object.
/// 2. Current Worksheet object.
/// 3. Current Cell object.
/// Others are custom function parameters text.
/// If a custom function name is not supported, please return a null reference.
/// </remarks>
object CalculateCustomFunction(string functionName, ArrayList paramsList, ArrayList contextObjects);
Thanks for the great reply. The third parameter does indeed return the information I was looking for. What I am wondering is how can you Bind the GridDesktop Control to the WorkBook object? Can this be done? I am only doing this for seeing my results as there are a lot of functions being fired off and I would like to view them immediately to see if everything is working.
Well, we are not very clear about your requirement. Please provide us some more details about your requirement. Also, GridDestop supports custom function too and the only way of loading the workbook to GridDesktop is to save the workbook to the file and then load the file to GridDesktop.
Thank you and best regards.
Cookie Notice
To provide you with the best experience, we use cookies for personalization, analytics, and ads. By using our site, you agree to our cookie policy.
Enables storage, such as cookies, related to analytics.
Sets consent for sending user data to Google for online advertising purposes.
Sets consent for personalized advertising.
Cookie Notice
To provide you with the best experience, we use cookies for personalization, analytics, and ads. By using our site, you agree to our cookie policy.
More info
Enables storage, such as cookies, related to analytics.
Enables storage, such as cookies, related to advertising.
Sets consent for sending user data to Google for online advertising purposes.
Sets consent for personalized advertising.
Cookie Notice
To provide you with the best experience, we use cookies for personalization, analytics, and ads. By using our site, you agree to our cookie policy.
More info
Enables storage, such as cookies, related to analytics.
Enables storage, such as cookies, related to advertising.
Sets consent for sending user data to Google for online advertising purposes.