How to use function edited in VBA in excel sheet

[kelly]

Hi, dear all
i write a funciton in vba and want to use it in excel sheet. the
script is like,

****************************************************************************************
Function sample(sheetname As Integer) As Integer

Dim R As Excel.Range
Dim c As Variant

sample = 0
For Each c In Worksheets(sheetname).Range("A1").CurrentRegion
If Right(c.Value, 1) = "*" Then
sample = 1
Exit Function
End If
Next

End Function
****************************************************************************************

When i type the formular in excel sheet, it can not work. the excel
version is 2000.
can anybody help me with this?

Thank you
Kelly

 

[Tim Zych]

CurrentRegion won't work in a UDF called from an Excel cell. From what I
remember reading a while ago on the newsgroups, CurrentRegion is a type of
selection, which is disallowed. Actions that manipulate the environment
cannot be performed this way even though it works as a straight VBA macro.
You'll have to set the range reference a different way.