When I wrote down ‘Excel skills” on my resume, I never thought about macros. I knew how to navigate Excel, but macros were separate. All of a sudden, I”m working on an Excel document with more than 1300 rows of data that is continually changing every week. Not only is it tedious to do the same tasks again and again, it is boring and a waste of time. To save time for everyone, I have created a two-part VBA survival guide. In Part 1, I want to demonstrate 3 different VBAs and what they can do. In Part 2, I will go over the different ways of calling macros to an Excel document. Let’s begin!
First off, macros are small programs in Excel that can automate repetitive tasks. Macros are so, so, so helpful, but learning how to make a macro is the absolute worst. Don’t worry, once you get the hang of it you will be writing your own macros in no time.
Now, before making macros, you need to prepare Excel for it.
Step 1: Open an Excel file and immediately Save As a .xls or .xlsm.
If you are working with a blank Excel file, it makes this step easier. However, if you opening an exported data set (as I was), make sure you are in .xls or .xlsm. Macros do not work with .csv, which is basically the standard of all exported table data. If you save as .csv, all your macro work will disappear and you would have wasted hours of your life. I am speaking from experience–please do not skip this crucial step!
Step 2: Import your data
Hopefully all of your data is already chunked together, but if it is not, I find it better to have all your hard data on one sheet so you know where to put any changes in the inputs. For this guide, I made up my movie list for Halloween.
Step 3: Add a Developer Ribbon and Open Visual Basic
Go to File–>Options–>Customize Ribbon and add a checkmark to Developer. Press “OK” and check out that Developer ribbon. For simplicity, you are only going to worry about Macros and Visual Basic. Go ahead and click Visual Basic.
Now, you are ready to begin making macros.
—————————————————–
To begin, macros are developed in little modules. The modules are instructions for the macro.
All macros begin the same. In Visual Basic, Go to Insert–>Module. Here you have a small, white sheet ready for anything. Go ahead and start writing in it.
“Sub INSERTNAME” Then press enter. It should automatically format it into the following.
INSERTNAME here is the name of the module. There can be no spaces and should describe what you are doing so you remember what it does in case you want to call it again. Two types are modules are “Sub” and “Function.” The only difference is that function modules return a value to a specific cell. For example, if you have some major computation that you cannot do in a formula but need to return a value, you would use a Function. However, if you do not need to return a set value to a set cell and just need to do a simple task over and over, Subs are your friend. For our purposes here, we only need to use Subs.
For simplicity, in the examples below, I am going to bold everything that is adjustable so you can hold on to these as templates.
How to Automatically Copy to Another Sheet
You need to have your sheet ready before copying. Let’s call this macro, “COPY_DATA”. The code is as follows.
Sub COPY_DATA ()
Sheets(“Sheet1”).Columns(“A:C”).Copy Destination:=Sheets(“Sheet1”).Range(“A:C”)
End Sub
In this case, you are moving the values in columns A:D in Sheet1 to the range E:H in Sheet 1. You can copy multiple columns or a single column. The end result looks like this.
If you want to copy the data onto another sheet, change the name of the destination sheet in the VBA.
How to Automatically Sort on Another Sheet
There are three ways to do this.
(1) First, you can just add a filter and manually sort them.
Sub AUTO_FILTER ()
Sheets(“Sheet2”).Columns(“A:D”).AutoFilter
End Sub
(2) Second, you could also automatically sort by descending value on one column.
Sub AUTO_FILTER ()
Sheets(“Sheet2”).Columns(“A:D”).Sort Key1:=Sheets(“Sheet2”).Columns(“C”), Order1:=xlDescending, Header:=xlGuess
End Sub
In this sub, you still are sorting columns A:D and treating each row as the same object, but Key1 specifies the first sort field, Order1 specifies the sort order (could also be xlAscending), and Header refers to whether that field has a header that should be ignored from the rest of the data (could also be xlYes or xlNo).
In theory, if you want to sort by two fields, you could add a Key2, Order2, but I won’t delve into that.
(3) The third way is only necessary if you want to sort for unique values. Let’s say I want to find all the unique directors (Sheet1, Column C) and place them on a column on another sheet.
Sub AUTOFILTER ()
Sheets(“Sheet2”).Columns(“A:A”).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets(“Sheet1”).Columns(“C:C”), Unique:=True
End Sub
In this case you put the destination first, followed by the column you are searching against.
You can see the three methods side by side here. Try to guess which is which.
How to Automatically Calculate a Column
Honestly, this one was the trickiest to learn for me. If you have a certain mathematical function you want to apply down a column, but you do not know how long the column is, just take my word for it this works.
Sub CALCULATE ()
With Sheets(“Sheet1”)
.Range(“E2”).Formula = “=D2”
.Range(“E3:E” & .Cells(.Rows.Count, “D”).End(xlUp).Row).Formula = “=D2+E2”
End With
End Sub
As shown above, the With…End With loop reiterates a statement referring back to a cell over and over. So, for this example, we are calculating two formulas down the same column on the same sheet. The With…End With loop is just a shortcut so you don’t have to copy each equation with every line. In this example, I want to populate a new column with a running sum of my total movie time from the previous rows with an exception being the first.
The end result looks like this.
My greatest fear with this VBA was the formula. Since we are referring to the cell above, I didn’t know if the VBA computes each cell simultaneously or sequentially. Luckily, with the With/End loop, you are guaranteed it is sequential so you can refer to the first cell above in your calculation.
Creating macros is the hardest tool in Excel, but it is the most powerful. In the next part, we will learn about the different ways to call macros.
