Home | xlmacros.com

Latest Blog Roll

xlmacros.com is primarily a tutorial website and aims to provide Excel Users (having little knowledge of Excel VBA), with the power to write efficient macros to supplement their efficiency while working on Excel. At the same time this websites aims at helping Excel user community develop necessary Excel skills by providing numerous tips, tutorials and forum platform.

At xlmacros.com we understand the frustration you have to go through, while trying to make something work in Excel and it just won’t work. We hereby try to make an attempt to compile various Excel tutorials, Excel VBA tutorials, Videos, a huge list of Excel Templates and Excel Addins for you to download and a Forum for you to chat and discuss about various issues you are facing in Excel.

Although, most of Excel users today are aware of something called VBA, there are not many who have taken advantage of this language to program Excel objects. Learning VBA, does not at all mean that you will become a programmer or you need to be one. VBA just helps you to save a lot of time by automating the repetitive tasks that you perform in Excel.

Please do remember, EVERYTHING...Yes, everything that you do in Excel manually can be programmed, and it does not need you to be a programmer to do so.

Excel has built in features which help you record actions and automatically generates the code for you.

All you need to do is to copy and paste these lines here and there and you get a program which you can use at the click of a button.

You can check out the step by step lessons with numerous examples and concepts in this Learn Excel VBA course here which will help you learn VBA quickly and easily.

Excel Training Video course is a series of videos developed by our team in case you want to learn Excel 2007 (but not the VBA)

Learn Excel Macros the Easy Way

This page explains Excel VBA with a new view (with Analogies). Excel VBA solves various complex Business problems and saves a lot of time by automating repeated tasks.

Basics of VBA

Concepts and Analogies:
The reason some of these concepts are very important to understand is that once you get on to write the Macros yourself you would have a better/generalized understanding of the way Macros and written. And also when Excel throws some exceptions like “Script out of Range” or “Object required” etc. they do not sound alien to you. And you can make out what Excel VBA expects you to do to make your Excel VBA Application program run.

Our World Vs Excel’s World

For a moment just think if Excel is the World (like our real World) Excel has a lot of people like you and me. Like they say everyone is unique in our World there would be many people in Excel (the new World) Now the People in our World are the Objects in the Excel World. Like there are many people here there are various objects in Excel. Like we all have our names these objects also have unique names all the Excel World’s people (objects) also have their names. The names of Excel World’s people are Application, Workbook, worksheet, range, PivotTable, Charts .

Now the People in our World are the Objects in the Excel World. Like there are many people here there are various objects in Excel. Like we all have our names these objects also have unique names all the Excel World’s people (objects) also have their names.
The names of Excel World’s people are Application, Workbook, worksheet, range, PivotTable, Charts .
Like sometimes one person can have two or more names some Excel Objects also can be referred by one or more names. i.e. if you want to refer to First cell in any worksheet you can either refer to it by Range(“A1”) or you can refer to it by Cells(1,1) , now both of these statements refer to an Object (Person) in the Excel World which is A1 cell in a sheet.

Best Ways to Learn Excel VBA

The best way to learn Excel VBA is to record as many macros as possible (Record Macros) and see what code Excel generates. By seeing the code Excel generates again and again you will get the feel of it and slowly you will become familiar with the way the code is written. In fact, this is one of the best part of Excel VBA you do not need to know and learn anything when you make programs in Excel. Excel itself generates the programs for you. You just need to modify and manipulate the statements program To get desired results.

Recording and Running Macros

Excel has built in Macro recorder which you can use to record your actions while working on Excel. To perform the same action again and again you can run the macros. Go to View I toolbars I Visual Basic to open the VB console. Once you have the VB Console, record the macro

You can give name to your macro, assign a shortcut key to it and also tell Excel where to store the Macro (Personal Macro Book or the Workbook you are working on).

To run the macro you have recorded simply click the play button, select your macro from a list and click run. Clicking the Run Button would run the Macro and will do all the actions you did while you were recording the macro. Just think of a Macro recorder as a Voice Tape recorder. You record some sounds once you have recorded them you can listen to the same sound anytime by playing it. Just like a Voice Tape Recorder will let you hear the same sound that you recorded a Macro as well perform the same actions which you recorded.

Where do you keep your code?

The code that you write or record can lay in either the worksheets or in the Modules (Think of Modules as simple containers of the code, this is the place where you start writing your code). Module is a place where you keep your recorded/created Macros (Procedure). An excel Macro is a procedure which is a set of statements that tell your application (Excel) what needs to be done with its objects.

