Today I want to share some Excel magic with you. If you are anything like me you probably want to optimize your workflow a lot. One way to do that is using Excel macros. But these require some VBA programming knowledge. I’ve been using macros to save time for quite some time now and want to share my favorite one with you.
Today we are making a macro that assembles many files into one. But what’s more interesting, I am going to show you a way to put this macro as an Excel function, in its own ribbon tab. We are also going to be creating a simple macro to delete empty rows, to make our own ribbon tab look better.
OK, let’s start with creating the macro itself. If you are unfamiliar with Excel macros, there are a few ways to create them. For one, you can use the Record Macro option, which I don’t recommend. The other way is to write some code.
Step 1. Developer Tab
Show the Developer tab in Excel by going to the File menu and then selecting Options. Go to Customize Ribbon and tick the check mark in front of the Developer tab to show it. Click OK and you should see the Developer tab in your Excel by default now.
Step 2. The VBA Editor
VBA stands for Visual Basic for Applications. It’s a language that is pretty much Visual Basic and is used to extend Excel, as well as other Office applications. You can use it to write macros, or develop whole applications relying on Excel, Word, PowerPoint, Outlook, Publisher, etc.
In my opinion writing VBA is a hassle. The editor’s auto-complete has always been my biggest issue… or more so the lack of comprehensive such.
What I usually do is I define the problem I want to solve and start googling for something similar that is close and readable enough for me to change.
To open the VBA editor you can either open it from the Developer tab, or you can press Alt + F11. The editor opens. On the right is the code are, where you write your magic. On the left side is the Project panel and the Properties panel.
Step 3. Delete empty rows
So let’s start with the simple macro – deleting empty rows.
In VBA for Excel you can attach code to Sheets, to the Workbook itself or to modules. It’s best to use modules as we can then distribute our macro by saving it as an Excel Add-In (.xlam). Go ahead and save as .xlam your empty file. The important thing about being able to save this macro as a distributable. To do that simply right-click the Modules folder of the now open file and select Insert -> Module.
This creates a new Module1 in the Project hierarchy.
It’s now time to write some code.
We are going to separate our delete empty rows code in its separate Sub Procedure. We will go over the entire column of the active cell and delete blank rows. We will also prepare to include this Sub in a Ribbon by making it take a IRibbonControl argument when it’s defined.
Sub deleteBlankRows(control As IRibbonControl) On Error Resume Next ActiveCell.EntireColumn.SpecialCells(xlCellTypeBlanks).EntireRow.Delete End Sub
What this code does is define a new Sub Procedure called deleteBlankRows, which takes an argument of type IRibbonControl (we need this for placing it within a ribbon tab). The procedure then states that if an Error occurs it will simply resume and not throw.
Then we use chaining to get the ActiveCell, then the EntireColumn it is in. we then filter cells by using an integrated cell style type xlCellTypeBlanks, which filters all the rows where this cell is blank. We then select the EntireRow and Delete it.
End Sub finishes our Sub Procedure. It’s not perfect, there are no warnings or confirmation for our actions, but it works. Important to remember is that Undo does not work after macros! Keep that in mind.
Step 4. Adding a custom Ribbon to our Excel Add-in (.xlam) file
Now that we have our macro saved as a module in an Excel Add-In file, let’s add a custom Ribbon tab to it. The easiest way to do that is to use an external tool called Custom UI Editor for Microsoft Office.
The tool is available on many places, just google it. It says that it’s for Office 2007, but for me it works perfect with Office 2013. After all, the Ribbon functionality has not changed in essence since Office 2007, and is still the same in Office 2016.
Once you have the tool installed simply open your file with it. You should get something like that.
So, now let’s place some code here as well. The interface of the Ribbon system is created via XML, which stands for eXtensible Markup Language. It’s pretty straight-forward once you know the right tags. I’m just going to give those to you.
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui"> <ribbon> <tabs> <tab id="customTab" label="D-TOOLS"> <group id="excelHelpers" label="Excel Helpers" imageMso="ChartRadarChart" autoScale="true"> <button id="customButton1" label="Delete Blank Rows" image="rowDelete" size="large" onAction="deleteBlankRows" /> <button id="customButton2" label="Combine Files" image="combine" size="large" onAction="combineFiles" /> </group> </tab> </tabs> </ribbon> </customUI>
What we do here is really simple. We create a customUI, nest a ribbon tag in it, to access the Excel ribbon menu system, then go into the tabs and create a new tab labeled D-TOOLS. In it we create the Excel Helpers group and add two buttons within. One is for our Delete Empty Rows macro and the other for the files combining macro we will code in a bit. Buttons take the following arguments:
- Id – id of the button, needs to be unique.
- Label of the button
- Image – we can add any png image as an icon, we need to add them from the Insert Icons button in Custom UI Editor for Microsoft Office, then we can reference it in the image argument by name
- Size – the size of the button
- onAction – the name of the Sub Procedure we want the button to call, be careful to type this properly, otherwise the button won’t work.
After adding the images (pick your own) and XML code, you should end up with something like this.
Step 5. Writing the combineFiles macro
Ok, now for the fun part. In order for our second button to work, we need to add our second macro in our .xlam file. First, close Custom UI Editor and save the file. Open it with Excel. You notice there’s no sheets in the file, this is because it’s an Add-In file only. Press Alt + F11 to bring up the VBA editor and go to the Module1 we created earlier. You should see your deleteBlankRows Sub Procedure.
For this macro we will use a Sub Procedure that the button will call when pressed. However, we will also use a Function, that will be used by the Sub Procedure.
Here’s the code for the Sub Procedure:
Sub combineFiles(control As IRibbonControl) Dim path As String Dim FileName As String Dim Wkb As Workbook Dim WkbCurrent As Workbook Dim ws As Worksheet Application.EnableEvents = False Application.ScreenUpdating = False Set WkbCurrent = ActiveWorkbook path = "C:\Users\user1\Desktop\1" 'Change as needed FileName = Dir(path & "\*.xlsx", vbNormal) Do Until FileName = "" Set Wkb = Workbooks.Open(FileName:=path & "\" & FileName) For Each ws In Wkb.Worksheets ws.Copy After:=WkbCurrent.Sheets(WkbCurrent.Sheets.Count) ActiveSheet.Name = FileName Next ws Wkb.Close False FileName = Dir() Loop Application.EnableEvents = True Application.ScreenUpdating = True End Sub
Let’s walk through our Sub Procedure and see what it does.
First we create some variables using Dim. Dim is short for the word Dimension and it allows us to declare variable names and their types. We need the following variables:
- path – where our files are, this will be a string
- FileName – current file’s name, also a string
- Wkb – the Workbook we are combining in
- WkbCurrent – the currently open workbook that we copy from
- ws – the worksheet we are currently copying
Then we set EnableEvents and ScreenUpdating to False while we run our macro so that nothing interferes. We set the WkbCurrent to the ActiveWorkbook, this is where we will accumulate all sheets from all files we select.
After that we set the path. For now we set it via a string directly, but later we will add a Function to let us pick the path each time we run the macro.
Then we use a loop. The way it works is that Dir function loops through all the files in the path and then does the Do Until block of code for each FileName while the list of files ends and FileName becomes empty.
What we do in the loop is repeated for each file. It opens the file and sets Wkb to be equal to this open workbook. Then it loops over all the worksheets in the current file with a For Each loop which goes over each Worksheet (ws) in the file’s all worksheets (Wkb.Worksheets). The code takes each ws and Copies it After the last sheet of the WkbCurrent (remember, our file where we combine all the other files). It gets the last worksheet via the construct WkbCurrent.Sheets() and provides the last sheet’s number to Sheets() by counting all the sheets in the file. As the copied sheet is now active, we set its name as the name of the FileName, to be easily readable after that.
Then we close the Wkb from which we are copying and go to the next file by caling Dir() again. We end the Loop with the Loop key word.
We then set Enable Events and ScreenUpdating to True again, as we have concluded the macro work and there’s nothing to interfere with.
I hope that’s clear enough, shoot me a message if you didn’t get any part of it!
And that’s it, we got our combineFiles macro up and running. But we do not want to have to move the files we want to combine to the same folder every time. This is where we can use a Funcion to help us select a specific folder every time we run our macro. It goes like this:
Function GetFolder(strPath As String) As String Dim fldr As FileDialog Dim sItem As String Set fldr = Application.FileDialog(msoFileDialogFolderPicker) With fldr .Title = "Select a Folder" .AllowMultiSelect = False .InitialFileName = strPath If .Show <> -1 Then GoTo NextCode sItem = .SelectedItems(1) End With NextCode: GetFolder = sItem Set fldr = Nothing End Function
What this function does is pretty much create a FileDialog of the type FolderPicker, sets some arguments on it and then returns the folder and sets if back to Nothing. It’s a bit more technical but for now just assume it does what it does and you can reuse it as many times as you want.
To make it work we change a line in our combineFiles Sub Procedure.
path = "C:\Users\user1\Desktop\1" 'Change as needed
path = GetFolder("Select folder")
That’s it. The “Select folder” we pass actually does nothing as it expects a default file name and we give it no such thing. But we do not need a default file name as we select a folder, so it should work out just fine.
Now save your .xlam file and voila, you just created your first Excel Add-In!
Step 6. Adding your Add-In to Excel
To add your Add-In, open an empty Excel file, go to File -> Options and go to Add-Ins. Go to Manage: Excel Add-ins and click Go… Then click Browse, navigate to your add-in file and open it. You should see it loaded in the Add-Ins dialog box after that.
Make sure it’s ticked on and click Ok. Now if you go to File -> Options -> Add-Ins you should see your very own Add-In within the Active Application Add-Ins
Restart Excel just to be sure and open a blank file. Here it is – your own Custom Ribbon Tab. Click on it and enjoy its glory!
Time to try it now. Type in something on Rows 1, 3, 4, 5.
Now press the Delete Blank Rows button and it should delete row 2.
Congrats, it worked!
I will let you try the other macro on your own J
I guess that’d be enough for today. I hope you enjoyed reading this ‘tutorial’. My idea was not to teach you coding or anything, just to open your eyes to the endless possibilities within Excel. If you use this to create your own custom Ribbons, I’d love to check them out. Leave a comment below, hit me up on twitter, send me a message from the Contacts page, or shoot me an e-mail at i @ brotherlemon . com.
P.S. Look out for more tutorials on Excel and maybe even some financial analysis stuff.