Intelligence for your function and one of the best thing is it requires no installation or registration and it therefore helps in distribution to reduce privileged environments so you don't need an admin permission to install this okay and for the sake of our viewers transitioning from vba who are following this channel from the vba background i will keep these tutorials very simplified so that even the beginners can follow so.
Before we begin please do not forget to subscribe if you haven't and please leave a thumbs up and comment if this video has helped you okay so this is the official website for excel dna and it has a quick start reference and then additional you know information on the package and then further link to github for sample codes and additional packages um and then the documentation of course so uh let's get started and then we'll.
Discuss more topics as we proceed first we need to create a class library uh with the net framework uh you can either code you know a visual basic c sharp or f sharp i'm gonna demonstrate this in visual basic but then it's pretty much the same in c sharp as well um okay so i'm gonna start a new project ctrl shift n i'm gonna type look for class and look for uh net framework okay so here dotnet.
Framework and um yeah choose if you're c sharp then you'll choose the one for c sharp for example from here you'll change the language and otherwise here you can proceed like this let's call this excel dna i'll just call this pj excel dna and then i'll store this in the youtube directory.
Okay so first we'll have to install this package uh you can find this from the nugget packet manager as well which will be here this one you can go here and search as well or alternatively you can just install by running this command line if you don't see this you can go to view other windows and then click on package manager console okay i'm gonna close this window for now.
This it's not too long as well so um yeah so we have installed our first um our package and now we can start writing our code or maybe we can just copy this into our class um i'll just actually just change that little module at least for now let's call this my functions okay um this will not work yet uh the.
Intelligence will come in after uh in the you know like later topic press f5 to start up so you can see you know like it's creating the package uh enable this for this session and then if i run this hello dna hello dna and then within the you know like pass the parameter um hello my yeah so this function basically uh takes the name as string and then return.
Hello and the name that you pass so this is basically a simple function okay so just end this um again if you go to this particular directory so here as you go here you'll be able to go to pin and then you can directly install from here as well so every time the package is published go to developer add-ins not this add-ins excel add-in browse.
And then you can select based on the bit of your office and then click on ok so that way as well you can still use hello dna and then yeah so that's basically the way to run it uh i think this will be showing now in uh yeah here hello dna so we can also create our own group here and then add that particular um the function is here though the intelligence.
Is not showing up yet okay so we'll talk more uh in a bit so that's a way to install it uh so that's our first function we can try um maybe a couple of more like um this is basically the description we'll talk a bit more on this once we do the intellisense part let's say add numbers something like that add numbers and then.
Number one s double number two as double we'll take two
Parameters here and uh you can either specify this or you can leave it like as well it will return um you can return number one plus number two sorry okay so if i run this now or uh i just build it from here you can build from here and since we already installed it there uh.It should be there now at numbers yeah so uh i can pause this number in this question so put one and one here is two right now let me quickly demonstrate how to add intelligence for the functions and the parameters um so let's go to this gallery and search for excel dna okay so we need to run this uh install this package or again you can.
Search from the manager here as well okay that will be more simpler i'm gonna install this specific version otherwise you can just run this line okay so uh in order to implement this we need to add a new plus add class let's call this excel dna um intellisense okay and now here we need to import.
integration and um here we need to implement complements ix and then okay so this part uh will take care of you know like the intelligence installing uh when it loads and then uninstall when it was this uh let me just command this.
Line donate this um server dot install and then this part will be uninstalled okay so this will take care of our uh intelligence that we have added here like the function um description that we have added so far so let me try and load this up now uh.
integration intelligence okay uh let me build this okay succeeded uh hello dna so you can see the intellisense now and then once you you know hit tab you are able to see the description now we can also add uh information about the parameters uh more summary we can also go in here and search.
So apparently there's uh the summary of the function uh the way to add uh first i'll show you how to add a category so here you just put in a comma and then just follow this category let's call this for example exo dna we'll just create a group called excel dna and then we'll store it there if i go here we should see a new uh group called excel dna okay so this is a.
Way to add uh your group there and um now if you want to add um intelligence for your parameters as well you can um edit just before the name of the parameter okay so um here you can specify excel argument and then in the bracket here you can specify uh like you did for um this function you can also specify.
Description and then input your name let's first see if this works then we'll add other ones hello and then so this is the description for the function and then here input your name so that shows up now so it works now again if you have to do this for other parameters you can here.
Input number two
Rebuild and uh of course yes you can add more information description and then you can also put the help file you can point it to you know like maybe the chm file that you have as a help for your uh application so you can input all this as well uh you can mark the trader safe uh volatile and you know like later on we will talk more about this if not i'll add more contents uh.In the blog so that you can try it out okay let me rebuild this so add numbers and then here is number one input number one and then number two five five add numbers yeah uh if you go here as well you'll be able to see the you know information the name of the.
Parameter and then the description for that particular okay uh so that's a way to go about it's i would say it's very simple to follow this um excel dna it's pretty easy to use so i'll definitely encourage you to try it out um we did uh some videos long time ago on creating custom function so if you are interested to do this in vba you can still use this you can also do add a description but uh in vba you cannot have the.
Intellisense part so excel dna is again there's no way to compare it it's definitely way powerful and faster uh okay um and then i'll add you know like uh more um you know all these links in the video description so you can read them uh it will guide you through um especially if you go to github.
And you click on excel dna you'll be able to see you know like registration actual dna tutorials intellisense the one we've discovered some samples i've covered some basic samples and then um x look up function integration uh you know like uh how if you're creating that by yourself how it's not gonna look like so definitely go and check it out and um now i'll demonstrate some more example on how to you know like read and write to excel or how to call the processor maybe this.
Is as a function so we'll write a uh process as well to run it from your vba code so uh before we proceed we can search for package here excel interlobe um okay let's install this package as well like where are your project is exposed to excel.
Objects and it's easier to work with it okay um and first example i'll show will be on how to maybe look through how to set up your application workbook worksheet for example and then we will call that from vba so let's import microsoft office dot interrupt excel um and then .
I think that will be it for now um and so in the documentation here you'll see somewhere um i'll let you check later on as well but basically here uh you can directly access the apis using this and this right excel call and excel reference for example so we'll try some of these examples um so first uh let's do this and let's look through the range so we'll instead of creating a function.
Now i'm going to create a sub procedure we'll call this um let's call this loop loop and set random numbers and here i'll say uh let's see it's a string we'll just take the string parameters uh start range as string and then.
And range as string okay first we'll set up our application uh like we did in vsto uh we'll cast our application excel dna uh you till dot application and then we'll cast this as so application okay and second will be our worksheet uh we can again as this will use this essay um that is there.
And at active workbook dodge sheet and then we'll use rsh and then this is gonna be okay and then um our range basically and then we'll just call this rows dot range and then here.
We can specify or start range and end range and then this will get in the codes and we will go further more into this topic and then rows dot and then we will and then excellent range.
And here we can specify or start an end range okay hopefully i put all the brackets correctly okay and then now we can look through the cells on rows reach row in rows and then put the cells dot value.
Let's go to application. i'll just use the worksheet function random and then let's put this something like that okay let me copy this okay so the bill succeeded i'm gonna call this let's call this test this function and we'll just say run.
And then here uh okay this micro name needs to go within the code first will be the name of the sheet and their parameter first range let's call this a1 and then we want to input this till let's say g10 okay so let me run this um okay it didn't run just give me a moment okay this name of the sheet was.
Missed okay so this is just a random number if i rerun this okay even if you put application.run or run should be okay yeah okay so that's the way to go about uh you know like setting up your application and then uh your worksheet and then you know like range for example uh here you're able to see you know like more methods once you put in this application similarly once you do a worksheet you.
Are able to see you know like methods associated with it okay so just play around i'll show one more example let's see another easy way to like i mentioned about uh how to use the excel reference um to enter a formula so let's call this insert formulas okay uh let's call this theme formula this is basically a variable name uh.
Equal to and then within the string we can just say uh equal to i don't know any formula maybe a vlookup formula anything within the string for now i'll just put in okay and then we'll say dim rev x v reference equal to new excel reference and then here you can specify the range okay for example um start row.
And n row and then start column and n columns and then we will use this excel call for example dot excel and then we'll uh this is there are other ways definitely to do that you can definitely put a string here as well but if you have to feel uh the formula this way do it faster to do it i would say and again this is just.
A demonstration there are so many other examples that you can use out there and then okay starting up so um oh sorry we'll just run this here we don't have any parameters so.
Yeah this is basically the formula uh in this section i'm going to quickly demonstrate how to add a custom ribbon to your xll add-in or plug-in um in vsto it's pretty uh easy you know like you can go add a component and then search for a designer you can add them but here uh the way to implement that is a little bit different so i'm to demonstrate that as well um we have done a lot of video on driven x.
In vba as well as in vsto so here are all the videos on ribbon x for vba and as you go up here you see all this ribbon eggs for um you know like.net or vsto products so i'm not gonna go through uh on that but i'll directly take a sample from there and if you have any questions uh please comment below and then i'll try to come back to you as soon as possible now the only difference is that um here you can add this first you'll have.
To add the uh xml here into this project the project name add-in dot dna just paste it here and then you need to add one more tag that is here at the top at the top you need to add with a capital c like this okay custom ui and then in the end you need to add again with the capital like this okay so that will be it uh in this uh we are saying scratch uh start from scratch is false so we will have the excel.
Ribbon and then we will have a the id for this tab is custom tab and then the name will be custom tab or we can just call it um excel dna and then the group is just a loader and then these are different buttons uh the label for those buttons and then the size is all normal uh the code code 1 2 3 4 so i'll just make this code all the same maybe i'll just say code 1 x or something micro one.
Let's call this micro one and this are the image that we want to add and then uh the way to implement this will be um i think we can add it here i'll create a new namespace i'll just call this excel dna namespace like that and then here we will add um we'll implement in in this class excel the.
Integration dot ribbon ribbon custom ui and um first we will need to add um our class new class maybe we'll just call my ribbon like this and uh inherits and here we can add uh the.
The callback for the micro one that we added uh where is that here okay uh so we'll just say um obliques up and then micro on and then here by val well this this can be any variable name control one s and then i ribbon control and then here you can put in the code that you want to run.
So let's say we want to run um the function that we have added here we can just insert this just for now something like that let's build this okay so see that so it should be there now um the ribbon is not here so some tagging must be wrong let me let me recheck.
We need to set this com visible property to true here let's try again okay so the ribbon is here and these are the buttons we added the group name um okay i think let's try to call another example here let's call try and call this maybe this function didn't compile.