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

bthoron

New Member
Joined
Oct 29, 2019
Messages
2
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
Code:
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
 
Last edited:
Upvote 0

bthoron

New Member
Joined
Oct 29, 2019
Messages
2
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
 
Upvote 0

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,187,123
Messages
5,961,698
Members
438,560
Latest member
chielmos

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top