Today, we’ll discuss the simple basics of Excel Macro and VBA.
[Intro Video]
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, which is the beginning inventory
minus sales plus purchasing, and weekly inventory turnover ratio.
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, 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 easy and fast, but also promotes your company’s
standardization.
Let's make this Macro together today.
Don’t worry, it’s not difficult.
Excel has a magic button called ‘Macro Record’.
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 the same steps over and over again.
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’.
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 ‘Macro Record’ 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 this screen and click ‘OK’.
From now on, just click this button and it will execute the Macro.
But 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 ‘Module1',
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.
These two lines 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, ‘3/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.
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.
Thank you very much for viewing.
Please click the ‘Subscribe’ button.
Also click and watch my other related videos.
Thanks.