Excel - Save your macro Tutorial

In this video, you will learn how to save, share, and organize content using OneNote.
The video covers the process of saving web pages to OneNote using the OneNote Web Clipper extension in Microsoft Edge or Google Chrome.
You can choose to save a specific region, article, or the entire page.
Once saved, you can view and add notes to your clips in OneNote.
This tutorial will help you efficiently save and organize your online content using OneNote.

2:59
3937 views

Available in Microsoft365

Objectifs :

This video aims to guide users on how to save a macro in the Personal Macro Workbook in Excel, ensuring that the macro is always available for use across different workbooks. It covers the steps to record a macro, copy existing macro code, and manage the visibility of the Personal Macro Workbook.

Chapitres :

Introduction to Personal Macro Workbook
The Personal Macro Workbook is a hidden workbook in Excel that loads automatically every time you start the application. This allows users to store macros that can be accessed from any workbook, enhancing productivity and efficiency.
Recording a Macro
To save a macro in the Personal Macro Workbook, start by launching the macro recorder. Assign the macro the same name as the original, 'FillDaysRelativeR', and select 'Personal Macro Workbook' under the 'Store macro in' option. After clicking OK, stop the recording immediately, as the steps will be copied from an existing macro.
Accessing and Editing Macros
Open the Macros dialog box to view the recorded macros. Locate the newly recorded macro in the Personal Macro Workbook. To edit it, click 'Edit' to open the Visual Basic Editor. Here, you will see the macro code starting with 'Sub FilldaysrelativeR'. Select all the code, including 'End Sub', and copy it.
Unhiding the Personal Macro Workbook
If you encounter a message stating that the workbook is hidden and cannot be edited, you need to unhide it. Click on the 'View' tab and select 'Unhide'. Choose the hidden workbook and click OK. Now, navigate to the 'Developer' tab, click 'Macros', and select the macro in the Personal Workbook to edit.
Pasting and Saving Changes
In the Visual Basic Editor, replace the limited instructions of the recorded macro with the copied code. After pasting, close the editor to save the changes. To clean up, hide the Personal Workbook by switching to it and clicking 'Hide' under the 'View' tab.
Ensuring Macro Availability
Once the Personal Macro Workbook is set up, the macro will be available whenever you open Excel, whether you are working on an existing workbook or creating a new one. Remember to save the Personal Workbook before closing Excel to ensure that your macro is retained.
Conclusion and Next Steps
In the next video, viewers will learn how to assign a button to a macro, further enhancing the usability of macros in Excel. This concludes the current tutorial on saving and managing macros in the Personal Macro Workbook.

FAQ :

What is a Personal Macro Workbook?

A Personal Macro Workbook is a hidden workbook in Excel that automatically opens every time you start Excel. It allows you to store macros that you want to use across different workbooks.

How do I save a macro to my Personal Macro Workbook?

To save a macro to your Personal Macro Workbook, start the macro recorder, choose 'Personal Macro Workbook' under 'Store macro in', and then record your macro. After recording, you can edit the macro in the Visual Basic Editor.

What is the Visual Basic Editor used for?

The Visual Basic Editor is used to write, edit, and manage macros in Excel. It allows users to modify the code of their macros for more advanced functionality.

How can I unhide my Personal Macro Workbook?

To unhide your Personal Macro Workbook, go to the 'View' tab in Excel and click on 'Unhide'. Select the Personal Macro Workbook from the list and click OK.

Can I use macros from my Personal Macro Workbook in any Excel file?

Yes, macros stored in your Personal Macro Workbook are available in any Excel file you open on the same computer where the workbook is saved.

Quelques cas d'usages :

Automating Data Entry

A financial analyst can use a macro stored in the Personal Macro Workbook to automate the process of entering monthly sales data into a report. This saves time and reduces the risk of errors.

Generating Reports

A project manager can create a macro that formats and generates weekly project status reports. By saving this macro in the Personal Macro Workbook, it can be reused for different projects without needing to recreate the formatting each time.

Batch Processing Data

A data scientist can develop a macro to clean and process large datasets. By storing this macro in the Personal Macro Workbook, it can be applied to any dataset opened in Excel, improving efficiency.

Customizing Excel Functions

An accountant can create a macro that customizes Excel functions for specific calculations. By saving it in the Personal Macro Workbook, the accountant can easily access and apply it across various financial models.

Glossaire :

Macro

A macro is a set of instructions that automate tasks in Excel. It allows users to perform repetitive actions quickly.

Personal Macro Workbook

A special hidden workbook in Excel that opens automatically every time Excel starts. It is used to store macros that you want to be available in any workbook.

Visual Basic Editor

A tool within Excel that allows users to write and edit macros using Visual Basic for Applications (VBA).

Sub

Short for 'Subroutine', it is a block of code in VBA that performs a specific task. Each macro begins with 'Sub' followed by the macro name.

End Sub

A statement in VBA that indicates the end of a subroutine. It is necessary to define where the macro code finishes.

Unhide

The action of making a hidden workbook visible again in Excel.

Hide

The action of making a workbook invisible in Excel without closing it.

00:00:08

I can do this by saving it to my personal macro workbook,

00:00:13

which is a special hidden workbook that loads every time you start Excel.

00:00:18

I start the macro recorder, and give this macro the same

00:00:22

name as our original macro: FillDaysRelativeR,

00:00:27

but this time, under Store macro in,

00:00:30

I choose Personal macro workbook, and click OK.

00:00:36

Just as quickly, I stop the recording,

00:00:39

because I didn't really need to record the steps;

00:00:42

because I'm just going to copy over them.

00:00:44

I bring up the Macro's dialog box

00:00:47

and now you can see the macros I have.

00:00:50

And here's the macro I just recorded in my personal macro workbook.

00:00:55

And here's the macro that I want to copy.

00:00:58

I click Edit, which brings up the Visual Basic Editor,

00:01:03

and this is the macro: Sub FilldaysrelativeR,

00:01:08

so we'll select all of it, including End Sub; copy it;

00:01:14

and then close the Visual Basic Editor.

00:01:18

Now I'll select the macro in my personal workbook that I want to edit,

00:01:24

but Excel says that the workbook is a hidden workbook,

00:01:27

and we can't edit a macro; so click OK and Cancel.

00:01:34

Now we'll unhide the personal workbook.

00:01:37

To do that, click the View tab, and then click Unhide.

00:01:43

Here's the hidden workbook, so click OK;

00:01:47

click the Developer tab; click Macros;

00:01:51

and select the macro in the personal workbook.

00:01:55

I click Edit, and here are the limited instructions that we recorded;

00:02:00

I'll just paste over them.

00:02:04

Now I just close the Visual Basic Editor, which saves the changes.

00:02:10

The last thing I want to do is clean up.

00:02:12

So I'll hide the personal .xlsb file, the hidden workbook.

00:02:18

Click the View tab; switch the window to the personal workbook;

00:02:25

and click Hide.

00:02:27

From now on, when I work on this computer, whether I open

00:02:31

an existing workbook, or create a new one, that macro in

00:02:35

the personal workbook is always available.

00:02:39

The next time I shut down Excel,

00:02:42

it will ask me if I want to save the changes to the personal workbook.

00:02:47

Since I want to save this macro to my personal workbook, I'll click Save.

00:02:53

In the next and final video, I'll show you how to assign

00:02:56

a button to a macro.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

2675 Views

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 Po...

ALL COURSES