Well so in case you missed them be sure to check the video description for the links while creating add-in like any other vba projects you can include functions and procedures um ribbon customization context menu hotkeys for the shortcuts and so on the main difference is that these functionalities will be available to any workbook and the sheets in the project workbook remains invisible in the future videos i'll also uh demonstrate how to create edit for access powerpoint or document.
And also for outlook okay so before we begin if you haven't please do not forget to subscribe for latest updates and also to support my channel let me add a new workbook let's start off by uh creating an add-in with basic functionality and then uh once you're familiar with this we'll you know like add more custom and advanced functions okay um so like i've said earlier if you you know like once you save the workbook as uh an add-in which is xla for 2003 versions and dot xlam.
Dot x l a for 2003 versions and then post that will be excel am okay these are the file extensions uh once you save your file as this extensions you will not be able to view your sheets okay so if you plan to use your sheets uh to add the data maybe so that you you can save some user preferences or for any xyz regions if you're going to use the sheets then you can start off by developing in you know like xla xlsm or xlsb maybe and then once you're done you.
Can pop this as xla or xlc excel am okay so now let me go to my editor uh first let's name this as excel you delete these okay and if you want you can put a password as well and for now i won't do that okay put a unique name so that once you install the add-in you'll be able to identify that easily okay um inserting a new module now let's add a function basic function just to add numbers um okay and i'll include two parameters number number one as double and then number two as also.
As double and it's going to return us now let's say that we want to return n one plus and two okay now copy this if i put in here it just recognizes that right now a1 and b1 okay let's say i want to add these two numbers so once i put a number here 310 right some of this now if i copy this and add a new workbook okay so this book eight and book nine if i add here it's not gonna understand because um this this particular function is specific to this particular.
Basically save as xlam okay so i'm gonna do again f12 and then i'm going to choose xlam okay so once you choose that it will navigate you to the add-ins folder but you can also save it to the folder of your choice okay and then you can install from there so now i'm gonna save as in a different name called excel utilities dot xl am okay okay so so let's close this now for now so here is our uh add-in file that we just published now i'm gonna copy this part open a.
Brand new workbook and from your developer tab and also for the shortcut is alt i it will bring up this window or you can click here um browse put in the pod and click ok now it you can see that excel utilities has been added okay now irrespective of the workbook uh this particular function is gonna work okay so i'm gonna add a couple of workbooks here book three book two okay and if i do this now it understands yeah so you can add um you can use this function anywhere now here as well.
Um you can put a number here as well directly okay so that's basically how you will be creating an um an add-in um and let's say that um let me just add one more functionality let's call this um public sub um let's call this hello world and then this will basically just return us message box hello yeah and now i'll just click on save from here okay normally when i create an add-in i have um because when you're working with the add-in file you're not able to see the add-in file itself uh you know the sheets so when you save.
You know like it creates a problem so normally i have one uh code to save the workbook so then i'll just say this workbook let's see so that way i don't have to go and click here maybe the it will save the active workbook instead of the project okay so just be sure of that um okay so we have created this uh hello world now now we we should be able to um if we have let's see another project okay so i'll call this um vba project do something like that.
Let's say we want to call this particular uh method or procedure from another you know like vba project so the simplest way will be just to add a reference to that particular project okay this editing project like this and now you should be able to call this test now i should be able to just say call this okay so if i run this it says hello world is able to understand that um normally let's say that this is vba projective okay let's say i have a process called um.
Micro one yeah and this will just say return micro one normally if you have to um call this micro one
From let's say another project okay let's say this is um we'll call this vba project one i hope this is not confusing you but uh i'm just trying to demonstrate that uh if you add a reference you can directly call the function like this otherwise you'll have to use a run function or run method okay so from project one i'm gonna say sub um test macro one now if i right here micro one and run it's not gonna understand because uh this micro there there is no way to.Understand where this micro is located okay so the way normally to go about it will be to use the run function and then um we'll take the name of the file where the code is located so here macro one is located in book two right so i'll say run um and then we'll put in the code macro one and then uh we'll just put the single code around the workbook name okay so if i run this now it says macro one.
Now one small drawback of this method is that you have to ensure that this particular workbook is open or is it going to throw an error whereas if you link to the add-in for example you can call the methods and procedures um as if you know like uh it is located within the same project okay now going back uh let's take a step further in this section we'll create a custom excel utility uh to highlight rows and columns could be a single or multiple cells yeah and then we'll make it work not only for this particular sheet but also to any work that is open we'll add this functionality to.
Our add-in uh this utility uh will be helpful when working with uh you know like a huge range of tables and data sets while doing your analysis so for example if you click on a cell like this the code will highlight like this and if you click on more cells holding down the control key it will highlight in this fashion so let's create a functionality like this and then we'll create a class object to handle this okay now let me go um first let me create in this uh project 2 xlsm file because add in file we cannot see the sheet right even if you add uh you know like the code here it.
Will be specific to this particular sheet okay so it's not gonna work so let me first demonstrate in this particular book and then we'll we'll take a step further from there okay so from here um select uh selection change so basically what is going to happen with this event is target dot address now basically if you click here it will show where you click yeah okay now to add this basic functionality let's create um.
Dim column x this will hold the column address and rows row x let's call this this r string yeah this dollar sign symbolize string okay and then column is going to be target we'll get the target entire column address okay so column is equal to target dot entire column dot address and then um we'll do a similarity for the rows rows is going to be target dot.
Entire row dot address and then um now we can a replace of those dollar signs so for example if i do this active cell it's going to bring us the dollar sign yeah so i'm just going to get rid of that by using a replace replace expression we want to remove the dollar sign with the blank okay so similarly we'll do it here.
So once i have this i can say range um the order does not matter now and then um row x that's column x row x dot select okay so let's see if this works okay sorry that's my pad let's first turn off the events and select and then because as soon as it selects it's further you know it runs on activation um so again let me first turn off select and then i'm gonna turn it back on.
So it's gonna turn false when it's clicked and then it will select the row and then turn back that events on okay okay now it looks good so let's say that you're working in a huge data set you can scroll all over or you know it could be way down as well yeah okay so now ideally uh what if you want to include this in all the sheets that are there in your workbook ideally what you'll do is this is basically you will have to add more handlers and everything but this is a basic uh code you'll have to keep.
Replicating this code in all the sheets let's say that you have so many sheets that you want to use this same functionality you'll have to keep adding them and if you have other workbooks where
You want to use them then you'll have to keep on replicating this code in those sheets as well yeah so now if you want this particular functionality to work in all the sheets of any workbook then you will have to create a basically you'll have to create a class object to handle this kind of events otherwise it's not going to work okay it's not going to work efficiently.So let's take a step further now i will create um let me just disable this now anyways this is located in the project too so it really does not matter um okay so let me create um a class um in this add-in file okay now if you're comfortable working with xlsm for example you can first work and then every time you have to create an add-in you can simply save us or you can do a publish uh basically a save as function with the name um and then you can call you know to create an add-in on runtime okay.
So first uh again going back to our project i'm gonna create a class let's call this class as um small call this as c c four class and then we'll call this application ensure okay and then in this class we're gonna add um basically we're gonna create an uh application object with now now i'm going to call this private and then with events so that events are associated with it with the events and then the name of the method object as and then application so now we have created one object and.
Now we can use this app now we'll look for a sheet change event okay this one yeah so um the events will be handled from here uh we'll first have to initiate it um let me get rid of this and plus initializer here we will set up our app uh equal to application which is excel so yeah so now we have this here once we're ready we'll move our code which we created here into you know like here it'll be handled from here okay so for now let me just leave this part out and we will have.
To you know like once the workbook is open which is our add-in workbook once this open we want to set this application to uh this class okay so um let's call this private uh let's call this variable this app let's see f okay and that is equal to near.
So i'll come here and run this code just to make sure probably see it okay um and i'm gonna disable this code now and let's try and restart and see if it works let me start off project one this is where there's now so if you go back here you can see there's no uh you know like events related to this particular code but this selection is happening from here now okay.
Like this now if i add a new sheet it works here if i add a new workbook also it works here this book too adding another workbook book tree okay so this is how you can handle um by creating a simple like object like this okay and then working with the events okay so now let's take a step further um this works only for a single cell what if you want to select more cells and highlight more you know columns yeah so we'll try to do that now.
Now before we go ahead and improve this code um we need to have a mechanism to turn this off because we don't want this auto selection like this to add the highlight to happen all the time okay so let's go ahead and add a toggle button here uh so that we can turn this highlight off and on um for that i'm gonna take uh some quick customization from um ribbon driven x series uh episode one and five basically for the toggle button okay um so i'm gonna launch this custom.
Ui editor um i'm selecting the excel utilities dot excel i am okay then i'm gonna copy this and paste it here so here um basically we are creating uh a custom ui uh scrat start from scratch is off um because we want to have the ribbon um now we don't want the custom type yet let's just leave it like that in the home tab we are adding a new group called custom utilities and then we're adding a toggle button this is the image for that and then the label is this and the.
Size is large when the button is clicked click is gonna run this particular code okay now if i go in here we can get a callback for this okay so i'm gonna save this and close let me open this file so now you can see there's a button here i'm gonna go in here add a new module and i'm gonna add this code here okay um and then i'm gonna create a public uh let's call this activate status so whenever this is clicked this button is click we'll get if it is true or false yeah.
And then we will make this code run only when this is true if this is true then else don't run this code yeah and next step will be to improve this code let me go back here and save this let me call this ribbon x okay so now it's off you should not do anything if i toggle this on it should start highlighting yeah okay so that's that's taken care of now now we can take a step further to improve this particular.
Code now if you're happy with this part of the code then you can definitely skip forward but uh for example this should work okay till a couple of cells or you know if you select adjacent cells like this it should work okay but the moment you start selecting you're like more than this it's gonna you know like turn black that is because it is selecting the entire cell multiple times uh because every time you click sell uh it's recording this uh address right so the first time.
You click it's just one address but then once you redo it it becomes more and more yeah like this okay so the idea here is um basically to add a unique address using a dictionary we're gonna identify which address is unique uh and does not exist yet in the selection then we're gonna add those uh we're gonna use array to uh you know like um store the data and then in the end we're gonna convert that array into a range and then do a selection okay so let's give it a shot.
Um and then we also need to identify if the control key is uh you know like hold down so uh it's active so we'll use a simple api as well for that okay but before that let's create um let me just call this some functions something like that and then this one can be highlights or something okay so let's call this um look address maybe target address.
And then we'll take range as uh input so every time the cell is clicked we will log that address and then in the end once the control key is up then we will consolidate all the address and highlight them yeah okay so team selection count just to make sure if uh the selection code is more than you know like if it is zero then we don't want to run it so we'll call the selection count variable as long and selection dot.
And well i think we'll have to put this within the error handler then we'll put a basic validation if selection count is lesser than one that is zero then we want to exit okay so there will be a start and that is to ensure the selection second we will have to identify um if before logging the address we need to identify if uh it is unique yeah so that because what happens when multiple cells get selected multiple times like this it becomes.
Dark so we don't want uh that to happen okay so basically that's why we need to identify if the address that is being passed here is unique or not so every time it selects uh it's not going to be one um the second time it passes it's gonna be you know like uh address of all this selection okay so just to handle that part now for this we will need um a dictionary but then we'll have to store this in the public variable because um we'll have to use this outside of this particular.
Function as well this just to lock the address and then we will need to create one more to select uh the final address okay so let's call this range underscore dictionary as uh scripting dot dictionary it is not uh let me first reference this microsoft scripting uh runtime okay let's check this box now we'll create a dictionary if this uh is nothing then we want to create that this equal to we will create object and this object is going to be the scripting dictionary now next uh let's collect this address.
For now let's not run this anymore i have to keep saving this just in case it gets lost okay so we will need this address the target will become our x range whatever range was passed um we will not select all right so now um we will need to concatenate this and then we will have to split this range because uh it it is going to come multiple so some of them it could be the whole row like this some address could be you know like uh duplicated so we'll have to split them using.
Uh using a comma and then we're gonna arrange that uh check if it exists in the dictionary and then if it doesn't we're gonna add that to the dictionary so that next time we can validate again and then we will use uh whatever is added to dictionary will be added to uh array as well okay so let me just uh quickly type this out and then we will um discuss this again so let's say dim range address addresses as string and then we will need to split so we'll call this.
Split this will be our array as string then we will need the index for looping later and then the key that we'll be using for dictionary this will be string all right and then this range address will be our this and we'll just basically concatenating them okay um now we will check if um log already exist first we will check that so uh let's create a new public variable this is going to be our final um array let's call this memory.
Array range or something like that a string this will be a string array string and then just copy and replicate this block now let's say uh js long this will just take u-bound off.
Just to check if our address already exists i mean the log already exists so that we can happen further and then now we can also have growth as long and then draw is gonna be this will be just to replace if the whole entire row i mean uh all the rows from one till you know like rose.com is selected so we're gonna replace that uh in a moment um.
And then now we're gonna split this address i'm sure you're already familiar with the split functionality it will split uh this address uh using a comma yeah so if there are multiple range i think once we run this with the debugging leader then we'll be able to understand more yeah so uh this will be equal to split then we want to split this address using a comma now we have once we have this we want to loop through all the range we'll start using our eye now.
More eyes go to lower bound lower bound of splittedness to your bound of this and then our dictionary key is going to be uh whatever key at that point of time it is actually key is going to be this and our i and now we can start uh to check if this particular uh value exists in the dictionary okay.
And if it doesn't then we will uh start storing this in our you know like memory all right if if not this dot exist and then whatever key it is at that point of time um if this key does not exist then we will add this uh we will increment our array so if the you know like uh it already exists it's gonna identify from here if it is not you know it keeps on incrementing.
J plus one and then we want to preserve the prior values let's see already we'll have to preserve the values and then this will be one to whatever is the total at that moment as string same data type and then we'll say this and then j will be equal to this key so now we basically we've just checked uh we're looking through um the addresses and then um we identifying the key whatever is that at that point of time after.
The split you know like split it and then we're taking if we didn't exist in the dictionary if it is not if not yeah then we are uh re-declaring our array and then preserving the prior value and then we are assigning uh the key to that particular memory location yeah to the array and now um we will have to add this also to the key otherwise the next time we want to recheck it does not then we will do add and then we will be d key and this part can be nothing.
All right uh so now we have added that now uh one small small changes for example uh whenever we do the first selection like this uh it's gonna select the entire cell like this uh rows and column so the next time you click it is also going to pick up the entire you know like row number one until whatever the rows is so here i'll just add a functionality just to make sure that that is excluded okay so in string and then within this we want to start checking from the first uh letter uh character and then we want to make sure this key whatever the key is at that point of time.