Excel Macro for Absolute Beginners

---

1. What is a macro?

If you find yourself doing the same routine task over and over again, you might want to consider creating a macro to complete the task for you. A macro helps you perform routine tasks by automating them.

2. Not clear with it,here is some more.

Let us consider a robot to which every instruction has to be given seperately.For example, if you want it to drink water, then you would tell him to get up, walk to cooler, fill the glass, drink it etc.Instead, I can write all these on a paper and give a title DRINK WATER and whenever I say drink water, it would take the paper and read and execuete the instructions automatically.

3. So what are the advanatges of it?

When I give the instructions one by one, I must wait till it finishes each one and only then I can give the next. But when I give the full list, I don't have to wait, I just need to tell once and everything would be completed in seconds. As humans we would make mistakes while giving the instructions one by one.

4. How to record a macro?

5. Let us create another macro.

1.Start macro recording.(Tools->Macro-Record new macro) 2.Enter How r u in the cell A1. 3.Copy it to the next 19 cells. 4.Stop recording. 5.Delete the conents of the cells A1 to A20. 6.Run the macro(Tools->Macro-Macros)

6. Is there any other way to create a macro?

Yes.It is by using VB Editor. It is written in the same way as VB.

7. How to use it?

1.Go to Macro->Visual Basic Editor 2.Start writing your code there. 3.The name you give for your subroutine is the name of the macro. 4.To run, go to Macro->Macros->select the name of your macro.

8. Let us create a macro that will display the name of the current worksheet.

Go to Macro->Visual Basic Editor Write the following code. Sub mcrDisplayName()   MsgBox(ActiveSheet.Name) End Sub

9. Display the name of the current cell.

Sub mcrDisplayCellName()   MsgBox (ActiveCell.Address) End Sub

10. Copy the content of cells A1 to A5 to B1.

Sub mcrCopy()   Range("a1:a5").Copy Range("b1") End Sub

11. Copy the content of cells A1 to A5 to B1 of another worksheet.

Sub mcrCopyToSheet()   Sheets("sheet1").Range("a1:a5").Copy Sheets("sheet2").Range("b1") End Sub

12. To delete a range of cells.

Sub mcrDelete()   Range("a1:a5").Clear End Sub

13. To cut and paste in another location.

Sub mcrCut()   Range("a1:a5").Select   Selection.Cut Range("h1") End Sub

14. Let us move further by using For Loop.

This macro will first show a text box in which you enter a number and then you can hear beeps for that many number of times and message box is shown after each beep. Sub BeepSeveral() x = InputBox("how many beeps?") For counter = 1 To x    Beep    MsgBox (counter) Next counter End Sub

15. Let us add a If-Else to the example.

We will first check whether the entered data is a number.If it is so then execute the For-Loop otherwise show a message. Sub BeepValidate() x = InputBox("how many beeps?") If IsNumeric(x) Then   For counter = 1 To x      Beep      MsgBox (counter)    Next counter Else   MsgBox ("You did not enter a number.") End If End Sub

16. An Example for using For-Each.

In this, the loop is executed for each element in the collection.We dont have to specify a start and end codtion in this case. Let us see an example that will change the color of the font of the numbers below 0 in a specific range of cells. Sub mcrChangeColorlt0()  For Each c In Range("a1:a20").Cells   If IsNumeric(c.Value) And c.Value<0 Then      c.Font.Color = RGB(255, 0, 0)   End If  Next c End Sub

Enough of Macros.Let's learn something interesting.

Do you know why is Neptune blue? Neptune's atmosphere is made up of hydrogen, helium and methane. The methane in Neptune's upper atmosphere absorbs the red light from the sun but reflects the blue light from the sun back into space. This is why Neptune appear blue. For more info about Neptune click here

---

Watch the latest videos on YouTube.com