ParamArray functionality

[Lupp]

I know next to nothing about VBA, and when I occasionally looked into VBA code I got an impression of outdated command formats and ... .
However, when writing user code for Calc I often missed the Excel/VBA feature ParamArray.
Well, there are the options
Option Compatible and
Option VBAsupport 1
but using them changes the interpretation of commands in some cases, and that's very unwelcome if you don't know all the details.

Therefore I wrote years ago a Basic function under VBA support to provide he functionality also in Calc and in UDFs contained in modules without VBA support.
The relevant VBA code is exactly one line: a simple assignment:

Code: Select all

Option VBAsupport 1

Function passToParamArray(ParamArray p As Variant)
passToParamArray = p
End Function

REM End module

But for a help to understand how to use it in modules without VBA support, you should possibly study the attached example with about 10 more lines of Basic.

Of course, I don't assume to be the first one doing it this way, but on the other hand I don't know a post about it.

Sorry! The original attachment had the indices in C10:C13 in wrong order.
Rectified:

paramArrayWithoutFullVBAsupport.ods

(18.22 KiB) Downloaded 4 times

[JeJe]

Just to mention the alternative to a paramarray, of using a sufficient number of optional arguments to encompass any call. Obviously there's a limit at which this becomes impracticable.

[Lupp]

... alternative to a paramarray, of using a sufficient number of optional arguments ...

Is this really an alternative?
You will need to ask one by one (BY NAME left to right or reversely) for each position if the respective optional parameter is missing.
And Calc (not Basic) passes a 0 (Dbl) for each missing parameter.
Compare solutions in this sloppy example:

aoo_113158_ParamArray.ods

(18.82 KiB) Downloaded 6 times

[JeJe]

If you want the array your function produces - then you would use paramarray. I meant generally, for the declaration, the only alternative to a paramarray, as far as I know is optional arguments.

[JeJe]

Digressing...
We can produce an array from optional parameters more simply - missing optional values will be of the empty vartype

Edit: For Basic, its putting aside your point about Calc passing a missing parameter as 0


Edit: with array(p1,p2,p3) then looping from the end for the first non-emply one to redim preserve, if that's desired.

[MrProgrammer]

However, when writing user code for Calc I often missed the Excel/VBA feature ParamArray.

I don't use Excel or VBA, but my understanding is that the ParamArray feature allows one to pass a variable number of parameters to a function as the last argument. For a Calc user-defined-function, I don't need the ParamArray feature since I can just pass variable-length arrays from Calc to a StarBasic function. Perhaps the ParamArray feature is needed because Excel and/or VBA are deficient and can't pass arrays like Calc and StarBasic can.

The array from Calc would often be a cell range, but it could also be an array constant. With a StarBasic function, an array can be passed as any argument, it doesn't have to be the last one. All that's needed is for the user-defined-function to be expecting the array(s). The function can use UBOUND to determine how many items are being passed.

This attachment shows an example of passing 3, 4, or 5 parameters in the second argument of a 3-argument function. This UDF ignores the first and third arguments to demonstrate that it isn't necessary to pass the array at the end of the parameter list.
=ZAVERAGE("Foo";{1;2;0;4;5};"Bar")

This is the function, showing how to handle an array. To keep it simple, focusing on the current topic, I have omitted the extra code to check p2 for a scalar instead of an array, or to check for strings instead of numbers in the array. Those tests can be done with VARTYPE.

202511011524.ods

(12.9 KiB) Downloaded 6 times

Option Explicit
Function ZAVERAGE(p1 As Variant,p2 As Variant,p3 As Variant) As Variant
Rem p1 and p3 are ignored; Returns avarage of non-zero values in p2
Rem If the second argument is an array, it will be a 2-dimensional array
Rem Calc always passes arrays dimensioned (1 To RowCount,1 To ColumnCount)
Dim s As Double : Dim c As Long                       ' Sum and count
Dim i As Long : Dim j As Long                         ' Loop indices
s = 0 : c = 0                                         ' Initialize sum and count
For i = 1 To UBOUND(p2,1)                             ' Outer loop
   For j = 1 To UBOUND(p2,2)                          ' Inner loop
      s = s + p2(i,j)                                 ' Add the value in the row
      If p2(i,j) <> 0 Then c = c + 1                  ' Count non-zero numbers
   Next j                                             ' End inner loop
Next i                                                ' End outer loop
If c = 0 Then ZAVERAGE = "#DIV/0!" : Exit Function    ' No non-zero numbers
ZAVERAGE = s/c                                        ' Return average
End Function

[Villeroy]

For a Calc user-defined-function, I don't need the ParamArray feature since I can just pass variable-length arrays from Calc to a StarBasic function. Perhaps the ParamArray feature is needed because Excel and/or VBA are deficient and can't pass arrays like Calc and StarBasic can.

StarBasic ignores missing parameters as far as they are not referred to. You can pass 10 arguments to a function that takes only 5. The remaining 5 will not raise any error like "mismatch of argument cont".
Other programming languages, including VBA, don't do that. VBA has ParamArray for "all the rest in right order of appearance". The UNO API takes arrays of NamedValues or arrays of PropertyValues for additional arguments.

[JeJe]

This might interest. VBA's default values work with option compatible in OO, named arguments work without it.

https://learn.microsoft.com/en-us/offic ... -arguments

Code: Select all

option compatible

Sub test
OptionalArgs varCountry:="England", strState:="MD" 
OptionalArgs "MD"
End Sub
Sub OptionalArgs(strState As String, Optional varRegion As Variant, Optional varCountry As Variant = "USA") 
msgbox varCountry
End Sub