how to use the function written in VBA in excel sheet

[kelly]

Hi, dear all

i wrote a small function 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

But when i type the formular in excel sheet, it can not work. the
version i use is Excel 2000.
My problem is how can i use this function in excel sheet???

Thank you

 

[Guest]

Hi Kelly:


User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=sample(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx
for specifics on UDFs