Google Answers Logo
View Question
Q: VBA programming ( Answered,   0 Comments )
Subject: VBA programming
Category: Computers > Programming
Asked by: nishakumar-ga
List Price: $15.00
Posted: 26 Mar 2003 11:07 PST
Expires: 25 Apr 2003 12:07 PDT
Question ID: 181249
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
Subject: Re: VBA programming
Answered By: theta-ga on 26 Mar 2003 15:32 PST
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
     ' string value instead of an Integer. You can remove it if you
     RangeArray(rowIndex, colIndex) = Val(Cellrange.Cells(rowIndex,
   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

Please do not rate this answer until you are completely satisfied with


Request for Answer Clarification by nishakumar-ga on 27 Mar 2003 11:07 PST 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
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!
There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy