+ Reply to Thread
Results 1 to 4 of 4

Making a custom function available in any Excel Sheet (Personal.xlsb) for Excel 2010

  1. #1
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

    Question Making a custom function available in any Excel Sheet (Personal.xlsb) for Excel 2010

    Hi There,

    I have a function (which is a Barcode generator) which I want to be available in any spreadsheet, simply by typing in the formula,

    Please Login or Register  to view this content.
    (where 'cellref' would be "A1" for example - the cell which contains the number that is to be converted into a barcode)

    I was under the impression that the way to do this was to place the module in the hidden "personal.xlsb" sheet and then it should be available in all sheets. This doesn't appear to work.

    I also read somewhere that when referring to a function located on the 'Personal' sheet that you must add "personal.xlsb!" before the function page, but either I'm not doing something right (very possible) or this doesn't work...

    Please Login or Register  to view this content.

    Can someone please shed a little light as to what I'm doing wrong? Is there an alternative way to achieving my goal? After I get this working on my PC, I will be adding it to a couple of other colleagues' PCs so that they have this function available to them too.

    (Using Excel 2010 on Windows 7)
    Last edited by vba_madness; 01-10-2013 at 05:13 AM. Reason: Wrong Excel Version!

  2. #2
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Making a custom function available in any Excel Sheet (Personal.xlsb) for Excel 2010

    Well, as your personal workbook is hidden, excel can not see it, try unhiding your personal workbook. Other option would be to create as add-in so that the function would be available ay any time. You can actaully check if the UDF is in your excel, by going to "Insert Check" and choose, UDF, or type the name of the function on search. If the name exists,it is most likley then there is an error with the function itself

  3. #3
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628

    Re: Making a custom function available in any Excel Sheet (Personal.xlsb) for Excel 2010

    You could try with:
    =personal.xlsb!Code128(cellref)
    Regards,
    Antonio

  4. #4
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

    Re: Making a custom function available in any Excel Sheet (Personal.xlsb) for Excel 2010

    Antoka05 - That's the ticket! Easy when you get the correct syntax - Thank you!

    Just one more question... Normally if I am entering a formula/function name into a cell, as I type, a list of function options reveals itself, but using the ' personal.xlsb! ' prefix seems to stop this from happening - Any way round this?


    AB33 - I must disagree with your first statement - personal.xlsb should generally always be hidden (from my understanding) and the macros I currently have lodged in there work in this state.
    I will look into the add-in option - thank you. I assume there is plenty on this in the forum so will have a search!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1