5
$\begingroup$

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.

$\endgroup$
3
  • $\begingroup$ These kinds of questions are fine on this site, but what this question is currently missing is a goal. $\endgroup$
    – Ben I.
    Jun 2, 2017 at 14:34
  • $\begingroup$ I've been considering creating a class in Excel for Programmers, so I am interested in what this thread produces $\endgroup$
    – Ben I.
    Jun 2, 2017 at 18:06
  • $\begingroup$ 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 $\endgroup$ Jul 22, 2017 at 14:43

2 Answers 2

2
$\begingroup$

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.

$\endgroup$
2
$\begingroup$

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.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.