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

[JimmyJimmy]

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...


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.

[Paul_Hossler]

You can use ParamArray

26077



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

[JimmyJimmy]

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.

[Paul_Hossler]

1. Glad it helped

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






...macro







3. Maybe




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

[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.

[Paul_Hossler]

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