Tutorial 3:A Quick Simple MS Excel Macro

by Pranav Ghode

www.verydashbored.com

What is an Excel Macro?

• A set of instructions to take care of repeat tasks, and save critical time at the press of a button (really!)

• Also, the set of instructions to help you at being more creative with hidden Excel capabilities

So then let us begin…with some basic experimentation…

Afterall, as William Blake puts it…

“The True method of Knowledge is Experiment”

Macro Experiment 1: Pattern Creator

1. We want to create a fixed pattern of letters (as shown in rows F, G and H)

2. Based on a letter mentioned in a particular cell (Cell C4)3. That too, at the press of a Button shown below

An Example for Letter “C”For Letter “B”

For Letter “A”

And so on…

SolutionStep 1 : Initial Setup (Visual Setup)– Draw boundaries for the pattern for cells shown below as

per your wish– Write respective titles and headers (i.e. Letter and Pattern)– Remove gridlines and make other visually appealing

changes

SolutionStep 1 : Initial Setup (Insert a Button)

Go to Developer Tab >> Insert >> Form Control >> Button

If Developer Tab is not visible, then please visit Slide no. 5 of Tutorial 2 (Part – II) for instructions

SolutionStep 1 : Initial Setup (Insert a Button)A dialog box will open up which will request to Assign a macro. Press OK. This is meantfor assigning existing macros. Since we have not created any macro yet, you can ignoreit by pressing OK and move on.

SolutionStep 2 : Create a Macro (Choose your method)There are primarily two ways of creating macros.1. Beginner but fast and smart: Record the macro and tweak the generated code as per

your requirements2. Super techie Expert geeky: Write all code/instructions by yourself from scratch without

using the record function

Our obvious choice is the first one

Go to “Developer tab” and search for “Record Macro” option

SolutionStep 2 : Create a Macro (Initiate Record)Write a name for the Macro you want to record and Press OK. In our case it is “Macro1”

SolutionStep 3 : Create a Macro (Record)Now the recording has begun and can be verified with the following indicator in the developer tab…

Step 3 : Create a Macro (Record)Upon initiation of any recording, MS Excel will capture any actions performed in the form of the macro code. This recorded code is important and can help to solve many repetitive tasks saving you endless hours of labor.

In our example of pattern creation:-We want anything present in Cell C4 to be copied in the destination Cells

Solution

Copy from C4 To these cells

Copy C4 >> Paste into F6, G6, H6, G5, G7 >> Press Esc

Upon completion. Stop Recording

Solution

CORRECT: Right Click on the boundary WRONG: Right Click on the body

Step 3 : Assign MacroWe will now add the recorded macro to the inserted button.Right Click on the Button as shown below and Assign Macro. A dialog box will open up with list of all your macros. Select “Macro1”. However follow the correct method as given below. You can spot the differences.

By the way, you can assign macros to any shapes that you insert.

SolutionStep 4 : Run the MacroLet us now Run the Macro and see what happens.

Insert A in C4 and check if you get this…

Again, Insert B in C4 and check if you get this…

If Yes Yayy….!!

Amazing, you have created your first macro…

If not, then don’t worry we will see why…go to next slide

If Yes

SolutionStep 5 : Open the macro (the VBA Editor)Go to “Developer Tab” >> “Macros”

You will see a dialog box. It shows all the macros you have created in this Excel file. Press “Edit” and your recorded code will show up

(as shown below)

SolutionStep 5 : Open the macro (The VBA Editor)

What you are seeing is the VBA Editor. Here you can tweak your recorded instructions as per your specific requirements.

I have crossed out areas which we should not bother ourselves with, as of now…

Only this area (code) is important for the moment…highlighted by a smiley**Gosh, that smiley is so primitive!

SolutionStep 6 : Assess and play with your code

Sub Macro1()'' Macro1 Macro'

' Range("C4").Select Selection.Copy Range("G5:G7").Select ActiveSheet.Paste Range("F6:H6").Select ActiveSheet.Paste Range("C4").Select Application.CutCopyMode = False

End Sub

When you initiated the record button at Step 3.MS Excel, started capturing all your actions.If you remember, the following actions were performed (Refer Slide 11):

1. Copy C42. Paste into F6, G6, H6, G5, G73. Press Esc

We can break each action down in its more detailed steps…For Step 1 i.e. Copy C4-I selected Cell C4-Then I pressed “CTRL + C” for initiating CopyFor Step 2 i.e. Paste into F6, G6, H6, H7, G7-I selected Cells from G5 to G7-I Pasted contents from Cell C4 using “CTRL +V”-Then selected Cells from F6 to H6-I Pasted contents from Cell C4 using “CTRL +V”For Step 3 i.e. for Press Esc-I selected Cell C4 again (no specific reason here)-I pressed Esc, and that deactivated the Copy Past mode (stopped the ants from moving in rectangles!)

So where do we now go from here??1. If you did not get the code right then…

Check it against code I have pasted in last slide…see what happened and try to solve it. It shouldn’t be difficult. If any problem, comment below or contact me on www.verydashbored.com

2. If you got it right then….now search for some more things…Start exploring by asking yourself some questions. For example, what was the “Sub Macro1 ()….and End Sub” in the previous slide.(Here is a clue: http://en.wikipedia.org/wiki/Subroutine)Or what were those two areas in the VBA editor which were crossed out?? Or what is the difference between Range and Cells (No Hints here! – go find it..all knowledge is available out there…reach out if you are stuck)

3. Lastly…I am sure you are excited as I was when I first figured out this whole macro thing. I always heard about Excel macros but I never knew what it was until I really forced myself to solve the mystery thus opening up new doors. Later I downloaded several macros. Pasted it into the code area. Experimented with them. Saw what code used to get generated for some very specific tasks like “Writing a comment in a cell, showing it, hiding it, etc..” and googled a lot for all various syntaxes, code logic and things which I wanted to become fast and smart at, or impress someone at the press of a button….Also, I merged several macros to create some really cool excel automations….So go ahead and have fun with Excel Macros.

Thank you

Visit www.verydashbored.com for more stuff onexcel dashboards, tutorials, other blog-posts