Excel - Why Vba Udf Won'T Work In Personal Macro Workbook - Episode 1385

4,169 views · Published 18 April 2011 · 4:48 · Indexed 8 November 2026

Channel: MrExcel.com · 2011 · Education

Watch on YouTube

Microsoft Excel Tutorial: Troubleshooting UDF in Personal Macro Workbook.

Welcome to another episode of the MrExcel podcast. In this episode, we will be discussing a user-defined function (UDF) from the personal macro workbook. If you remember from episode 1375, I created a tiny UDF called JOIN out of frustration with the CONCATENATE function in Excel. However, I recently received an email from a viewer, Lon, who pointed out that the UDF doesn't work when moved to the personal macro workbook. He also asked why it doesn't work with the CHAR 10 line feed character. Let's address that issue first.

To fix the issue with the CHAR 10 character, you simply need to format the cells and turn on the Wrap Text option. This can be found in the Alignment tab in the Format Cells menu. However, Lon's main problem was that the UDF didn't work when moved to the personal macro workbook. This is where things get a little tricky. Microsoft now requires you to include the name of the workbook in the UDF, which can be quite tedious to type every time. So, I came up with a solution that I will be sharing with you in this episode.

To solve this issue, I created a new workbook and pasted the UDF code into a module. Then, I saved the workbook as an add-in (.xlam) and named it JoinMacro. This add-in is automatically stored in the add-ins folder, making it easily accessible. Now, whenever I need to use the UDF, I can simply type =JOIN and it will work without having to include the workbook name. This is similar to the concept of the personal macro workbook, but for user-defined functions.

The great thing about this solution is that you can add more UDFs to the same add-in and they will all be available for use. So, instead of using the personal macro workbook, you can create your own personal user-defined function workbook stored as an add-in. This way, you will always have access to your UDFs without having to type in the workbook name every time. I want to thank Lon for bringing this issue to my attention and I hope this solution helps you as well. Thank you for tuning in to this episode of the MrExcel podcast. Don't forget to subscribe and hit the notification bell to stay updated on our latest episodes. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

Table of Contents
(00:00) Can you Run a VBA UDF from Personal Macro Workbook?
(00:35) Linefeed is Character 10
(01:03) UDF from Personal.xlsb won't run
(01:44) Add name of workbook before function to make it work
(02:38) Saving as an add-in
(03:44) Works perfectly as an add-in
(04:14) Like a Personal UDF workbook
(04:35) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #excelvba #excelmacrorecorder #personalmacroworkbook #exceludf

This video answers these common search terms:
how to write a udf in vba excel
why vba udf won't work in personal macro workbook
troubleshoot user defined function in personal macro workbook
troubleshooting udf in personal macro workbook
personal macro workbook udf problem
excel custom function not functioning in personal workbook
vba udf not recognized in personal macro workbook
fixing udf issues in excel's personal.xlsb
debugging udf errors in personal macro workbook
resolving udf problems in personal vba workbook
personal macro workbook not recognizing udf

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153064/




Lon liked the User Defined Function from Episode #1375. He tried to move it to the Personal Macro Workbook and it quit working. There are two workarounds. Today, in Episode #1385, Bill will show us how to create a personal add-in for your UDF's.

More from this channel