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
– DiskBiskit, Commented Jul 29, 2020 at 2:27
Pass it an array instead of individual variables, and then the routine can check the bounds of the array. — braX
– braX, Commented Jul 29, 2020 at 2: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
– DiskBiskit, Commented Jul 29, 2020 at 2:49

Gustav · Accepted Answer · 2020-07-29 07:08:30Z

2

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

answered Jul 29, 2020 at 7:08

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DiskBiskit · Accepted Answer · 2020-07-29 03:34:49Z

0

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.

answered Jul 29, 2020 at 3:34

DiskBiskit

253 bronze badges

1 Comment

T.M. Over a year ago

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 :-)

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related