===Amendment 2018-10-08 12:06 CEST ===
(See the comment by the OQ to the answer by @mikekaganski and my answer to this comment.)
This very roughly sketeched demo shows a possible workaround based on global parameters.
To make it reliably usable “in production” would require a lot of programming developing a complete system for the management of global parameter(s) used for the purpose.
In completion of what @Regina wrote: Values from cell ranges and results of array expressions in Calc are always passed as 2D-arrays. You may try this code:
REM p must be an array as it is passed to the function for a range of cells
REM or as the result uf a respective array expression.
REM For rare cases where this may occur, also a single value is accepted:
If Not IsArray(p) Then
Dim h(1 To 1, 1 To 1)
h(1, 1) = p : p = h
REM Now the relevant part calculating the conductivity first.
l1 = Lbound(p, 1) : u1 = Ubound(p, 1)
l2 = Lbound(p, 2) : u2 = Ubound(p, 2)
Dim s As Double
s = 0
For j = l1 To u1
For k = l2 To u2
s = s+ IIf((TypeName(p(j, k))<>"Empty") AND (TypeName(p(j, k))<>"String"),1/p(j,k), 0)
parRes = 1/s
It’s clearly longer than what the OP posted, but it avoids inefficient recursion.
Basic does not allowing for a parameter list of arbitrary length. You can circumvent this to a certain degree with a helper function collecting non-missing optional parameters in an array. It needs a few lines of code per each new introduced parameter position.
===Amendment regarding the comments===
StarBasic comes with a few “commands” and functions allowing for an arbitrary number of parameters.
The one relevant here is the Array() Function. Resricting the aim to a limited but sufficiently high number of allowed parameters you can use it as is demonstrated in the code below. I can think of many variants, enhancements, and specialised modifications.
However, if a function should be useful as a (sub-)expression in Calc formulae there is a serious issue I judge to be a bug: tdf#102381. It prohibits the omission of parameters by leaving unused places. See the demo ask166628variadicRoutines.ods. (To avoid a misunderstanding: The function is not supposed to be used for direct output mainly.) Below the crude code for up to 30 arguments. Use the “(more)” hyperlink to see it completely.
Function prepareArguments( _
Optional p00, Optional p01, Optional p02, Optional p03, Optional p04, _
Optional p05, Optional p06, Optional p07, Optional p08, Optional p09, _
Optional p10, Optional p11, Optional p12, Optional p13, Optional p14, _
Optional p15, Optional p16, Optional p17, Optional p18, Optional p19, _
Optional p20, Optional p21, Optional p22, Optional p23, Optional p24, _
Optional p25, Optional p26, Optional p27, Optional p28, Optional p29)
helper = Array(p00,p01,p02,p03,p04,p05,p06,p07,p08,p09,p10,p11,p12,p13,p14, _
lB = Lbound(helper) : uB = Ubound(helper)
Dim isPresent(lB To uB) As Boolean
k = -1
For j = lB To uB
If NOT IsMissing(helper(j)) Then
k = k + 1
isPresent(j) = True
helper(lB + k) = helper(j)
If k > -1 Then
Redim Preserve helper(lB To lB + k)
prepareArguments = helper
Dim dummy As Object
prepareArguments = dummy