Optional Parameters in UDFs

[pficky]

Hello Everyone,

I'm writing a function to calculate confidence bands for a linear regression of some data. I currently have my function set so that I can choose what range I show my bands over and what % Confidence is used for each fit. As of right now I have successfully made these two parameters optional, however I would like to be able to specify % Without Specifying range. Based on they way I have the order of parameters, to change the percent used I HAVE to specify a range, but I don't want to switch their order and then make the reverse impossible either. If there is any work-arounds for this issue I'd love to know about them! Code below.

Function ConfidenceBand(m As Variant, b As Variant, xvalues As Range, yvalues As Range, Optional Span As Range, Optional Percent As Variant)
'Pre-define variables
    Dim n As Integer
    Dim i As Integer
    Dim tvalue As Variant
    Dim syx As Variant
    Dim average As Variant
    Dim ssx As Variant
    Dim CB() As Variant
    Dim Output() As Variant


'If no span is provided, just use the given x-values
    If Not Span Is Nothing Then
        xrange = Span.Value
    Else
        xrange = xvalues.Value
    End If
    
'Set variables
    x = xvalues.Value
    y = yvalues.Value
    syx = WorksheetFunction.StEyx(y, x)
    average = WorksheetFunction.average(x)
    ssx = WorksheetFunction.DevSq(x)
    n = WorksheetFunction.Count(x)


'Calculate T-Value either from input or assuming 95% Confidence
    If IsMissing(Percent) Then
        tvalue = WorksheetFunction.TInv(0.05, n - 2)
    Else
        tvalue = WorksheetFunction.TInv((100 - Percent) / 100, n - 2)
    End If


'Resize Arrays based on Inputs
    ReDim CB(1 To WorksheetFunction.Count(xrange))
    ReDim Output(1 To WorksheetFunction.Count(xrange), 1 To 2)


'Calculate Outputs
    For i = 1 To UBound(xrange, 1)
        CB(i) = tvalue * syx * (1 / n + (xrange(i, 1) - average) ^ 2 / ssx ^ 2) ^ (1 / 2)
    Next i
    For i = 1 To UBound(xrange, 1)
        Output(i, 1) = m * xrange(i, 1) + b + CB(i)
    Next i
    For i = 1 To UBound(xrange, 1)
        Output(i, 2) = m * xrange(i, 1) + b - CB(i)
    Next i


'Give Answer
ConfidenceBand = Output
End Function

 

[CharlesChuckieCharles]

Maybe I'm not understanding but

=ConfidenceBand(J15,J16,M15:M18,N15:N18,,8)

is ConfidenceBand called without a Span Parameter note comma's before 8)

 

[Rick Rothstein]

I guess you have a few ways you could approach this...

[table="width: 500"]
[tr]
	[td]1) Just omit the range, but keep its comma...

    MsgBox ConfidenceBand(Mval, Bval, Crange, , SomeVal)

    Note the empty spot between commas where the optional range would go. Inside your code, you can test for the range being missing using this...

    If Span Is Nothing Then

    and do whatever you want when that is the case...

2) Use named argument to skip over the preceding optional argument(s)...

    MsgBox ConfidenceBand(Mval, Bval, Crange, Percent:=SomeVal)

    Personally, of the these two, I'd just do the first.

3) Change the data type for the first to Variant and then test what the data type is...

    If Not TypeOf Span Is Range Then
      Percent = Span
      Span = ""
    End If

4) Use a ParamArray instead of Optional arguments and investigate what was passed in...

    Function ConfidenceBand(m As Variant, b As Variant, xvalues As Range, yvalues As Range, ParamArray SpanPercent() As Variant)
      If Not TypeOf SpanPercent(0) Is Range Then 'only a percent was passed in[/td]
[/tr]
[/table]

 

[pficky]

The double commas worked perfectly thanks! I'm new to programming with VBA so I wasn't aware I could do this.