• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Creating a Reference to PERSONAL.XLSB for User Defined Functions (UDF’s)

You are here: Home / Excel VBA / Creating a Reference to PERSONAL.XLSB for User Defined Functions (UDF’s)
creating a reference to personal.xlsb for user defined function udf
October 27, 2016 by Philip Treacy

You've created your own PERSONAL.XLSB and want to use it to store VBA that you are going to use all the time. Sounds cool.

You are really keen to know what the cube of numbers are so you write this function and place it into a module in PERSONAL.XLSB.


Function cube(number As Long) As Long

   cube = number ^ 3

End Function

Looks pretty straightforward, pass in a number and get the cube of that number returned.

You then start typing the formula into your sheet and the first thing you notice is that your function isn't listed:

list of functions

Create an Add-In

An alternative to the method described here is to put your functions into an add-in.

and if you complete the formula and hit enter, you get a #NAME error.

#NAME Error

So what's going on?

In order to use the function you have to precede the function name with PERSONAL.XLSB! e.g.

using function in personal.xlsb

Create a Reference to PERSONAL.XLSB

To avoid having to type PERSONAL.XLSB! before every function in our personal macro workbook, we can create a reference to PERSONAL.XLSB.

Before you do this, it's a good idea to give your PERSONAL.XLSB a 'nice' name so that it's easy to find later because we have to look though a long list of file names and libraries.

To do this you must open the VBA editor (ALT+F11) and click on the PERSONAL.XLSB name at the top of the Project Explorer (press CTRL+R) if this isn't visible.

In the Properties window you should now see the name PERSONAL.XLSB has. Type over this and press Enter. I've called mine PGTPersonal.

Nice name for PERSONAL.XLSB

Now to create the reference. If it isn't already open, open the workbook you want to use the function in.

Click on the name of the workbook in the Project Explorer, then click on the Tools menu, and References.

Tools, References

Look for the name you gave to PERSONAL.XLSB, check/tick the box beside it, and click on OK.

create reference to personal.xlsb

Your workbook should now have a new reference created to your PERSONAL.XLSB.

new reference created to personal.xlsb

Use the Function

You can now use your function without having to use PERSONAL.XLSB before it, but when you start typing your function name, it still will not appear in the list of available functions (IntelliSense). But it is there, just go ahead and try it.

If you use the Excel-DNA IntelliSense you can get your UDF's to appear in the IntelliSense list.

Every workbook in which you want to use a UDF from PERSONAL.XLSB, has to have a reference created like this.

Write Your Own Add-In

As mentioned earlier, an alternative to creating a reference is to create an add-in with the function(s) you want to use. and load them into Excel that way. Functions loaded this way will appear in IntelliSense.

It's a lot easier to do this than you might think.

creating a reference to personal.xlsb for user defined function udf

More User Defined Function Posts

writing udfs in excel with python

Writing UDFs in Excel With Python

Write Excel UDFs in Python. Use the power of the vast Python libraries for data science, statistics, web scraping or database connectivity.
Return an array from a udf

Return An Array From A UDF

If you can return an array from a udf you are able to return multiple pieces of data and place them into multiple cells
Return a range from a UDF

Return a Range from a UDF

If you return a range from a UDF this allows you to use that range in other functions and calculations.
create an excel add-in for user defined functions udfs

Create an Excel Add-In for User Defined Functions (UDF’s)

