Free Excel VBA Course #32 - Creating Excel Add-ins

hello and welcome to the VBA jetpack course by trumpet sir I am Samantha and in this video we will learn how to create and how to use add-ins in VBA in Excel so let's get started in one of the previous videos we learn how to create custom functions and here I have a workbook with the name custom functions dot XLS M and I have a function a custom function in the backend and let me first show you what this function does so the name of the function is a single cell extract and here I have the data I have country and I have the sales rep name for each country and then I have these country names here and what I'm doing is I will use this custom formula to bake this as the lookup value and then it would fetch all the matching sales rep name for that particular country so let's say if it is India then it would it would give back Joe Sam Jane and Akash because these are the four matching sales rep names for this country similarly for us it will check for all these names let's here here it is Jack and Jane and Joe and Sam so it would give these names in a single cell separated by a comma and a space so let me show you how this function works I would type single and you can see that this function gets shown in the intellisense I press tab to autocomplete and then it doesn't show me any health menu but I know what the arguments would be the first argument is the lookup value I would press comma and then I would select this range I would press f4 to lock it and then I would select the column number which would be 2 in this case and now when I press ENTER you can see that it gives me these results these names in a single cell and I can do this for all these countries now this custom function has made life so easy I don't want to I don't have to do it manually and I don't have to go for other tricks so this custom function could be something that you have to use again and again in various workbooks maybe you're getting such kind of data from a database or from a colleague or maybe you're collating it yourself and you need to use this function on various workbooks now to do that one way is that you go to the VB editor let me press alt F 11 to go to the VB editor and here I have the function the code for the function one way is that I copy this and then I paste it again and again whenever I have to use it so let's say I open a new workbook I would press ctrl n it opens this new workbook book 1 and if I try and use this function I will not be shown that function because this function does not exist in this workbook and if I have to make it available for this workbook one way would be to go back to the VBA editor and then paste the code for this workbook so if I have to do this I would come here I would have to paste this function here insert a module for book 1 and paste this function here but this is not the most efficient way if you have to do it for many functions let's say 20 custom functions or it could be 20 functions and 10 procedures then it's not a very practical way to go back and copy/paste it again and again in various workbooks the workaround for this is to save this as an add-in and then you can make this add-in available to all the workbooks and then you can keep on editing the add-in you can add more functions you can add more code to it and that would be available for all the works for books whenever you open a new workbook so the first thing that we need to do to save this as an add-in is go to file and here we would go to save as and I would come here and open browse and within browse I would change the Excel macro enabled workbook type I would come here and I would select dot excel am which is Excel add-in if you are using 2003 or prior versions you would have to use dot excel a in this case let me select dot X L am and see what has happened as soon as I selected it you would see that it has automatically changed the location of the file so Excel Microsoft Excel has already provided a default location where you should save your add-ins and it's a good place to save your add-ins but it's not necessary that you have to save it here only you can save it anywhere so let me go to the desktop and here let me save this and let me call this single cell extract so let me call this with the name only you can call it with any name you want I will call it single cell extract and I save it now if I go back to the desktop I have this single cell extract add-in and you can see the icon has changed now to add this icon to a workbook say in this case it's book 1 and to add it to this workbook I would go to developer and I would go to add ins when I click on this button it opens the add-ins dialog box and here you can add add-ins which are of L which would be available for all the workbooks so to do that I would first have to browse it when I browse this and go to desktop I have this single cell extract dot excel am file I would select this file and I would click OK and you can see this is a dense type as soon as I click OK it gets available here and now I can simply click OK and that is it now this function is available for this workbook or any workbook for that matter let's try this and you can see this function is now available which was not the case earlier if I go back and if I copy this data here and I try and do that thing again let me use this function and I would take this as the lookup value this would be the range let me press f4 to lock it and the column number would be 2 and now if I hit enter you can see that this function is working so I don't have to take the longer route of making that code copy paste again and again for different workbooks I can simply make it available as an adding now this atom is available let's try again I open a new book this is book two and if I type this name single cell extract it's available here let me close this workbook now what if you have to make changes to the adding file how do you do that so to do that whenever you wherever you are in whichever workbook simply press alt F 11 to go back to the VB editor and here have a look at the project and you would see that you always have this project available which is VBA project single cell extract dot excel am this would always be available and when I open this you can see that there are modules and this is the module where I have the code now let's say I do not want comma as a separator I want something else and you want to make this change here then you can simply delete the comma and remove it maybe let's say this pipe symbol and now I can click anywhere in this VBA project I can click here or anywhere and press save button and now your VBA add-in has been saved the changes have been saved here now you can go back and let me simply update this so as soon as I do this you can see that comma has gone away and now we have this pipe separator and similarly you can add more code add more functions add more procedures to it now you can save this add-in anywhere you can save it in a desktop if you want you can save it in a shared folder and then you can even email it to other people and they can add it to their workbook so this is the way you can create custom functions you can create your own codes and you can have it available across workbooks now if you do not want other people to have a look at the code that you have created then you can protect the add-in as well so to do that let me press alt F 11 and here what you need to do is go to the VB project maybe a project where you have the name of the ad in a right click and go to VBA project properties and when I click on this it opens this dialog box VB a project project properties and you have the protection tab here I would simply select lock project for viewing and I can give a password to it in this case let me give the password as 1 2 3 five and I would confirm the password and as soon as I click okay again I need to click somewhere in this project maybe it's this VB a project or any object within it and I can simply go to save button and now I can close this and I would also have to close the Excel workbook so if I close everything and I open a new Excel workbook let's try and go back to the VBA editor and see if we can view this code or not and when I click on this plus icon you can see that it shows VBA project password and I would have to key in the password to view the code if I came the right password then it would open this for me and now I can view this module so this is how you can also protect an add-in and then when you share it with other people they will not be able to see the code so this is how you can create Adan's and you can then use it across workbooks that's it in this video I hope you found this useful thank you and have a nice day

Channel: TrumpExcel

Views: 10,019

Rating: 4.9302325 out of 5

Keywords: excel add-ins, excel add-in tutorial, create excel add in vba, create add-in, create add-in excel, create add ins for excel, create excel add in ribbon, excel macros, excel vba, excel vba basics, excel vba for beginners, excel vba training, free excel vba course, sumit bansal, trumpexcel, vba, creating excel add in, creating excel add in vba

Id: XnZ1Vz4GvqY

Length: 9min 40sec (580 seconds)

Published: Tue Mar 24 2020