How to use function edited in VBA in excel sheet
 

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

How to use function edited in VBA in excel sheet
 

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.

--
Tim Zych
SF, CA


"kelly" wrote in message
oups.com...
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