You are done!!
Now if you take a look at the associated Excel sheet under ‘Macros'(Alt-F8), you will find a macro with the name you have just given. You may then go to ‘Options’ and add a Shortcut key and Description if you so desire

For simple automation of tasks, any tasks, Macros are an extremely powerful tool. The Macro functionality in Microsoft Office is extremely mature and sophisticated. So chances are that what we have seen so far should be enough in 90% of the cases.

However, if you want to do any of the following things,

Check for conditionality

Loop an action(Repeat the same action many times automatically without user intervention)

Create custom forms

you will find the simple Macro creator insufficient for your needs. In such cases, you will have to resort to the extremely powerful and sophisticated VBA editor for your needs.

The Visual Basic for Applications Editor allows you to do almost anything with Microsoft Office Applications. Its potential is limited only by your imagination and ability. I have found it the most intuitive and the most effective tool for the creation of collaborative applications.
My only gripe against VBA is that its documentation literally sucks. If you are a seasoned programmer, you can make do with the documentation. But if you are new to VBA and want to learn it, it seems to be specifically designed to kill your interest and increase your frustration. Through this series of articles, I hope to enable a wider group of people to use this powerful tool.

And, believing in the adage that doing is much better than merely reading, let us delve into the beautiful world of VBA directly.

Let us take the example of the Macro that we created that makes cell contents ‘Bold’, ‘Italics’ and ‘Underlined’. For your benefit, if you haven’t gone through the section on Macros, I’ll enter it here as blockquote

Let us make the first macro very simple to begin with. I will guide you now, through the steps to create a macro that makes the text of a cell bold, italics and underlined. Three operations at one go.
Procedure:

Select a Blank Cell

Click on Tools-Macro->Record New Macro.
[At this point a small window opens up. In this window,

Name your macro
[Your macro should always have a meaningful name so that you can recognise it from the others you may create]

Define a Shortcut Key for the Macro
[Note: This shortcut key supercedes all other shortcut keys in excel as long as the Macro is active. For example, if you assign Ctrl-B to this macro, pressing Ctrl-B will only call this Macro. You therefore lose the ability to use Ctrl-B for Bold. My rule of Thumb is to use Ctrl-Shift-() for Macros]

Define whether you want this macro available ony for the present workbook or globally. If you want it for every workbook, choose to store it in Personal Macro Workbook

Give a brief description if required.
[Now you will notice a small toolbar come up on the screen with just two buttons. One button stops the macro. The other is for relative references that we shall explore later.]

Click on the ‘Bold’, ‘Italic’ and ‘Underline’ buttons on the excel toolbar.

Click on the ‘Stop Recording’ Button.

You are now done!
To test it, select another cell and type something into it. Now use the shortcut key that you defined while creating the macro. and voila! you have bold, underlined and italicized text.

Note: For the purpose of this exercise, please ensure that you make this macro available for the ‘Current Workbook’ only while creating it.
Now, you need to understand that what this entire operation does is to let Excel know the steps to take when you run the Macro. The problem is that Excel does not speak English quite as fluently as us. Hence we need to go down to its level and speak its language. What this process has done is to convert the actions that we have done into a ‘script’ in the language that Excel speaks, which is Visual Basic for Applications(VBA).
In order to see the steps in VBA, all you need to do is do the following:

Go to Tools->Macro->Macros

Choose the Macro that you have created

Choose ‘Edit’
At this point, a new window opens up which will have 3 ‘mini screens’. Let us forget the screens and focus on the text instead. If you have done what I have asked you to do, your code will look something like this:

Let us try and analyze these statements keeping in mind that this is just the language that Excel speaks.

Sub BIU()

End Sub

This tells Excel that what lies inbetween the Sub and End Sub is a series of steps. Excel calls this series of steps a Subroutine. And hence the word ‘Sub’. BIU() is the name that I gave my macro. And it basically tells Excel, that if this name is called, it must go ahead and execute the steps between Sub and End Sub.
So why are some things in Blue and some in Black? Well, words like Sub, End Sub etc are reserved words in Excel. which means that Excel reserves to use them for its own purposes.

The stuff in Green now. These are called comments and are ignored by Excel completely. They are meant only for information for the Users. You can create a comment by using the single apostrophe(‘) at the beginning of the line.

Now we get to the meat of the subroutine. These three lines basically tell excel what to do.
The word ‘Selection’ means whatever has been selected. So this refers to either a single cell or multiple cells that you have selected before calling the macro. The rest of it is self explanatory.
Now if you want to modify your macro, you can modify the code here. Lets try some modifications now with the limited knowledge that we possess at this stage.

1. Remove the ‘bold’ part.
Hmm… in order to avoid making the cell bold we can delete the first line. Or even better still, let us comment it out. So put in the (‘) symbol at the beginning of the line, go to the end of the line and hit enter. This changes the line into green thereby telling you it is now a comment. Now you can just close the VBA window, go back to excel and run your macro. you will see that it now has only italics and underline and no bold.

2. Underline in a different syleOk. you can see from the code that underline basically is in the form ‘xlUnderlineStyleSingle’ which I would take logically to be a Single Line Underline. Does that mean that there can be an ‘xlUnderlineStyleDouble’? Let us try. Let us change the line 3 to read

Selection.Font.Underline = xlUnderlineStyleDouble

Now if I go to excel and try the macro… Well whaddya know it works!!! Whoopeeeeee!!!!

Conclusion.You have now seen a very simple example of modifying a VBA subroutine and getting it to work for you. Now in order to proceed further, you need to know three things.

Excel Object Model

How Excel handles errors in your code

How to ask Excel for help

These topics shall be the subject of our further articles. Once we get these under our belts, we shall then go on to creating much more useful and complex subroutines in excel.

Let us make the first macro very simple to begin with. I will guide you now, through the steps to create a macro that makes the text of a cell bold, italics and underlined. Three operations at one go.Procedure:

Select a Blank Cell

Click on Tools-LMacro->Record New Macro.[At this point a small window opens up. In this window,

Name your macro[Your macro should always have a meaningful name so that you can recognise it from the others you may create]

Define a Shortcut Key for the Macro[Note: This shortcut key supercedes all other shortcut keys in excel as long as the Macro is active. For example, if you assign Ctrl-B to this macro, pressing Ctrl-B will only call this Macro. You therefore lose the ability to use Ctrl-B for Bold. My rule of Thumb is to use Ctrl-Shift-() for Macros]

Define whether you want this macro available ony for the present workbook or globally. If you want it for every workbook, choose to store it in Personal Macro Workbook

Give a brief description if required.[Now you will notice a small toolbar come up on the screen with just two buttons. One button stops the macro. The other is for relative references that we shall explore later.]

Click on the ‘Bold’, ‘Italic’ and ‘Underline’ buttons on the excel toolbar.

Click on the ‘Stop Recording’ Button.

You are now done!To test it, select another cell and type something into it. Now use the shortcut key that you defined while creating the macro. and voila! you have bold, underlined and italicized text. :)

Q: How do I enable Macros in Excel?
A: Go to Tools->OptionsSecurity
Macro SecurityAll you need to do now is change the setting to medium or low. Medium will ask you before a macro is run and is the setting I use most often. Low will run macros without asking and can be dangerous.

A macro is just a set of tasks stored together under a common name.
A task is anything that you do in Excel, powerpoint, Word, etc.
For example, when you make a text bold, it is a task. So is putting a border around a cell, importing a document, etc. In many cases, we find that we are repeating a series of tasks again and again. Using a macro, we can group these tasks together, and invoke them using a name that we give, using a shortcut key, a button on a toolbar or a Menu Item(only from Office 2002).

Through the Tools Menu
Go to Tools->Macro->Macros (or hit Alt-F8)
Choose the Macro you want to run
Choose ‘Run’

Use the Shortcut Key that you defined when you created the Macro

If you have assigned the Macro to a button, click on the respective button on the toolbar

If you have assigned the Macro to a Menu item, click on the respective Menu Item.

The last three options need you to have specifically created options for these actions. A shortcut key can be assigned while creating the Macro itself. For information on how to assign a Macro to a button and a Menu Item, read the respective posts.

NOTICE

THIS SITE WILL BE UP AND RUNNING ONLY BY NOV. 15. I AM JUST TRYING OUT A FEW THINGS NOW. PLS COME BACK IN NOV. IF YOU WOULD LIKE TO BE INTIMATED WHEN THE SITE IS UP, PLEASE MAIL feedback@rvc.in

Search

About Me

Hi,
My name is Vasanth and I own RV Consulting.
I have always passionately believed in sharing information with the world. And this site will be an attempt do do just that. Please do go through the tutorials and let me know how you feel. If you need help with anything, just mail me. vasanth@rvc.in should reach me. And if you need professional consulting, you know where to look
www.rvc.in

About RV Consulting

RV Consulting is an organization with a mission to improve productivity. All around us, we see instances of tremendous software spending with the returns on this spending highly suspect.
RV Consulting attempts to
1. Reduce the spending by using Open Source Solutions
2. Reduce the time to deploy by using pre-built applications
3. Increase effectiveness of use of existing Software.
Overall, we try to improve your Return on Investment by enhancing effectiveness of your workforce and deployed solutions