How to Write a Simple Macro in Microsoft Excel (with Pictures)

Table of contents:

How to Write a Simple Macro in Microsoft Excel (with Pictures)
How to Write a Simple Macro in Microsoft Excel (with Pictures)

Video: How to Write a Simple Macro in Microsoft Excel (with Pictures)

Video: How to Write a Simple Macro in Microsoft Excel (with Pictures)
Video: How to compare two Excel files for differences 2024, December
Anonim

This wikiHow teaches you how to create a simple macro for an Excel spreadsheet.

Step

Part 1 of 3: Enabling Macros

Write a Simple Macro in Microsoft Excel Step 1
Write a Simple Macro in Microsoft Excel Step 1

Step 1. Run Excel

You can do the same process to enable macros in Excel 2010, 2013, and 2016. There are slight differences in Excel for Mac, which are explained below.

Write a Simple Macro in Microsoft Excel Step 2
Write a Simple Macro in Microsoft Excel Step 2

Step 2. Click the File tab

On a Mac, click the "Excel" menu

Write a Simple Macro in Microsoft Excel Step 3
Write a Simple Macro in Microsoft Excel Step 3

Step 3. Click Options

On a Mac, click the " Preferences " option

Write a Simple Macro in Microsoft Excel Step 4
Write a Simple Macro in Microsoft Excel Step 4

Step 4. Click the Customize Ribbon option

On a Mac, click " Ribbon & Toolbar " in the " Authoring " section

Write a Simple Macro in Microsoft Excel Step 5
Write a Simple Macro in Microsoft Excel Step 5

Step 5. Check the Developer box in the list on the right

On a Mac, you can find "Developer" in the "Tab or Group Title" list

Write a Simple Macro in Microsoft Excel Step 6
Write a Simple Macro in Microsoft Excel Step 6

Step 6. Click OK

The Developer tab will appear at the end of the tabs list.

Part 2 of 3: Recording Macros

Write a Simple Macro in Microsoft Excel Step 7
Write a Simple Macro in Microsoft Excel Step 7

Step 1. Practice the macro sequence

When recording a macro, anything clicked or done is recorded. So, one mistake can ruin everything. Do the practice of running the recording command a few times so you can do it without hesitation and without mis-clicking.

Write a Simple Macro in Microsoft Excel Step 8
Write a Simple Macro in Microsoft Excel Step 8

Step 2. Click the Developer tab

Write a Simple Macro in Microsoft Excel Step 9
Write a Simple Macro in Microsoft Excel Step 9

Step 3. Click Record Macro

This option is in the Code section of Excel's ribbon. You can also start a new macro by pressing Alt+T+M+R keys (Windows only).

Write a Simple Macro in Microsoft Excel Step 10
Write a Simple Macro in Microsoft Excel Step 10

Step 4. Name the macro

Use an easily recognizable name, especially if you want to create multiple macros.

You can also provide a description to explain the function of the macro

Write a Simple Macro in Microsoft Excel Step 11
Write a Simple Macro in Microsoft Excel Step 11

Step 5. Click the Shortcut key field

You can create a keyboard shortcut so that the macro can be run easily. However, this is optional.

Write a Simple Macro in Microsoft Excel Step 12
Write a Simple Macro in Microsoft Excel Step 12

Step 6. Press the Shift key and a letter

A keyboard combination of Ctrl+⇧ Shift+letter will be created to run the macro.

On Mac computers, the keyboard combination is Opt+⌘ Command+letter

Write a Simple Macro in Microsoft Excel Step 13
Write a Simple Macro in Microsoft Excel Step 13

Step 7. Click the Store macro in menu

Write a Simple Macro in Microsoft Excel Step 14
Write a Simple Macro in Microsoft Excel Step 14

Step 8. Click the location where you want to save the macro

If the macro is only used for the spreadsheet at this point, leave its location in " This Workbook ". If you want a macro to be used for all the spreadsheets you work with, select " Personal Macro Workbook ".

Write a Simple Macro in Microsoft Excel Step 15
Write a Simple Macro in Microsoft Excel Step 15

Step 9. Click OK

The macro will start recording.

Write a Simple Macro in Microsoft Excel Step 16
Write a Simple Macro in Microsoft Excel Step 16

Step 10. Perform the command you want to record

Anything you do at this point will be recorded and added to the macro. For example, if you run the sum formula A2 and B2 in cell C7, Excel will add up A2 and B2, and then display the result in C7 when you run the macro in the future.

