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.
Transcript of the video:
MrExcel podcast is sponsered by Easy-XL.
Learn excel form MrExcel podcast episode 1385.
UDF from Personal Macro Workbook.
All right well hey, this is a VBA podcast, but we're going to go back to episode 1375 when I created a tiny little user-defined function called JOIN.
If you remember, I was frustrated with the concatenate function in Excel and so I created this little UDF.
I got an email from Lon.
Lon said hey, that was brilliant, but I tried to put it in the personal Macro workbook doesn't work anymore.
He also said hey, why doesn't work with CHAR 10?
The line feed character and let's just address that one real fast CHAR 10.
So, you see jams everything together what you have to do is you have to Format Cells and then go into Alignment and turn on Wrap text, click OK.
Of course oh hey, that's up there in the Excel 2007 or 2010 ribbon.
Keep for getting into there it's nice that it's there Okay, but here's you know Lon's problem.
He took that Macro, and he moved it to his personal Macro workbook.
Let's go into VBA with Alt+F11.
We're going to take that Macro code from episode 1375 when I cut that and let's see over here in our personal.xlsb.
Find the Modules, see that we have a few Macro's there already. I'll paste this one in right now.
You know for regular old subroutines that don't start with function.
They just start with sub those work perfectly in the personal Macro workbook.
It's a great, great way to go, but unfortunately now, it does not work anymore and what Microsoft wants you to do and I think this is ridiculous is you have to put the name of the workbook.
So, =personal.xlsb!
and then all of the sudden it works, but whose gonna type that every single time.
So, here's my solution to this I'm going to create just a brand new workbook control+N, and so, right now. It's called Book 2, will go back to VBA will take this code from personal, control+X and here's our new workbook, Insert a Module, paste.
All right so now, that is part of Book 2 and then we'll go back to Excel.
So, yeah I'm in Book 2, File, Save As, files of type and we're going to look through the list for and add and we're save this as an Add-In right there.
So, its in .xlam, instead of xlsm, and we're just gonna call it the JoinMacro.
Now hey, notice here that they're automatically storing it in the add-ins folder and that actually is going to work out very nicely for us here in a couple of seconds.
All right so, that's it workbook with no data.
Whatsoever, just the Macro got saved life is good. I'm actually going to close Excel.
Ok so, we reopen Excel. I need to get back to the old add-ins manager.
You know the easiest way to do that is Alt+T followed by the letter I brings up the old add-ins manager and right there.
It's already in the list JoinedMacro just choose that click OK.
Right now, what's the difference?
It's not in a personal Macro workbook anymore.
It's in a little add in that I just created.
The beautiful thing about that is when I need to use it I can just type equal join and for whatever reason I don't know what their rationale behind this is anything in an add-in and open add-in, you don't need to prefix it with the added name it just automatically works.
All right, so it's like the concept of the personal Macro workbook, but it's used for your user defined functions.
And oh hey, by the way if you get more user defined functions, will just put them right in that same add-in back.
So, JoinMacro.xlam.
There's a module you can paste more functions in here, and they'll all be available.
So, it's like having your own personal Macro workbook, but it's a personal user-defined function workbook stored as an add-in and then things will automatically work.
All right so, there you have it. When you have a user-defined function instead of going to personal.xlsb create your own xlam or if your back in Excel 2003 xla and store it there.
You will then have an access to that all the time.
Wanna thank Lon for sending that question in and wanna thank you for stopping by.
We'll see you next time another netcast for MrExcel.
Learn excel form MrExcel podcast episode 1385.
UDF from Personal Macro Workbook.
All right well hey, this is a VBA podcast, but we're going to go back to episode 1375 when I created a tiny little user-defined function called JOIN.
If you remember, I was frustrated with the concatenate function in Excel and so I created this little UDF.
I got an email from Lon.
Lon said hey, that was brilliant, but I tried to put it in the personal Macro workbook doesn't work anymore.
He also said hey, why doesn't work with CHAR 10?
The line feed character and let's just address that one real fast CHAR 10.
So, you see jams everything together what you have to do is you have to Format Cells and then go into Alignment and turn on Wrap text, click OK.
Of course oh hey, that's up there in the Excel 2007 or 2010 ribbon.
Keep for getting into there it's nice that it's there Okay, but here's you know Lon's problem.
He took that Macro, and he moved it to his personal Macro workbook.
Let's go into VBA with Alt+F11.
We're going to take that Macro code from episode 1375 when I cut that and let's see over here in our personal.xlsb.
Find the Modules, see that we have a few Macro's there already. I'll paste this one in right now.
You know for regular old subroutines that don't start with function.
They just start with sub those work perfectly in the personal Macro workbook.
It's a great, great way to go, but unfortunately now, it does not work anymore and what Microsoft wants you to do and I think this is ridiculous is you have to put the name of the workbook.
So, =personal.xlsb!
and then all of the sudden it works, but whose gonna type that every single time.
So, here's my solution to this I'm going to create just a brand new workbook control+N, and so, right now. It's called Book 2, will go back to VBA will take this code from personal, control+X and here's our new workbook, Insert a Module, paste.
All right so now, that is part of Book 2 and then we'll go back to Excel.
So, yeah I'm in Book 2, File, Save As, files of type and we're going to look through the list for and add and we're save this as an Add-In right there.
So, its in .xlam, instead of xlsm, and we're just gonna call it the JoinMacro.
Now hey, notice here that they're automatically storing it in the add-ins folder and that actually is going to work out very nicely for us here in a couple of seconds.
All right so, that's it workbook with no data.
Whatsoever, just the Macro got saved life is good. I'm actually going to close Excel.
Ok so, we reopen Excel. I need to get back to the old add-ins manager.
You know the easiest way to do that is Alt+T followed by the letter I brings up the old add-ins manager and right there.
It's already in the list JoinedMacro just choose that click OK.
Right now, what's the difference?
It's not in a personal Macro workbook anymore.
It's in a little add in that I just created.
The beautiful thing about that is when I need to use it I can just type equal join and for whatever reason I don't know what their rationale behind this is anything in an add-in and open add-in, you don't need to prefix it with the added name it just automatically works.
All right, so it's like the concept of the personal Macro workbook, but it's used for your user defined functions.
And oh hey, by the way if you get more user defined functions, will just put them right in that same add-in back.
So, JoinMacro.xlam.
There's a module you can paste more functions in here, and they'll all be available.
So, it's like having your own personal Macro workbook, but it's a personal user-defined function workbook stored as an add-in and then things will automatically work.
All right so, there you have it. When you have a user-defined function instead of going to personal.xlsb create your own xlam or if your back in Excel 2003 xla and store it there.
You will then have an access to that all the time.
Wanna thank Lon for sending that question in and wanna thank you for stopping by.
We'll see you next time another netcast for MrExcel.
