View Question
 ```1. Write a VBA function that takes an Excel range of number as input and return the sum of the numbers. The program should follow the following pseudo code: a. Declare all variables used in the program b. Convert the range into a variant type data (array) c. Identify the number of columns and rows of the matrix d. Use FOR loops to calculate the summation e. Return the result```
 ```Hi nishakumar-ga, From your query, I understand that you want a VBA function that will take a range of cells(in an Excel Worksheet) as its argument, and return the sum of the contents of all these cells. I have provided the code for such a function below. The code provided has been successfully tested with Excel XP/2002. ================== Begin Code ====================== 'Takes in range of cells as its arguments 'Returns the summ of the contents of all the cells contained in the given range Function SumRange(Cellrange As Range) As Long 'Declare all the variables used 'Store the number of rows and columns in the given range Dim numRows As Integer, numCols As Integer 'Used in For..Next loops Dim rowIndex As Integer, colIndex As Integer 'Variant array into which the range content is stored Dim RangeArray() As Variant 'Stores the sum of all the cells in the range Dim ArraySum As Long 'Find the number of rows and columns in the array numRows = Cellrange.Rows.Count numCols = Cellrange.Columns.Count 'Resize the array to be of the required size ReDim RangeArray(1 To numRows, 1 To numCols) 'Copy the cell values into the array For rowIndex = 1 To numRows For colIndex = 1 To numCols ' Use Val() so that it does not give error when the cells contain a ' string value instead of an Integer. You can remove it if you want. RangeArray(rowIndex, colIndex) = Val(Cellrange.Cells(rowIndex, colIndex)) Next colIndex Next rowIndex 'Sum the contents of the array ArraySum = 0 For rowIndex = 1 To numRows For colIndex = 1 To numCols ArraySum = ArraySum + RangeArray(rowIndex, colIndex) Next colIndex Next rowIndex SumRange = ArraySum 'Return the sum of all the cells End Function ================== End Code ========================= If you require any further modifications to the code in order to meet your requirements, just ask for a clarification, and I will gladly make the required modifications. The code is pretty simple, and I have added suitable comments to explain what is happening. If you need further clarifications, just ask! Please do not rate this answer until you are completely satisfied with it. Regards, Theta-ga :-)``` Request for Answer Clarification by nishakumar-ga on 27 Mar 2003 11:07 PST `hi..it si still giving A SYNTAX ERROR..NOT WORKING IN EXCEL` Clarification of Answer by theta-ga on 28 Mar 2003 06:25 PST ```Hi nishakumar-ga, Unfortunately, the errors are being caused by the way Google Answers formats any text posted on this page. Parts of a long line of code are automatically pushed onto the next line. As a result, when you paste the code directly into the VBA editor, it gives a syntax error. This problem has occurred four times in the code posted above, which explains your errors. For your convinience, I have re-posted the VBA code online at http://www31.brinkster.com/tanm/GA/VBA.htm Just copy and paste the code on this page into your Excel workbook, and it should work without a problem. :-) If you need any clarifications, just ask! Regards, Theta-ga :-)```