VBA Cell Summation

I'm trying to create a UDF that sums a range of cells. I know there is
a function for this, but it doesn't work well with concatenated values.
Could someone tell me what is wrong with this code? When it gets to the
'With Worksheets(s_Book).Cells(rwIndex, colIndex)' line it bounces out
of the loop and throws a #VALUE error. Values that I've come into the
function with would be...

s_Book = "[Orders_June_2006.xls]Orders"
Begin_Column = 140
End_Column = 149
Begin_Row = 30
End_Row = 30

Function SUM_RANGE(s_Book As String, Begin_Column As Integer, End_Column
As Integer, Begin_Row As Integer, End_Row As Integer) As Integer

Rem *Application.Volatile

Rem * tracks total of the cells
Dim n_Total As Integer
n_Total = 0

Rem *cycling through the cell range
For rwIndex = Begin_Row To End_Row
For colIndex = Begin_Column To End_Column
With Worksheets(s_Book).Cells(rwIndex, colIndex)
n_Total = n_Total + .Value
End With
Next colIndex
Next rwIndex

End Function

=-SUM_RANGE("[Orders_June_2006.xls]Orders",140,149,30,30)
does not work
=-SUM_RANGE("Orders",140,149,30,30)
works just fine

you may need to parse the string variable, ACTVATE the workbook, and then
use only the worksheet in your WITH statement.
--
Gary''s Student


"Grymjack" wrote:

> I'm trying to create a UDF that sums a range of cells. I know there is
> a function for this, but it doesn't work well with concatenated values.
> Could someone tell me what is wrong with this code? When it gets to the
> 'With Worksheets(s_Book).Cells(rwIndex, colIndex)' line it bounces out
> of the loop and throws a #VALUE error. Values that I've come into the
> function with would be...
>
> s_Book = "[Orders_June_2006.xls]Orders"
> Begin_Column = 140
> End_Column = 149
> Begin_Row = 30
> End_Row = 30
>
> Function SUM_RANGE(s_Book As String, Begin_Column As Integer, End_Column
> As Integer, Begin_Row As Integer, End_Row As Integer) As Integer
>
> Rem *Application.Volatile
>
> Rem * tracks total of the cells
> Dim n_Total As Integer
> n_Total = 0
>
> Rem *cycling through the cell range
> For rwIndex = Begin_Row To End_Row
> For colIndex = Begin_Column To End_Column
> With Worksheets(s_Book).Cells(rwIndex, colIndex)
> n_Total = n_Total + .Value
> End With
> Next colIndex
> Next rwIndex
>
> End Function
>

Alternatively, you could modify your code to include a separate
workbook parameter:
=-SUM_RANGE("Orders_June_2006.xls","Orders",140,149,30,30)

And in your VBA:
Function SUM_RANGE(s_Book As String, s_Sheet As String, Begin_Column As
Integer, End_Column As Integer, Begin_Row As Integer, End_Row As
Integer) As Integer

Dim n_Total As Integer
n_Total = 0

For rwIndex = Begin_Row To End_Row
For colIndex = Begin_Column To End_Column
With Workbooks(s_Book).Worksheets(s_Sheet).Cells(rwIndex, colIndex)
n_Total = n_Total + .Value
End With
Next colIndex
Next rwIndex

End Function

Gary''s Student schreef:

> =-SUM_RANGE("[Orders_June_2006.xls]Orders",140,149,30,30)
> does not work
> =-SUM_RANGE("Orders",140,149,30,30)
> works just fine
>
> you may need to parse the string variable, ACTVATE the workbook, and then
> use only the worksheet in your WITH statement.
> --
> Gary''s Student
>
>
> "Grymjack" wrote:
>
> > I'm trying to create a UDF that sums a range of cells. I know there is
> > a function for this, but it doesn't work well with concatenated values.
> > Could someone tell me what is wrong with this code? When it gets to the
> > 'With Worksheets(s_Book).Cells(rwIndex, colIndex)' line it bounces out
> > of the loop and throws a #VALUE error. Values that I've come into the
> > function with would be...
> >
> > s_Book = "[Orders_June_2006.xls]Orders"
> > Begin_Column = 140
> > End_Column = 149
> > Begin_Row = 30
> > End_Row = 30
> >
> > Function SUM_RANGE(s_Book As String, Begin_Column As Integer, End_Column
> > As Integer, Begin_Row As Integer, End_Row As Integer) As Integer
> >
> > Rem *Application.Volatile
> >
> > Rem * tracks total of the cells
> > Dim n_Total As Integer
> > n_Total = 0
> >
> > Rem *cycling through the cell range
> > For rwIndex = Begin_Row To End_Row
> > For colIndex = Begin_Column To End_Column
> > With Worksheets(s_Book).Cells(rwIndex, colIndex)
> > n_Total = n_Total + .Value
> > End With
> > Next colIndex
> > Next rwIndex
> >
> > End Function
> >

Thanks for taking the time to answer my question. I tried your
suggestion. Unfortunately it still dumps out at the 'With' line
throwing a #VALUE error. The Workbook is open, the Sheet is properly named.


