Function that uses a variable number of arguments

[microbebrew]

Hello!

Long time reader, first time poster. I have been able to write some very time-saving functions and macros from advice in this forum, and I really appreciate it. Now, I have a question that I don't know how to search for, and I hope someone can give me a hand.

I wrote a (very inelegant!) function to calculate the log difference plus/minus standard deviation for sets of triplicate samples. As it is, the function works perfectly, as long as I have exactly six arguments for it. I don't know enough about the terminology to search for exactly what I am trying to do. I would like to set up my function to accept maybe 5 arguments, or maybe 20, depending on the situation. I just don't know how to make it more dynamic like, say, the built in AVERAGE function: =Average(number1, [number2],...)

Like I said, the logic and the math all work the way they are supposed to. If anyone could point me in the right direction, I would really appreciate it!

Here is my existing function:

[VBA]
Function logred(U1, U2, U3, T1, T2, T3)


Dim logU1
Dim logU2
Dim logU3
Dim logT1
Dim logT2
Dim logT3
Dim geoU
Dim geoT
Dim varU
Dim varT
Dim SD
Dim logdiff
Dim rd








If T1 = "<10" And T2 <> "<10" And T3 <> "<10" Then
T1 = 10
ElseIf T1 <> "<10" And T2 = "<10" And T3 <> "<10" Then
T2 = 10
ElseIf T1 <> "<10" And T2 <> "<10" And T3 = "<10" Then
T3 = 10
ElseIf T1 = "<10" And T2 = "<10" And T3 <> "<10" Then
T1 = 10
T2 = 10
ElseIf T1 = "<10" And T2 <> "<10" And T3 = "<10" Then
T1 = 10
T3 = 10
ElseIf T1 <> "<10" And T2 = "<10" And T3 = "<10" Then
T2 = 10
T3 = 10
ElseIf T1 = "<10" And T2 = "<10" And T3 = "<10" Then
T1 = 10
T2 = 10
T3 = 10
End If

If T1 = "<1" And T2 <> "<1" And T3 <> "<1" Then
T1 = 1
ElseIf T1 <> "<1" And T2 = "<1" And T3 <> "<1" Then
T2 = 1
ElseIf T1 <> "<1" And T2 <> "<1" And T3 = "<1" Then
T3 = 1
ElseIf T1 = "<1" And T2 = "<1" And T3 <> "<1" Then
T1 = 1
T2 = 1
ElseIf T1 = "<1" And T2 <> "<1" And T3 = "<1" Then
T1 = 1
T3 = 1
ElseIf T1 <> "<1" And T2 = "<1" And T3 = "<1" Then
T2 = 1
T3 = 1
ElseIf T1 = "<1" And T2 = "<1" And T3 = "<1" Then
T1 = 1
T2 = 1
T3 = 1
End If

logU1 = Application.WorksheetFunction.Log(U1)
logU2 = Application.WorksheetFunction.Log(U2)
logU3 = Application.WorksheetFunction.Log(U3)
logT1 = Application.WorksheetFunction.Log(T1)
logT2 = Application.WorksheetFunction.Log(T2)
logT3 = Application.WorksheetFunction.Log(T3)


If logT1 = 0 Then logT1 = 0.0001
If logT2 = 0 Then logT2 = 0.0001
If logT3 = 0 Then logT3 = 0.0001








geoU = Application.WorksheetFunction.GeoMean(logU1, logU2, logU3)
geoT = Application.WorksheetFunction.GeoMean(logT1, logT2, logT3)


varU = Application.WorksheetFunction.VarP(logU1, logU2, logU3)
varT = Application.WorksheetFunction.VarP(logT1, logT2, logT3)


SD = Sqr((varU / 3) + (varT / 3))






logdiff = geoU - geoT
rd = 3 - Len(Int(logdiff))


logdiff = Application.WorksheetFunction.Round(logdiff, rd)
SD = Application.WorksheetFunction.Round(SD, rd)
logred = Application.WorksheetFunction.Text(logdiff & " ± " & SD, "#,##")




End Function
[/VBA]
Thanks in advance for any advice!

Mat

[mancubus]

hi.

it is "Optional Parameters."

http://www.cpearson.com/excel/writin...ionsinvba.aspx
http://www.cpearson.com/excel/Option...rocedures.aspx

google result
https://www.google.com.tr/webhp?clie...nal+parameters

[GTO]

Greetings,

You might also like to look at ParamArray.

Option Explicit
  
Sub Example()
  
  With Sheet2
    MsgBox ConcatenateStuff(.[A1])
    MsgBox ConcatenateStuff(.[A1], .[A2], .[A3], .[A4], .[A5])
  End With
  
End Sub
  
Function ConcatenateStuff(ParamArray MyValues() As Variant) As String
Dim sMyString As String
Dim lIndex As Long
  
  For lIndex = 0 To UBound(MyValues, 1)
    sMyString = sMyString & Chr$(32) & MyValues(lIndex)
  Next
  
  ConcatenateStuff = Trim$(sMyString)
  
End Function

This of course does not handle what we should do based on how many elements are present.

Mark

[mancubus]

i would use;
T1 < 10 rather than T1 = "<10"
T2 >= 10 rather than T2 <> "<10"

[mancubus]

i'm not sure i understand correctly but it seems, in the first If-End If block, if the value of any T parameter's value is less than 10 you make it equal to 10. (1 in the second block)

if this is the case If blocks can be simplified as below:

    If T1 < 10 Then T1 = 10
    If T2 < 10 Then T2 = 10
    If T3 < 10 Then T2 = 10
    
    If T1 < 1 Then T1 = 1
    If T2 < 1 Then T2 = 1
    If T3 < 1 Then T3 = 1

another point, assume T1 is 0.5
both <10 and <1 conditions are met.
the code makes T1 first 10 then 1.

[Paul_Hossler]

Only commenting on the Calling technique -- Didn't look at the logic

Sample usage

Option Explicit

'Need to change assumed order: Function logred(U1, T1, U2, T2, U3, T3, ....)

Function logred(ParamArray DataPairs()) As Variant
    Dim i As Long
    Dim dblTemp As Double
    
    For i = LBound(DataPairs) To UBound(DataPairs) - 1
        dblTemp = dblTemp + DataPairs(i) * DataPairs(i + 1)
    Next I
    
    logred = dblTemp
End Function


Sub drv()
    MsgBox logred(1.1, 2.2)
    MsgBox logred(1.1, 2.2, 3.3, 4.4)
    MsgBox logred(1.1, 2.2, 3.3, 4.4, 5.5, 6.6)
    MsgBox logred(1.1, 2.2, 3.3, 4.4, 5.5, 6.6, 7.7, 8.8)
End Sub

[GTO]

@Paul:

Kudos! Much nicer example :-)

Mark

[Paul_Hossler]

@Mark --

[BLUSH]

Thanks, high praise indeed

[/BLUSH]

[microbebrew]

All -
Thanks for the very quick replies! It looks like I will have to spend some time researching optional parameters today.

mancubus -Thanks for the suggestions. We actually use "<10" and "<1" as values, which Excel doesn't recognize, hence the change to 10 and 1, respectively.
Paul - will give it a try, thanks!

Thanks again for the help and suggestions. I will let you know what I come up with.