How to build an open-ended Excel VBA Function that allows multiple input variables (arg1, arg2, arg3, arg4, arg5)

Question

I'm trying to make a simple averaging function that does a bit of extra data processing.

The problem I'm facing is that I can currently work with a range input with my code (AvgFunc(rng as Range)), however when selecting multiple inputs ([In a cell] =AvgFunc(A1, B8, C22)) it breaks.

So I decided instead of making a universal function code, I wrote a separate code to deal with the problem

Public Function AvgFunc_Selection(arg1 as Double, arg2 as Double, arg3 as Double, arg4 as Double, arg5 as Double)

However I'm obviously doing something wrong because I cannot get beyond that point.

Any suggestions on how to make a function that works with multiple inputs?

Here is the full code:

Public Function AvgFunc_Selection(arg1 As Double, arg2 As Double, arg3 As Double, arg4 As Double, arg5 As Double)

Dim temp1 As Double
Dim temp2 As Double
Dim Count As Integer

temp1 = arg1
If temp1 > 0 Then
temp2 = temp2 + temp1
Count = Count + 1
End If
temp1 = arg2
If temp1 > 0 Then
temp2 = temp2 + temp1
Count = Count + 1
End If
temp1 = arg3
If temp1 > 0 Then
temp2 = temp2 + temp1
Count = Count + 1
End If
temp1 = arg4
If temp1 > 0 Then
temp2 = temp2 + temp1
Count = Count + 1
End If
temp1 = arg5
If temp1 > 0 Then
temp2 = temp2 + temp1
Count = Count + 1
End If

AvgFunc_Selection = temp2 / Count

End Function

Added full code. Yes it looks like a simple AverageIf function, but it's the basis for a larger project. — DiskBiskit, Jul 29 '20 at 02:27
Pass it an array instead of individual variables, and then the routine can check the bounds of the array. — braX, Jul 29 '20 at 02:44
I can't I have to pass it individual variables. The excel variables I'm selecting always change and my function has to adapt to those changes. My current code uses an array, I need to find a way to deal with multiple variables without an array. I wish excel would show the code behind the WorksheetFunction.average() function (=average() in excel cells) — DiskBiskit, Jul 29 '20 at 02:49
I can think of no good reason why, as that makes little sense... your only other option would be optional arguments as explained here: https://stackoverflow.com/questions/28770412/vba-function-optional-parameters — braX, Jul 29 '20 at 02:52

score 2 · Answer 1 · answered Jul 29 '20 at 07:08

This can be done much simpler using ParamArray and a loop, allowing an "endless" series of arguments:

Public Function AvgFunc_Selection(ParamArray Args() As Variant) As Double

    Dim Index   As Integer
    Dim Count   As Integer
    Dim Value   As Double
    Dim Average As Double
    
    If UBound(Args) = -1 Then
        ' No arguments passed.
    Else
        ' Calculate the count.
        Count = 1 + UBound(Args) - LBound(Args)
        ' Calculate the sum of values.
        For Index = LBound(Args) To UBound(Args)
            If IsNumeric(Args(Index)) Then
                Value = Value + CDbl(Args(Index))
            End If
        Next
        ' Calculate the average.
        Average = Value / Count
    End If
    
    AvgFunc_Selection = Average

End Function

score 0 · Answer 2 · answered Jul 29 '20 at 03:34

So apparently my problem was not including the term "Optional" in front of each argument.

Here's the corrected code:

Public Function AvgFunc_Selection(Optional arg1 As Double, Optional arg2 As Double, Optional arg3 As Double, Optional arg4 As Double, Optional arg5 As Double)

Dim temp1 As Double
Dim temp2 As Double
Dim Count As Integer

temp1 = arg1
If temp1 > 0 Then
temp2 = temp2 + temp1
Count = Count + 1
End If
temp1 = arg2
If temp1 > 0 Then
temp2 = temp2 + temp1
Count = Count + 1
End If
temp1 = arg3
If temp1 > 0 Then
temp2 = temp2 + temp1
Count = Count + 1
End If
temp1 = arg4
If temp1 > 0 Then
temp2 = temp2 + temp1
Count = Count + 1
End If
temp1 = arg5
If temp1 > 0 Then
temp2 = temp2 + temp1
Count = Count + 1
End If

AvgFunc_Selection = temp2 / Count

End Function

That should work. It works for my application.

Aren't you misinterpreting your own question asking how to build an **opend-ended** function allowing **multiple** input variables? It would be good use to accept @Gustav 's answer by ticking the green checkmark as it seems to be the right solution :-) — T.M., Jul 29 '20 at 14:12

How to build an open-ended Excel VBA Function that allows multiple input variables (arg1, arg2, arg3, arg4, arg5)

2 Answers2