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

**vba_madness** · 01-10-2013, 05:09 AM

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)

**AB33** · 01-10-2013, 05:19 AM

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

**antoka05** · 01-10-2013, 05:22 AM

You could try with:

=personal.xlsb!Code128(cellref)

Regards,
Antonio

**vba_madness** · 01-10-2013, 05:34 AM

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!

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions