none
Help creating a Function to add cells RRS feed

  • Question

  • Hello, 

    I am new to VBA and i have a homework coding assignment. The current question I am stuck on requires me to create a function that can add an array of data. The array is simply a column of data, and is not defined within the code. I am having trouble figuring out even where to start. I would like for the function to have an input that the user would simply select a range of cells and the function would add them together. I am not asking for someone to completely solve the problem. Can anyone point me in the right direction>

    So far i have tried:

    Dim Range1 As Range
    Set Range1=ActiveSheet.Range("C6:C30")
    
    Report.Cells(30, 6).Value = Excel.WorksheetFunction.Sum(Report.Range("C6:C30"))
    

    This was a code that i pieced together from reading another forum topic on StackOverflow, I understand that the last line is telling Excel to call the Worksheet defined Function "SUM" but i don't understand how to define an input variable within the function to select that range, or how to return that value in a cell. 

    Any help would greatly be appreciated!


    Monday, September 5, 2016 12:08 AM

Answers

  • I think that maybe you are required to write the code for a UDF (User Defined Function). If so, then then following code and it goes in a Standard Module. Note that it is called a Function; not a Sub and it has End Function; not End Sub.

    Function AddCells(rngToAdd As Range)
        AddCells = WorksheetFunction.Sum(rngToAdd)
    End Function

    On the worksheet you would enter the following formula where A2:A9 is the range to sum.

    =AddCells(A2:A9)

    However, if you are required to add the values in an array then firstly you need to assign the range to an array and then loop through the array and sum the values.

    Function SumArray(rngToSum As Range)
        Dim MyArray() As Variant
        Dim i As Long
        MyArray() = rngToSum.Value
        For i = LBound(MyArray()) To UBound(MyArray())
            SumArray = SumArray + MyArray(i, 1)
        Next i
    End Function

    On the worksheet you would enter  the following formula where A2:A9 are the values to assign to an array.

     =SumArray(A2:A9)

    You can use any name you like for the UDF so long as it is not the same as an inbuilt Excel function.

    When a range of cells is assigned to an array then the array is a one based by 2 dimensional array by default and it then must be referenced like a 2 dimensional array so that the element references become 1,1    2,1     3,1 etc even though it might only contain one column.

    See Help for more information on use of LBound and UBound.


    Regards, OssieMac

    • Proposed as answer by David_JunFeng Tuesday, September 13, 2016 2:33 PM
    • Marked as answer by David_JunFeng Thursday, September 15, 2016 2:30 PM
    Monday, September 5, 2016 3:44 AM