Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to create my own functions??
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 -- anthropomorfic ------------------------------------------------------------------------ anthropomorfic's Profile: http://www.excelforum.com/member.php...o&userid=31382 View this thread: http://www.excelforum.com/showthread...hreadid=510788 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to create my own functions??
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/ExcelArticle...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" <anthropomorfic.22zdvo_1139520303.5775@excelforu m-nospam.com wrote in message news:anthropomorfic.22zdvo_1139520303.5775@excelfo rum-nospam.com... 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 -- anthropomorfic ------------------------------------------------------------------------ anthropomorfic's Profile: http://www.excelforum.com/member.php...o&userid=31382 View this thread: http://www.excelforum.com/showthread...hreadid=510788 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to create my own functions??
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. -- Kevin Vaughn "anthropomorfic" wrote: 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 -- anthropomorfic ------------------------------------------------------------------------ anthropomorfic's Profile: http://www.excelforum.com/member.php...o&userid=31382 View this thread: http://www.excelforum.com/showthread...hreadid=510788 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Improvements for text finding functions | Excel Discussion (Misc queries) | |||
Nesting functions in the functions dialog box | Excel Worksheet Functions | |||
PASTE DOWN FUNCTIONS | Excel Worksheet Functions | |||
How can I create a formula out of more than 7 functions in excel? | Excel Worksheet Functions | |||
How do I create complex functions? | Excel Worksheet Functions |