VBA function - Inputting multiple cells instead of a complete range of cells?

Hi everyone,

I have a vba function that returns the logarithmic average of a selected range of cells.

I want to be able to pick individual cells from across the worksheet as inputs to the function, instead of having to select a continuous range of cells.

Here is the current script. Any help would be greatly appreciated...

Code:

---

Function logsum(rngValues As Variant) As Variant
    With Application.WorksheetFunction
   
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
       
        Dim SumValues As Double
        Dim Antilog As Double
        Dim rngLoop As Variant
   
        SumValues = 0
        Antilog = 0
       
    'Add the antilogs of the values in the range
       
        For Each rngLoop In rngValues
            Antilog = .Power(10, 0.1 * rngLoop.Value)
            SumValues = SumValues + Antilog
        Next


    'Perform logarithmic average
       
        logsum = 10 * .Log10(SumValues)
       
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
       
    End With
End Function

---

Cheers,
Jimmy.

You can use ParamArray

Attachment 26077

Code:

---

Option Explicit


Function LogSum(ParamArray rngValues()) As Variant
        Dim SumValues As Double
        Dim Antilog As Double
        Dim rngLoop As Range
        Dim i As Long
   
   
    With Application.WorksheetFunction
       
        SumValues = 0#
        Antilog = 0#
       
        'do each piece of range passed
        For i = LBound(rngValues) To UBound(rngValues)
       
            'Add the antilogs of the values in the range
            For Each rngLoop In rngValues(i).Cells
                Antilog = .Power(10, 0.1 * rngLoop.Value)
                SumValues = SumValues + Antilog
            Next
        Next i




        'Perform logarithmic average
      LogSum = 10 * .Log10(SumValues)
       
    End With
End Function

---

That's great Paul, a massive help!

That function uses log addition. I also want to adapt that code to do log averaging which is the same calc but divides the summed Antilog values by the number of values passed to the function. Do you know how I can count the number of cells passed to the function? My clumsy attempt below:
[Option Explicit

Function logave(ParamArray rngValues()) As Variant
Dim SumValues As Double
Dim CountValues As Double
Dim Antilog As Double
Dim rngLoop As Range
Dim i As Long

With Application.WorksheetFunction

SumValues = 0#
Antilog = 0#


'do each piece of range passed
For i = LBound(rngValues) To UBound(rngValues)

'Add the antilogs of the values in the range
For Each rngLoop In rngValues(i).Cells
Antilog = .Power(10, 0.1 * rngLoop.Value)
SumValues = SumValues + Antilog
Next
Next i

'Count number of values in range
CountValues = rngValues(i).Count


'Perform logarithmic average
logave = 10 * .Log10(SumValues / CountValues)

End With
End Function]

Thanks in advance...

James.

1. Glad it helped

2. To enter CODE tags, use the [#] and then paste the macro between the



[CODE]

...macro


[/CODE]




3. Maybe

Code:

---


Option Explicit


Function LogAvg(ParamArray rngValues()) As Variant
        Dim SumValues As Double
        Dim Antilog As Double
        Dim rngLoop As Range
        Dim i As Long, CountValues As Long
   
   
    With Application.WorksheetFunction
       
        SumValues = 0#
        Antilog = 0#
        CountValues = 0
       
        'do each piece of range passed
        For i = LBound(rngValues) To UBound(rngValues)
       
            'Add the antilogs of the values in the range
            For Each rngLoop In rngValues(i).Cells
                Antilog = .Power(10, 0.1 * rngLoop.Value)
                SumValues = SumValues + Antilog
            Next
       
            CountValues = CountValues + rngValues(i).Cells.Count
        Next i


        'Perform logarithmic average
      LogAvg = 10 * .Log10(SumValues / CountValues)
       
    End With
End Function

---

That's got it, you're a champion.

I'll post code in the correct manner in future, sorry about that.

Thanks again Paul,

James.

Quote:

---

Originally Posted by JimmyJimmy

That's got it, you're a champion.

I'll post code in the correct manner in future, sorry about that.

Thanks again Paul,

James.

---

Glad I could help

Don't worry, we like the CODE tags because they set off the macro and does a little formatting