Creating Your First Microsoft Excel Macro

Excel is one of the most widely used software in the working world, yet the average person does not know how to take advantages of its plethora of features. One of these features are called Macros. Macros are very powerful tools that allow you to automate many things in Excel by using the Visual Basic Programming Language. This instruction set will provide a brief tutorial to introduce you to the power of macros and how they can be used to simplify your daily life.

Open a blank workbook in Microsoft Excel (These instructions are for Excel 2016, although they should be fairly accurate for any version of Excel that you may be using).

By default, the Developer Tools do not appear in the Excel Toolbar. To add it, go to File -> Options -> Click Customize Ribbon and check the box next to Developer on the right-hand Side

Add the following text to the exact same cells as shown in the screenshot below

To add a button like the ones in the screenshot above, go to the Developer tab, click the toolbox with the text “Insert” beneath it, and select the first option for “Button.” Then click on the location where you would like to add a button, so in this case next to “Amount Owned”. In the popup, change the Macro Name to “AddTransaction_Click” then click “New.”

The screenshot above should automatically appear. What we want to do is prompt the user to provide the necessary information about their transaction so we can populate our Excel table. We will be collecting this information from the user with something called a UserForm. To create one, click on the “Insert” tab followed by the “UserForm” selection.

First, make the following changes circled in red above. Change the name of the Form to “AddTransactionForm” and the caption to “Add Transaction”. Now we are going to make the form look like the screenshot above. To begin creating the form, click anywhere within the box highlighted in yellow in the screenshot above. A toolbox should now appear. Use the template above for reference. Use the tool called “Label” to create the text items “Contributor:”, “Description:”, and “Amount Paid:”. Then use the “Dropdown” tool to create a dropdown box to the right of the “Contributor:” label. Finally, use the “Text Box” tool to create text boxes to the right of the “Description:” and “Amount Paid:” labels. Now, select your “Contributor:” dropdown by clicking on it, and rename it to be “ContributorComboBox” Do the same thing for your textboxes with the names “DescriptionTextBox” and “AmountPaidTextBox” The last thing we need to add to the form is the “Add” button at the bottom. To do this, select the “Button” option from the toolbox and click where you would like the button to be. Click inside the button to change its Label to “Add” and then rename your button to be “AddTransactionButton” like you did for the dropdown and text boxes.

Now that the form is complete, we want it to show up when the button we added to our Excel Sheet is pressed. To make this happen, we need to add the code to the button that will open our newly created form. We already saw where this code will need to be added when we assigned the macro “AddTransaction_Click” to our button. To make this macro reappear, click on “Module1”in the Project section. Add the line “AddTransactionForm.Show” to the body of the AddTransaction_Click() method shown above and save your file (press Ctrl-S). To translate what the above code does, lets walk through it step by step. The first line is saying that we have a function called AddTransaction_Click. Because we assigned this function to our button, the body of this function will be executed each time we click our button. We then write “AddTransactionForm.Show” in order to make the form that we created appear. Go ahead and go to your Excel sheet and try it out. (If your button does not work for some reason, right-click on your button and click “Assign Macro” and then select “AddTransaction_Click” See Below)

Now let’s create a new button called “Add Contributor” and assign it a new macro called “AddContributor_Click”. Once again the following screen should appear and Module2 should appear with the snippet of code from the screenshot above. (if this screen does not show up, go back to the Developer tab in the Excel window and click the “Visual Basic” button on the far left side of the toolbar)

Next we need to add a form that will appear when the Add Contributor button is pressed. So click Insert-> then select UserForm. Make this new UserForm look the screenshot. Click anywhere on the form and change the name to “AddContributorForm” and change its Caption to be “Add Contributor”. Then click on the text box and change its name to “ContributorTextBox”. Finally click on the button and add the name. “AddContributorButton”

Now that are form objects are named, go ahead and double click on the “Add Contributor” button and add the following lines of code to the body of the “AddContributorButton_Click” method from the screenshot. The way this code works is it takes note of the last row in column L and adds 1 to the row number in order to determine which row number the next name should be added. It then checks if the ContributorTextBox has anything in it by checking if the .Text attribute of the text box is equal to “” or nothing. If there is nothing in the textbox, then the next line that is executed is “MsgBox (“Please Enter a Name in the Text Box Below”)”. What this line does is it creates a pop up message that informs the user that they forgot to enter a name into the Contributor text box. If there is text in the Contributor text box, the code beneath the word “Else” will be executed. What this code does is uses the Range function to take the cell “L” and the number of the row stored in “nextRow” and assign the .Value of the cell to be the .Text of the ContributorTextBox. The last line, “Unload Me” tells the form to close itself. A good way to get a better understanding of what is happening is to try it out for yourself. So go ahead and save (Ctrl-S) and go back to your Excel sheet. Press the “Add Contributor” button, add a name to the text box and click add. You should see a name appear in column L beneath the last row with text. (If your button does not work this way, go back through the steps above to try to find out what went wrong.) Go ahead and add as many contributors as you like and your Excel sheet should now look something like this:

Now that we have some contributors, we can now add some transactions and associate them with one of our contributors. To make this happen, go back to your Visual Basic window (go to the Developer tab and click Visual Basic in the far left side of the toolbar) and click on the form “AddTransactionForm”. Then double click on the Add button to make the code window appear.

Add the lines of code displayed in the screenshot to the previous window, overwriting what is already there. The first line of code you see says “Dim loadMe as Boolean”. What this does is create a global variable called loadMe that can be accessed from any of the methods described below. A Boolean holds the value True or False and you will see how we use it in the code below. The first function you see is called UserForm_Initialize(). This function is called as soon as AddTransactionForm.Show is called from pressing the button. Within this function, we do a quick check of the column L, which contains our list of contributors. In the even that there have not been any contributors added yet, a Message Box will be shown informing the user to add contributors before adding transactions. In this case, we set the loadMe variable to be false. In the next function “UserForm_Activiate()”, we check this variable loadMe. If we have set the value of loadMe to false (when there are no contributors yet), we call Unload Me which will immediately close the Add Transaction Form as we cannot yet add any transactions. If contributors do exist, we select the list of contributors using the Range function. Then for each contributor, we add an item do our dropdown list that will contain the name of the contributor. Finally, our form is loaded with the dropdown properly populated. The last function is what is called when the Add Transaction button is pressed on the form. The first thing this function does it check each of the input objects for values and alerts the user if any are left blank. If all of them have values, it will start to populate our Excel sheet with the correct values for the columns we described. It assigns the ID column with the previous ID column’s + 1 by offsetting by the Row number. The Date column is populated using the built-in Excel method Date. The next two rows are populated using the text values from the Contributor and Description Text Boxes. The Amount Paid column is then populated with the correct values from the text box passed into a method called CCur that takes the text and converts it into currency format. The next line get the number of contributors by counting the number of items in the dropdown lists and makes sure that it takes on the number form by passing it into the method CInt which converts the given text into integer form. Finally, the Amount Owed column takes the Amount Paid and subtracts from it the Amount Paid divided by the number of contributors to inform the user of the amount that the contributor is owed in total from the other contributors. Once again the best way to understand the code is to test it out

Go to the Excel Sheet, make sure you have some contributors added, and they add a few transactions and you should see something similar to the screenshot. And voilà! You have successfully created a Macro-Enabled Excel Workbook! (make sure you save this file as a Macro-Enabled Excel Workbook to maintain your precious macros!)