Way back in Episode 499, I talked about adding the SpellNumber custom function to a spreadsheet. But, Gary wrote in with a problem how can I simply add this to Excel so it opens on all of my workbooks? In Episode 550, we take a look at creating your own Excel Add-In to solve this problem.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey welcome back to the MrExcel netcast, I'm Bill Jelen.
Today we have a question sent in by Gary, Gary kind of caught me a little podcast because episode 499 that's we showed you how to spell out number and words using a little bit of code for Microsoft.
He says; hey that works great in the workbook where I pasted the code but it doesn't work in any other workbooks, how can I make it work in all my workbooks?
Well, the solution to that is we have to put the code in a workbook especially kind of work book that's called an Add In, and what I'm going to do is I went out to Google, I searched for spell number get to this Microsoft knowledge base article where it shows us basically the bit of macro codes, I'm going to copy all of this from the website; use CTRL C to copy, go back to excel, and I'll go to the visual basic editor that's alt F 11 from the project Explorer I want to choose my open workbook, book 6, use Insert Module and now I get a blank module, I'll paste that data from Excel and now we should be able to test this that we should be able to use =SPELLNUMBER(1234.56) and it will sure enough spell the number out, but that only works in this workbook it doesn't work anywhere else here's the key; we have to use File, Save as and in the save as type box for a scroll all the way towards the bottom and choose Microsoft Office Excel add-in the .XLS file type, now that automatically navigates to our add-ins folder and that actually works out well here we're going to give it a good name such as spell number and we'll click save now, what we can do is go to tools add-ins and make sure that spell number is in the list if it's not in the list then won't want to browse choose the file from the add-ins folder, click OK, we now have spell number there, click ok and now the great thing is we can go to any files so let me just open any file and now we can use =SPELLNUMBER and even though the code is not in this workbook because it happens to be in an atom that's installed, it will work in all of the workbooks, so very long answer great question from Gary if you see a bit of code on the web that you want to use in all of your workbooks you basically have to add it to a blank workbook and then use file save as office add-in in order to be able to make that useful for every workbook that you ever open, of course now the big is this only works on your computer or Gary's computer if you send the file to someone else who doesn't have the add-in they will get a name error for that particular cell.
You could always convert the formulas to values pay special values before you send it off to anyone else.
Hey thanks for stopping by; we'll see you next time for another netcast from MrExcel.
Today we have a question sent in by Gary, Gary kind of caught me a little podcast because episode 499 that's we showed you how to spell out number and words using a little bit of code for Microsoft.
He says; hey that works great in the workbook where I pasted the code but it doesn't work in any other workbooks, how can I make it work in all my workbooks?
Well, the solution to that is we have to put the code in a workbook especially kind of work book that's called an Add In, and what I'm going to do is I went out to Google, I searched for spell number get to this Microsoft knowledge base article where it shows us basically the bit of macro codes, I'm going to copy all of this from the website; use CTRL C to copy, go back to excel, and I'll go to the visual basic editor that's alt F 11 from the project Explorer I want to choose my open workbook, book 6, use Insert Module and now I get a blank module, I'll paste that data from Excel and now we should be able to test this that we should be able to use =SPELLNUMBER(1234.56) and it will sure enough spell the number out, but that only works in this workbook it doesn't work anywhere else here's the key; we have to use File, Save as and in the save as type box for a scroll all the way towards the bottom and choose Microsoft Office Excel add-in the .XLS file type, now that automatically navigates to our add-ins folder and that actually works out well here we're going to give it a good name such as spell number and we'll click save now, what we can do is go to tools add-ins and make sure that spell number is in the list if it's not in the list then won't want to browse choose the file from the add-ins folder, click OK, we now have spell number there, click ok and now the great thing is we can go to any files so let me just open any file and now we can use =SPELLNUMBER and even though the code is not in this workbook because it happens to be in an atom that's installed, it will work in all of the workbooks, so very long answer great question from Gary if you see a bit of code on the web that you want to use in all of your workbooks you basically have to add it to a blank workbook and then use file save as office add-in in order to be able to make that useful for every workbook that you ever open, of course now the big is this only works on your computer or Gary's computer if you send the file to someone else who doesn't have the add-in they will get a name error for that particular cell.
You could always convert the formulas to values pay special values before you send it off to anyone else.
Hey thanks for stopping by; we'll see you next time for another netcast from MrExcel.
