Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Excel has found a permanent niche in most corporate environments. Its usage is not limited to accounting tasks either. In this movie, I'm going to show you how to extend it even further by creating your own add-in. Remember that an add-in is an application that is installed once and then loaded each time Excel runs. It's not tied to a specific document. For this demonstration, I'm going to create a custom ribbon inside Excel 2010. I'm inside a project called ProcessAnalyzer. I've already written the demonstration code for today. I'm going to start by showing you how to add your own custom ribbon to an Excel project.
You right-click on the project, and choose Add > New Item. Here is the ribbon designer that I want to use. Now, I already have a ribbon, so I'm not going to go ahead and click on the Add button. Instead, I'm going to cancel this one and show you the existing ProcessMemoryRibbon I've written. As you can see, it has a designer surface. There's also a special section on the Toolbox that contains Office Ribbon Controls. So if I wanted to, I could take this CheckBox here and then drag it over and drop it on my designer surface.
Of course, I can go to the code behind and manipulate this item, or read this information and push it into the Excel document. Now let me show you what I mean. I have this button here that I've added an icon and some pictures to. You can see it's a Button though. If I switch over to the code behind view by pressing F7, and scroll down here, you can see I've written a Button1_Click procedure. I start by creating a reference to an Excel worksheet. Then I go to the Globals area, and I find the current application.
I get its Worksheet collection, and I tell it to add a brand-new worksheet. Once I've done that, I go into the sheet and I print some text into the A1 cell. I then print some other text into the B1 cell. And then I have a small link query here that goes out and talks to .NET and gets all the running processes on the computer. I then take those, apply a link query to that, and I say, I want to order them By their WorkingSet, how much memory are they using, in a descending order.
Then I select out the processes. Then down here, I take the number of items--five, ten, fifteen, or twenty--and I convert this to Array and assign it to their process. And then down here, I'm going to generate some cells in my spreadsheet. This number is coming from my Ribbon value. If I go back to my Ribbon, you'll see that I have a Count dropdown here, and I've populated this with words like "Five, Ten, Fifteen." So that's why I'm getting that Count value.
As you can see up here on line 20, I'm getting that from the Gallery, my Gallery, SelectedItem.Tag, and then I'm just returning an integer value. So down here, I am iterating over the processes.Length times one--the number of items inside this array--and I'm printing out a cell with the ProcessName, and its WorkingSet. Then I'm changing the NumberFormat for the cell. Once that's done, I select that range, and I create a brand-new chart.
The name of the chart's going to be Memory Used. So, what's going to happen, when you click on my add-in, I'm going to look at the operating system, tell you the number of processes that are using the most WorkingSet memory, and then generate a chart out of that. Does that sound good? Let's see if it works. I'm going to press F5 to run the application, which will launch Excel, load my add-in. My add-in is living in this Add-Ins section. Office 2010 has a separate area for all custom Add-Ins. Here is my item.
Here is my Count. There is Five, Ten, Fifteen, right? So I'll choose Ten. Now, I'm going to click in this button, and it generated this sheet with the names of the processes and the amount of memory used, and it generated this chart, which shows that Visual Studio is using the most memory of all the applications. Now, your user can uninstall this add- in by going to their Add-Ins menu. Or if you're a programmer, you can go to the Developer toolset to remove this.
Let me show you how to do that. I'm going to go to File > Options, click on the Customize Ribbon, and then I'm going to check the Developer tab here and then OK. Now, I have a new tab right here, and I can go into the COM Add-Ins and find out my add-ins. Here is the Load Test Report add-in, and I can uninstall that. The other way to uninstall this is to go back to Visual Studio and choose this Build > Clean Solution.
This will go out and remove it from the list of add-ins to run the next time Excel runs. So, I'll choose Clean Solution for my final demo. This concludes the section on Office automation. Many of the other Office products have similar add-in templates. I encourage you to explore your own ideas in your favorite Office tool.
Get unlimited access to all courses for just $25/month.Become a member
61 Video lessons · 100103 Viewers
56 Video lessons · 113140 Viewers
71 Video lessons · 82006 Viewers
131 Video lessons · 39344 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.