Question:
How can I call REGEX(p1,p2, [omitted], p4) from a macro?
Some explanations may be helpful:
Situation:
Some standard functions of Calc have optional parameters.
In some cases there is specified a default (or a conditional default) to be used if the paramter is omitted.
Generally the optional parameters are (only one or) assembled in a sequence at the end of the parameter list.
Concerning a few functions a parameter is declared optional though another parameter following in the list not is optional - or is optional but may be charged nontheless even if a previous optional parameter was omitted).
In very few relevant cases now again there is no default for the omitted parameter.
Namely in the cases of INDEX()
and REGEX()
the omission of such an optional parameter (second in case of INDEX()
, third in case of REGEX()
) functionally is not truely an omission, but a distinct information which not can be passed to the function putting any explicit value in that place.
The distinct information is:
For REGEX(pText; pSearchString; ; pSpecial)
: Return the match using pSpecial as ordina (index).
For INDEX(pRangeRef; ; pColumnIndex)
: Return the whole column as a column-array.
[Now the functionality is respectively the same with omitted pColumnIndex or both index parameters omitted].
Issue:
Trying to call such a function via FunctionAccess
with a distinct omission of an optional parameter I couldn’t find a way to charge the parameters Array()
. Simply omitting a parameter if there is a subsequent one causes an error. Array(pRangeRef, , pColumnIndex)
e.g. first does not broadcast an error but only returns the error value Error 448
for the middle element - which is the best it can do. But the call to INDEX()
then using FunctionAccess
doesn’t evaluate this as intended but throws an error IllegalArgumentException
. It’s respectively the same with REGEX()
.
Relief (kind of):
In case if INDEX()
there are workarounds, and the problem isn’t acute anyway. But in case of REGEX()
the functionality is unique and I only found an unreasonably complicated and slightly unsafe workaround. Therefore the question at top.