How To Create a VBA Macro Or Script In Excel

Can save you oodles of time

Microsoft Excel enables users to automate features and commands using macros and Visual Basic for Applications (VBA) scripting. VBA is the programming language Excel uses to create macros. It will also execute automated commands based on specific conditions.

Macros are a series of pre-recorded commands. They run automatically when a specific command is given. If you have tasks in Microsoft Excel that you repeatedly do, such as accounting, project management, or payroll, automating these processes can save a lot of time.

Under theDevelopertab on theRibbonin Excel, users can record mouse clicks and keystrokes (macros). However, some functions require more in-depth scripting than macros can provide. This is where VBA scripting becomes a huge benefit. It allows users to create more complex scripts.

In this article, we will explain the following:

Enabling Scripts & Macros

Before you can create macros or VBA scripts in Excel, you must enable theDevelopertab on theRibbonmenu. The Developer tab is not enabled by default. To enable it:

The reason macros are not turned on by default and come with a warning is that they are computer code that could 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 are done using your scripts and macros, disable all macros to prevent potentially malicious code from infecting other documents.

Create a Macro in Excel

All the actions you take in Excel while recording a macro are added to it.

Decide where you want to store the macro from the following options:

When done, clickOK.

Specific Example Of a Macro

Let’s start with a simple spreadsheet for customers and how much they owe. We will begin by creating a macro to format the worksheet.

Let’s assume that you decide that all spreadsheets should use a different format such as putting first and last name in separate columns.

You can manually change this. Or you can create a program using a macro to automatically format it correctly for you.

This will highlight the cells that have a balance due. We added a few customers with no balance due to further illustrate the formatting.

Let’s start with the original spreadsheet before we recorded the macro to format it correctly. Click onMacros, select andRunthe macro you just created.

When you run a macro, all the formatting is done for you. This macro we just created is stored in theVisual Basic Editor.

Users can run macros in several different ways. ReadRun a macroto learn more.

Learn More About VBA

To learn about VBA, click onMacrofrom theDevelopertab. Find one you have created and clickEdit.

The code you see in the box above is what was created when you recorded your macro.

It is also what you will run when you want to format other customer payment spreadsheets in the same way.

Create a Button To Get Started With VBA

Using the same spreadsheet above with customers and how much they owe, let’s create a currency converter.

Add Code To Give The Button Functionality

VBA coding doesn’t take place in the Excel interface. It is done in a separate environment.

ActiveCell.Value = (ActiveCell * 1.28)

Our purpose in this section is to convert the currency in our spreadsheet. The script above reflects the exchange rate from GBP to USD. The new value of a cell will be what is currently there multiplied by 1.28.

The screenshot below shows you how the code looks in the VBA window after you insert it .

Did It Work?

Before you can test your code, you must first disable Design Mode (click on it) to avoid further modifications and give the button functionality.

For this example, I put the number 4 into a cell. After clickingConvert, the number changed to 5.12. Since 4 times 1.28 is 5.12, the code was carried out correctly.

Now that you understand how to create a macro or script in Excel, you can use them to automate a multitude of actions in Excel.

David has a background in small business and lives in Australia. He is a WordPress and Ubuntu Developer who enjoys design, CSS and tech tool integration.Read David’s Full Bio

Welcome to Help Desk Geek- a blog full of tech tips from trusted tech experts. We have thousands of articles and guides to help you troubleshoot any issue. Our articles have been read over 150 million times since we launched in 2008.

HomeAbout UsEditorial StandardsContact UsTerms of Use

How To Create a VBA Macro Or Script In Excel#

Enabling Scripts & Macros#

Create a Macro in Excel#

Specific Example Of a Macro#

Learn More About VBA#

Create a Button To Get Started With VBA#

Add Code To Give The Button Functionality#

Did It Work?#