Platform Banner

Excel - Edit a macro Tutorial

In this video, you will learn how to edit a macro using Microsoft 365. The video covers the steps to modify a macro and demonstrates the process in a clear and concise manner.
This tutorial will help you enhance your skills in using macros and make the most out of Microsoft 365.

3:29
3959 views
Parteger

Description

Objectifs :

This video aims to guide viewers on how to edit an existing macro in Excel to change its functionality from entering day names to entering day numbers, and to arrange them vertically in a column instead of horizontally in a row.

Chapitres :

Introduction to Macro Editing
In this video, we continue from our previous lesson on recording macros. We will focus on editing the macro named 'FillDaysRelative', which currently enters the days of the week across a row. Our goal is to modify this macro so that it enters the days as numbers and arranges them vertically in a column.
Accessing the Macro for Editing
To begin editing the macro, click the macro button. Select the last recorded macro, 'FillDaysRelative', and then click 'Edit'. This action opens the macro in the Visual Basic Editor, a specialized text editor for programming macros.
Understanding the Macro Structure
In the Visual Basic Editor, the macro is defined as a 'Sub' or subroutine, with the name 'FillDaysRelative' followed by parentheses. To modify this macro, select all the text from 'Sub' to 'End Sub' and copy it using Ctrl + C. Then, click at the end of the text, press Enter, and paste the copied text.
Renaming the Macros
Next, we will rename the original macro to 'FillDaysRelativeR' for rows and the copied macro to 'FillDaysRelativeC' for columns. Since macros cannot share the same name, this step is crucial for avoiding conflicts.
Modifying the Macro Instructions
The first modification involves changing the ActiveCell formula from 'Sunday' to the number '1'. The next change is in the autofill designation. We will alter the range from 'A1:G1' (across the row) to 'A1:A7' (down the A column). Make sure to change it in both specified locations to ensure the macro behaves as intended.
Saving Changes and Running the New Macro
After making these minor changes, close the Visual Basic Editor by clicking the red 'X'. Now, if you click on 'Macros', you will see 'FillDaysRelativeC', the new macro. Running this macro will now enter the numbers 1 through 7 down column A instead of the day names across row 1.
Conclusion and Next Steps
In this video, we demonstrated how a few minor changes in the Macro Editor can significantly alter the functionality of a macro. In the next video, we will learn how to save your macro so that it can be used anytime with any workbook on your computer.

FAQ :

What is a macro in Excel?

A macro in Excel is a sequence of instructions that automate tasks, allowing users to perform repetitive actions quickly and efficiently.

How do I create a macro?

You can create a macro by recording your actions in Excel or by writing the code manually in the Visual Basic Editor.

What are relative references in macros?

Relative references allow a macro to adjust its cell references based on the current position of the active cell, making it adaptable to different situations.

Can I edit an existing macro?

Yes, you can edit an existing macro by opening it in the Visual Basic Editor and modifying the code as needed.

What is the Visual Basic Editor?

The Visual Basic Editor is a tool within Excel that allows users to write, edit, and manage macros using Visual Basic for Applications (VBA).

How do I save a macro for future use?

To save a macro for future use, you can save it in your personal macro workbook or in a specific workbook where you want to use it.

Quelques cas d'usages :

Automating Weekly Reports

A financial analyst can use macros to automate the generation of weekly reports by entering data for each day of the week, saving time and reducing errors.

Data Entry for Surveys

A data entry clerk can create a macro to quickly fill in survey responses, allowing for efficient data collection and analysis.

Inventory Management

A warehouse manager can use macros to update inventory levels automatically, ensuring accurate stock counts and timely reordering.

Creating Custom Dashboards

A business analyst can develop macros to populate custom dashboards with data from various sources, streamlining reporting processes.

Batch Processing of Data

A researcher can apply macros to process large datasets in batches, applying consistent formatting and calculations across multiple files.

Glossaire :

Macro

