Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Constants and functions (2000 sr-1)

    Hello all, need advice....yet again

    I need to know how to set a constant
    for instance I am using Pi = 3.14159 in various functions that I made for use as an add in but in each one i have to initialize what Pi is. I know that Pi is built into excel but not sure how to use it in VBA. Also I have another variable Qflow = 1.113 which i would like to place in the GENERAL section of code so I don't have to initialize it all the time...please help

    last Question..once a function is made and installed as an add in. If you go to the functions part of excel, your function shows up in the 'all' menu, how do i get the function to show the little help text at the bottom and how do I get it to have a help file and finally how do I either out the function into another catagory (like engineering) or create my own catatgory (perhaps called 'my special functions').

    I know that's a bunch of questions but you guys and gals are incredible so i figured this would be at snap for most of you to answer. Thanks a bunch

    Half of being smart is knowing what your dumb at.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Constants and functions (2000 sr-1)

    <<I know that Pi is built into excel but not sure how to use it in VBA. Also I have another variable Qflow = 1.113 which i would like to place in the GENERAL section of code so I don't have to initialize it all the time...please help>>

    The code for setting a constant is like this:

    Const myPI as Double=3.1514926536

    Pi can also be generated by using the worksheetfunction:

    Application.Worksheetfunction.Pi
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Constants and functions (2000 sr-1)

    You can obviously also set your value of Qflow in the same way as Jan describes....

    Public Const Qflow as Double=1.113

    (I always like to explicitly write 'Public' or 'Private' for a constant so that it's easy to see it's scope just by looking at it)

    As for adding help to your function, you can add help thru the object browser in the VBA editor. In the VBA editor, press F2 to open the object browser. Find your function in the browser. (It may help to first select your project from the drop-down in the top left of the window, or by typing the name of your function into the second drop down and clicking the search button.). Now, right click your function, and click 'Properties' from the context menu. Type your help text into the Description box. That text should now be displayed in the function wizard in Excel.

    As for categorising your function, I don't believe this can be done with VBA functions. There is a way of categorising macro-language functions or functions in XLL files, but I'm not an expert on this (old) technology!

    Hope this helps,

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

    Re: Constants and functions (2000 sr-1)

    Just to add to the above responses, you can assign your user defined functions to one of the existing function categories as shown in the function wizard. The following code will assign a function called TestFunction in the Personal.xls workbook to Math and Trig category(3) and also insert a description. See MacroOptions in VBA help for more functionality such as shortkey assignments etc :<pre>Sub AddFunctionToCategory()
    Application.MacroOptions Macro:="Personal.xls!TestFunction", _
    Description:="Enter Description here", _
    Category:=3
    End Sub</pre>

    The category numbers are reflections of the order in which the category is listed in the function wizard, starting at 0 for All. (Most Recently Used is not counted). Note that if you assign a category 0 (All) to your function, it will only appear in the All category. The numbers go from 0 to 14 (though numbers 10 through 13 are hiiden normally). Number 14 is the User Defined category to which all UDF's are assigned by default. If you have the analysis toolpak installed and activated there is additional Engineering category numbered 15.

    You can define your own categories by a rather convoluted procedure which involves the use of an XLMacro4 macrosheet. The procedure is described in the MS KnowledgeBase article <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q213/8/13.ASP?LN=EN-US&SD=gn&FR=0&qry=Q213813&rnk=1&src=DHCS_MSPSS_gn_ SRCH&SPR=OFW97>Q213813</A>. Although the procudere only mentions XL2000, I have in past used the same procdeure with XL97 without any problem. Note that any new catgories you create will be numbered in sequence after the the existing categories.

    With regard to worksheet functions you can use the evaluate operator (square brackets [ ] ) as shorthand, e.g, you can replace Application.WorksheetFunction.Pi with [Pi()] so that <pre>circumf = [Pi()] *r ^2</pre>

    will return the circumference of circle of radius r.

    Andrew C

Posting Permissions

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