VBA - Why Not Record Macro Every Time

By now we know that we can record actions you perform in Excel by using built-in macro recorder. If that is the case, why do we need to write code by ourselves? Well, there are few problems with code recorded by macro recorder and few advantages with writing the code yourself, let’s see few of them.

Lengthy Code

We saw in one our previous posts that code recorded by Excel will be very lengthy because it tries to give values to many default items. We saw how a 40+ line recorded code for simple action can be rewritten in 4+ lines. Lengthy code not only makes your project looks clumsy, it also reduces the execution speed.

Cannot Record All Actions

Yes. Macro recorder cannot record all actions you perform. Don’t believe me? Then do the below steps and see it for yourself. (I gave example of Excel 2007/2010 only)

• Click on Insert > SmartArt > select any diagram and click OK.
• Now start recording a macro (you can see here on how to record)
• Select any box in inserted smart art and change the format (you may try to change colours of the boxes by clicking on SmartArt Tools > Change Colors)
• Open the VBE and observe the code recorded. Can you see any code for change of formatting? I can see a simple one line code ActiveSheet.Shapes.Range(Array("Diagram 1")).Select

Looping

You cannot record a code that works in a loop. Think of a situation where in you have to create a new workbook for each sheet of your current file and save each of such workbook with the sheet name. You can record your macro to replicate saving one sheet and run the same macro on each sheet manually. That means if you have 10 sheets, you will have to run that macro 10 times. You cannot ask the macro to run for 10 times automatically. Whereas in VBE, you can write a line of code that makes the macro run to work on any number of sheets.

Variables

You can’t define variables and assign values to them using macro recorder.

Can’t Display Custom Messages

If you want to show an alert message to user with two buttons viz Proceed, Cancel; you simply can’t do this with macro recorder.

Create Custom Function

I have no clue on how to create a new function that works how I want using VBA recorder.

Well, you got an idea. There are many things you can do with VBE code that is not possible with recorder. But recorder is still a better place to start with for your code. That’s a start, having established that writing a code yourself has many advantages, we will see more about code and related stuff in future posts. Stay tuned!!

VBA - A little more

We now know a little bit about VBA history and what it is, thanks to an earlier post. Catching up from there, we write series of instructions in VBE to tell Excel to perform some task for us. This code is called a Macro.

Let's see more details on what you can do using VBA and if there are any disadvantages.

Why to Use Macros

You use macros because you can:

Automate Almost Everything You Do In Excel Front End

Need to apply a particular formatting to a cell? Just write macro and use it. You can write code to perform any task you do in the front end Excel.

• Perform those time consuming activities way much faster
• Automate repetitive commands
• Perform tasks with a single command or short cut or click

Automate Things Which Are Not Possible Through Excel Front End

Accept it, we all are lazy or forgetful at times. I normally protect sheets with confidential data. But when I have to change anything in those sheets, I un-protect it but often forget to re-protect it when I am done (sounds familiar?). Result, your secret data is no more secret.

Do you also have confidential data in a sheet and want to automatically protect a worksheet whenever you close an Excel file, use 'events' in Excel to do the task for you.

Record Code & Replay

Even if you don't know a bit about VBA code and stuff, you can still record your actions and repeat as many times as required in future. Yes, Excel comes with an built-in macro recorder. Recording a macro and analysing is the best way one can start stepping into the VBA world.

Develop Custom Functions

If Excel's built-in functions are not enough for you or if they are not helping in a particular situation you are in, you can create functions that works the way you want (of course following VBA structure).

For example, there is no function in Excel which gives you a sum of cell values based on their background colour or font colour. You can create a User Defined Function (UDF going forward) which just will do the this task.

Create Add-Ins

If you want the macros to be run every time you open Excel, just save the macro file as an add-in. You can even share those files with anyone.

I want my UDFs to be available in every Excel file I open, so I just created an add-in and placed all the UDFs and macros in it. Now when I open any Excel file, I get to use all these macros and UDFs.

Work With Other Office Applications

You can play with other Office applications while working with in Excel. For example: when you are working with Excel, you can put up the charts and graphs in a MS PowerPoint. You can write code in Excel to send automatic birthday wishes through Outlook.

Feel Good

You are writing code to automate things. Feel like a programmer & developer. You will notice that awesome feeling once you mastered VBA. All that praise by your colleagues around would make you addicted to more learning.

