5 ways to automate Microsoft Excel through VBA
Microsoft Excel. Love it or hate it - there is no denying the dominance of this spreadsheet software across businesses around the world. Millions of people use it everyday from accounting to business intelligence to financial modeling. While most people have at least a basic understanding of how to use Excel (e.g., inputting data into cells, creating graphs, making calculations), a large majority have never tapped into Excel’s true potential as a workflow automation machine.
If you don’t already know, you can use Excel to automatically create PowerPoint presentations, send Outlook emails, move and copy files around your computer, and so much more. This can all be done through Visual Basic for Applications (VBA), the programming language of Excel and other Microsoft Office products.
Now for my non-technical readers, performing basic automation tasks in VBA is actually not that hard. In fact, I will be walking you through the easiest way to utilize VBA: recording a macro, which requires no coding whatsoever! If you are already familiar with VBA, I encourage you to scroll down to the use case sections to get some ideas on how to put VBA into action.
Still not sold on learning Excel VBA? All I will say is if you’ve ever done a tedious, repetitive task in Excel such as updating a spreadsheet with new data, using VBA will save you hundreds of hours of work in the long run and make your life much easier.
Setting up VBA in Excel
Important Note: this tutorial is assuming that you’re working on Windows 10 with Microsoft Excel 2016. If you’re using an older version of Windows or Excel, most of this tutorial should still work for you (though not guaranteed). I also can’t guarantee this tutorial will work with Excel on macOS. In addition, you need to ensure macros are enabled in Excel for VBA to work in your environment.
Setting up Excel VBA is relatively simple. All you have to do is click the “Developer” tab in the ribbon, and you will gain access to writing VBA scripts and recording macros.
If the “Developer” tab is not showing up in your ribbon, no need to panic! To add it, just click the “File” tab from the ribbon and then the “Options” button on the next screen in the left-hand column. In the “Options” window, click the “Customize Ribbon” option in the left-hand column and on the next screen, click the checkbox next to “Developer” under the “Main Tabs” column and click “Ok” at the bottom. The “Developer” tab should now appear in your ribbon.
Recording your first macro
A macro is an action or a set of actions that you can run as many times as you want in Excel. You can either code a macro using Excel VBA or simply record a set of actions you manually perform in Excel and assign them to a macro.
For my non-technical readers, recording a macro is the easiest way to get started with Excel VBA, and it requires no coding whatsoever. All you need to do is click the “Developer” tab in the ribbon, click the button “Use Relative References” and when you’re ready to start recording, click the “Record a Macro” button in the ribbon.
You will then get a pop-up prompting you to name your macro, write a description, and call it using a shortcut. For the purposes of this tutorial, just click the “Ok” button when you see the screen and keep the default name as “Macro1”
From this point forward, every action you perform in Excel will now be recorded (e.g., entering data into cells, copying data into different worksheets) and assigned to a macro.
To see how it works, write the numbers “1”, “2”, and “3” in cells “A1”, “A2”, and “A3” respectively. It’s important to avoid mouse clicking as much as possible since this can potentially screw up the macro when applying it to different cells and worksheets. When performing the task above, simply click the “Enter” button on your keyboard to get to the next cell.
Once you’re done, click the “Stop Recording” button and your macro will then be created! To run it, click on a new cell and then click the “Macros” button in the ribbon. In the pop-up screen, select your macro and click “Run”. “1”, “2”, and “3” should now appear below your active cell.
To make running macros even easier, I recommend you assign the macro to a shape, so when you click on the shape, the macro will run automatically. To do this, go to “Insert” in the ribbon, click the “Shapes” option, and choose the shape you want to add. Once the shape has been created, right click on it and choose the “Assign Macro” button.
In the pop-up screen, choose your macro to assign and click the “Ok” button. Now everytime you click the shape, it will act like a button to run your macro. You can can even add text to the shape like “Run Macro” to really make it look like a button.
And it’s that simple. You can use the “Record a Macro” feature for a number of different use cases. I encourage you to test it out.
Writing your first VBA script
Now if you want to get into actual coding and more dynamic use cases for VBA, you will have to learn how to write a VBA script. To do so, just click the “Developer” tab in the ribbon and then click the “Visual Basic” icon in the left-hand area of the ribbon.
The next screen will look pretty complex, but there is really only one section you need to understand to get started. In the left-hand column, you will find a folder called “Microsoft Excel Objects”. Right click on this folder and then click “Insert” and then click “Module”.
A blank sheet will then appear on your screen. This is where you will write your VBA code.
Now we get to the fun part. Below is the code that replicates the exercise we did above when recording the macro to input “1”, “2”, “3” in consecutive rows.
Public Sub firstscript()
ActiveCell.Value = 1
ActiveCell.Offset(1, 0) = 2
ActiveCell.Offset(2, 0) = 3
End Sub
Paste this code directly into the blank sheet and then click green arrow in the toolbar to run the script. See below for a visual of the code execution.
Now you may have a lot of questions around the syntax of the script and how this script even works. Rather than get into the details in this article, I recommend you follow a more in-depth tutorial to learn how to write VBA. I highly recommend this excellent YouTube VBA tutorial series by WiseOwlTutorials. I used this exact tutorial to learn VBA!
If you’re interested in seeing more use cases for Excel VBA, check out the five sections below.
1) Running a calculation for cells across multiple worksheets
Let’s say you have a value in cell “A1” across five worksheets that you want to aggregate (e.g., get the sum). See below for a calculation VBA script that will aggregate these values and output the sum to cell “A1” in “Sheet6”.
Public Sub sumOfCells()
For x = 1 To 5
finalValue = Sheets(x).Range("A1").Value + finalValue
Next x
Sheets(6).Range("B1") = finalValue
End Sub
2) Copy a worksheet from one workbook to another
Ever had to merge two workbooks together or copy a worksheet from one workbook to another? If so, see below for a VBA script that copies “Sheet2” from “Workbook2” and pastes it into “Workbook1”.
Public Sub copyWorksheet()
Workbooks("Workbook2").Sheets(1).Copy _
After:=Workbooks("Workbook1").Sheets(1)
End Sub
3) Create a pie chart
As you progress to more advanced Excel VBA, learning how to automatically create graphs and charts can be very useful. Check out the VBA script below to create a simple pie chart.
Public Sub createPieChart()
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlPie
ActiveChart.SetSourceData Source:=Range("A1:B2")
ActiveChart.Parent.Name = "Pie Chart"
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ApplyDataLabels
ActiveChart.SetElement (msoElementChartTitleAboveChart)
ActiveChart.ChartTitle.Text = "Pie Chart"
Selection.Format.TextFrame2.TextRange.Font.Size = 10
ActiveChart.Legend.Select
Selection.Format.TextFrame2.TextRange.Font.Size = 8
ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.Format.TextFrame2.TextRange.Font.Size = 8
ActiveChart.ChartArea.Select
End Sub
4) Create a PowerPoint slide
One of the best features of VBA is that you can use it across several Microsoft Office products. This can be very useful since many people use Excel to analyze data and create charts for PowerPoint presentations. Check out the VBA script below to export the pie chart we created in the previous script to a PowerPoint slide.
Public Sub createPPT()
Dim newPowerPoint As Object
Dim myPresentation As Object
Dim mySlide As Object
ActiveSheet.ChartObjects("Pie Chart").Activate
ActiveChart.ChartArea.Copy
If newPowerPoint Is Nothing Then
Set newPowerPoint = CreateObject("PowerPoint.Application")
End If
If newPowerPoint.Presentations.Count = 0 Then
Set myPresentation = newPowerPoint.Presentations.Add
End If
newPowerPoint.Visible = True
Set mySlide = myPresentation.Slides.Add(1, 11) '11 = ppLayoutTitleOnly
mySlide.Shapes.PasteSpecial DataType:=2 '2 = ppPasteEnhancedMetafile
Set myShape = mySlide.Shapes(mySlide.Shapes.Count)
myShape.Left = 66
myShape.Top = 152
myShape.Height = 300
myShape.Width = 600
mySlide.Shapes(1).TextFrame.TextRange.Text _
= "Percentage of people who know VBA!"
End Sub
5) Draft an Outlook email
If you liked creating PowerPoint slides through Excel VBA, you’ll love using it to draft Outlook emails! Check out the VBA script below to export the pie chart we created in the previous script to an Outlook draft email.
Public Sub draftEmail()
Dim OutApp As Object
Dim OutMail As Object
Dim wordDoc As Object
ActiveSheet.ChartObjects("Pie Chart").Activate
ActiveChart.ChartArea.Copy
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = "[email protected]"
.Subject = "Percentage of People Who Know VBA!"
.Display
Set wordDoc = OutMail.GetInspector.WordEditor
wordDoc.Range.PasteAndFormat wdChartPicture
End With
End Sub
