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.

  • $\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 '17 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 '17 at 18:06

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.

| improve this answer | |

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.

| improve this answer | |

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 need VBA, because it allows you to teach programming. Then remember that without the macro language spreadsheets implement a functional programming language. I had a teacher come to me in a panic as he had to teach functional programming to his A-Level pupils, I helped by reminding him that he already know a functional language.

| improve this answer | |
  • $\begingroup$ Thanks for the thoughts, but you are mistaken in a few points. VBA is practically VB6, so it is dated, but not "dreadful". It is by no means a good choice as a general purpose programming language, but the question was not about that. VBA is the best option for MS Office automation. About viruses: VBA enabled Office documents expose the whole system to the VBA code, and that is what makes it risky - practically the VBA code can be the "virus". For the last paragraph: I think I need to teach VBA because corporations pay big $$$ for me to teach VBA to their employees - simple as that. :) $\endgroup$ – vacip Jul 23 '17 at 13:55
  • $\begingroup$ Don't get me wrong, VBA is not great. When corporate employees start using a lot of VBA macros that usually means they are stretching way beyond the limits of Excel. In most cases they would be better off using some RDBMS or a specialized software. BUT these employees don't have the time nor the knowledge to learn programming in C# for example, or to design, build and maintain an MS SQL database. But they CAN learn to create simple VBA macros. You'd be amazed at the things they can do after a 4 day intensive VBA training. $\endgroup$ – vacip Jul 23 '17 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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