Consulting

Results 1 to 6 of 6

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

  1. #1

    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...
    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.
    Last edited by Paul_Hossler; 02-24-2020 at 05:34 PM. Reason: Added CODE tags

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    You can use ParamArray

    Capture.JPG

    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
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

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

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    1. Glad it helped

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



    [CODE]

    ...macro


    [/CODE]




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

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

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

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by JimmyJimmy View Post
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •