More on Visual Basic Editor or VBE

Although we already about the how to record a macro let us familiarize ourselves with the Visual Basic Editor Window.

Below is the snapshot of the VBE and its components:

Snapshot of VBE Window

I do not want to provide further explanation regarding these feature as they are quite self-explanatory. The idea is to familiarize you with the notations that I shall be using henceforth to refer to any of these components. Also, note that there is a feature called “Immediate Window”, it can be activated by pressing Ctrl+G together. The usage is related to debugging the code. We shall understand its role later.

Macros!

Ever wondered if we can customize excel? Yes, we can to a certain extent!

Excel has something called as Macros to offer. These are nothing but short codes which can reduce the time and effort required for any monotonous or complicated task.

Just code it, assign the task a shortcut notation and you are ready to use it across. It sounds simple and it is really simple!!

You can just record a macro and run it whenever required. You can also write your own code.

You do not have to be a prodigy to write a code. Understand the logic, draw a flow chart and finally get acquainted with the syntax. That’s it! There is no Secret Ingredient to it J

Let’s understand a small example to kick start..

Consider a case where the user wants to copy and paste the data on a new sheet each day. With the help of school math and logic, the user would be required to perform four steps, i.e. select the data, copy the data, click on the new sheet and paste the data. To perform all of this in a single step we can record a simple macro and run it each day. Just for three steps!! Well that is a really small task but it has to executed on a daily basis, so you can multiply the number of days with four to get your answer. Besides, there are lengthy task which are supposed to be performed on a daily basis. Macro can come handy when you want to perform the task only once and repeat it later with just a single click of a button.J

Let’s get back to our simple example…

This goal will be too easy to achieve and would be a stepping stone for the upcoming one..J

Goal: To Select, Copy and Paste the data from one sheet to another using a Macro!

Solution: 

Step 1: Consider the following data exists on Sheet1

Click on View Tab present on the Toolbar

Step 2: Click on Macros tab under View.

Step 3: Select Record Macro (second option). An overlay opens as shown below.

You can give any name to your macro. By default it will be Macro1 if you are recording it for the first time.
You can store the macro in the current workbook by selecting Store macro in This Workbook option.
There is a field to add a description about the operation that your macro intends to perform.
Also, you can assign a shortcut key to your macro. You just have to click Ctrl+key and your macro will run automatically.
Having done that click on OK. I have kept the default options.

Step 4: Now we will be teaching excel what we want it to do for us.

Select all the data present on Sheet1 as below

Step 5: Copy the content by hitting Ctrl+C

Step 6: Browse to the Sheet2 (Keeping the cursor on the first cell on Sheet2). Paste the data by hittin Ctrl+V

Step 7: Go to the Macros tab and select Stop Recording as below

Step 8: Now delete the data pasted on Sheet2 and let's test if our macro understood what it has to perform.

Step 9: Keeping the cursor on the first cell on both the sheets, let's run the macro after browsing on Sheet1.

Step 10: Click on View Macro and select This Workbook under Macros in option as below and run the macro by selecting it from the list of Macros (if any) and clicking Run button

Step 11: See the MACRO WORK for you!!

If you add additional data/change the data under Sheet1 and run the macro then it will paste the latest data for you!

We shall learn better ways to utilize this in the next post. Till then Happy Learning! J