how to create my own functions??

A

anthropomorfic

HI

I ever like to know if there is a way to create my own functions, (ex:
the distance between two coordinates of points in space).

if there is a way I'll be happy if someone can tell me how

:)

bye and tvm
 
B

Bernard Liengme

Would take too long to give a good answer.
You want to make a User Defined Function (aka Custom FUnction)
Use Google to search:Excel + VBA + user defined function (advanved search is
best)
Here are a few hits:
http://www.vertex42.com/ExcelArticles/user-defined-functions.html
http://www.tushar-mehta.com/excel/vba/

You function will look like this:

Function Distance(x1,y1,x2,y2)
Distance =Sqr( (x1-x2)^2 + (y1-y2)^2)
End Function

It would be called with (for example) =DISTANCE(A1, A2, B1, B2)

You need to know what function Visual Basic has (e.g Sqr for square root)
And how to call Excel functions; example
x=Application.WorksheetFunction.Asin(y)
If VBA has equivalent function (Sqr) you cannot use Excel's (SQRT)

Have fun learning
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"anthropomorfic"
 
K

Kevin Vaughn

Yes you can create your own functions using VBA. They are called UDF (User
Defined Functions.) An example that I just created (which could be done
using a formula, but for demonstration purposes) is :

Function CountCharacters(myVar As String, myChar As String) As Integer
Dim i As Integer, l As Integer, cnt As Integer
Dim testChar As String

cnt = 0
l = Len(myVar)
For i = 1 To l
testChar = Mid(myVar, i, 1)
If testChar = myChar Then
cnt = cnt + 1
End If
Next i
CountCharacters = cnt
End Function

Which I created in a Module (not in a workbook or worksheet event.)
Then I used the following formula in my worksheet:
=countcharacters(B7,",")
B7 contained : 1,2,3,4,5
and the result of the function was 4.

If you are interested in VBA there are plenty of books or web sites that can
get you started. Also, check out the programming forum.
 
Top