5000+ Great Articles

How To Create a VBA Macro Or Script In Excel

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:

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:

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.

Choose where you want to save the macro from the following options:

When done, click OK.

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

This will highlight the cells that require balance. We’ve added some unbalanced clients to illustrate the formatting.

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.

Add a symbol to give the button a function

VBA coding fails in the Excel interface. This is done in a separate environment.

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.

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.

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.

Exit mobile version