Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    New Lounger
    Join Date
    Oct 2001
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Saving a Function (Excel 97)

    This is probably super easy, but I'm not not having much luck figuring it out. I have created a function that I want to use in the future, but don't want to have to re-create each time. How can I save this for future use?

  2. #2
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving a Function (Excel 97)

    Marie,

    You could do something as simple as coping it into a txt file and storing it until you wish to use it again.

    You could also store the function in a vba routine in the Personal.xls file.

    <pre>Sub MyFunction()
    ActiveCell.Formula = "=MyWorksheetFunction"
    End Sub
    </pre>


    When you wish to insert the function you would run the routine.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving a Function (Excel 97)

    Put the macro in Personal.xls and it will always be available. If you don't have a Personal.xls, then just record a do nothing macro and tell XL to store it in your Personal macro file. XL will create Personal.xls for you. You can then delete the do nothing macro and put your good macro there.
    Legare Coleman

  4. #4
    New Lounger
    Join Date
    Oct 2001
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving a Function (Excel 97)

    Thank you both for your help.

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Saving a Function (Excel 97)

    Marie, are you sure this solves your problem? You said that you had a function that you wanted to save. The personal workbook works great for macros, but I have never been able to store user-functions there, always have to copy them to the actual workbook. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Saving a Function (Excel 97)

    Sam, you can store functions in Personal WB, but you can't write them directly into any spreadsheet, you have to reference the Personal WB as in =PERSONAL.XLS!UDF(); the easiest way to access UDF's (IMO) is to go through the Paste Function button and select the User Defined category.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Saving a Function (Excel 97)

    2 <img src=/S/cool.gif border=0 alt=cool width=15 height=15>! I learned something today; now can I go home? See,you really are an MVP! The Paste Function button even makes it easy! But, what is the IsShared function and why is it on User Defined category?
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving a Function (Excel 97)

    Personally, I save my User Defined functions in an add-in. Always available, no reference required.

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Saving a Function (Excel 97)

    I have maybe 50 UDF's that I didn't write or install, and have no clue what they are, I assume they come from other add-ins I have installed and activated. I'd guess the =ISSHARED() function returns information either about if a file has been opened in shared mode, or possibly if it is permitted to be opened in shared mode. I don't share any files (yes, I was a kindergartener who did not play well with others) so I don't have a test-bed to find out.

    See Hans note about creating an XLA; I must do that someday.
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Saving a Function (Excel 97)

    We all need to give more information. Marie said to me via email that she really had no idea what we were talking about. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> Just the way I feel when I go to the Word board! Her experience with Excel is only a short class. In this class, the instructor showed some user-functions that he uses frequently. Marie wants to use them, but knows nothing about the personal workbook or how to add a Macro and/or a UDF to it. At a minimum, I think we need to give details or a reference on the following items.
    1) Overview of the personal workbook
    2) Creating a personal workbook
    3) Quirks of a personal workbook
    4) Adding a macro to a personal workbook
    5) Adding a UDF to a personal workbook
    6) Creating a new workbook with a reference to that UDF

    If we do that, then this thread will be a great reference, especially if we add instructions for creating an Add-In. I know, you're saying, "Do it yourself, Sam," but my boss keeps expecting me to do real work! What a drag! Thanks for reposting details! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

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

    Re: Saving a Function (Excel 97)

    John,

    IsShared is a function in the Analysis Toolpak (atpvbaen.xla) - I have no idea why it presents itself in the UserDefined category, except that all UDFs get assigned to that category by default. Either the creators forgot to assign this particular function or could not decide where to put it. Though I ithink it should go to the Information category, or maybe Lookup & Reference. It does indeed report on the shared status (True or False) of the workbbok passed to it.

    I agree with Hans that an AddIn is the way to go for Functions, especially if you have a number of them.

    Andrew C

  12. #12
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving a Function (Excel 97)

    Here is a <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=xl&Number=16879&page=&view= &sb=&o=&vc=1> thread </A> that contain links that explain how to make an add-in.

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Saving a Function (Excel 97)

    I have the same real work problem; what is it with expecting us to actually earn our paycheck? I hope in the future we can set up a couple of "star posts" which provide a short tutorial on personal.xls, and a walk through the VBE, so that folks can be referred to them for the basics.
    -John ... I float in liquid gardens
    UTC -7ąDS

  14. #14
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Saving a Function (Excel 97)

    Same thing here - only its' my spouse giving me the gimlet eye and asking if I shouldn't be studying for my Lotus Notes exam...
    I keep saying that I'm learning soooo much about VBA here. Unfortunately, I know that isn't on the exam <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>.
    We all need more hours in the day or night!

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  15. #15
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Saving a Function (Excel 97)

    <img src=/S/megashout.gif border=0 alt=megashout width=33 height=17> OK, stop making excuses and get to work. <A target="_blank" HREF=http://teaching.ust.hk/~ismt339/labs/lab01.htm>Here</A> is the shortest and simplest tutorial that I could find that includes information on the personal workbook. Read and do the stuff in it and then we will add a user function. HAGD --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

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
  •