View Full Version : [SOLVED:] VBA function - Inputting multiple cells instead of a complete range of cells?
JimmyJimmy
02-24-2020, 05:06 PM
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
02-24-2020, 05:48 PM
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
02-24-2020, 06:30 PM
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
02-24-2020, 06:41 PM
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
02-24-2020, 06:58 PM
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
02-24-2020, 07:39 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.