using optional arrays as function arguments

[JosenOne]

Hello together,

I am currently working on converting a handful of UDFs from Excel-vba into Excel-DNA addins using VB.net. All functions could be executed error-free in vba. This will not be discussed further here.

The problem to be discussed here is to be demonstrated with the following function:

Various values are passed to the function, including two arrays (RngComponents, RngCompositions) whose passing is to be optional.

At first I tried to call the function as follows:

Function GasHumid_phi(ByVal T As Double, ByVal p As Double, ByVal Y As Double, Optional ByVal RngComponents As Object(), Optional ByVal RngComposition As Object()) As Double

Here I get the error code BC30812 "Optional parameters must specify a default value.
Through various forum entries I ended up with the following display:

Function GasHumid_phi(ByVal T As Double, ByVal p As Double, ByVal Y As Double, Optional ByVal RngComponents As Object() = Nothing, Optional ByVal RngComposition As Object() = Nothing) As Double

The function can be executed with this, but in the case that no array is passed, this is not recognised in the following query:

If RngComponents(0) Is Nothing Then

        M_air = 0.0289583

Else...

However, it is essential that in the case that no array is passed, the variable M_air is assigned the specified value.
When I look at the array in debugging, {Length=1} and the value is given in place (0) with {Excel.Integration.ExcelMissing}.

Adjusting the If query as follows has not brought any improvement:

1. If RngComponents(0) Is "{Excel.Integration.ExcelMissing}" Then
2. If RngComponents(0) Is Excel.Integration.ExcelMissing} Then
3. If RngComponents(0) Is "Excel.Integration.ExcelMissing" Then
4. If RngComponents(0) Is "" Then
5. If RngComponents(0) Is 0 Then

By the way, in the case that occupied arrays are passed, the function runs without errors and delivers the desired result.
I hope that I was able to describe the problem in a comprehensible way. Are there any suggestions for a solution from your side?

[Govert van Drimmelen]

Hi Joseph,

In VB.NET you would use the `TypeOf ... Is ...` operator to do the type check.

If TypeOf value Is ExcelMissing Then ' The value is of type ExcelMissing End If

To support optional values, you should make the parameter type just 'Object' instead of 'Object()'. Then you can inspect the type - it might be `ExcelMissing`, or `Object(,)` or `String` etc. and proceed accordingly.

We also have is a helper library called ExcelDna.Registration that can automate some of this boilerplate code to check for optional parameters etc.

Here are some samples that use it: Samples/Registration.Sample.VisualBasic at master · Excel-DNA/Samples (github.com) , but those samples won't quite work as is - they need some clean up still.

-Govert

[JosenOne]

Hi Govert,

thanks a lot, that works.

I was searching quite a while for a solution.

Kind regards

Joseph

[JosenOne]

I now have the following problem: by declaring the Rng-variables as an object, my row/column read in from Excel becomes a 2-dimensional array. When Rng was declared as Object(), a 1 dimensional array (i.e. vector) was read in. Depending on whether the values in excel are horizontal or vertical in the table, the first row or column of the 2-dimensional array is occupied. This has a significant influence on the further calculations.

Is there a way to bring the values back into 1-dimensional form?

Govert van Drimmelen schrieb am Montag, 2. Oktober 2023 um 12:50:26 UTC+2:

[Govert van Drimmelen]

You’ll have to convert to a 1D array in your code. Here is how ChatGPT suggests you might implement this:

 

Function To1DArray(input As Object(,)) As Object()

    Dim rows As Integer = input.GetLength(0)

    Dim cols As Integer = input.GetLength(1)

    Dim output As Object() = New Object(rows * cols - 1) {}

 

    Dim counter As Integer = 0

    For i As Integer = 0 To rows - 1

        For j As Integer = 0 To cols - 1

            output(counter) = input(i, j)

            counter += 1

        Next

    Next

 

    Return output

End Function

 

-Govert

 

From: 'JosenOne' via Excel-DNA <exce...@googlegroups.com>
Sent: Thursday, October 5, 2023 2:57 PM
To: Excel-DNA <exce...@googlegroups.com>
Subject: [ExcelDna] Re: using optional arrays as function arguments

 

I now have the following problem: by declaring the Rng-variables as an object, my row/column read in from Excel becomes a 2-dimensional array. When Rng was declared as Object(), a 1 dimensional array (i.e. vector) was read in. Depending on whether the values in excel are horizontal or vertical in the table, the first row or column of the 2-dimensional array is occupied. This has a significant influence on the further calculations.

Is there a way to bring the values back into 1-dimensional form?

Govert van Drimmelen schrieb am Montag, 2. Oktober 2023 um 12:50:26 UTC+2:

Hi Joseph,

 

In VB.NET you would use the `TypeOf ... Is ...` operator to do the type check.

 

If TypeOf value Is ExcelMissing Then

    ' The value is of type ExcelMissing

End If

To support optional values, you should make the parameter type just 'Object' instead of 'Object()'. Then you can inspect the type - it might be `ExcelMissing`, or `Object(,)` or `String` etc. and proceed accordingly.

 

We also have is a helper library called ExcelDna.Registration that can automate some of this boilerplate code to check for optional parameters etc.

Here are some samples that use it: Samples/Registration.Sample.VisualBasic at master · Excel-DNA/Samples (github.com) , but those samples won't quite work as is - they need some clean up still.

 

-Govert

 

On Friday, 29 September 2023 at 14:36:50 UTC+2 JosenOne wrote:

Hello together,

 

I am currently working on converting a handful of UDFs from Excel-vba into Excel-DNA addins using VB.net. All functions could be executed error-free in vba. This will not be discussed further here.

The problem to be discussed here is to be demonstrated with the following function:

 

 

Various values are passed to the function, including two arrays (RngComponents, RngCompositions) whose passing is to be optional.

At first I tried to call the function as follows:

 

Function GasHumid_phi(ByVal T As Double, ByVal p As Double, ByVal Y As Double, Optional ByVal RngComponents As Object(), Optional ByVal RngComposition As Object()) As Double

 

Here I get the error code BC30812 "Optional parameters must specify a default value.
Through various forum entries I ended up with the following display:

 

Function GasHumid_phi(ByVal T As Double, ByVal p As Double, ByVal Y As Double, Optional ByVal RngComponents As Object() = Nothing, Optional ByVal RngComposition As Object() = Nothing) As Double

 

The function can be executed with this, but in the case that no array is passed, this is not recognised in the following query:

 

If RngComponents(0) Is Nothing Then

        M_air = 0.0289583

Else...

 

However, it is essential that in the case that no array is passed, the variable M_air is assigned the specified value.
When I look at the array in debugging, {Length=1} and the value is given in place (0) with {Excel.Integration.ExcelMissing}.

Adjusting the If query as follows has not brought any improvement:

1. If RngComponents(0) Is "{Excel.Integration.ExcelMissing}" Then
2. If RngComponents(0) Is Excel.Integration.ExcelMissing} Then
3. If RngComponents(0) Is "Excel.Integration.ExcelMissing" Then
4. If RngComponents(0) Is "" Then
5. If RngComponents(0) Is 0 Then

By the way, in the case that occupied arrays are passed, the function runs without errors and delivers the desired result.
I hope that I was able to describe the problem in a comprehensible way. Are there any suggestions for a solution from your side?

 

 

 

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/743274fe-af74-4d35-90e3-502dd3f609f9n%40googlegroups.com.