A macro is a set of instructions that automate repetitive tasks in software applications, particularly in spreadsheet programs like Excel.

Relative References

Relative references in macros allow the macro to adjust the cell references based on the position of the active cell, making the macro more flexible.

Visual Basic Editor

The Visual Basic Editor is a specialized text editor used for writing and editing macros in Visual Basic for Applications (VBA).

Subroutine

A subroutine, or 'Sub', is a block of code in VBA that performs a specific task and can be called from other parts of the program.

Autofill

Autofill is a feature that allows users to automatically fill a series of cells with data based on a pattern or existing data.

R1C1 Reference Style

R1C1 reference style is a way of referencing cells in Excel where 'R' stands for row and 'C' stands for column, allowing for more dynamic cell references.

Available in Microsoft365

00:00:07

and how and why to turn on relative references,

00:00:10

so the macro behaves the way we want.

00:00:13

Let's pick up where we left off with our macro: FillDaysRelative;

00:00:18

which enters the days of the week in cells across a row.

00:00:23

We will change it, so it enters the days as numbers,

00:00:26

instead of names, and arrange them vertically in

00:00:29

a column, instead of horizontally in a row.

00:00:34

We could record a new macro, or build off of this one

00:00:37

by editing it manually.

00:00:40

Let's edit the macro, to see what its instructions look like.

00:00:45

Click the macro button;

00:00:47

here's the last macro I recorded: FillDaysRelative;

00:00:52

select it; and then click Edit.

00:00:57

This opens the macro in a special text editor

00:00:59

called the Visual Basic Editor.

00:01:02

Visual Basic is the programming language that macros are recorded in.

00:01:07

You can also create macros from scratch in this editor,

00:01:10

which is doing honest to goodness programming.

00:01:14

Here's the macro; it's called a sub, or subroutine,

00:01:18

and its name FillDaysRelative is followed by a pair of parentheses.

00:01:24

I'll select all of this text, starting with Sub,

00:01:28

and ending with End Sub, and copy it by pressing Ctrl-C.

00:01:35

I'll click here at the end, press Enter

00:01:38

and then paste what I just copied.

00:01:42

Let's rename the original macro FillDaysRelativeR,

00:01:46

for rows, and rename the copy, FillDaysRelativeC, for columns.

00:01:51

Since these two macros can't have the same name.

00:01:55

I'm going to change the instructions in this copy of the macro, FillDaysRelativesC.

00:02:02

The first thing we need to change is the first instruction

00:02:06

ActiveCell.FormulaR1C1 = "Sunday"

00:02:12

We'll change the dayname to the number "1"

00:02:17

The next thing will do, is in the autofill designation.

00:02:21

Change A1:G1, which is going across the row,

00:02:27

to A1:A7, which goes down the A column.

00:02:32

Change it in two places, here, and also here.

00:02:37

Those are just a couple of minor changes,

00:02:39

but they'll change the behavior of the macro.

00:02:42

I'll close the Visual Basic Editor by clicking this X in the red box,

00:02:47

and that'll save my changes.

00:02:50

And now if I click Macros, I have three macros,

00:02:54

and here's FillDaysRelativeC,

00:02:57

which is the new macro, that will behave a little differently.

00:03:02

I'll run it, and you can see that instead of entering

00:03:04

Sunday, Monday, and so on across row 1,

00:03:08

it enters the numbers 1 through 7, down column A.

00:03:13

All that, just from a couple of minor changes

00:03:16

that we made in the Macro Editor.

00:03:19

In the next video, we will save your macro to

00:03:22

your personal Excel workbook, so that you can use it any time

00:03:25

with any workbook on your computer.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

Video Training Microsoft Excel - Advanced level

E-learning Intermediate

Vu 3142 fois

Video Training Microsoft Excel - Advanced level

With our "advanced" tutorials, discover and master the full potential of Excel, using complex functions, macros and the power of DCTs and Power Pivot, combined with the Power Query tool.

ALL COURSES