Param Array issues with input

Question

So I've been trying to create a function to add together decibels in VBA. I've tried to use ParamArray but I'm having trouble with information processing. Ideally, it would work as SUM() and just take all of the inputs and throw it in a formula and boom.

Right now, I have:

Function DBADD3(ParamArray nums()) As Double
Dim DBPrTot As Variant
'this will be input into the log function at the end

DBPrTot = 0
'initializing value for use in for loop
For i = LBound(nums) To UBound(nums)
    DBPrTot = DBPrTot + 10 ^ (nums(i) / 10)
    'all of the values gathered from ParamArray are being input into this running total

Next i

DBADD3 = 10 * WorksheetFunction.Log10(DBPrTot)
'throwing the DBPrTot running value into our end equation

End Function

I'd like it to take an input like DBADD3(A1:A3,A5,A7) and still produce something. Can someone help?

basodre · Accepted Answer · 2016-04-28 17:32:51Z

4

So, you need to be aware of the type of information that's being passed in. In the prior example from your other post, numbers were being passed in directly, so their value could be easily accessed. In this case, you are passing in an array of ranges (both groups of ranges and individual cells). So you have to loop over each array to pull the range group, then loop over the range to get individual values.

See below for an example of a basic SUM style function:

Function ReturnValues(ParamArray args()) As Double
    Dim dRunningTotal As Double

    dRunningTotal = 0

    For i = LBound(args) To UBound(args)
        For Each cel In args(i)
            dRunningTotal = dRunningTotal + cel.Value
        Next cel
    Next i

    ReturnValues = dRunningTotal
End Function

EDITED TO ADD: You can test for the type of the parameter so that you can protect against throwing errors, and also to handle circumstances where you may have both Range and Values in the parameters, ie =ReturnValues(A1:A10, 7, 9, B1:B4)

Function ReturnValues(ParamArray args()) As Double
    Dim dRunningTotal As Double

    dRunningTotal = 0

    For i = LBound(args) To UBound(args)
        If TypeName(args(i)) = "Range" Then
            For Each cel In args(i)
                dRunningTotal = dRunningTotal + cel.Value
            Next cel
        Else
            dRunningTotal = dRunningTotal + args(i)
        End If
    Next i

    ReturnValues = dRunningTotal
End Function

answered Apr 28, 2016 at 17:32

basodre

5,7901 gold badge18 silver badges25 bronze badges

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

3 Comments

tyg Over a year ago

Please use a For Each vArg In args loop instead of a simple For loop with a counter, declare the variable cel, use TypeOf args(i) Is Range instead of TypeName and test if IsNumeric(cel.Value) before adding to dRunningTotal. Otherwise this is a good answer, +1.

T. Heng Over a year ago

Hey @user3561813 I have another addendum. This works well BUT when the value is 0, it's coming up weird. I'd like to skip all iterations that include 0 using an if statement or something. What do you think? I think something along the lines of If args(i) = 0 Then dRunningTotal = dRunningTotal

basodre Over a year ago

@T.Heng There's nothing wrong with adding your own business logic to the code. It would probably look something like: If cel.Value <> 0 Then dRunningTotal = dRunningTotal + cel.Value

Collectives™ on Stack Overflow

Param Array issues with input

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related