Events and also to automatically insert a line of code snippets using a clipboard api to continue that in this short video i'll demonstrate how to dynamically.
Construct your vba code on runtime and insert it into a current module okay so before we begin if you haven't please do not forget to subscribe for.
Latest updates and to support the channel okay before we continue let me give a quick recap of what we have learned in our prior videos so uh.
This is the code that creates uh a control in the insert menu as you can see here and here is the insert and for example if you want to add it.
In the bar here itself you don't have to you know like include include this you can run this and it will get you know added here this can be a menu and with sub controls.
A control here and the important thing here is that we have created a class object uh to handle the you know like the events and this is the method we're calling.
Okay so um here we are know like creating a a control and then we're linking this um you know like this method here with the command bar.
Uh control yeah and then uh we are storing it here so that we can you know once we do the cleanup of the memory we can delete it later uh that's from the collection and then.
One other thing we pass the parameter basically the address from where we want to pick the code so once this button is clicked it's gonna go here uh this event is.
Gonna fire and then it's gonna pick up you know like what should be the parameter distorts arrange address and then on action it runs the code.
And then once the code is fired it
Stores uh in the clipboard it stores whatever the code is there in the address so for example it will.Pick up this particular code in this example and it does a control b wherever the cursor is at that point of time yeah so let me continue from here.
We'll insert a new module let's call this um stop at that new sub or something like that yeah um.
Let's make this the function that we can call multiple times okay so a couple of things first we will need to grab uh where the you know like the current.
Module is at the moment where the cursor is placed at that point of time so that we can directly insert a code there itself instead of using a control v.
We're going to identify the cursor location and then we're going to insert a code okay so first we will need um to know where the current module is so for that we can.
Use um application application dot project uh some of these um prerequisites which we have learned in our private video so don't forget.
That you need to set that up otherwise it's not going to work okay so it says this module 3 so if i go in here and run this code it returns name of the current crusher.
Okay so we'll get this and first we'll say uh theme code module let's call this code module object as what module okay and then it's gonna be.
This
And then um i think this is gonna be the name let's call this current module so this is the name of the current.Module and then we'll set up the the object uh this will be active workbook so that you know like once we save this as an add-in it will.
Run in any workbook that you're working on instead of uh this will be at the back end and then uh it will insert a code in the project that you're working on okay.
So active workbook dot vb project dot vb components and then this is where we'll specify the module that we want to work with and then we'll.
Say this is called module and yeah and um now to get the crusher location uh there is a metaphor get selection so for example if i run this now.
Application dot baby dot difficult pain dot um get selection here you can uh you will be able to arrive at.
Oh you know i all these variables so let's say that this is a b c and d yeah uh so this will uh return us you know like the where the cursor is at.
That point up there so i'm gonna do this dim a as long okay this symbolize long if you're not familiar okay so that will be our variables so as.
Soon as this code is executed till here if i press f5 uh commodial skin on a second this kind of a string so if i f8 so it says it's at 12 okay.
Now if i run by placing my cursor here i can really do that because now let's say that i put it here in 11 and if i rerun this now uh it's gonna change because you know i.
I'm doing f8 but anyways uh later on when we implement this it's going to be okay so this will return us the cursor location um at that particular time when.
You click the button for example let's see i click here and uh um my cursor is here and click the button it's going to return 18 as you can see here yeah okay.
Now let's continue um so from here we'll get let's say let's call this next line that will represent the line as long and then let's say that we want to.
Insert it if you want to insert right where it is selected you can put a or plus one should be okay and then uh first let's construct a simple code.
Let's say we want to insert uh five six lines of data so let's say dim uh snippets both snippets and this can be one two six yeah and then.
So let's say that we want to insert a sub procedure every time we click this button dynamically and then construct the error handling and everything okay so.
I'll just um let's see i'll make this quickly then i'll just say hello world um instead of functional and then i can say on error go to error let's make it standard.
Error handler and then we'll delete itself error handler location and then we'll say message box.
Error info let's say that we want to generate this code automatically here first let me construct it here error dot.
Number and then let's say description and maybe we will um the description just close this for now and if you want to include where the.
Error happens so we can also say source code or something like that and here we can specify what will be our circuit so for now i'll just hard code as a hello.
World and then this can be passed as critical unexpect that something like that yeah so let's say.
That we want to construct a code like this so some of the variables here will be this will be dynamic and as well as this yeah so i'm going to.
Copy this code um for now i'll just put it in this version okay so snippet one is gonna be this.
Similarly this this this and this for this we will have to reconstruct a bit because there are you know like double quotes there it's not gonna.
Be good okay uh one two six so one two three four five six so the easiest way will be to have this kind of code in the spreadsheet.
And then you read it from there by dynamically passing the value but i'll show you this example so that it gives you some um you know like some other ideas okay.
So let me do this let me replace all the this with this symbol okay and then i first put it here like this.
So that it does not error and to return this we can replace it back um by using a replace function replace and then this will be the string.