<p>I'll start by adding the exercises I'm currently using, but please don't let this stop you from posting more!</p>

<ul>
<li>Create a macro that inserts a header row. (Story: "Every day I download a csv file. Always the same structure, but it is missing the header row. I want a macro that inserts a formatted header above my data.")</li>
<li>Number formatting macro (Story: "I regularly use a certain currency format, and I hate having to dig through menus to get it. I want a shortcut for it.")</li>
<li>Date stamp macro ("I want a macro that puts the current date into the selected cell. I always want the current date, but I want it to stay the same and not change every day.")</li>
<li>Fill empty cells from above ("I need a macro that fills all the empty cells in the selected region with the value above the empty cells. A good use case is a pivot table that has been pasted as values, and is missing the values in the first column.")</li>
</ul>

<p>On the more complex side, I have an exercise where I ask them to create a macro to copy entire rows from one sheet to the first empty row in another one.</p>

<p>I have a few more, but they are totally artificial practice exercises.</p>

CC BY-SA 3.0
2017-06-02T13:15:39.463Z
2017-06-02T13:15:39.463Z
vacip
<p>One extremely handy macro is creating names ranges.</p>

<ol>
<li>Start recording</li>
<li>Select the range you want to name</li>
<li>Press the "create named range" in the "formulas" tab</li>
<li>give name</li>
<li>stop recording.</li>
</ol>

<p>Voila. This has many uses in real life</p>

<p>Another one might be creating a table with headers.</p>

<ol>
<li>Record</li>
<li>Select the table</li>
<li>detect the headers with "format as table" and check "my table has headers"</li>
<li>stop recording.</li>
</ol>

<p>Voila #2.</p>

CC BY-SA 3.0
2017-07-23T20:31:50.707Z
2017-07-23T20:31:50.707Z
ItamarG3
<p>On advanced Excel courses I teach attendees how to use the macro recorder. I also start VBA beginner trainings with the recorder and analysing the recorded code. I find the macro recorder a good entry point to learning VBA and programming. I'm always struggling with exercises though.</p>

<p>What exercises do you use when teaching MS Office macro recorder? Exercises that make sense in the real word. (E.g. creating a macro that writes "Hello World" in the active cell is fun and educational, but completely pointless in the real world.)</p>

<p>The main goal here is to give them a feel for the rigid sort of thinking macros and programming requires, and also to whet their appetite for macro programming, to show them how mundane tasks can be automated.</p>

CC BY-SA 3.0
2017-07-22T14:43:01.130Z
Spreadsheets implement a fine and upstanding functional language. Excel also implements Visual Basic (VBA). Spreadsheets are at there best when there are functions in the cells. To make things simpler there can be intermediate cells, in hidden columns. Visual Basic is a dreadful language, is poorly implemented, a security risk (documents using VBA can have viruses), and not visual. Almost everything I have seen done with VBA, both in the classroom and out, can be done without it: just functions in cells. This may be why you are struggling to find a use for it. It you are thinking that, you nee
ctrl-alt-delor
CC BY-SA 3.0
2017-06-02T14:34:42.230Z
These kinds of questions are fine on this site, but what this question is currently missing is a goal.
Ben I.
CC BY-SA 3.0
2017-06-02T18:06:32.683Z
I've been considering creating a class in Excel for Programmers, so I am interested in what this thread produces
Ben I.
<lesson-ideas><programming><exercises>
Excel macro recorder exercises
# Excel macro recorder exercises

(Asked by: username_0 on 2017-06-02)

On advanced Excel courses I teach attendees how to use the macro recorder. I also start VBA beginner trainings with the recorder and analysing the recorded code. I find the macro recorder a good entry point to learning VBA and programming. I'm always struggling with exercises though.

What exercises do you use when teaching MS Office macro recorder? Exercises that make sense in the real word. (E.g. creating a macro that writes "Hello World" in the active cell is fun and educational, but completely pointless in the real world.)

The main goal here is to give them a feel for the rigid sort of thinking macros and programming requires, and also to whet their appetite for macro programming, to show them how mundane tasks can be automated.

**Tags:** lesson-ideas, programming, exercises

> **Comments:**
> **username_2**: These kinds of questions are fine on this site, but what this question is currently missing is a goal.  
> **username_2**: I've been considering creating a class in Excel for Programmers, so I am interested in what this thread produces  
> **username_1**: Spreadsheets implement a fine and upstanding functional language. Excel also implements Visual Basic (VBA). Spreadsheets are at there best when there are functions in the cells. To make things simpler there can be intermediate cells, in hidden columns. Visual Basic is a dreadful language, is poorly implemented, a security risk (documents using VBA can have viruses), and not visual. Almost everything I have seen done with VBA, both in the classroom and out, can be done without it: just functions in cells. This may be why you are struggling to find a use for it. It you are thinking that, you nee  

---

## 2 answers

### Answer by username_0 on 2017-06-02. Score: 2

I'll start by adding the exercises I'm currently using, but please don't let this stop you from posting more!

* Create a macro that inserts a header row. (Story: "Every day I download a csv file. Always the same structure, but it is missing the header row. I want a macro that inserts a formatted header above my data.")
* Number formatting macro (Story: "I regularly use a certain currency format, and I hate having to dig through menus to get it. I want a shortcut for it.")
* Date stamp macro ("I want a macro that puts the current date into the selected cell. I always want the current date, but I want it to stay the same and not change every day.")
* Fill empty cells from above ("I need a macro that fills all the empty cells in the selected region with the value above the empty cells. A good use case is a pivot table that has been pasted as values, and is missing the values in the first column.")

On the more complex side, I have an exercise where I ask them to create a macro to copy entire rows from one sheet to the first empty row in another one.

I have a few more, but they are totally artificial practice exercises.


---

### Answer by username_3 on 2017-07-23. Score: 2

One extremely handy macro is creating names ranges.

1. Start recording
2. Select the range you want to name
3. Press the "create named range" in the "formulas" tab
4. give name
5. stop recording.

Voila. This has many uses in real life

Another one might be creating a table with headers.

1. Record
2. Select the table
3. detect the headers with "format as table" and check "my table has headers"
4. stop recording.

Voila #2.


---