Get Help

If you are stuck or in doubt with any code related stuff, Microsoft website and countless on-line help forums are at your rescue. There is a possibility that you will instantly find what you are looking for. No? No problem. Just post a query in any on-line forum and you will get the solution at the earliest.

Simplify Front End

You can simplify appearance of Excel front end user interface by using various Forms, controls and dialogue boxes available in VBA.

It Is Within Your Application

VBA is built in Excel and the code you write is also saved with in Excel file. So you need not spend money in buying a separate application to automate your tasks. Also no worries to share the files with other Excel users.

Are There Any Disadvantages?

There are many advantages and uses of VBA as we saw above. But are there any problems using this? Let's see.

Security

Like any other application, security is a big problem with VBA. Since VBA can manipulate other applications behaviour while residing in host application, it can be used to spread virus or steal personal data or for other fraudulent purposes. Though end users can disable macros and protect themselves from the risk of running viruses, few times this may still be not so useful.

And to run the macro in other users systems, you must also share the macro code along with the file. VBA offers protection features to lock users from viewing the code but an expert can easily thwart the protection.

You Have To Learn The language

You will have learn the technical jargon of VBA. You also will have to understand the coding structure, syntax, arguments and so on. Unless you are so much interested in coding and decoding like me, this may be a bit overwhelming to cope up.

Not Standalone

This is advantage of VBA and also the disadvantage that it is not stand alone program. It requires host application (Excel for example) to reside and work. Consequently, macros written in Excel will work only in Excel and you cannot use it in anything other than Excel, like Word or PowerPoint.

Other Users May Not Understand

If you share the macro with other's, they might not be able to understand how to work with macros and what it does. You will have to provide a detailed instruction sheet or build a robust help within your macro.

Compatibility

Microsoft comes up with new versions of Excel almost once in every 2 years and might probably update VBA structure too. So macros you write in recent version may not work well in earlier versions. You will have to keep the compatibility in mind while writing a piece of code.

Infinite Looping

If you are careless in testing the code or just run a code provided by a friend for fun sake, you might end up in doing infinite loops of calculations which will crash both the macro and Excel, more often than not, your system too.

We just saw what are the benefits offered by VBA and probable disadvantages too. But the drawbacks are over-weighed by benefits. After analysing, we all decided to learn VBA. So let's go ahead and see a quick introduction on VBE and related windows appearance in next post. Till then, Adiós Amigos, hasta la vista!!

VBA - Analyse The Recorded Code

Hi guys, welcome back for another session on VBA. In our previous post, we recorded a macro and executed to see how it works. In this post, let’s see the code behind that macro and analyse what’s going on behind the curtains.

See the Code of MyFirstMacro & Analyse

Open your VBA learning workbook that we saved with MyFirstMacro. Press Alt+F11. This should open up the VBE window. (Read about VBE appearance here).

In Project Explorer window, you can notice that Excel created a new folder Modules with a plus sign next to it. Clicking on that sign or double clicking on folder name shows you the child item under that folder which is Module1. If you are unable to view Project Explorer window, access it from View menu of VBE window.

Double click Module1 and your code window will be opened on the right hand side. I pasted the code below (coloured part). Have a quick look and we will analyse it line by line below. Remember, we have recorded MyFirstMacro using absolute references.

Sub MyFirstMacro()
'
' MyFirstMacro Macro
' This is my first macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'    Range("A1:A10").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
End Sub

Let’s start analysing this code. As we analyse, we will also be discussing the code structure we should follow  in general, so follow closely.

Sub MyFirstMacro()

Every macro code should start with a word Sub. This word refers to Subroutine. It tells Excel that a code is going to follow and Excel has to perform task given in that code.

Next to Sub is the name we gave to our macro i.e. MyFirstMacro. Followed by this, there are open and close parenthesis (we know as brackets but get used to the word parentheses). Remember excel built in functions where we give arguments in parentheses? Here also the same logic follows. If you need any arguments as inputs to your macro, they can be given in this parentheses (we will see more about it as we move deeper into coding). If no arguments are required, just give empty parentheses.

Sub should be always be closed with End Sub at the end of the code. After this line, VBA stops executing the code for this macro. Notice the very last line of the code above.

' (Apostrophe)

