#record macro

New Post has been published on http://access-excel.tips/first-excel-macro/

Writing your first Excel Macro by recording Macro

This Excel tutorial guides you to create your first Excel Macro by Record Macro function, and explain how to modify Macro in Visual Basic Editor

You may also want to read:

Create Excel Add-in and add in Ribbon

Excel Record Macro

Writing your first Excel Macro by recording Macro, why?

First of all, you should face the fact that you cannot know everything. You can use Microsoft Windows does not mean you can write your own Windows. Even if you are a Windows Administrator, it only means you have better knowledge on modifying parameters, but you do not need to how to write a Windows. Similarly, you can use Photoshop does not mean you can write the software. Depending on the role, different people are required different skill set, there are people who are “users”, some are “administrators”, and some are “developers”.

If you are a starter in Excel VBA, your role is changed from a “user” to a “developer”. It is a big change, you have to understand Excel well enough to make that change, and it takes a lot of efforts even if you are an expert user. During the transition, you should try being an “administrator”, someone who knows the software well and is able to modify parameters. Even as a developer, you don’t always need to write a program from zero, you just need to pull different sets of code together to become your own work.

As an administrator, you may not understand every line of code of a program but you should be able to change the parameters. Therefore, the first step of understanding Excel VBA is to record a Macro, read a program’s code and practice modifying the code and see what happens through trials and errors.

Understanding recorded Macro

Before continuing reading, you should have knowledge in recording Macro. Read the below article if you do not know how.

Excel Record Macro

Once you know the Excel VBA code behind an action, you can write your own actions or modify the code for your own need.

For example, you have recorded actions that do the followings

1) Select worksheet “Sheet1″ 2) Type “1” in Range A1 3) Delete value in Range A1

Having recorded the above actions, go to VB Editor (Alt+F11) to see how the codes look like for each of those actions

1   Sub Macro1() 2      Sheets("Sheet1").Select 3      Range("A1").Select 4      ActiveCell.FormulaR1C1 = "1" 5      Range("A1").Select 6      Selection.ClearContents 7    End Sub

You can easily identify which line of code is representing the corresponding action. Now I am translating each code below into English.

1    Create a Macro called Macro1 with no parameters 2   Select worksheet “Sheet1″ 3   Select Range A1 4   For the active cell (the one you have just selected), type formula  “1” 5   Select Range A1 6   For the selected Range, clear the contents 7   End of the Macro

It isn’t so hard to understand, right? Some codes are very easy to read, such as “Select”, “Range”, “Sheets”. If you don’t understand any of the code, just search it in goggle. For example, if you don’t understand what is FormulaR1C1, just search “Excel VBA FormulaR1C1″, and you will get a lot of results.

Writing your first Excel Macro

Now that you have identified the underlying code behind each action, you should be capable of writing your own first Excel Macro.

For example, I want to write “2” in A1 of “Sheet2″

1   Sub Macro2()      'change a different name for each Macro 2      Sheets("Sheet2").Select 3      Range("A1").Select 4      ActiveCell.FormulaR1C1 = "2" 5    End Sub

You have created your first VBA by modifying a recorded Macro, it is a good start! Try different actions like changing cell color, copy and paste, creating pivot table, search a text, and see how the codes look like, I assure you they are very easy and straight forward to use.

What’s Next?

There are things that cannot be recorded, but you should learn them step by step. The next thing you need to learn is the programming basics, such as define variables, VBA function, Objects and their Properties, condition (such as If..Else), Loop (such as For…Next)

You are recommended to read my other posts under the Excel VBA category in order of Unit.

Outbound References