Macros can be very complex, and you can even use them to run other Office programs. When a macro records, almost anything that was done in Excel is added to the macro

Write a Simple Macro in Microsoft Excel Step 17
Write a Simple Macro in Microsoft Excel Step 17

Step 11. Click Stop Recording when you are done

The recording of the macro will end and the result will be saved.

Write a Simple Macro in Microsoft Excel Step 18
Write a Simple Macro in Microsoft Excel Step 18

Step 12. Save the file in a format that has macro enabled

In order for macros to be preserved, you must save the workbook in an Excel format that has macros enabled:

  • Select the File menu, then click Save.
  • Click the File Type menu under the file name field.
  • Click Excel Macro-Enabled Workbook.

Part 3 of 3: Using Macros

Write a Simple Macro in Microsoft Excel Step 19
Write a Simple Macro in Microsoft Excel Step 19

Step 1. Open the workbook file that has macros enabled

If you closed the file before running the macro, you will be prompted to activate the content.

Write a Simple Macro in Microsoft Excel Step 20
Write a Simple Macro in Microsoft Excel Step 20

Step 2. Click Enable Content

This option will appear at the top of the Excel spreadsheet on the Security Warning bar whenever a workbook that has macros enabled is opened. Since this is your own file, of course you can trust it. However, be careful when opening files that have other people's macros enabled.

Write a Simple Macro in Microsoft Excel Step 21
Write a Simple Macro in Microsoft Excel Step 21

Step 3. Hit your macro shortcut

If you want to use a macro, you can run it quickly by pressing the shortcut you have created.

Write a Simple Macro in Microsoft Excel Step 22
Write a Simple Macro in Microsoft Excel Step 22

Step 4. Click the Macros button in the Developer tab

All available macros in your spreadsheet at this point will be displayed.

Write a Simple Macro in Microsoft Excel Step 23
Write a Simple Macro in Microsoft Excel Step 23

Step 5. Click the macro you want to run

Write a Simple Macro in Microsoft Excel Step 24
Write a Simple Macro in Microsoft Excel Step 24

Step 6. Click the Run button

The macro will run in your cell or selection at this point.

Write a Simple Macro in Microsoft Excel Step 25
Write a Simple Macro in Microsoft Excel Step 25

Step 7. Look at the macro code

If you want to know how macro coding works in more detail, open the macro code you created and tinker with its contents:

  • Click the Macros button located on the Developer tab.
  • Click the macro you want to view.
  • Click Edit.
  • Look at the macro code in the Visual Basic code editing program.

Recommended:

How to Build a Simple Electric Motor: 10 Steps (with Pictures)

How to Build a Simple Electric Motor: 10 Steps (with Pictures)

This article will teach you how to build a great basic electric motor for both simple experimental purposes and scientific projects. You will use the electrical energy from the battery to generate mechanical energy that rotates the coils. These motors are very simple versions of the motors in household appliances, power tools, computer hard drives, and other devices that make your life easier.

How to Make a Simple Dress (with Pictures)

How to Make a Simple Dress (with Pictures)

Maybe you're looking for a cute and simple dress but can't find it, or maybe it's too expensive. You shouldn't have so much trouble finding the perfect dress for a party, funeral, or wedding, because you can always make your own. It is easy, you just need to follow these simple steps.

How to Make Simple Mashed Potatoes (with Pictures)

How to Make Simple Mashed Potatoes (with Pictures)

It can be difficult to find a more classic side dish than mashed potatoes. The food that is always served during this holiday is one of the special foods that you rarely encounter throughout the year. Whether served with fried chicken, roast turkey, stew, or as part of shepherd's pie, mashed potatoes can be made as smooth and creamy as possible, or slightly large and coarse.

4 Ways to Create a Custom Button Macro in Excel

4 Ways to Create a Custom Button Macro in Excel

Macros in Excel can save a lot of time on repetitive work. By adding macros to custom buttons, you can save even more time by making macros run with just one click. Step Method 1 of 4: Excel 2003 Step 1. Click Tools → Customize Step 2.

How to Create a Simple Check Register in Excel (with Pictures)

How to Create a Simple Check Register in Excel (with Pictures)

Check account activity is easier to record using check registers created from Microsoft Excel. You can set up check registers to categorize expenses so you can track any money spent. You can create a formula to find out the balance of money in the account.