Joost Lenaerts wrote:
> Alternatively, you could modify your code to include a separate
> workbook parameter:
> =-SUM_RANGE("Orders_June_2006.xls","Orders",140,149,30,30)
>
> And in your VBA:
> Function SUM_RANGE(s_Book As String, s_Sheet As String, Begin_Column As
> Integer, End_Column As Integer, Begin_Row As Integer, End_Row As
> Integer) As Integer
>
> Dim n_Total As Integer
> n_Total = 0
>
> For rwIndex = Begin_Row To End_Row
> For colIndex = Begin_Column To End_Column
> With Workbooks(s_Book).Worksheets(s_Sheet).Cells(rwIndex, colIndex)
> n_Total = n_Total + .Value
> End With
> Next colIndex
> Next rwIndex
>
> End Function
>
> Gary''s Student schreef:
>
>> =-SUM_RANGE("[Orders_June_2006.xls]Orders",140,149,30,30)
>> does not work
>> =-SUM_RANGE("Orders",140,149,30,30)
>> works just fine
>>
>> you may need to parse the string variable, ACTVATE the workbook, and then
>> use only the worksheet in your WITH statement.
>> --
>> Gary''s Student
>>

Thanks Gary for taking the time. Would you have a code snippet example
that you could post as an example?





=-SUM_RANGE("[Orders_June_2006.xls]Orders",140,149,30,30)
does not work
=-SUM_RANGE("Orders",140,149,30,30)
works just fine

you may need to parse the string variable, ACTVATE the workbook, and then
use only the worksheet in your WITH statement.
-- Gary''s Student "Grymjack" wrote:



> > I'm trying to create a UDF that sums a range of cells. I know
there is
> > a function for this, but it doesn't work well with concatenated
values.
> > Could someone tell me what is wrong with this code? When it gets to
the
> > 'With Worksheets(s_Book).Cells(rwIndex, colIndex)' line it bounces out
> > of the loop and throws a #VALUE error. Values that I've come into the
> > function with would be...
> >
> > s_Book = "[Orders_June_2006.xls]Orders"
> > Begin_Column = 140
> > End_Column = 149
> > Begin_Row = 30
> > End_Row = 30
> >
> > Function SUM_RANGE(s_Book As String, Begin_Column As Integer,
End_Column
> > As Integer, Begin_Row As Integer, End_Row As Integer) As Integer
> >
> > Rem *Application.Volatile
> >
> > Rem * tracks total of the cells
> > Dim n_Total As Integer
> > n_Total = 0
> >
> > Rem *cycling through the cell range
> > For rwIndex = Begin_Row To End_Row
> > For colIndex = Begin_Column To End_Column
> > With Worksheets(s_Book).Cells(rwIndex, colIndex)
> > n_Total = n_Total + .Value
> > End With
> > Next colIndex
> > Next rwIndex
> >
> > End Function
> >

Perhaps:

Function SUM_RANGE(s_Book As String, Begin_Column As Integer, End_Column As
Integer, Begin_Row As Integer, End_Row As Integer) As Integer
Dim sBook, sSheet As String
Dim n_Total As Integer

sBook = Mid(s_Book, 2, Application.Find("]", s_Book, 1) - 2)
sSheet = Right(s_Book, Len(s_Book) - Application.Find("]", s_Book, 1))
n_Total = 0

For rwIndex = Begin_Row To End_Row
For colIndex = Begin_Column To End_Column
n_Total = n_Total +
Workbooks(sBook).Sheets(sSheet).Cells(rwIndex, colIndex).Value
Next colIndex
Next rwIndex
SUM_RANGE = n_Total
End Function


works for =-SUM_RANGE("[Orders_June_2006.xls]Orders",140,149,30,30)
--
Gary's Student


"Grymjack" wrote:

> Thanks Gary for taking the time. Would you have a code snippet example
> that you could post as an example?
>
>
>
>
>
> =-SUM_RANGE("[Orders_June_2006.xls]Orders",140,149,30,30)
> does not work
> =-SUM_RANGE("Orders",140,149,30,30)
> works just fine
>
> you may need to parse the string variable, ACTVATE the workbook, and then
> use only the worksheet in your WITH statement.
> -- Gary''s Student "Grymjack" wrote:
>
>
>
> > > I'm trying to create a UDF that sums a range of cells. I know
> there is
> > > a function for this, but it doesn't work well with concatenated
> values.
> > > Could someone tell me what is wrong with this code? When it gets to
> the
> > > 'With Worksheets(s_Book).Cells(rwIndex, colIndex)' line it bounces out
> > > of the loop and throws a #VALUE error. Values that I've come into the
> > > function with would be...
> > >
> > > s_Book = "[Orders_June_2006.xls]Orders"
> > > Begin_Column = 140
> > > End_Column = 149
> > > Begin_Row = 30
> > > End_Row = 30
> > >
> > > Function SUM_RANGE(s_Book As String, Begin_Column As Integer,
> End_Column
> > > As Integer, Begin_Row As Integer, End_Row As Integer) As Integer
> > >
> > > Rem *Application.Volatile
> > >
> > > Rem * tracks total of the cells
> > > Dim n_Total As Integer
> > > n_Total = 0
> > >
> > > Rem *cycling through the cell range
> > > For rwIndex = Begin_Row To End_Row
> > > For colIndex = Begin_Column To End_Column
> > > With Worksheets(s_Book).Cells(rwIndex, colIndex)
> > > n_Total = n_Total + .Value
> > > End With
> > > Next colIndex
> > > Next rwIndex
> > >
> > > End Function
> > >
>