cell value under VBA

[Grymjack]

OK, I seem to be mis-coding this. Can anyone tell me what is wrong with
this VBA code?.....or a better way to do it?

*******
This line is supposed to return a value in a specific cell based on
row/column position. The correct values seem to be getting fed into
there, but it throws a #VALUE error? Is there a better way to retrieve a
specific cell value somewhere on a worksheet?
*******

n_Total = Workbooks(s_Book).Worksheets(s_Cases_Sheet).Cells(n_Row, n_Column)

####
Sample values for above
s_Book = "Inventory_Entry_June_2006.xls"
s_Cases_Sheet = "Cases"
n_Row = 4
n_Column = 10
####


*******
This line is supposed to return a sum in a range of cells. Again the
right values appear to be getting fed into the function?
*******

n_Total = Application.WorksheetFunction.Sum(Range(s_Start_Book + s_Sheet
+ s_Start_Cell + ":" + s_End_Cell))

####
Sample values for above
s_Start_Book = "[Orders_June_2006.xls]"
s_Sheet = "Orders!"
s_Start_Cell = "O_6697_19"
s_End_Cell = "O_6697_29"
^^^ These are names cell ranges, and are properly named
####

 

[Harlan Grove]

Grymjack wrote...
....

This line is supposed to return a value in a specific cell based on
row/column position. The correct values seem to be getting fed into
there, but it throws a #VALUE error? Is there a better way to retrieve a
specific cell value somewhere on a worksheet?

n_Total = Workbooks(s_Book).Worksheets(s_Cases_Sheet).Cells(n_Row, n_Column)

....

Nothing wrong with this syntax, but maybe you have a type mismatch.
That is, if n_Total is a numeric type, you can't assign arbitrary text
values to it.

And VBA variables don't store #VALUE! errors, so what's *ACTUALLY*
going wrong? Is this line in a udf, and the UDF returns a #VALUE!
error?

FTHOI, you could tack a .Value after the .Cells() property call, but
it's unnecessary.

This line is supposed to return a sum in a range of cells. Again the
right values appear to be getting fed into the function?

n_Total = Application.WorksheetFunction.Sum(Range(s_Start_Book + s_Sheet
+ s_Start_Cell + ":" + s_End_Cell))

....

If you mean string concatenation, generally better to use & instead of
+.

s_Start_Cell = "O_6697_19"
s_End_Cell = "O_6697_29"
^^^ These are names cell ranges, and are properly named

If these are workbook-level defined names, your reference syntax is
wrong. If foo and bar were workbook-level names, the proper reference
syntax would be

filename.xls!O_6697_19:O_6697_29

and if they were worksheet-level defined names in the same worksheet,
you can only use range expressions outside that worksheet,

([filename.xls]sheetname!O_6697_19)[filename.xls]sheetname!O_6697_29)