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
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
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920