Course Content – Learning the Magic of Macros / VBA

Learning the Magic of Macros is a course specially written for 2 groups of advanced Excel users who do not have any background in programming.

1) Those who are keen to explore the use of macros to automate and speed up tedious Excel tasks that needs to be completed on a regular basis

2) Those who like to modify existing macros built by someone else and customize them for his own use.

In the course, we will guide the participants from recording of macros to editing the recorded macros. We have also prepared exercises so that participants can practise those concepts they have just acquired. This is to make sure that participants have a good grasp of the concept learnt. The following videos are recorded from our face to face training.

Recording a macro that works

Using format cells as an example, the following video shows you how to record a macro and the different methods you can use to run the macros.

[jwplayer mediaid="1842"]

Recording macro – exercise 1 – record a macro to unhide worksheets

In this exercise, you are required to create a workbook with 6 worksheets. Hide 5 of them and then record a macro to unhide the 5 worksheets.

[jwplayer mediaid="1849"]


Recording macro – exercise 2 – record macro to split data into multiple workbooks

Using the Distribute Data file, record a macro that split the data by country and store the relevant data into their respective workbooks and give each file a name.

[jwplayer mediaid="1850"]


Accessing your codes through the Visual Basic Editor

To understand the macro you have recorded and make modification to it so that it can become more robust, you will need to know Visual Basic Editor (VBE), the tool that let you view and modify your codes after you have recorded them.

[jwplayer mediaid="1851"]


Understanding the macro codes that you have recorded

One of the great way to learn about macros is through the macro you have recorded. But with the macro running instantly, it is not possible to pause it half way. This problem can be solved through the Debug function, which allows you to run through the macro codes line by line and at your own pace. This will help you learn what each line of code does.

[jwplayer mediaid="1854"]