Excel Macro and VBA: Fundamental Basics (Record Macro Button and VBE)
This article is about Excel Macro fundamental basics and explains how to use the ‘Record Macro’ button, how to edit the codes and save the file with Macros in a step-by-step way.
(Duration: 7:41)
＜＜ Related Posts ＞＞
- Excel Macro Application: Synergy Effect of Lean Six Sigma and Excel/Excel VBA
- How to conduct a simple operating system development with Excel
Today, we’ll discuss the very basics of Excel Macro and VBA.
Hi, this is Mike Negami, Lean Sigma Black Belt.
I received a video request about how to use Excel Macros from a viewer in the Mentoring Program. I would like to show you the very basics of that topic today. First, I’ll show you an example.
Here is a simple weekly inventory calculation sheet. From the top, there are cells for, this Monday’s date, beginning inventory, this week’s sales and this week’s purchased dollar amount.
The last two cells have formulas to calculate ending inventory ‘Beginning Inventory – Sales + Purchasing’, and weekly inventory turnover ratio ‘Sales÷((Beginning Inventory＋Ending Inventory)÷２)’.
After filling in this week’s data, we want to prepare for next week entry. We would copy the past five weeks’ results and paste them into the next column, enter next Monday’s date (Cell I5), copy this week’s ending inventory, paste it as next week’s beginning inventory, and clear the sales and purchasing cells.
Until now we’ve done this manually, but we can automate this with a Macro. We made a button assigned to that so you can just click the button and Excel will do it in a flash.
This automation not only makes your job fast and easy, but also increases accuracy and promotes your company’s standardization. Let’s make this Macro together today.
How to use the magic button, ‘Record Macro’
Don’t worry, it’s not difficult. Excel has a magic button called ‘Record Macro’. Simply click the button and it will record all steps you make on the worksheets until you click that button again. Then you can make the Macro do those same steps over and over again automatically. Let’s do it now.
If you’re using Macro Record for the first time, you have to set it up first. There is a small upside-down triangle at the top of the screen, click on it and select ‘More Commands’. (See the image below.)
Select ‘Customize Ribbon’ in the left pane. After you put a check by the ‘Developer’ and click ‘OK’, the ‘Developer’ tab will appear and you will be able to use the ‘Record Macro’ button.
Let’s start recording. In this screen, you can name your Macro. The default is ‘Macro1’. I’ll just click ‘OK’.
Next I’ll do the same manual operations I did earlier. After finishing the operations, I’ll click the ‘Stop Recording’ button to end the Macro recording. Now re-set everything with ‘Undo’ until you return to the original point.
Let’s play the Macro we made. When you click on ‘Macros’, all Macros that you created will appear. The one I made earlier is ‘Macro1’. I’ll select it and click ‘Run’. It’s done so fast. By the way, after playing a Macro, you cannot do ‘Undo’ the play, so be aware that.
Let’s make a button. Click the ‘Insert’ tab and ‘Shape’. You can use any shape as a button. I‘ll use a rectangle and name it ‘Next Week’s Preparation’. Right-click on the shape and select ‘Assign Macros’. I’ll select ‘Macro1’ on the next screen and click ‘OK’. From now on, just click this button and it will execute the Macro.
Edit your Macros in the ‘Visual Basic Editor (VBE)’.
However, one issue is that Monday’s date will not change. Let’s modify the Macro we just made. Go back to the ‘Developer’ tab and click ‘Visual Basic’, then it’ll start the ‘Visual Basic Editor’. You can edit your Macros and fix any bugs on this screen.
I’ll not give a detailed explanation today, but in this ‘Modules’, in the ‘Module 1’, my manual steps have been recorded like this.
In addition to the term ‘Macro’, there is another term: ‘VBA’. These two are easy to be mixed up but they are actually different. VBA stands for ‘Visual Basic for Application’ and is the name of a programming language that Microsoft developed. On the other hand, a Macro is a chunk of commands given to a computer. This ‘Macro1’ is one chunk. In VBA we write one command per line.
Those two highlighted lines above were recorded when Monday’s date was entered. ‘Range(“I5”)’means Cell I5. ‘.Select’ means ‘Select that cell’.
In the next line, ‘ActiveCell’ means that the cell that is currently selected. The next, an ‘=‘ has two meanings in the programming language world. One is to show that the two values are equal. The other meaning is to assign the right-side value to the left-side container.
Here, ‘4/2/2018’ is assigned to Cell I5. We would like to put next Monday’s date instead. How can we do that? There are various ways to do this. I’ll copy Range(“I5”) and put ‘Range(“I5”).value + 7’ instead of the date.
This means to add 7 days to the date in Cell I5 and assign the new date to Cell I5, which is one week after. Let’s go back to the Excel sheet. Now each time you click the button, next week’s date will be shown.
How to save a file with Macros: ‘Excel Macro-Enabled Workbook’
Lastly, I’ll show you how to save this Macro. It will be saved with the Excel file. At the end of the name of an ordinary Excel file, what we call an extension, is ‘.xlsx’. When saving an Excel file with Macros, choose ‘Excel Macro-Enabled Workbook’. The extension will then be ‘.xlsm’.
Today’s target process doesn’t take much time to do, even manually, but you can do this with more complicated processes with much more data, and you can execute it in the same way with a single click as well. The improvement effect is tremendous, so please try to create and use Macros.
