Get Parameter from array of custom function

Aspose.Cells Product Family
1

How do I pull a value from the following customer function parameter?

Dim secondParamC1C25 As Array = CType(paramsList.Item(1), Array)

Dim myelement As String = CType(secondParamC1C25.GetValue(3), String)

Or

For Each s As String In secondParamC1C25

Debug.Print(s)

Next

_____________________________________________________________________

Public Class CustomFunction

Implements Aspose.Cells.ICustomFunction

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()

' perform your processing

Return firstParamB1

End Function

End Class
2

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

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

Thank You & Best Regards,

3

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

Thank you very much for your Help.

Troy

4

Hi Troy,

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

I am afraid your requested features are not supported with Aspose.Cells component at the moment.

Sorry for any inconvenience caused,

5

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.

6

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Aspose.Cells supports to preserve 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 to manipulate the macros in a template file. So, if you only want to preserve the macros as it is then you may not require writing the UDFs.

Thank You & Best Regards,

7

Hello,

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.

Thank you,

8

Hi,

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

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.

Thank You & Best Regards,

9

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.

Thank you,

10

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

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.

Thank You & Best Regards,
11

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

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:

///

/// Calculates the result of custom function.

///

/// Custom function name, such as "MyFunc1".

/// A list of parameters value for custom functions.

/// A list of context objects.

/// Result of custom function.

/// 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.

object CalculateCustomFunction(string functionName, ArrayList paramsList, ArrayList contextObjects);

Hope this will help.

Thank You & Best Regards,

12

Hello Nausherwan,

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.

Thank you very much,.

Troy

13

Hi Troy, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for your feedback.

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 griddestop is to save the workbook to the file and then load the file to GridDesktop.

Thank You & Best Regards,