Apply dynamically defined aggregation function on array values

Question

I want apply a user defined aggregation function (string-value from a certain cell, like "SUM" or "STDEV") on an array of values. Here's a simplified example. However I don't know how to make the aggregation (last line):

Sub test()
    Dim values() As Double
    ReDim values(1 To 3)
    values(1) = 3.5
    values(2) = 5
    values(3) = 4.8

    Dim aggregate_fn As String
    aggregate_fn = "SUM"       

    Dim result As Double
    result = Evaluate("=" & aggregate_fn & "(" & values & ")")  ' <-- This doesn't work, but hopefully it's clear what it should do

End Sub

EDIT

My original code is also creating the values array dynamically from a spreadsheet which uses , as decimal sign. This causes an issue with Scott's answer below.

Const datasht = "Daten"
Const aggregate_cell = "G1"

Sub run()
    Dim sht As Worksheet
    Dim n_rows As Integer
    Dim rw As Integer

    Application.DecimalSeparator = "."

    Set sht = ActiveWorkbook.Worksheets(datasht)
    n_rows = sht.Cells(1, 1).CurrentRegion.Rows.Count  ' Get range of data

    Dim values() As String
    ReDim values(1 To n_rows)

    For rw = 1 To n_rows
        values(rw) = sht.Cells(rw, 1).Value
    Next rw
    Debug.Print (aggregate(values))

End Sub

Function aggregate(values() As String)
    ' Get aggregated value
    Dim aggregate_fn As String
    aggregate_fn = ActiveWorkbook.Worksheets(datasht).Range(aggregate_cell).Value
    aggregate = Evaluate("=" & aggregate_fn & "(" & Join(values, ",") & ")") ' <-- doesn't work as intended

End Function

Use Join(values,",") instead of values

Scott Craner
– Scott Craner

2018-04-23 15:33:45 +00:00
Commented Apr 23, 2018 at 15:33 — Scott Craner
– Scott Craner, Commented Apr 23, 2018 at 15:33

Tim Williams · Accepted Answer · 2018-04-23 17:27:04Z

2

This should work for any function available via Application.WorksheetFunction, and is less likely to give an error if you pass too many values.

Sub Tester()

    Dim arr(0 To 1000), res, x As Long, f as String

    'get some values to work with...
    For x = 0 To 1000
        arr(x) = Rnd() * 10
    Next x

    f = "SUM"

    res = CallByName(Application.WorksheetFunction, f, VbMethod, arr)

    Debug.Print res

End Sub

answered Apr 23, 2018 at 17:27

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

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

1 Comment

ascripter Over a year ago

This also works correctly if the application's decimal sign is ,

dwirony · Accepted Answer · 2018-04-23 16:06:46Z

1

I was able to get this to work by creating a string from your array, then using that instead:

Sub test()

    Dim values() As Double
    ReDim values(1 To 3)
    values(1) = 3.5
    values(2) = 5
    values(3) = 4.8

    Dim mystring As String, i As Long
    mystring = values(LBound(values))

    For i = LBound(values) + 1 To UBound(values)
        mystring = mystring & "," & values(i)
    Next i

    Dim aggregate_fn As String
    aggregate_fn = "SUM"

    Dim result As Double
    result = Evaluate("=" & aggregate_fn & "(" & mystring & ")")

    Debug.Print result

End Sub

edited Apr 23, 2018 at 16:06

answered Apr 23, 2018 at 15:45

dwirony

5,4604 gold badges26 silver badges49 bronze badges

1 Comment

dwirony Over a year ago

@LatafaShi Added picture above - cannot reproduce your results.

Scott Craner · Accepted Answer · 2018-04-23 17:18:22Z

1

Join works with Strings change the values to String instead of Double:

Sub test()
    Dim values() As String
    ReDim values(1 To 3)
    values(1) = 3.5
    values(2) = 5
    values(3) = 4.8

    Dim aggregate_fn As String
    aggregate_fn = "SUM"

    Dim result As Double
    result = Evaluate("=" & aggregate_fn & "(" & Join(values, ",") & ")")
    Debug.Print result
End Sub

As per your edit there is no need for the array at all, just pass the range and worksheet to the function and use .Address on the range.

Const datasht = "Daten"
Const aggregate_cell = "G1"

Sub run()
    Dim sht As Worksheet
    Dim n_rows As Integer
    Dim rw As Integer

    'Application.DecimalSeparator = "."

    Set sht = ActiveWorkbook.Worksheets(datasht)
    n_rows = sht.Cells(1, 1).CurrentRegion.Rows.Count  ' Get range of data

    Dim rng As Range
    Set rng = sht.Range(sht.Cells(1, 1), sht.Cells(n_rows, 1))

    Debug.Print (aggregate(sht, rng))

End Sub

Function aggregate(sht As Worksheet, rng As Range)
    ' Get aggregated value
    Dim aggregate_fn As String
    aggregate_fn = sht.Range(aggregate_cell).Value
    aggregate = sht.Evaluate("=" & aggregate_fn & "(" & rng.Address(1, 1) & ")") 
End Function

edited Apr 23, 2018 at 17:18

answered Apr 23, 2018 at 15:55

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

12 Comments

Scott Craner Over a year ago

@LatifaShi not sure why yours is different unless you are using , instead of . that vba requires.

ascripter Over a year ago

Same for me. Join(values, ",") result in 3,5,5,4,8 if you have , as decimal sign. Do you know how to change decimal sign during runtime of the procedure?

Scott Craner Over a year ago

@ascripter you need to show then how you are loading the array in the first place. Because as per your question you are putting the values in directly in EN-US format.

dwirony Over a year ago

@ascripter Just loop through array LBound(values) to UBound(values), values(i) = Replace(values(i), ",", ".").

Scott Craner Over a year ago

@dwirony but if the OP is loading a consecutive range then skip the whole array and just use: result = Worksheet("Sheet1").Evaluate(aggregate_fn & "(" & Range("A1:A3").Address(1,1) & ")") that is why I want to see how the array is being loaded, it may be unnecessary.

|

Collectives™ on Stack Overflow

Apply dynamically defined aggregation function on array values

3 Answers 3

1 Comment

1 Comment

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related