PDA

View Full Version : Function that uses a variable number of arguments



microbebrew
05-07-2015, 08:50 AM
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:


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

Thanks in advance for any advice!

Mat

mancubus
05-07-2015, 01:41 PM
hi.

it is "Optional Parameters."

http://www.cpearson.com/excel/writingfunctionsinvba.aspx
http://www.cpearson.com/excel/OptionalArgumentsToProcedures.aspx

google result
https://www.google.com.tr/webhp?client=aff-maxthon-newtab&channel=t5&gws_rd=cr,ssl&ei=ZstLVejAC-fD7gag14GoAw#q=excel+vba+optional+parameters

GTO
05-07-2015, 01:50 PM
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
05-07-2015, 01:56 PM
i would use;
T1 < 10 rather than T1 = "<10"
T2 >= 10 rather than T2 <> "<10"

mancubus
05-07-2015, 02:04 PM
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
05-07-2015, 02:21 PM
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
05-07-2015, 05:25 PM
@Paul:

Kudos! Much nicer example :-)

Mark

Paul_Hossler
05-07-2015, 05:57 PM
@Mark --



Thanks, high praise indeed

microbebrew
05-08-2015, 06:41 AM
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.