Apostrophe is used by VBA to indicate a comment or information that need not be executed as code. Whatever you enter right side of apostrophe will not be executed by VBA. This character is used extensively by developers to explain the code. In our code, VBA enters the description, short-cut keys next to apostrophe.

Though we gave a short-cut key, code will have it just as information and handling the key press is Excel user interface's job and not VBA's job.

Range("A1:A10").Select

Pretty straight forward, isn’t it? We are selecting Range A1 to A10 here. But look at the construction. Range is an object; it has one of the method Select. We are using a dot after Range to call Range object related method i.e. Select.

Range takes an argument as input that refers to an Excel cell range. You can supply a real range like we have in this example i.e. A1:A10 within quotes or you can give a named range etc. as an argument.

Tip: Press F1 in VBA help and type "Range Object" in search. Once you read about "Range". Once this is done, also read about "Range Object Members".

With Selection.Interior untill End With

We already know that we have to follow a particular hierarchy to work with lower level objects. For example, in cell A1, I want to apply bold, italic and underline to the font. I will have to write 3 different lines of code with full hierarchy. It looks like below.

Range(“A1”).Font.Bold = True
Range(“A1”).Font.Italic = True
Range(“A1”).Font.Underline = True

We are only starting from Range object going 2 levels below. This is still very simple line. Think of drill-down from an application object level until some 8/9 levels down, each line will be lengthy and since VBA will have to interpret many number of drill-downs, it's performance would be slower in longer codes. So VBA has a workaround too. Above 3 lines can also be written like below.

With Range(“A1”).Font

   .Bold = True

   .Italic = true

   .Underline = true

End With

Here we are trying to combine like items under one qualifier With and once we are done using it closing it off by End With.

Selection is another object in VBA that represents the range of currently selected cells. Since while recording code we selected A1:A10, Selection indirectly represents Range(“A1:A10”). One of the methods of Selection object is Interior object. Interior is where the background colour is set. In this line of code, we are defining a With qualifier to work with interior of selected cells in next lines of code until End With code line.

Other lines below this Selection.Interior are essentially to set various fields required in Interior. What we are concerned is the line .Color = 255. This is where you are setting the colour required as a background of cell. Number 255 essentially used to represent red colour  You can read more about the numbers for all colours in VBA help.

With Selection.Borders(xlEdgeTop) till the end of the Code

All these lines are used to define various properties Excel requires to set border line. You can pretty much derive what each line is trying to set by reading the line. This code is in human readable form, right?

Having said that best way to learn VBA coding is to record a macro and analyse the code, there is a serious flaw with this method. At times, VBA tries to give larger code where there is no need for. This often makes code very lengthy and confusing.

For example, to apply colour to a cell, you need not select the cell. Remember our code line Range("A1:A10").Select? This line selects the range defined. But to work with a range in VBA, there is no need to select it. So instead of our original code;

Range("A1:A10").Select

    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 255

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

We can also write

With Range(“A1:A10”).Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 255

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

Notice that I have merged first two lines of initial code in 1 line. This modified version works exactly same way but with fewer lines of code, there by improving quality of the code and speed of execution.

This is not the perfect coding either. You need not define values for some default properties unless we need to change them specifically. For example: to apply colour to a cell, we need not change its values for Pattern, TintAndShade etc as given in our original code. So our code can be further reduced like below.

With Range(“A1:A10”).Interior

        .Color = 255

End With

Needless to say, this snippet of code works exactly same as our lengthy version. So is this effective? No. When we are working with only one property as above, we need not use With qualifier. So final code will be;

Range(“A1:A10”).Interior.Color = 255

Our 8 line initial code is reduced to one simple and cute line. This line applies red background to cells A1:A10. On similar lines, let’s see how the lengthy 32 lines border related code can be condensed in 8 lines.

With Range("A1:A10")

        .Borders(xlEdgeLeft).LineStyle = xlContinuous

        .Borders(xlEdgeTop).LineStyle = xlContinuous

        .Borders(xlEdgeBottom).LineStyle = xlContinuous

        .Borders(xlEdgeRight).LineStyle = xlContinuous

        .Borders(xlInsideVertical).LineStyle = xlContinuous

        .Borders(xlInsideHorizontal).LineStyle = xlContinuous

End With

Now look at the code again, line by line. We are writing code to apply border on left, right, top, bottom, vertical & horizontal of each cell, all in separate lines. With a bit of research in VBA help, we would find that all borders are referred in a collection named as Borders. Setting a value to this collection will be applied to individual items in the collection.

