Results 1 to 15 of 15
  1. #1
    Lounger
    Join Date
    May 2001
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Storing user-defined functions in personal.xls (Excel 2000 /Windows XP)

    Hi, I have developed some user functions in VBA which I want all workbooks that I create on my computer to have access to. I've tried to store them in the modules section of Personal.xls but I can't get the workbook to recognize them. I just get the #NAME? error. The only place I can put them where they can be recognized is in the modules section of the workbook I am working on. But this is no use because I want all future workbooks to access the same functions. As an example of what I'm doing, here's a dummy function:

    In modules section of Personal.xls:

    Public Function sayHello() As String

    sayHello = "Hi!"

    End Function

    In workbook cell:
    =sayHello()

    Result:
    #NAME?

    I've checked that Personal.xls is open and hidden. My macro security level is set to low.

    Can anybody help? Thanks,

    Mike C

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

    Re: Storing user-defined functions in personal.xls (Excel 2000 /Windows XP

    Mike, you have to address all UDF's contained in Personal.xls in the form:

    =Personal.xls!SAYHELLO()

    If you turn them into an add-in and add them in, then you can use them as:

    =SAYHELLO()
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Lounger
    Join Date
    May 2001
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Storing user-defined functions in personal.xls (Excel 2000 /Windows XP

    Thanks John. I did a find and replace on all UDF references (four sheets) and all is working well now. I can see why you might want to create an add-in though since the formulas can easily become quite longwinded with Personal.xls! in front of everything.

    Mike C.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Storing user-defined functions in personal.xls (Excel 2000 /Windows XP

    John

    How do you turn a UDF to an Add-In?

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

    Re: Storing user-defined functions in personal.xls (Excel 2000 /Windows XP

    You turn the file containing UDF(s) into an Add-In. It's pretty simple, <!mskb=211563>Microsoft Knowledge Base Article 211563<!/mskb> explains it. (A shame that Search is broken, as there have been some good threads on the subject.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Storing user-defined functions in personal.xls (Excel 2000 /Windows XP

    Thanks John,

    That article is very good, but it is not clear if the add-in file can be personal.xls (which is where my UDFs are at present), or if it is better (or essential) to use a different workbook. Can you help please?

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

    Re: Storing user-defined functions in personal.xls (Excel 2000 /Windows XP

    Personal.xls has a specific meaning to Excel; it should be a standard Workbook, not an add-in. You wouldn't be able to record new macros in it if you saved it as an add-in. So either transfer your functions to a new workbook and turn that into an add-in, or rename Personal.xls and make it into an add-in. In the latter case, Excel will create a new Personal.xls when needed.

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Storing user-defined functions in personal.xls (Excel 2000 /Windows XP

    Thanks Hans

    I thought that would be the case.

  9. #9
    Lounger
    Join Date
    May 2001
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Storing user-defined functions in personal.xls (Excel 2000 /Windows XP

    Just as an extra, I recently converted UDFs from my Personal.xls to an Addin and I found that function names longer a certain value would not work. I had

    Public Function aFnGradeToScore(score) as string

    which would not work. When I shortened it to:

    Public Function fnToScore(score) as String

    it worked. Don't know why this was so?

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

    Re: Storing user-defined functions in personal.xls (Excel 2000 /Windows XP

    It's not the length; as a test I created an add-in with a function with a much longer name and it "functions" without problem.

    Perhaps there was a name conflict?

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

    Re: Storing user-defined functions in personal.xls (Excel 2000 /Windows XP

    As Hans notes, probably a name conflict because you left the function in Personal.xls as well as having it in the new Add-in.
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    Lounger
    Join Date
    May 2001
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Storing user-defined functions in personal.xls (Excel 2000 /Windows XP

    You're right Hans and John, it is not length. But there is something funny going on. I've checked all open workbooks (including Personal.xls) for VBA functions and there aren't any except the ones I have in my Addin. Yet it won't allow me to name my UDF with the same name it had when I transferred them (via cut and paste) from the Personal.xls workbook. Neither does it allow me to extend the name of the function. Both the following cause NAME? errors:

    aFnGradeToScore (ORIGINAL in Personal.xls)
    aFnGradeToScoreScore

    I should say I'm working on my home Xp after emailing the addin from my work computer. Where could that original function be hiding?

    Best, Mike C

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

    Re: Storing user-defined functions in personal.xls (Excel 2000 /Windows XP

    Have you deleted the modules in Personal.xls that used to contain the UDF's ?

    VBA does a terrible job cleaning up after itself.

    For example.

    Type this in the declaration section of a module:

    Dim MyTest as String

    (note the capitalisation!)

    Now delete that line.

    Type this :

    Sub Test()
    mytest="Hi"
    msgbox mytest
    end sub

    (note the lack of capitalisation!)

    You end up with:

    Sub test()
    MyTest = "Hi"
    MsgBox MyTest
    End Sub

    But get a compile error (with option explicit set) because VBA does not know mytest. It does remember how you capitalised it though.
    This behaviour even "works" if you declare the variable in module1 and try typing the code I gave in another module.

    And things are even worse...

    - Declare the variable in a normal module.
    - Delete the module
    - save and close the file
    - open the file
    - insert a brand new module
    - type the code again
    - the capitalisation is still there!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Storing user-defined functions in personal.xls (Excel 2000 /Windows XP

    Jan Karel has reminded me that you might want to install and run Rob Bovey's Code Cleaner.
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Storing user-defined functions in personal.xls (Excel 2000 /Windows XP

    Unfortunately, the code cleaner cannot cleanup the stuff I wrote in my previous post. I guess that would require importing all modules into a fresh workbook.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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