By: Andy Kim
When it comes to analyzing data, Microsoft Excel spreadsheets have always been a great tool to do so, and they will continue to be commonly used amongst data analysts for the foreseeable future. Have you ever found yourself performing repetitive tasks in Excel? Are you familiar with macros and Visual Basic for Applications (VBA)? If not, now is a great time to learn more about these. They could save lots of keystrokes and time, and they can also help add cool functionality to a spreadsheet.
In this post, I’m going to introduce you to these topics in a hands-on fashion.
Setup: Activate the Developer Tab in the Ribbon using Options
In order to work with Macros and VBA, you will first need to activate the hidden Developer tab.
Click on File in the main menu.
Click on Options in the sub-menu.
Click on Customize Ribbon , check the Developer box, then click the OK button.
You should now have the Developer tab on the main menu/ribbon. You are now ready to work with Macros and VBA.
Macro Experiment
Record a Macro
Recording a macro will record events like your mouse clicks and your keystrokes. You can record macros, then run them to replicate the events that you recorded. Let’s try an example.
Open up a new Excel workbook/sheet, click on the Developer tab, then click on the Record Macro button. A window will pop up giving you some options, such as naming the Macro and choosing where to store the macro. Click OK. It is now recording each mouse click and keystroke, and basically any action you make.
In column A, start at the top and type in the numbers 1-5, as shown in the figure below.
Next, in column B, we will square the value in column A. In cell B1, type in the formula:
“= POWER(A1,2)”, then copy/paste the formula down to column B5.
Click on Stop Recording.
View the VBA Code that was Generated by the Recorded Macro
Open a new worksheet so we have a blank canvas.
Next, click on the Macros button. It takes you to a list of macros you have recorded.
Highlight the macro we just recorded and click the Edit button.
This brings up the VBA editor where you can view or edit the VBA code that was generated based upon your recorded macro.
Run the Recorded Macro
Close the VBA window, click on Macros again, highlight the macro we just recorded, and this time click Run, rather than Edit.
It has just replicated your recorded steps on the new worksheet.
We have just completed a very simple example that demonstrates how recording and running macros can help you complete repetitive tasks.
VBA Experiment
Create a Command Button to Run VBA Code
In the Developer tab, click on the Insert button, then click on the Command Button icon.
Now you can click and drag to create a command button on the excel spreadsheet, as shown below. You will also notice that this action enables Design Mode, which can be toggled on/off depending on whether you are working on developing the button, or executing/testing it.
While in design mode, double click anywhere on top of the command button that we just added. The VBA Code editor will pop up with the shell of a subroutine that is executed when you click on the command button. Between these two lines of code, you can add simple to very complex VBA code for what you want to happen when the button is clicked.
Replace the code with the code shown below. The first couple of lines adds a new sheet, and the rest of the code is a slightly modified version of the code from Macro1 that we previously recorded.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
Private Sub CommandButton1_Click() 'Add a new sheet and name it ActiveWorkbook.Sheets.Add Before:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count) 'This is the slightly modified code from Macro1 we previously recorded ActiveCell.FormulaR1C1 = "1" ActiveSheet.Range("A2").Select ActiveCell.FormulaR1C1 = "2" ActiveSheet.Range("A3").Select ActiveCell.FormulaR1C1 = "3" ActiveSheet.Range("A4").Select ActiveCell.FormulaR1C1 = "4" ActiveSheet.Range("A5").Select ActiveCell.FormulaR1C1 = "5" ActiveSheet.Range("B1").Select ActiveCell.FormulaR1C1 = "=POWER(RC[-1],2)" ActiveSheet.Range("B1").Select Selection.AutoFill Destination:=ActiveSheet.Range("B1:B5"), Type:=xlFillDefault ActiveSheet.Range("B1:B5").Select ActiveSheet.Range("B5").Select End Sub |
Now, click on Design Mode to toggle it off. Then, click on the CommandButton1 that we just added code to in order to execute the VBA code behind it.
By clicking the new command button, it has added a new sheet (Sheet3), and replicated the steps from earlier, just by clicking the new command button we coded on Sheet2.
Conclusion
As you can see, macros and VBA can be powerful tools. They can perform simple tasks or very complicated tasks, such as:
- crunching numbers and complex formulas across hundreds of worksheets
- interacting with users via User Forms
- and creating pivot tables and charts
I have completed all of these tasks in previous projects. Since this is just a brief introduction, we did not go into that level of detail. The possibilities are endless, and they are only limited by your programming knowledge, as it is obviously based on the Visual Basic programming language.
In my personal experience, I typically record macros in order to reverse engineer the generated VBA code to be more dynamic (rather than hard-coded). If you would like to learn more about VBA for Excel, please check out the following link: https://docs.microsoft.com/en-us/office/vba/api/overview/. In addition, there is a wealth of knowledge and a very large community to help you achieve your goals online.
Have Questions?
Thanks for reading. We hope you found this blog post to be useful. Do let us know if you have any questions or topic ideas related to BI, analytics, the cloud, machine learning, SQL Server, (Star Wars), or anything else of the like that you’d like us to write about. Simply leave us a comment below, and we’ll see what we can do!
Keep Your Business Intelligence Knowledge Sharp by Subscribing to our Email List
Get fresh Key2 content around Business Intelligence, Data Warehousing, Analytics, and more delivered right to your inbox!