Also see both the codes to apply colour and borders. Both works with Range(“A1:A10”) so you can combine both of them with the With qualifier as below.

With Range("A1:A10")

        .Interior.Color = 255

        .Borders.LineStyle = xlContinuous

End With

Started with a 40+ lines of code given by VBA recorder, we arrived at 4 lines which works exactly the same way but with increased efficiency. Go ahead and replace MyFirstMacro code with above snippet between Sub and End Sub and see if this works exactly same as before. (Or create a new macro with this code, you can compare better this way)

But still, without knowing head and tail of VBA, recording and analysing is the best option you have to manoeuvre this mammoth. Whenever you have time, go through the Object Hierarchy to know the short-cuts and techniques in using objects effectively.

See the Code of MySecondMacro & Analyse

We have recorded this second macro using relative references. In your VBE code window, scroll down the code until you see a horizontal line across the code after End Sub line of MyFirstMacro code. This horizontal line represents starting of another macro. You will be able to see Sub MySecondMacro() immediately below this line.

Compare the code of MyFirstMacro and MySecondMacro. You will notice only one difference in the code (ofcourse, apart from difference in name, description and short-cut key) i.e. MySecondMacro contains ActiveCell.Range("A1:A10").Select instead of Range("A1:A10").Select in MyFirstMacro.

What it means is simple, instead of hard coding range A1 to A10, MySecondMacro tells Excel to consider current active cell as the base cell and from there select 10 cells below. That is what relative reference is, right?

I trust we are clear on how to analyse the code. Once you know what the recorded code means, it only takes changing it to suit your requirement. For example, in our code to apply background colour, change the number 255 to something you wish and see what happens. Whenever there is an error (if you enter a value outside allowed range), don’t forget to consult VBA Help to see why. It will be fun to see how the changed values effects the background colour of the cell. See it yourself!

We will have many opportunities to write codes to achieve different tasks in Excel. We will analyse tons of code during those times. Before diving head first into the serious coding, we will see some good practises you should follow while writing VBA code. Stay tuned for next post!!

VBA - Record A Macro

It's been a while we touched VBA. Let's do a quick recap on what we discussed on this topic so far. VBA is an object oriented language, everything inside Excel is treated as an object. Objects have properties, methods and events. You can change the attributes of objects by changing its properties, you can perform actions using methods of objects.

Excel has an object structure called Object Model. We need to follow this structured hierarchy to reach the object we want to work with. We also saw the short-cuts to reach the desired object without following the lengthy code lines.

Enough already, let's follow the best way to learn VBA coding i.e. using the built-in recorder to analyse code. So we will go ahead now and record a macro first. What actions should we record? How about recording selecting few cells and colouring them in bright red and apply border to them.

Let’s create a new workbook and save it as VBA Learning and select Excel Macro-Enabled Workbook in Save as type field (only in Excel 2007 or later). We will use this book to do all our exercises going forward.

Record Your First Macro

In Excel 2007 or later: Click on View ribbon > Macros drop-down button > Record Macro

In Excel 2003 or earlier: Click on Tools menu > Macro > Record New Macro

This action opens up a dialogue box named Record Macro. Before moving on to record a macro let's analyse what this box contains.

Record Macro box in Excel 2007

Macro Name: You can provide a name to the macro you are recording. By default, names given by Excel are Macro1, Macro2 etc. You can define a new name that says what the code does. Remember, you have to follow couple of rules while naming the macro. Just like Named Ranges, name should always begin with a letter or underscore. Name should not contain spaces or invalid characters in-between. There are few names reserved by Excel so you should not use them. Macro names which are already used with in the same workbook will also be not allowed.

Shortcut key: Assign a key combination so that you can just press them and your macro is run. You can avoid multiple clicks to access the macro this way. You can use a combination of Ctrl, Shift and any one number, alphabet or special symbol keys on your keyboard, Ctrl key is to be used mandatorily. But remember to be careful while selecting your short-cut keys. Macro short-cut keys override Excel built-in functionality of those keys. For example, if you use Ctrl+S as short-cut key for your macro, then pressing these keys will always run the macro within this workbook but you will lose the Excel save command using Ctrl+S.

Store macro in: This is a drop-down list having three options. You have option to save the macro in ThisWorkbook, New Workbook or Personal Macro Workbook.

