Create a VBA macro or script in Excel

Microsoft Excel allows users to automate functions and commands using macros and Visual Basic for Applications (VBA) scripting. VBA is the programming language Excel used to create macros. It will also run automated commands based on specific conditions.

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

Below the Developer tab on the Ribbon Excel allows users to 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 advantage. Allows users to create more complex scripts.

In this article we explain the following:

  • Enable scripts and macros
  • How to make 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?

Enable scripts and macros

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

  • Open an Excel worksheet.
  • click on File Options Customize ribbon.
  • Put a check in the box next to Developer
  • Click on the Developer tab of the Ribbon menu.
  • Then click Macro security and check the box next to Enable all macros (not recommended; potentially dangerous code can run).
  • Then click OKAY.

The reason macros are not enabled by default and come with a warning is that it is computer code that can contain malware.

If you’re working on a shared project in Excel and other Microsoft programs, make sure the document comes from a trusted source.

When you’re done using your scripts and macros, disable all macros to prevent potentially malicious code from infecting other documents.

Create a macro in Excel

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

  • On the Developer tab, click Record Macro
  • Enter one Macro namea Hotkeyand a Description. Macro names must start with a letter and cannot contain spaces. The shortcut key must be a letter.

Choose from the following options where to save the macro:

  • Personal Macro Workbook: Creates a hidden Excel document with saved macros that can be used with all Excel documents.
  • New workbook: Will create a new Excel document to save the created macros.
  • This workbook: This will only be applied to the document you are currently editing.

When you’re done, click Okay

  • Go through the actions you want to automate. When you’re done, click Stop recording
  • To access your macro, use the keyboard shortcut you gave it.

Specific example of a macro

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

Let’s assume you decide that all spreadsheets should use a different format, such as putting 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 the macro

  • click on Record Macro† Let’s call it Format_Customer_Data and click Okay
  • To get the format you want, we will rename the first column to First name
  • Then add a column next to A and name it Last name
  • Highlight all names in the first column (which still contain the first and last name) and click Facts of the ribbon navigation.
  • click on Text to Columns
  • tick limitedNext one Separated by spaceNext one Finish† See the screenshot below and how the first and last names were separated by the above process.
  • Highlight the amounts to format the Balance Due field. click on House Conditional FormattingHighlight cell linesGreater than 0

Highlights the cells that have a balance. We’ve added a few customers with no balance to further illustrate the formatting.

  • Go back to Developer and click Stop recording

Apply the macro

Before recording the macro, let’s start with the original spreadsheet to format it correctly. click on Macrosselect and Run the macro you just created.

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

Users can run macros in several ways. Read Run a macro learn more.

Learn more about VBA

To learn more about VBA, click macro of the Developer tab. Find one you made and click edit.

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

It’s also what you’ll be using if you want to format other customer payment spreadsheets in the same way.

Create a button to get started with VBA

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

  • To insert a button element, navigate to the Developer tab.
  • Select ActiveX Command Button from the drop-down list next to Insert in the Service section
  • Drag the button anywhere in the spreadsheet so you can easily open it and change it later if you want.
  • To add the code, right click on the button and select Properties† We love the Name as Command button and the caption until transfer (this is the button text).

Add code to give the button functionality

VBA encoding does not take place in the Excel interface. This takes place in a separate environment.

  • Go to the Developer tab and make sure Design mode is active.
  • To access the code of the button we just created, right click on it and select View code
  • Looking at the code in the screenshot below, note the beginning (Private Sub) and end (end sub) of the code is already there.
  • The code below drives the currency conversion procedure.

ActiveCell.Value = (ActiveCell * 1.28)

Our goal in this section is to convert the currency in our spreadsheet. The above script displays the exchange rate from GBP to USD. A cell’s new value is what it currently has multiplied by 1.28.

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

  • Go to File in the top navigation and click Close and return to Microsoft Excel to return to the main Excel interface.

Did it work?

Before you can test your code, you must first disable design mode (click it) to prevent further changes and give the button functionality.

  • Type any number into your spreadsheet, then click the transfer knob. If the value of your number increases by about a quarter, it’s done.

For this example I put the number 4 in a cell. After clicking transfer, the number changed to 5.12. Since 4 times 1.28 is 5.12, the code was executed correctly.

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

Leave a Reply

Your email address will not be published.