Microsoft Excel enables users to automate functions and commands using macros and Visual Basic for Applications (VBA) scripts. VBA is the programming language Excel uses to create macros. It will also execute automatic commands based on certain conditions.
Macros are a series of pre-recorded commands. They start automatically when a specific command is given. If you have tasks in Microsoft Excel that you do all the time, such as accounting, project management, or payroll, automating these processes can save you a lot of time.
On the Developer tab of the ribbon in Excel, users can record mouse clicks and keystrokes (macros). However, some features require more in-depth scripting than macros can provide. This is where VBA scripting becomes a huge advantage. This allows users to create more complex scenarios.
In this article, we will explain the following:
- Including scripts and macros
- How to create a macro in Excel.
- A concrete example of a macro
- Learn more about VBA.
- Create a button to get started with VBA.
- Add code to make the button functional.
- Did it work?
Enable Scripts and Macros
Before you can create macros or VBA scripts in Excel, you must enable the Developer tab in the ribbon menu. The Developer tab is disabled by default. To enable it:
- Open an Excel worksheet.
- Click File> Options> Customize Ribbon.
- Select the Developer checkbox.
- Click the Developer tab on the ribbon menu.
- Then click Macro Security and select the Enable all macros check box (not recommended; potentially dangerous code may run).
- Then click OK.
The reason macros are not enabled by default and are displayed with a warning is because they are computer code that may contain malware.
– / –
Make sure the document is from a trusted source if you are working on a shared project in Excel and other Microsoft programs.
When you’re done using your scripts and macros, disable all macros to prevent potentially malicious code from infecting other documents.
Create Macro in Excel
It adds all the actions you take in Excel when you record a macro.
- On the Developer tab, click Record Macro.
- Enter a macro name, keyboard shortcut, and description. Macro names must begin with a letter and not contain spaces. The keyboard shortcut must be a letter.
Choose where you want to save the macro from the following options:
- Personal Macro Book: A hidden Excel document with saved macros will be created for use with any Excel documents.
- New Workbook: Creates a new Excel document to store the generated macros.
- This Workbook: Will only be applied to the document you are currently editing.
When done, click OK.
- Perform the actions you want to automate. When you’re done, click Stop Recording.
- If you want to access your macro, use the keyboard shortcut you specified.
A specific example of a macro
Let’s start with a simple spreadsheet of clients and their debt. We’ll start by creating a macro to format the worksheet.
Suppose you decide that all spreadsheets should use a different format, such as first and last names in separate columns.
You can change this manually. Or, you can create a program using a macro to automatically format it correctly for you.
Record Macro
- Click Record Macro. Let’s call it Format_Customer_Data and click OK.
- To get the formatting we want, we’ll change the name of the first column to Name.
- Then insert a column next to the letter A and name it “Last Name”.
- Select all the first names in the first column (which still include the first and last name) and click Data in the navigation ribbon.
- Click Text To Columns.
- Delimited Check Mark> Next> Separate with Space> Next> Done. See screenshot below which shows how first name and last name were separated by the above process.
- To format the field for payment, select the amounts. Click Home> Conditional Formatting> Highlight Cell Rules> Greater than 0.
This will highlight the cells that require balance. We’ve added some unbalanced clients to illustrate the formatting.
- Go back to the developer and click Stop Recording.
Apply Macro
Let’s start with the original table before we recorded the macro to format it correctly. Click Macros, select and run the macro you just created.
When you run a macro, all formatting is done for you. This macro we just created is saved in the Visual Basic editor.
Users can run macros in several ways. Read “Running a Macro” to learn more.
Learn more about VBA
To learn about VBA, click Macro in the Developer tab. Find the one you created and click “Edit”.
The code you see in the box above was generated while recording the macro.
This is also what you run if you want to format other customer payment spreadsheets in a similar way.
Create a button to get started with VBA
Using the above table of customer and debt data, let’s create a currency converter.
- To insert a button element, go to the Developer tab.
- Select an ActiveX command button from the drop-down list next to the Insert button in the Controls section.
- Drag the button anywhere in the table so that you can easily access it and modify it later if you want.
- To attach the code, right-click the button and select Properties. We will save the name as CommandButton and the title for the transformation (this is the button text).
Add a symbol to give the button a function
VBA coding fails in the Excel interface. This is done in a separate environment.
- Click the Developer tab and make sure Design mode is active.
- To access the code for the button you just created, right-click it and choose View Code.
- Looking at the code in the screenshot below, notice that the beginning (Private Sub) and the end (End Sub) of the code are already there.
- The code below will control the currency conversion procedure.
ActiveCell.Value = (ActiveCell * 1.28)
Our goal in this section is to convert currency in our spreadsheet. The script above reflects the GBP / USD exchange rate. The new cell value will be the current value multiplied by 1.28.
The screenshot below shows how the code looks in the VBA window after pasting it.
- Go to the File section at the top of the navigation and click Close and Return to Microsoft Excel to return to the main Excel interface.
Did you succeed?
Before you can test your code, you must first disable design mode (click on it) to avoid further changes and give the button functionality.
- Enter any number in your spreadsheet and then click the Convert button. If your number increases by about a quarter, it will work.
In this example, I have placed the number 4 in the cell. After clicking the “Convert” button, the number changed to 5.12. Since 4 times 1.28 equals 5.12, the code is correct.
Now that you know how to create a macro or script in Excel, you can use it to automate many things in Excel.
–