How do I code a function
CellVal(rownum, colnum [, sheetname])
that will be the equivalent of the worksheet formula:
INDIRECT(ADDRESS(rownum, colnum [, sheetname]))
that will return the value of the specified single cell?

My knowledge of VBA is not good enough for me to find how to specify a
single-cell RANGE by its co-ordinates.

Try the below. Sheetname is optional..

Function CellVal(lngRow As Long, lngColumn As Long, _
Optional strSheet As String) As Variant
If strSheet = vbNullString Then strSheet = Application.Caller.Worksheet.Name
CellVal = Sheets(strSheet).Cells(lngRow, lngColumn).Value
End Function

If this post helps click Yes
---------------
Jacob Skaria


"Hershmab" wrote:

> How do I code a function
> CellVal(rownum, colnum [, sheetname])
> that will be the equivalent of the worksheet formula:
> INDIRECT(ADDRESS(rownum, colnum [, sheetname]))
> that will return the value of the specified single cell?
>
> My knowledge of VBA is not good enough for me to find how to specify a
> single-cell RANGE by its co-ordinates.

Needs to be Volatile

Function CellVal(lngRow As Long, lngColumn As Long, _
Optional strSheet As String) As Variant

Application.Volatile
on Error GoTo Fail:
If strSheet = vbNullString Then strSheet = Application.Caller.Parent.Name
CellVal = Sheets(strSheet).Cells(lngRow, lngColumn).Value
exit function
Fail:
CellVal=cverr(xlerrna)
End Function

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Jacob Skaria" <(E-Mail Removed)> wrote in message
news:CEF460D1-C2FC-440D-8EB3-(E-Mail Removed)...
> Try the below. Sheetname is optional..
>
> Function CellVal(lngRow As Long, lngColumn As Long, _
> Optional strSheet As String) As Variant
> If strSheet = vbNullString Then strSheet =
> Application.Caller.Worksheet.Name
> CellVal = Sheets(strSheet).Cells(lngRow, lngColumn).Value
> End Function
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "Hershmab" wrote:
>
>> How do I code a function
>> CellVal(rownum, colnum [, sheetname])
>> that will be the equivalent of the worksheet formula:
>> INDIRECT(ADDRESS(rownum, colnum [, sheetname]))
>> that will return the value of the specified single cell?
>>
>> My knowledge of VBA is not good enough for me to find how to specify a
>> single-cell RANGE by its co-ordinates.
>