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
- 3686 views
-
Power BI - Customising your Visuals
- 2:58
- Viewed 4035 times
-
Power BI - Customising your Report
- 3:12
- Viewed 3566 times
-
Excel - Replace values
- 0:53
- Viewed 531 times
-
Excel - Import a series of files into a folder
- 2:58
- Viewed 539 times
-
Excel - Delete columns
- 3:38
- Viewed 620 times
-
Excel - Delete rows
- 2:06
- Viewed 436 times
-
Remove a watermark
- 2:20
- Viewed 40025 times
-
Change the default font for your emails
- 1:09
- Viewed 23720 times
-
Collapsible headings
- 3:03
- Viewed 20324 times
-
How to recall or replace a sent email in Outlook Web
- 0:53
- Viewed 20272 times
-
Activate the features of Teams Premium
- 3:48
- Viewed 19719 times
-
How do I prevent the transfer of an email?
- 2:07
- Viewed 16987 times
-
Create automatic reminders
- 4:10
- Viewed 13142 times
-
Protect a document shared by password
- 1:41
- Viewed 12060 times
-
Morph transition
- 0:43
- Viewed 11235 times
-
Add a sound effect to a transition
- 3:45
- Viewed 10737 times
-
Remove a watermark
- 2:20
- Viewed 40025 times
-
Change the default font for your emails
- 1:09
- Viewed 23720 times
-
Collapsible headings
- 3:03
- Viewed 20324 times
-
How to recall or replace a sent email in Outlook Web
- 0:53
- Viewed 20272 times
-
Activate the features of Teams Premium
- 3:48
- Viewed 19719 times
-
How do I prevent the transfer of an email?
- 2:07
- Viewed 16987 times
-
Create automatic reminders
- 4:10
- Viewed 13142 times
-
Protect a document shared by password
- 1:41
- Viewed 12060 times
-
Morph transition
- 0:43
- Viewed 11235 times
-
Add a sound effect to a transition
- 3:45
- Viewed 10737 times
-
Block the transfer of a Teams meeting
- 02:40
- Viewed 26 times
-
Control the start of recording and transcription in Teams
- 03:03
- Viewed 23 times
-
Manage access to recordings and transcripts in Teams
- 02:59
- Viewed 20 times
-
Enable voice isolation in Teams
- 02:14
- Viewed 34 times
-
Add a collaborative page to a Teams channel
- 03:06
- Viewed 19 times
-
Manage a channel’s files with the Shared tab in Teams
- 03:34
- Viewed 32 times
-
Track conversations and organize channels in Teams
- 03:26
- Viewed 22 times
-
Create a team and set up the first channel in Teams
- 03:02
- Viewed 22 times
-
Create a newsletter in Outlook
- 02:23
- Viewed 56 times
-
Create an issue in an Outlook newsletter
- 03:27
- Viewed 60 times
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.