How to create an Excel add-in for user defined functions (UDF's) to make them easier to use.
Creating Multi-Function UDF's

Creating Multi-Function UDF’s

Combine separate functions into one, multi function UDF (user defined function)

Creating a UDF (User Defined Function) in Excel

Create your own functions in Excel

More Excel VBA Posts

Display All Matches from Search in Userform ListBox

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts

Animating Excel Charts

Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms

Dynamic Data Validation Lists in Userforms

Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables

Show Report Filter Pages for Power Pivot PivotTables

PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel

Charting Real Time Data in Excel

Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list

Select Multiple Items from Drop Down (Data Validation) List

Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform

Searching for Data With a User Form

Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.




Category: Excel VBATag: user defined function
Previous Post:Excel IFS Function
Next Post:Power Query if Statements

Reader Interactions

Comments

  1. Guy Rondeau

    July 19, 2018 at 3:59 am

    when I save my xls workbook the links to the personal.xlsb refence function is not saved.

    thanks for the help

    Reply
    • Philip Treacy

      July 19, 2018 at 10:54 am

      Hi Guy,

      When you say ‘links’ do you mean the reference you created in the VBA editor?

      When you re-open your xls, and you then look at it in the VBA editor, does it not show the reference to your PERSONAL.XLSB?

      It might be better if you can create a post on the forum and supply screenshots of what you are seeing in your VBA editor so I can see what is going on.

      An alternative to creating a reference like this is to write your function as an add-in

      Regards

      Phil

      Reply
  2. Rafael Oliveira

    June 15, 2018 at 9:09 am

    Thanks! This was the thing i was looking for. I’ll check if it works for me.

    Reply
    • Philip Treacy

      June 15, 2018 at 9:27 am

      You’re welcome

      Reply
      • Rafael Oliveira

        June 15, 2018 at 10:46 pm

        In my case this solution did not worked. Because I need to use this reference in several files. There’s no way I can register the reference in all of these files, because I may not even know they exist.
        But the solution it self works.
        The thing that i’m sure of is the use of the PERSONAL in these files. There’s a way to do this without having to reference every time in different workbooks?

        Reply
        • Catalin Bombea

          June 16, 2018 at 3:04 pm

          Hi Rafael,
          Here is a link to an alternative solution.

          Reply
  3. Dave

    July 13, 2017 at 7:07 am

    thank you very much this was very clear and helpful

    Reply
    • Philip Treacy

      July 13, 2017 at 8:45 am

      You’re welcome

      Reply
  4. Danny

    April 6, 2017 at 7:28 pm

    This used to work on my Excel versions 2013 and 2016, but doesn’t anymore.
    Anytime I add the reference to Personal.xlsb in VBA editor and try, the functions added won’t appear and when I close the file and reopen, the reference to the renamed Personal.xlsb is gone…
    any ideas?

    Reply
    • Catalin Bombea

      April 7, 2017 at 2:50 am

      Hi Danny, try creating an add-in with your functions, you will no longer need to add a reference to personal.xlsb. Here is an article that will help: create-an-excel-add-in-for-user-defined-functions-udfs
      Catalin

      Reply
  5. Peter Buyze

    October 28, 2016 at 2:01 am

    Just a minor point: I still don’t understand why you have to give the PERSONAL.XLSB a different name. You it is because “it’s easy to find later because we have to look though a long list of file names and libraries.” But how does that make it easier??? What difference does it make having to look for PERSONAL.XLSB or PGTPersonal or ABRAcadabra or whatever?

    Reply
    • Philip Treacy

      October 28, 2016 at 10:04 am

      Hi Peter,

      If you don’t give PERSONAL.XLSB a ‘nice’ name, then when you look at the list of available files/libraries you can create a reference to, PERSONAL.XLSB just appears with the name VBAProject as any other open workbook will too.

      By giving it a nice name, it’s easier to find in this list.

      This ‘nice’ name is a name used internally by Excel, it doesn’t actually change the filename of PERSONAL.XLSB.

      Phil

      Reply
  6. Glenn Case

    October 27, 2016 at 11:05 pm

    Phil:

    Thanks for this post! I have long wondered why my UDFs had to be local vs in Personal.xlsb in order to work. It had never occurred to me to try to create a reference.

    You might note that the reason to change the name of Personal.xlsb in the VBA editor is that it’s default name of VBAProject hides it amongst any other open projects. Also, that this does not change the filename of your Personal.xlsb file, it just changes the VBA project name. I found that confusing until I sorted out why I couldn’t find Personal.xlsb as such in the list until I changed the VBAProject name.

    Again, thanks for the good tip!

    Reply
    • Philip Treacy

      October 28, 2016 at 10:01 am

      Thanks Glenn, glad to shed some light on this for you.

      Phil

      Reply
    • Gadi Bizinyan

      May 28, 2017 at 1:00 am

      Hi Phil,

      I would just like to emphasize about the necessity to change the reference name of Personal.xlsb file in the VBA Project if you want to add reference to it.

      By default every new file you open has the exact same reference name: “VBAProject”
      If you try to add a reference to Personal.xlsb without renaming its default name (i.e. “VBAProject”), you will end up with the following error: “Name conflicts with existing module, project, or object library”
      Of course you would not be able to create a reference to Personal.xlsb after receiving this error.

      I got this in Excel 2010 but I’m pretty sure it’s the same behavior across all Excel versions. Therefore renaming the reference of Personal.xlsb before referring to it is not a recommendation, it’s mandatory.

      Another point, I believe that regardless of the name you decide to give to it (even if you don’t rename it), it would always appear at the top of the references list.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.