Create VBA function (UDF) in Excel 2003

  • Thread starter Thread starter Hershmab
  • Start date Start date
H

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.
 
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
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
Back
Top