VBA adjusting an existing user function to operate on rows instead of columns

[bthoron]

I need help adjusting an existing UDF to meet a new need.
The script below is designed to take a vertical range of cells with comma separated values, then add all the values together and display the result in the same form- Like so...

6,4,1
5,6,1
3,2,1
________
14,12,3
Make sense?

It works great. Even if I don't understand what's happening (I didn't make it, I inherited it).

What I would like it to do differently is evaluate a ROW instead of a column.
I tried it myself and was disappointed... (mostly in myself)

I appreciate your help.

_____________________________________________________
Public Function ColumnsSum(source As Range) As String
Dim Result As String
Dim aResult() As Variant
Dim Arr() As Double
Dim index As Long
Dim rowindex As Long
Dim ThisRow As Range
Dim splitter As Variant
Dim maxdepth As Long
Dim maxwidth As Long


maxdepth = source.Rows.Count
For Each ThisRow In source.Rows
splitter = Split(ThisRow.Value, ",")
rowindex = rowindex + 1
maxwidth = UBound(splitter, 1)
ReDim Preserve Arr(1 To maxdepth, 0 To maxwidth)
For index = 0 To maxwidth
Arr(rowindex, index) = splitter(index)
Next
Next
ReDim aResult(0 To maxwidth)
For rowindex = 1 To maxdepth
For index = 0 To maxwidth
aResult(index) = aResult(index) + Arr(rowindex, index)
Next
Next
Result = Join(aResult, ",")
ColumnsSum = Result

End Function

 

[Yongle]

Welcome to the forum

For future posts ...
The # icon adds code tags to a post
[CODE ] paste your code in here [/CODE ]

making it easier to read ...
... as illustrated in the amended function below

Public Function [COLOR=#006400]RowSum[/COLOR](source As Range) As String
    Dim Result As String
    Dim aResult() As Variant
    Dim Arr() As Double
    Dim index As Long
   [COLOR=#006400] Dim colindex As Long[/COLOR]
    [COLOR=#006400]Dim ThisCol As Range[/COLOR]
    Dim splitter As Variant
    Dim maxdepth As Long
    Dim maxwidth As Long
    maxdepth =[COLOR=#006400] source.Columns.Count[/COLOR]
    For Each[COLOR=#006400] ThisCol In source.Columns[/COLOR]
        splitter = Split([COLOR=#006400]ThisCol[/COLOR].Value, ",")
        [COLOR=#006400]colindex [/COLOR]=[COLOR=#006400] colindex [/COLOR]+ 1
        maxwidth = UBound(splitter, 1)
        ReDim Preserve Arr(1 To maxdepth, 0 To maxwidth)
        For index = 0 To maxwidth
            Arr([COLOR=#006400]colindex[/COLOR], index) = splitter(index)
        Next
    Next
    ReDim aResult(0 To maxwidth)
    For [COLOR=#006400]colindex[/COLOR] = 1 To maxdepth
        For index = 0 To maxwidth
            aResult(index) = aResult(index) + Arr([COLOR=#006400]colindex,[/COLOR] index)
        Next
    Next
    Result = Join(aResult, ",")
    [COLOR=#006400]RowSum[/COLOR] = Result
End Function

 

[bthoron]

Thank you Yongle!
It works!

Can I trouble you to explain how this actually adds the data correctly?
It's a bit of a black box to me!

Thanks

ben

 

[Yongle]

explain how this actually adds the data correctly

Perhaps this will help you see how the values are built up

- Place the code below in a NEW workbook
- Place values in A1 1,2,3 , B1 4,5,6 and C1 7,8,9
- Put the formula in another cell =RowSum(A1:C1) which returns 12,15,18
- Now go to VBA and look at immediate window which can be made visible with {Ctrl} G
- I have pasted what you will see below

Public Function RowSum(source As Range) As String
    Dim Result As String
    Dim aResult() As Variant
    Dim arr() As Double
    Dim index As Long
    Dim colindex As Long
    Dim ThisCol As Range
    Dim splitter As Variant
    Dim maxdepth As Long
    Dim maxwidth As Long

    maxdepth = source.Columns.Count
    For Each ThisCol In source.Columns
        splitter = Split(ThisCol.Value, ",")
        colindex = colindex + 1
        maxwidth = UBound(splitter, 1)
        ReDim Preserve arr(1 To maxdepth, 0 To maxwidth)
        For index = 0 To maxwidth
            arr(colindex, index) = splitter(index)
        Next
    Next
    
    ReDim aResult(0 To maxwidth)
    For colindex = 1 To maxdepth

        For index = 0 To maxwidth
[COLOR=#ff0000]Debug.Print 1, "colindex = " & colindex, "index = " & index[/COLOR]
[COLOR=#ff0000]Debug.Print 2, , "Before = " & aResult(index)[/COLOR]
[COLOR=#ff0000]Debug.Print 3, , "Add = " & arr(colindex, index)[/COLOR]
            aResult(index) = aResult(index) + arr(colindex, index)
[COLOR=#ff0000]Debug.Print 4, , , "Result = " & aResult(index)[/COLOR]
        Next
    Next
    
    Result = Join(aResult, ",")
    RowSum = Result

End Function

Immediate window shows each element building
- index 0 is the 1st element (highlighted in red so that it is easier to follow)
- index 1 is the 2nd element
- index 2 is the 3rd element

- the items being added are the values in each element in each cell

1 colindex = 1 index = 0
2 Before =
3 Add = 1
4 Result = 1
1 colindex = 1 index = 1
2 Before =
3 Add = 2
4 Result = 2
1 colindex = 1 index = 2
2 Before =
3 Add = 3
4 Result = 3
1 colindex = 2 index = 0
2 Before = 1
3 Add = 4
4 Result = 5
1 colindex = 2 index = 1
2 Before = 2
3 Add = 5
4 Result = 7
1 colindex = 2 index = 2
2 Before = 3
3 Add = 6
4 Result = 9
1 colindex = 3 index = 0
2 Before = 5
3 Add = 7
4 Result = 12
1 colindex = 3 index = 1
2 Before = 7
3 Add = 8
4 Result = 15
1 colindex = 3 index = 2
2 Before = 9
3 Add = 9
4 Result = 18

The values in the original cells are split (at each comma) and the values end up in array named arr
colindex and index are row and column references in arr