period of sum of sine waves

[LB]

I'm trying to create a function to calculate the period of a sum of
sinusoids. This is nothing more than the least common multiple of the
periods of all contributions. I note that worksheet function LCM does
not appear to be available in VBA. But that wouldn't work anyway since
it can't handle real arguments, so I create my own called LCM_real.
Problem with LCM_real is that it only accepts 2 arguments, but that can
be solved through using it in a looping function. I would like this
function to operate like the worksheet one, in that you can place in the
argument up to 29 sine wave periods separated by commas, or you can
enter a range (no commas). I have the following code for comma
delimited argument:

Function PeriodOfSinuSum(ParamArray T() As Variant) As Double
Dim i As Integer, tmp As Double
tmp = LCM_Real(T(0), T(1))
For i = 2 To UBound(T())
tmp = LCM_Real(tmp, T(i))
Next i
PeriodOfSinuSum = tmp
End Function

I note that this function doesn't work if you try entering a range, so I
come up with another one for that purpose:

Function PeriodOfSinuSumRange(T As Range) As Double
Dim i As Integer, tmp As Double
tmp = LCM_Real(T(1), T(2))
For i = 3 To UBound(T())
tmp = LCM_Real(tmp, T(i))
Next i
PeriodOfSinuSumRange = tmp
End Function

This one works with a range, but doesn't work if you input each period
separated by a comma. Can anyone recommend a single function that will
accept both types of input?

 

[Robin Clay]

I don't really understand the background <grin>, but
perhaps a suggestion might solve the actual challenge ?

This is, to create a User Defined Function (UDF) that
will accept both discreet entries or a range - is that
right ?

How about something along the lines of ;

Function myAnswer(Optional myRange as Range, _
Optional myNum1, _
Optional myNum2, _
Optional myNum3, _
Optional myNum4, _
Optional myNum5, _
Optional myNum6, _
Optional myNum7, etc. ... )

Then, if the user is inputting a Range,
she just bangs it in

= myAnswer(F1:J7)

but if you want individual entries,
then you put a comma first,
e.g.
= myAnswer(,F1,J7,Q9,K9)

 

[Bob Kilmer]

Look up ParamArray. There are examples. ParamArray allows a procedure to
accept a variable number of arguments. You'll have to choose a type that is
most appropriate and handle interpretation of the arguments.

 

[LB]

Thanks, Robin. What I wanted boiled down to creating a user defined
function that will accept both discreet entries or a range. I branched
off your idea to the following:

Function PeriodOfSinuSum(T1 As Range, Optional T2 As Range, _
Optional T3 As Range, Optional T4 As Range) As Double
'This function returns the period of a sum of sinusoids
'T1, T2, etc. are the period ranges, up to 4 (or whatever)
Dim i As Integer, Tmp As Double
On Error Resume Next
Tmp = 1
For i = 1 To T1.Cells.Count
Tmp = LCM_Real(Tmp, T1(i))
Next i
For i = 1 To T2.Cells.Count
Tmp = LCM_Real(Tmp, T2(i))
Next i
For i = 1 To T3.Cells.Count
Tmp = LCM_Real(Tmp, T3(i))
Next i
For i = 1 To T4.Cells.Count
Tmp = LCM_Real(Tmp, T4(i))
Next i
PeriodOfSinuSum = Tmp
End Function

This function seems to behave like the worksheet LCM function in that it
can accept one or more ranges or constants separated by commas. It is
an improvement over LCM because it can deal with real numbers. Your
input got me over the hump. Thanks again.