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
:-)