When you record the Macros they also get stored in the Modules, so when you need to run a Macro it can be called. You can insert any number of Modules in a workbook and you can call the macro by simply writing the name of the Module it is in and a ‘dot’ and the name of the Macro. For example if you wish to Call a Macro named “ABC” which is in Module1,
to call this one you would need to write Module1.ABC.

Apart from the module you can also put your code in the worksheets and also in the workbook. To insert a code in a worksheet (which is primarily sheet specific i.e. the Macro should run when you press a button in the sheet) just double click on the Worksheet, or workbook to see the window wherein you may put your code. We will talk more about events in the later sections which talks about if you wish to run a code every time a sheet is activated or every time the Workbook is saved etc.

Referring to an Objects (People in Excel World)

Now when we know that using Excel VBA and Macros we manipulate the Excel Objects, we also need to know the appropriate way to refer to the Excel Objects. Tell me, how would you refer to a person in the Real World? If u simply uses the name of the person it might create confusion as there could be more than one person of the same name. To be more specific you would probably say I that “I am talking about “John” who live in “Taxes” state in “USA” country. That would be good. right? The chances of somebody who wants to meet “John” would be more than the case where you would have said “I am talking about “John”.

Now the Excel Application also has its own Hierarchy of objects. And the Hierarchy is Application >> Workbook/s >> Worksheet/s >> Range/s. For the time being just consider this as the Excel Object Hierarchy model. So Lets say if you want to refer to cell A1 in a sheet called “Sheet1”. The best way to do this would be to refer to sheet1 as Application.workbooks(“NameOfBook”).sheets(“NameOfSheet”).Range(“A1”). Once you refer to the appropriate object you can very well manipulate its property or may be do some action on it i.e. ClearContents, change Background Color etc.
So, if you wish to change the background color of a Range, you would need to say Application.Workbooks(“NameOfBook”).Sheets(“NameOfSheet”).Range(“A1”).ClearContents now, this statement when written in a Procedure deletes the contents in the referred Range.

Absolute and Relative Reference

There are two ways to refer to an object the absolute and the relative reference. I will give you and example, if you wish to refer to cell A1, you can refer to it by Range(“A1”) or you may also refer to it by saying that this cell is one cell above Cell A2 i.e. Range(“A2”).Offset(-1,0). Offset takes the row and the column argument, by -1 as row index we mean one row above and by 0 as column index we mean same column.
Relative referencing to a cell is important when you are dealing with dynamic ranges.

Try putting in a small snippet of the code below and run it in a Module. This will give you an idea of how the index number work and how Excel looks at all the cells.

Sub test ()
For I = 1 to 512
Cells(i).value=i
Next i
End sub

Object Properties and Methods

Like all of us in the real World, Excel Objects also have their unique set of properties and Methods. One person may be Black and other may be White, somebody has Brown eyes somebody has Black. In the similar way each and every Excel Object also has a unique set of properties and methods attached to him/ her /it.

To see what are the various properties attached to an object, go to the VBE window (By Pressing ALT + F11) , in the window double click on the Sheet (Any sheet) and type Range(“A1”). , now did you notice a drop down showing up with a list of some English like Words?

This list that appears has all the Properties and Methods associated with the Range Object. And to distinguish between Properties and Methods from this list - Any item that starts with a small green box is a Method and the rest are properties.

Now let’s understand the difference between a method and a property. A property is an attribute of an Object. Like John has Black Hairs in the real world could be Range (“A1”) has Red Background colour. The Black hairs and the Red Background are the properties of the objects (John in the Real World and Range Object in the Excel World). Now if somebody asks you , What is the colour of John’s hair you would say its Black or if somebody asks you what is the Background colour of Range(“A1”) you would say its Red. So, in Excel you can get the properties of various objects and also you can set/ change the properties of the objects. Although, It might be a bit difficult to change the name of real person in real World. So, using the Excel VBA code statements you can modify and get the properties of Excel Objects.

By using the Excel Methods you can perform actions on the Excel Objects. Like activating a Range, Delete the contents, copy, paste and many more are the methods of the range object.

All the objects in Excel are modified and manipulated using properties and methods. In fact what we do when we run a macro. Using a macro we manipulate Excel Objects by using their properties and methods.

New Posts

Recent blog posts

Similar entries

New forum topics

Products from Sautinsoft.com

Syndicate