This Workbook refers to the workbook you are trying to create the macro. You will use this option if you want to keep the code you are writing in the workbook you are working in.

As the name suggests, New Workbook refers to a new workbook. Code will be saved under a new workbook that will be created once you record the macro.

Selecting Personal Macro Workbook keeps the code in a central workbook. Code saved this way will be available across any of the Excel file that you open.

Description: Enter a quick overview of your macro for future reference. You can enter anything you wish here and this field is not mandatory. If you are using Excel 2003, you can see a line of description already entered by Excel. In my system, it is this line: Macro recorded 21/02/2014 by Hari Krishna Popuri.

Fine! Let’s fill these fields with our data.

1. Enter "Macro Name" as MyFirstMacro (I am avoiding spaces; you can also name something like My_First_Macro, but decide one way and have a standard naming convention across your projects. It looks neat)
2. For "Shortcut key", click in white space next to Ctrl+ and press Shift key and Q.
3. Let the "Store macro in" field contain This Workbook
4. Enter a "Description", This is my first macro.

Record Macro dialogue box should look like below at this point.

Click OK. Remember, from now on, all your actions in Excel will be recorded. So we have to do precisely what we want to do. Avoid unnecessary clicks here and there etc.

After clicking OK, you can notice a blue coloured button in Excel 2007 or later and a word "Recording" on the status bar in Excel 2003 or earlier.

2010 Screenshot

2003 Screenshot

This means your macro is being recorded. Excel 2003 will also show a new floating menu ‘Stop Recording’ with two buttons. One button is blue coloured like the one in Excel 2007 status bar. You can click this button to stop recording the macro. The other button is ‘Relative Reference’. In Excel 2007, you can access this option from View ribbon > Macros drop-down button > Use Relative References.

Remember our post on Absolute and Relative references? In Macros, by default absolute references are used. You have to click this relative reference option if you want to use relative references so that the actions you recorded will be coded depending on the relative position of the active cell. For now, let’s stick to default option and see what happens.

Now that our macro is being recorded, click on cell A1 to A10, apply background colour red. Apply ‘All Borders’. My sheet now looks like below.

Click on “Stop Recording” button now (or blue button in Excel 2007). You have successfully recorded a macro.

Now let’s test it. We used Absolute References while recording our macro. So every time you run this macro, no matter which cell is selected currently, cells A1:A10 of active sheet will always be coloured in red and bordered.

To test this;

1.  Go to Sheet2 and click on cell B10 (or anywhere you like)
2. Run the macro by pressing key combination we chose (Ctrl+Shift+Q). You can also go by menu/ribbon way.

If everything goes fine, cells A1:A10 in Sheet2 must have been coloured in red and bordered. Try this with different cells selected but result should always be same. Do you also see this way?

Use Relative References 

Let's record another macro using relative references. We will name it MySecondMacro

1. Click on cell A1
2. Click on View > Macros > Relative References
3. Select cell A1:A10, record another macro with name MySecondMacro
4. Give shortcut of Ctrl+Shift+W
5. Enter description of your choice

Now let’s test it. We used relative references and cell A1 was selected before recording macro. Once the macro started recording, from A1 we selected 10 cells below and coloured them red and bordered. So every time you run this macro, wherever the cursor is that cell and 10 cells below should be coloured and bordered.

To test this; 

1. Go to Sheet2 and click on cell B2
2. Run the macro by pressing key combination we chose (Ctrl+Shift+W). You can also go by menu/ribbon way.

If everything goes fine, cells B2:B11 in sheet2 must have been coloured in red and bordered. Try selecting different cells and check.

By recording your actions, you can automate most of the tasks without even knowing any code. In the next post, we will see the code behind these 2 macros and analyse it to get better understanding on what’s happening behind the curtains. Bye till then!!

VBA Code Structure

Hello….….. 

We already saw little bit of introduction about VBA introduction here and here and VBA appearance. Before diving head first into the coding, let's finish introduction with another short post.

