Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    user-defined functions (Excel XP)

    How do I create a user-defined function in Excel?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: user-defined functions (Excel XP)

    Hans,

    Just an addendum to your advice.

    If functions are placed in an installed AddIn, then there would be no requirement to include the the workbook name (Personal.xls in your examples) when using the function(s) in any worksheet.

    Andrew C

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: user-defined functions (Excel XP)

    Yes, that is a good point.

  4. #4
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: user-defined functions (Excel XP)

    How would a function be palced in an AddIn?
    Would this be worth doing to avoid the need for the Personal.xls name?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: user-defined functions (Excel XP)

    You can save a workbook as an add-in using File | Save As...
    Microsoft Excel Add-In (*.xla) is the last item in the Save As Type dropdown list.
    Excel will automatically suggest to save it in the Add-Ins folder.
    You will then have to tick it in Tools | Add-Ins... to activate it.

    Using an add-in has two advantages:
    - You can easily distribute it to others. In general, you won't want to distribute Personal.xls, since it would overwrite the customizations the recipient had.
    - You can use functions just as if they are built-in functions, no need to prefix them with the workbook name.

    The downside is that it is a bit more work to create one, but it is not really difficult.

  6. #6
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: user-defined functions (Excel XP)

    Thanks, I'll try it!

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: user-defined functions (Excel XP)

    Probably the best approach to storing User Defined Functions is to :

  8. #8
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: user-defined functions (Excel XP)

    Gotcha. Thanks, I'm gonna try it soon!

  9. #9
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: user-defined functions (Excel XP)

    What does it take to make such a UDF "active"?
    When I save the visual basic file, the spreadsheet still doesn't seem to recognize the UDF...
    ...nor when I exit the spreadsheet and come back in...!?!?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: user-defined functions (Excel XP)

    Where did you create the user-defined function? And how are you trying to use it?

    If you created it in a module in a standard workbook, the function should be available from the moment you create it, in the sense that you can use it in formulas in cells.

  11. #11
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: user-defined functions (Excel XP)

    Somehow, my UDF's are not active. They are there under module 1 (personal.xls) when I do an alt+F11, but the spreadsheet doesn't seem to know them. for instance, in module 1 I have the following:

    Function IP(LL, PL)
    LL = Val(LL): PL = Val(PL)
    If LL < PL Then
    IP = "LL<PL"
    ElseIf LL = 0 Or PL = 0 Then
    IP = "NP"
    Else
    IP = LL - PL
    End If
    End Function

    yet when I enter =ip( and designate two arguments, I end up with "#NAME?" showing, not the result I should be getting...

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: user-defined functions (Excel XP)

    As I wrote in my first reply, if you put the function in Personal.xls, you must prefix the function with Personal.xls. Try this:

    =Personal.xls!IP(A1,A2)

  13. #13
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: user-defined functions (Excel XP)

    Oh, Yea-a-a-h-h-h-h ?!?!
    That does seem to make it work...
    My bad.
    Thanks again.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    User-defined functions (Excel, all versions)

    How to create and use user-defined functions in Excel

    If you want the function to be available in all workbooks, you can put it in an Excel add-in or in your personal macro workbook Personal.xls. See <post#=380382>post 380382</post#> in this thread for a very short instruction on how to create an add-in, and see Legare Coleman's <!post=Personal.xls Tutorial (All),118382>Personal.xls Tutorial (All)<!/post> for information about Personal.xls. If the function is meant to be used in a single workbook, put it in that workbook.

    In all cases, you create the function in the Visual Basic Editor. You activate it by selecting Tools | Macro | Visual Basic Editor or by pressing Alt+F11. In the Project Explorer, the Windows Explorer-like tree view in the left hand pane, click on the workbook in which you want to create the function, then select Insert | Module. A module is a storage space for Visual Basic code; it can contain multiple macros and functions.

    A function will usually take one or more arguments, and it must return one value. Here is a simple example: if you want to convert a temperature from Centigrade (degrees Celsius) to Fahrenheit, you must multiply the temperature in Centigrade by 1.8 and add 32 to the result. Type or copy the following code into the module:

    <code>Function Fahrenheit(Celsius)</code>
    <code> Fahrenheit = Celsius * 1.8 + 32</code>
    <code>End Function</code>

    For those who are interested, you can find an extended version with some explanations at the end of this post.

    You can use this function in a worksheet formula. Say that A1 contains a temperature in Centigrade.

    If the function has been created in a module in the same workbook, or in an add-in, use this formula in another cell, for example B1:

    <code>=Fahrenheit(A1)</code>

    If the function has been created in Personal.xls, use this formula:

    <code>=Personal.xls!Fahrenheit(A1)</code>

    User-defined functions will be listed in the Insert Function wizard under the User Defined category.

    <img src=/w3timages/blueline.gif width=33% height=2>

    Here is a slightly extended and improved version of the function, with an explanation for some of the details:

    <code>Function Fahrenheit(Celsius As Variant) As Variant</code>
    <code> If Celsius = "" Then</code>
    <code> Fahrenheit = ""</code>
    <code> Else</code>
    <code> Fahrenheit = Celsius * 1.8 + 32</code>
    <code> End If</code>
    <code>End Function</code>

    The function takes one argument, Celsius; it is declared as a Variant; that means that we don't tell Visual Basic its exact type; it could be a text string, or a number, or a cell reference. The outcome of the function is also declared as a Variant.

    The function first tests if the input value is an empty string "". This is the case if the input is a reference to an empty cell. If so, the result is also an empty string. Otherwise, the input is multiplied by 1.8 and 32 is added, and this is the outcome of the function.

    So the result can be a string (empty) or a number; that is why we declare the result as a Variant.

    Note: if you pass a non-empty text value to the function, the result will be #VALUE. That is as expected, since we can't convert text to Fahrenheit.

    <img src=/w3timages/blueline.gif width=33% height=2>

    Thanks to Jan Karel Pieterse, Andrew Cronnolly, Stuart Rance, Steve Aprahamian and Jefferson Scher for their encouragement, valuable comments and additions.

  15. #15
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: user-defined functions (Excel XP)

    When I save it as an addin, what gets saved? The spreadsheet part (which would be empty, right) or the visual basic part, or both?

    I assume that when one invokes the addin, it will seem "transparent"...just like a new empty workbook?

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •