Create VBA function (UDF) in Excel 2003

[Hershmab]

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.

 

[Jacob Skaria]

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

 

[Charles Williams]

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

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

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.