VBA is an Object Oriented Language (not cent per cent, but that wouldn't affect our learning). Object is something like a tool or a thing that has certain functions and properties, and can contain data. For example in Excel, single cell is an object. Collection of cells is another object; a sheet is an object etc. Likewise, everything you see in Excel is represented by an object in VBA. Nearly everything you do in VBA will be with objects.

So, object represents an element of Excel, such as a cell, a sheet, a workbook. A collection is an object that contains several other objects, usually of the same type; for example, all the Sheet objects in a workbook are contained in a single collection object called Sheets. 

Objects will have properties, Methods and Events. Using properties and methods, you can modify a single object or an entire collection of objects. 

Property: 

A property is an attribute of an object or an aspect of its behaviour. For example, properties of a workbook include its name, number of sheets, and it’s save status, as well as whether change tracking is turned on. To change the characteristics of an object, you change the values of its properties. 

Method: 

A method is an action that an object can perform. For example, Excel can update the formula calculations automatically every time you change a related cell value. This is a method for Application object. 

In most cases, methods are actions and properties are qualities. Using a method causes something to happen to an object, while using property returns information about the object or it causes a quality about the object to change. 

Event: 

An Event is an action in Excel front end, initiated either by user action or by other VBA code. You can use the events to trigger a procedure that tells Excel what to do. Remember the post on why use macros? I mentioned that you can use Excel’s events if you want to protect a sheet automatically whenever the book is saved. 

Saving a book is an event. You can write code to protect under this event so that Excel always runs the code when the save button is clicked (of course, on pressing of Ctrl + S too). 

Events and event procedures are the same for versions 97 through 2007. No significant changes have been made to the event model since its introduction in Excel97. A few new events have been added, but the overall structure of the event system is unchanged. 

We will see about various events and coding structure for events during our plethora of future posts. 

Object Model: 

Excel has an object model. The object model is a big hierarchy of all the objects that you can use in VBA. At the top of this hierarchy is the Application object, and all the other objects are below it. To use the object properties and methods etc, you will have to drill down to that object from top. 

The dot operator (.) is used to navigate through the hierarchy. You connect objects with a dot to get to lesser objects. For example, if you want to apply red font colour in cell A1. 

A cell in VBA is represented by object named Range. Range has a property called Font. Font is also an object which has a property Color. You can set value of this property to a value that returns red colour. Red colour in VBA is represented by RGB (255,0,0). 

We have Application as a higher level object. Application has a property ActiveWorkBook. Using this you can work with whichever book was active when the macro is run. ActiveWorkBook contains a property ActiveSheet which works with whichever sheet is active when the macro is run. Range object is a sub set of ActiveSheet. 

So we will start with Application object and reach the Font object gradually. Our code should look like below. 

Application.ActiveWorkbook.ActiveSheet.Range("A1").Font.Color = RGB(255, 0, 0) 

That's it. You just wrote a code that will colour Range A1 in red.

Default Objects 

Whenever you’re dealing with Excel’s object model, there are always default objects. For instance, the Application object is always assumed, regardless of where your code is. The path to the Font object could be written as;

ActiveWorkbook.ActiveSheet.Range("A1").Font.Color = RGB(255, 0, 0)

Notice that Application object is not used above. When you omit the Application object, Excel assumes that it’s there. There would not be any change in what this line of code does.

Standard Modules 

When you’re in a standard module (like Module1), there are some more default objects at your disposal. When you omit the workbook reference, Excel assumes you want the ActiveWorkbook. Below code is the same as the previous two snippets.

ActiveSheet.Range("A1").Font.Color = RGB(255, 0, 0) 

You can omit the worksheet reference and the ActiveSheet will be assumed. Yet another equivalent code snippet is;

Range("A1").Font.Color = RGB(255, 0, 0) 

That sure is shorter than the line we started with. Don’t get too excited, there’s a downside. When you omit the workbook reference in the ThisWorkbook module (not standard module like Module1), the workbook that contains the code is assumed, not the ActiveWorkbook. Similarly, omitted sheet references in a sheet module are assumed to be the sheet which module the code is in, not the ActiveSheet. So I suggest using the long line with entire code till you get used to the object structure. Following full hierarchy is called Fully Qualified Reference. There is a problem with this too. Your code is going to be ten times longer. But that’s ok for starters, right? 

Knowing the VBA Object Model is half battle won. Just check the VBA help (through the famous F1) for Object Model and you can see what all objects are at your disposal. VBA help is pretty much comprehensive. 

Getting Help on objects, methods, and properties 

Until you become familiar with the Excel object model, there are a few tools you can use to help you to drill down through the hierarchy. 

Auto List Members: When you type a period (.) after an object in the Visual Basic Editor, a list of available properties and methods is displayed. For example, if you type Application followed by a dot, a drop-down list of methods and properties of the Application object is displayed.

If Auto list members are not displayed, click on Tools > Options > Editor tab > Check Auto List Members box.

Help: You can also use Help to find out which properties and methods can be used with an object. Each object topic in Help includes a 'See Also' jump that displays a list of properties and methods for the object. Press F1 in the Object Browser or a module to jump to the appropriate Help topic. 

Object Browser: The Object Browser in the Visual Basic Editor displays the members (properties and methods) of the Excel objects. You can access this by pressing F2 in VBA. 

In the next post on VBA, we will record a macro for few actions in Excel and analyse the code to get deeper in to the VBA world. Stay tuned!!

Visual Basic for Applications - The Appearance

Hello again!! We saw a brief history and uses and problems of VBA in our last two posts. Now let's see how to access VBA from Excel and how does it look.

We know that VBA is a special program that is hosted by Excel. To access this program, follow any one of these steps.

In Excel 2007 or later

• Press Alt+F11
• Right click on any sheet name tab and click on 'View Code'
• If macros are already available, click on View Tab > Macros. From the Macros dialogue box, select any macro available and click on Edit. 
• If you make Developer tab visible, click on 'Visual Basic' in it.
• Right click on any object in your workbook and click on Assign macro. In the Assign Macro dialogue box, click on New or Edit (if macro is already assigned).

In Excel 2003 or earlier

• Press Alt+F11
• Right click on any sheet name tab and click on View Code
• If macros are already available, click on Tools Menu > Macros > Macros. From the Macros dialogue box, select any macro available and click on Edit. 
• Click on Tools Menu > Macros > Visual Basic Editor
• Right click on any object in your workbook and click on Assign macro. In the Assign macro dialogue box, click on New or Edit (if macro is already assigned).

Performing any one of the above options will open up VBA. Windows and appearance of VBA windows that Excel opens up will be different in different options. Let's try to learn about each one of them.

Best way to learn VBA is to experience by yourself. So, along with reading below info, make sure you open and do a little R&D in VBA module.

1. Project Explorer

When you open the VBA window for the first time, project explorer is the most likely item you see. If you don't see this by any chance, make it visible by clicking on View > Project Explorer or simply press Ctrl+R. (We are talking about VBA menus and short cuts here, so make sure you are clicking in VBA window and not in Excel).

Project Explorer displays a hierarchical list of the projects and all of the items contained in and referenced by each of the projects. See below a closer look at project explorer window.

What this window contains: (Refer numbering in above picture)

1. View Code - Displays the Code window so you can write and edit code associated with the selected item. 
2. View Object - Displays the Object window for the selected item, Excel front end or UserForm.
3. Toggle Folders - Hides and shows the object folders while still showing the individual items contained within them.
4. Project Name - Excel book name will be the project name
5. List window - Lists the all loaded projects and the items included in each project. For a plain Excel book, it lists down all the sheets and one more item called ThisWorkBook.

Though we will cover about them during our future posts, I will give a heads up now. You will save code that is specific to the sheets under respective sheet's code window (example: macro code to execute when a sheet is activated). Code that is applicable to entire workbook should be saved under ThisWorkbook  (example : code to execute before saving a workbook). When you record a macro from Excel, VBA creates another list item called 'Module' and recorded macro code will be saved under it.

Double clicking any of the list item opens up a white space towards the right which is the container for code for that item. Clicking on + / - will expand/collapses the list.

Right clicking on any list item or in the white space in Project Explorer will give you further options like rename your project name, lock and protect your code, insert UserForms, import / export code from other files etc.

2. Code Window

This is where a developer spends most of the time. You write, save and display your code here. You can open as many code windows as required (one for each item in the list window). Just double click on each item in the Project Explorer and you can notice the code window for that object opens up. You can minimise, maximize, restore and close the code windows by clicking on the respective buttons on top right corner which are just below the main VBA buttons. You can select the code from one code window and drag it to another window.

But be careful, it is not possible to identify which item's related code window is open at any given point of time. So if you want to open a particular item's code window, better double click on that item rather than toggling.

As said, we will be working mostly in the code window. So surely we will learn more about this window while coding.

3. Properties Window

Every object in Excel will have properties. For example, if you take a sheet, name of the sheet is a property of the sheet, visibility of the page (hidden or visible) is one of the property.

Properties window contains properties for the item selected in Project Explorer. You can view and modify the properties of the item from code window. Each item might have view only properties or few properties will be editable. So make sure the property you are trying to modify is editable, otherwise VBA complains. Code for the item need not be displayed in order to show the properties.

4. Immediate Window

Immediate window allows you to give a line of code and it will show the result instantly without actually executing the code. This is used extensively while writing code to see if the code is resulting into an error.

5. Locals Window

Locals window displays all of the declared variables in the current procedure and their values. It is automatically updated every time a code line is executed. This is used to observe the variables behaviour in the code testing.

Using the Locals window is easier to display variable values than examining the value from the Immediate window. For simple variable types (e.g., Long and String variables), the value is displayed in one line. For complex types or objects (e.g., a Range variable), its properties are displayed in a collapsible tree-like structure.

6. Watch Window

You can set up watch criteria to either momentarily stop executing your code when the condition is met. The Watch window displays all the Watches in effect. You can display the Watch window by choosing Watch Window from the View menu. A Watch is an instruction to VBA to pause code when an expression is True or when the variable being watched changes value. To create a Watch on a variable, open the Watch window and right-click in the Watch window and choose Add Watch from the popup menu or choose Add Watch from the Debug windows. In the Add Watch dialogue  enter in the Expression text box a variable name whose value you want to watch. Then choose 'Break When Value Changes'. When you run the code, execution will pause at the line after the line that modifies the variable's value. When code pauses, the value of the variable will have already been updated.

To remove a Watch, right-click it in the Watch window and choose Delete Watch from the popup menu. To modify a Watch, right-click it in the Watch window and choose Edit Watch from the popup menu.

Okay, guess that's enough to get you started. As I mentioned above, the best way to learn VBA is to experience it by yourself. So make sure you open and do a little R&D in VBA module. Happy learning!!

Click here to know about VBA object structure.

Introduction to Visual Basic for Applications

Alright guys!! I guess the time has come to go beyond the regular posts on Excel front end, into developer front. Before diving deep into developer stuff, I thought it's appropriate to look a little bit about Excel programming history first.

Some Technical Info

Excel programming language is called Visual Basic for Applications (VBA) which is a dialect of Visual Basic (VB). VB was released by Microsoft and quickly became famous for its ease of use to create application components.

Before VB, various macro languages were previously supplied with Office applications to automate tasks. It was Microsoft's idea to cut all those different macro languages and make one universal language to embed in applications. But VB is a separate programming language altogether and it needs to be purchased and installed separately to work with. Hence a cut down version of VB is made and named as VBA. That's how VBA was born and evolved.

VBA is distributed using host applications like MS Office products such as Excel, Word etc. So, writing code, saving and executing can only be done within a host application rather than as a standalone program. But you can use VBA to control and work with other VBA hosted applications. For example, if you are using VBA in Excel, you can open and read the contents of MS Word file. VBA is proprietary to Microsoft hence you cannot use VBA written program in any third party application.

VBA code is written in Visual Basic Editor (VBE). Code is then compiled by host application into an intermediate language called 'p-code' which then be executed by a virtual machine which is also hosted by host application. Code you write in VBA would almost look like common spoken English. We will see different terms used and appearance of VBA in forthcoming posts. 

Quick History

• VBA was first launched with MS Excel in 1993. It became an instant success among developers to create corporate solutions using Excel.
• VBA 4.0 is the next famous release with a totally upgraded version compared to previous one. Released in 1996, it is written in C++ language and became object oriented language.
• VBA 5.0 was launched in 1997 along with all of MS Office 97 products. Only exceptions for this is Outlook which was using VBScript to automate things.
• Year 1998 saw the launch of VBA 6.0 and continued to be supplied with MS Office suite.
• Office 2010 is included with VBA 7.0. There is nothing much offered to developers from VBA 6.0 except for 64-bit support.

Once entered in the world of VBA, we will learn how to automate simple tasks, write bits of complex codes and custom made functions etc. We will dismantle the code to analyse what it means and how to develop Excel applications using VBA. We will also see a series of practical situations and write code to work efficiently. The journey to future will be surely exciting and truly rewarding in terms of knowledge gain. Stay tuned!!

Click here to read about why and why not we should use VBA.