VBA Looping - For Next (3 of 3)

Hi, we saw how to use For Next to loop through any number of times and perform some tasks you code. In the last post that we saw, we wrote a code to check if any cell in the selection has formula using For Each statement. There is a slight efficiency problem in that code. When a particular loop identifies a cell that contains formula, there is no more need to continue looping, right? Since all that you want is to check if any one cell contains formula or not. Why to continue looping after you find one such cell? To avoid looping after a particular situation is encountered, you can use Exit For statement that stops looping. Let’s see how, using the very same example from previous post. Check out the code below.

Sub CheckFormula()                                                                                             
Dim Cl As Range 'Declaring a variable for cell
 For Each Cl In Selection 'Declaring loop for each cell in selected range
    If Cl.HasFormula = True Then 'Check if that cell contains formula
        MsgBox ("There is atleast one cell with formula")
        Exit For
    End If
Next Cl
 End Sub

Compare this code with our code from the previous post. I am using only one variable compared to two earlier. When my Cl.HasFormula statement returns True (that means formula is there), a message box is displayed and Exit For statement is executed. Loop will end at this point of time. This saves you on the precious execution time.

You may also look at: Unhide All Hidden Sheets using For Next & For Each

VBA Looping - For Next (2 of 3)

Hiiii!

We have been discussing about looping in VBA and saw about For Nextlooping in our earlier post. Today, we will see about one more type of For Next loop.

For Each Loop

Our earlier method will work fine when we know exactly how many numbers of times the loop should work. But there might be some cases where your macro should run depending upon the situation. For example: what if your macro should run based on the number of sheets in the workbook? There might be 2/3/4….n sheets in that workbook where macro is being run by user.

In these cases, you can use For Each loop. Let’s see how to code this by an example. I need to write a macro to check that;

• any cell in a range that user selects contains a formula
• If at least one cell is found with formula, a message box should be displayed.

Since how many cells can be selected is in the hands of the user, we can’t hard code the looping number in the code. Let’s see how we can use For Each in this example.

Sub CheckFormula()
Dim Cl As Range 'Declaring a variable for cell
Dim HasF As Boolean 'Declaring a variable to flag if cell has formula
 For Each Cl In Selection 'Declaring loop for each cell in selected range
    If Cl.HasFormula = True Then 'Check if that cell contains formula
        HasF = True 'formula is there, so HasF should be true
    End If
Next Cl
 If HasF = True Then MsgBox ("There is atleast one cell with formula")
End Sub

I am using 2 variables here. Cl holds a range and HasF is a Boolean. Our code will loop through all the cells in a Selection (Selection is an object that is collection of all the cells selected). When you start executing the macro, each cell in the selection will be checked for Cl.HasFormula statement. This will return either True (if formula is there) or False (no formula). HasF will get its value from this result. At the end, we are checking the HasF final status; if it is true (that means formula is found somewhere) then a message box is displayed. Otherwise macro ends.

There is one more example of For Each available in the post: Unhide All Hidden Sheets using For Next & For Each. You may also continue reading about For Each Looping - For Next (3 of 3)

VBA Looping - For Next (1 of 3)

Hello. We have been reading about VBA useful features in series of posts. Understanding these will help you to a great extent when you start serious coding. Do you remember our post on why not record the macro all the time instead of coding? We saw that recording macros does not support looping. That means if you have to perform a task n number of times, you have to either record performing the task n number of times or run the macro n number of times. This is major drawback in recording macros and we saw that VBA coding would help in this aspect. In this post, we are going to see how to use looping in VBA.

For starters, we can use looping to perform a task n number of times where n can be defined by you or get from user by an InputBox or UserForm. n need not be defined as exact number, it can also be decided based on the situation. To make it clear, you can loop a procedure 10 times by hard coding the number 10 or decide it based on the situation. For example: you can loop a macro those many times as the number of cells selected before executing the macro or you can loop those many times as the number of sheets in that workbook etc.

There are two kinds of loops in VBA viz. For Next Loop, Do Loop. We will see about For Next Loop in this post and continue with other kind in a future post. Let’s rock.

For Next Loop

You can use this loop in two ways. First one is by using a variable as a counter to tell VBA how many times the procedure to run.

Syntax is like below:

For VariableName = Start To end
Statements…..
Next VariableName

Let’s see what the syntax means.

For: key word to call the loop.

VariableName: Is any name you give to your variable. It will be used as counter.

Start: Is the value you want to start your variable at. It can be any number.

End: Is the value where you want to end the loop. It can be any number more than Start. Loop will be executed those many number of times as the difference between end and start.

Next: key word to loop. VariableName is not mandatory to be placed next to Next but it looks neat and when you have chain of loops with in loops, it will be clear.

Example:

I want to show a message box 2 times, with a prompt showing how many times the loop is run already. I use a variable named MyCount in this statement. See the sentence construction.

Sub Test()
Dim MyCount As Integer 'Declaring my variable
For MyCount = 1 To 2 'Declaring how many times it should loop
    MsgBox MyCount 'Show message box
Next MyCount 'Looping
End Sub

Copy above code and paste it in your code module. Make Locals window visible on the screen and press F8 repeatedly and observe what happens to the variable value on each press. I will take you through step by step on what’s happening.

When the execution point (yellow highlight) reaches For statement, MyCount value is empty. On press of F8, focus will be moved to MsgBox line and MyCount Value is set at 1 as in the below screenshot.

This 1 is the value I set in For statement at the start. If you set 10 there, you will get 10 as the value in the Locals window. In that case, your code line will be For MyCount = 10 To …..

Pressing F8 again will display a message box with 1.

Once you click OK in the message box, execution point moves to Next statement. Press F8 again and notice the code and locals window. Focus will move to MsgBox line again (hey, that means we successfully looped) and Locals window now changes the value of MyCount to 2.

It means, when VBA executes Next statement, it increases the count of variable automatically by adding 1 to it. Go ahead, press F8 again and VBA will show the message box again.

Click on OK in the message box, execution point moves to Next statement again. So we wanted to loop 2 times and we got message 2 times. So, ideally looping should stop now. Let’s see what happens if you press F8 now.

Press F8 and you will notice that execution point moves to End Sub statement and MyCount now shows a value 3 in Locals window. This is because focus moved to Next statement 3 times so counter increased to 3. That’s normal and you need not worry about it. Pressing F8 again would end the procedure.

Step

We saw that counter will be increased by 1 every time you reach the Next statement. You can change this number to any other number.  VBA adds that number on each execution of Next. This is done by using Step word as show below.

For MyCount = 1 To 10 Step 2

Here, I am trying to loop starting 1 till 10 but at the end of every Next, VBA adds 2 to the variable value instead of 1. You can enter any number next to Step. If I run above line placed in our example code at the start, I will get message box 5 times.

Step In Reverse

If you want to loop in reverse order, you can use the code as below.

For MyCount = 10 To 1 Step -2

This will take MyCount value as 10 at the start and keeps on reducing by 2 when Next statement is executed.

We will use this looping method in various codes going forward. Just know the basics and when we use it in a code later, we will be clearer. Happy Learning!!

There is one more example of For Each available in the post: Unhide All Hidden Sheets using For Next & For Each. You may also continue reading about For Each Looping - For Next (2 of 3)

VBA - Entering Arguments

You use Objects when you are working with codes. Objects have Methods which you can use to perform some tasks for you in Excel. These methods require you to pass arguments to them to work with. We have been seeing many methods in our previous posts like Application.InputBox. Many built in VBA functions like MsgBox & InputBox also uses arguments to do some task.

In this post, let’s see few details about arguments and how to pass them to Methods or Functions

What is an Argument?

No, argument here is not used in the sense of quarrel. Arguments are values that VBA need to perform certain task for you. For example: if you want to show MsgBox, you need to supply a prompt that should be displayed to user, may be a title and what are the buttons you want the MsgBox contain. Here, the prompt, the title and the buttons will be arguments for MsgBox function.

When you enter a function or method that requires arguments and press space, tab or open a parenthesis, VBA will show you list of arguments required for that function / method as a tool tip. See the picture below.

Notes:

• Optional arguments will have square brackets surrounds them. You can ignore them if you don’t want.
• Mandatory arguments will not have any brackets, you must provide these in order to work with the function.
• Argument you are entering currently will be bolded in the tip.
• Optional arguments have default settings which will be used by VBA if you do not enter values for those arguments.

You can type argument values (by placing them in quotes if it is text) or pass on a variable as its value. For example: both the codes below show a message box with a message Hello World. Second code snippet uses a variable to show the message.

Sub Test()
MsgBox (“Hello World”)
End Sub

Sub Test()
MyMsg = “Hello World”
MsgBox (MyMsg)
End Sub

Skip an Argument

Method 1 – Using Comma

When there are multiple arguments to enter like in above picture for MsgBox, when you are done entering the first argument, you can move to the next argument by placing a comma. See the below picture, I entered first argument “Hello” and typed a comma, VBA moved the argument focus to second argument by bolding it.

You may skip any optional arguments if you wish to use VBA default settings for them. For example: for MsgBox, if you skip 2^nd argument, VBA displays OK button only. If you want to skip an argument, you can place a comma to represent the end of the argument without actually entering anything in it as shown below.

As soon as I entered a comma, VBA focus moved to next argument. Note that you should only skip optional arguments. VBA will complain if you try to omit mandatory arguments.

Entering commas are mandatory only if you want to skip one or more arguments in between and enter a later argument. In the above example: you need to enter a comma to skip Buttons argument only if I have to enter Title or HelpFile or Context. If you have no other arguments to provide after entering mandatory arguments, you can simply close the parenthesis.

For example: if you only have a prompt to display to user with OK button and have no other criteria, code as in below is sufficient.

Sub Test()
MsgBox ("Hello")
End Sub

If you have a prompt and a title to be displayed and nothing else, use below code.

Sub Test()
MyMsg = MsgBox("Hello", , "Welcome")
End Sub

Notice that I am assigning the MsgBox to a variable (because I am using more than one argument) and skipping second argument with a comma; third argument is provided and function is closed with a parenthesis. Though there are 2 more arguments for MsgBox, I haven’t skipped them with commas.

This commas method is basic and good enough to work with code. But if you want to keep your code clean without much of those skipping commas and to look clean, there is another method you can use to skip arguments.

Named Arguments

Methods / Functions support what is called as a named argument. Notice the MsgBox arguments below. First argument is shown as Prompt, next is Buttons, third is Title and so on.

You can use these names while you pass values to them. Named arguments can be called by using the name of that argument followed by a colon followed by an equal sign (:=). An example code using named arguments is given below.

Sub Test()
MyMsg = MsgBox(prompt:="Hello", Title:="Welcome")
End Sub

Note that there is no comma in-between to skip arguments. Above code works just like what we saw earlier using commas to skip an argument.

Sub Test()
MyMsg = MsgBox("Hello", , "Welcome")
End Sub

Though both ways of entering arguments work in similar fashion, using named arguments make your code more understandable to users.  Take your pick on which method you want to follow. Happy Learning!!

VBA - Application.InputBox

We saw about InputBox function in our previous post. We learnt that you cannot restrict user to enter a particular type of values in InputBox. Of course, you can write couple of lines of code to validate user entry, but wouldn’t it be nice if your InputBox itself does that validation for you?

Application.InputBox method is at your rescue. Let’s see what it is and how to use it.

Application.InputBox calls a method of the Application object. A plain entry of InputBox in the code calls a function of InputBox that we discussed in our previous post.

Application.InputBox is a powerful tool to restrict the users to enter particular type of values. It also gives more flexibility to choose from various types of values that normal InputBox cannot. For example: you can let user select a range using Application.Input which is simply not possible with standard InputBox.

Also, returned value form the Application.InputBox method is a variant as opposed to a text string that is returned by plain InputBox. A variant can represent any kind of variable data type we discussed in post on varaibles. For example: it can hold a text string, a formula, a Boolean value, a complete Range reference and an array etc.

Syntax

Application.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type)

Application.InputBox: This is the key word to call this method.

Prompt: Mandatory. Whatever you enter in this argument will be displayed to the user. If you are entering a string in prompt, you will have to enclose that string with double quotes. You can also pass one of the variable values as an argument for this.

Title: Optional. This is the text shown on top of the message box. If omitted, “Input” will be shown. Notice the difference. In InputBox, if this argument is omitted “Microsoft Excel” is shown.

Default: Optional. This is shown as default value in the InputBox.

Left: Optional. Specifies an x position for the dialog box in relation to the upper-left corner of the screen

Tops: Optional. Specifies a y position for the dialog box in relation to the upper-left corner of the screen

Helpfile: Optional. If you notice the alert produced by Excel will have either a help button or a question mark button on the alert. Those are to display a help file that is saved with the program. If you have a help file created for your message box, you can provide that here. But creating a help file deserves multiple separate posts but that’s not our concerns at this point of time. You can conveniently ignore this.

HelpContextID: Optional. It is the number of the topic to be displayed from helpfile. If this is provided, helpfile is mandatory (quite obvious)

Type: This is where it is way more powerful than plain InputBox function. Type specifies the return data type. If this argument is omitted or number 2 is entered, the dialog box returns text (which replicates plain InputBox function). You have below options to pass on for this argument.

Notes:

• It is mandatory to assign the Application.InputBox function to a variable if you are using more than one argument
• If you want to omit any argument and move to next one, you should include a comma
• You can allow different types of data input by combining the values with plus sign. For example: if you want to allow numbers and text too in a box, your Type argument would be 1+2.

Let’s see this in action by few examples.

Example 1: Display a message to the user asking to enter a number between 1 and 10.

Sub Test()
Application.InputBox("Enter a number between 1 and 10")
End Sub

Running the code displays below box. This is just like InputBox in operation. You are not restricting any user entry.

Notice the prompt “Input” and placement of buttons at the bottom. This is different if you compare with InputBox dialogue (below picture is copied from InputBox post)

Example 2: Display a message to the user asking to enter a number between 1 and 10. With number 10 entered already in the box that will be displayed.

Sub Test()
MyInput = Application.InputBox("Enter a number between 1 and 10", , 10)
End Sub

Notice the code: Since I am using more than one argument, I assigned Application.InputBox to MyInput variable. As third argument, I entered number 10 which is what I wanted in the box.

Executing code above will display below box.

Number 10 pre-entered in the box will help user if that is what commonly used. It reduces few key presses from the user. If user wants to enter a different number, he can delete existing and enter whatever he wants.

Example 3: Let user select a range of cells.

Sub Test()
MyInput = Application.InputBox("Select a range", , , , , , 8)
End Sub

Running the above code would show up a box and you can click on any cell or cells in Excel, address of that cells is reflected in dialogue box like a formula as shown below.

Example 4: With the above settings, restrict user to enter only numbers.

Sub Test()
MyInput = Application.InputBox("Enter a number between 1 and 10", , 10, , , , , 1)
End Sub

Notice the commas; I am skipping the arguments by placing a comma for each argument. For last argument, I entered number 1 which represents the number Type. After executing this code, a dialogue box just like above will be displayed. However, if the user enters anything other than a number, it will show an error. This process continues until user clicks Cancel or enters a number.

Below is the error displayed if I enter some text in this box and click OK.

Values returned by Application.InputBox can be used just like how we used InputBox returned values. For example, you can let user select few cells and apply formatting to those cells only or ask user to enter a number (let’s call it n) and highlight every n^th row in the sheet etc.

We can see Application.InputBox in action while we move ahead in our learning and start writing complex codes to achieve big tasks. Till then, happy learning!!

VBA - Input Box

Thank you readers for encouraging my posts on Excel. With this post, this blog now has 100 posts and many more to go. Let's spread the knowledge. Cheers!!!!

We saw about MsgBox in one of our previous posts. MsgBox is used to alert user with some information. We can also provide some buttons such as Yes, No, Retry etc for the user to click. According to user response, we can continue with next steps of macro.

In some cases, it might not be enough to just get user click a button. You may have to solicit some information from the user for further action. For example: you may have to get a name from the user and use that name in a subsequent procedure. MsgBox function falls short here as it can only offer buttons to click.

In such cases, InputBox function is your friend. This function is similar to MsgBox with an additional functionality to solicit input from user. InputBox function displays a prompt in a dialog box, waits for the user to input text or click a button, and returns a string containing the contents of the text box.

InputBox syntax

InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])

InputBox: This is the key word to call Input Box

Prompt: Mandatory. Whatever you enter in this argument will be displayed to the user. If you are entering a string in prompt, you will have to enclose that string with double quotes. You can also pass one of the variable values as an argument for this. Do you remember the line we used in variables post?

Title: Optional. This is the text shown on top of the input box. If omitted, “Microsoft Excel” will be shown.

Default: Optional. This is shown as default value in the InputBox.

Xpos: Optional. This is a number that specifies the horizontal distance of the left edge of the dialog box from the left edge of the screen. If xpos is omitted, the dialog box is horizontally centred.

Ypos: Optional. This is a number that specifies, the vertical distance of the upper edge of the dialog box from the top of the screen. If ypos is omitted, the dialog box is vertically positioned approximately one-third of the way down the screen.

Helpfile: Optional. If you notice, the alert produced by Excel will have either a help button or a question mark button on the alert. Those are to display a help file that is saved with the program. If you have a help file created for your box, you can provide that here. But creating a help file deserves multiple separate posts but that’s not our concerns at this point of time. You can conveniently ignore this.

Context: Optional. It is the number of the topic to be displayed from helpfile. If this is provided, helpfile is mandatory (quite obvious)

Notes:

• It is mandatory to assign the InputBox function to a variable if you are using more than one argument.
• If you want to omit any argument and move to next one, you should include a comma.

Let’s see the InputBox in action with few examples.

Example 1: Display a message to the user asking to enter a number between 1 and 10.

Sub Test()
InputBox ("Hello, please enter a number between 1 and 10")
End Sub

Executing the code above will display a dialogue like below.

In the bottom of the above box, user can enter anything and / or click OK or Cancel buttons. We will see how to work with user response shortly.

Example 2: Display a message to the user asking to enter a number between 1 and 10. With number 10 entered already in the box that will be displayed.

Sub Test()
Dim MyInput As String
MyInput = InputBox("Hello, please enter a number between 1 and 10", , 10)
End Sub

Notice the code: Since I am using more than one argument, I am declaring a variable MyInput and assigned InputBox to that variable. And I used a comma to skip second argument. As third argument, I entered number 10 which is what I wanted in the box by default.

Executing code above will display below box.

Number 10 pre-entered in the box will help user if that is what commonly used. It reduces few key presses from the user. If user wants to enter a different number, he can delete existing and enter whatever he wants.

When the user clicks OK button, whatever is entered in the box will be assigned to the variable. If Cancel button is clicked then our variable get a null string (empty or nothing is represented by “” i.e. two double quotes with nothing in-between)

Problem with InputBox is that you cannot define the buttons you need. OK and Cancel buttons are always displayed.

Work With User Response

InputBox returns user entry as a text string. Using this string; you can further write code that uses it. Let’s extend our example code above to show the user entered value back to him in an MsgBox for confirmation.

Sub Test()
Dim MyInput As String
MyInput = InputBox("Hello, please enter a number between 1 and 10", , 10)
If MyInput = "" Then 'User didn't enter anything or clicked cancel
    MsgBox ("You entered nothing")
Else 'Ok. something is entered
    MsgBox ("You entered" & MyInput & vbCrLf & "Click OK to Proceed")
End If
End Sub

Running above code displays an input box first (refer to above picture). Based on user action, there will be subsequent message boxes.

If user clicked Cancel button or didn’t not enter anything and clicked on OK button, below message is displayed.

If user entered something (not necessarily a number between 1 and 10) and clicked on OK button, below message is displayed. (Of course, with whatever user entered in place of 10)

As mentioned above, InputBox returns anything entered in it as a string i.e. as text. Even if you enter a number 10 as above, you cannot use it in subsequent calculations as a number. If you are sure that only number is entered, multiply the string with 1 in your code. That would force the number in string to be identified as a number.

Sub Test()
MyInput = InputBox("Hello, please enter a number between 1 and 10", , 10) * 1
End Sub

But remember, above code will result into an error if what is entered in the InputBox is not a number.

MsgBox & InputBox are the two basic dialogues used to solicit user’s response. Both have their own limitations, for example in InputBox, you cannot restrict the user to enter only a number. You cannot customize appearance of these boxes etc.

But there are alternatives. If you are looking to restrict what user enters, you can use Application.InputBox (note that what we saw above is a plain InputBox. With Application qualifier, a different function is called altogether). We will see about this in our next post.

You can make your own designed message boxes and input boxes using UserForms. We will see about them eventually. Till then, explore what we discussed on this series till now. Cheerio!!!

Minimise The Ribbon

This article is applicable only to Excel 2007 and later versions where Microsoft Office fluent Interface (aka Ribbon) is implemented. Ribbon replaced the earlier menus and it is quite handy.

There is no way to delete or replace the Ribbon with the toolbars and menus to appear like earlier versions of Microsoft Office. However, you can minimize the Ribbon to make more space available on your screen. When you minimise ribbon, tabs are visible but not the options under those tabs.

Ribbon When Visible

Ribbon When Minimised

Minimising ribbon shows few rows more in the sheet than when it is visible. However, you can still use all the key board shortcuts to do your job. You can also click any tab once when the Ribbon is minimised to get the ribbon temporarily visible.

How to Minimise Ribbon

Easiest Way

Just press Ctrl and F1 together. The Ribbon is gone until you press the same combination again.

Easy Way

With the mouse pointer, double click the active tab name. For example, if Home tab is activated, double click on the word Home. Double click on any active tab again to get the Ribbon back.

Long Way

Click on Quick Access Toolbar (QAT) drop down button.

In the drop down, click on More Commands… In the dialogue box that opens up, select All Commands in Choose commands from dropdown. Locate Minimize the Ribbon in box that is below the dropdown and click Add >>. Click OK.

Now you should be able to see a new button added to your QAT.

Click on the button to minimise the ribbon or make it visible.

VBA Way

Copy the code below to a code window and execute. That should minimise the ribbon. Run again to make it normal.

Sub HideRibbon()
    CommandBars.ExecuteMso "MinimizeRibbon"
End Sub

VBA Way 2

Copy below code and paste in a code window. Go to Excel and run macro from View > Macros (i.e. not from the code window directly)

Sub HideRibbon()
    Application.SendKeys ("^{F1}")
End Sub

Change the Default View of Newly Created Sheet

When you add a new sheet, Excel by default shows the newly created sheet in Normal mode (as opposed to Page Layout view or Page Break view). If you want to change this behaviour and let Excel opens in the mode you want, change the default setting as below.

Excel 2010 and later:

File tab > Options > General > When creating new workbooks segment > select mode you like in the Default view for new sheets drop down. Options available are Normal View (default), Page Break Preview, and Page Layout View.

Excel 2007:

Click the Microsoft Office button > Excel Options > Popular tab > When creating new workbooks segment > select mode you like in the Default view for new sheets drop down. Options available are Normal View (default), Page Break Preview, and Page Layout View.

This feature was introduced in Excel 2007. Users of earlier versions are out of luck here.

Use A Template For Pre-Applied Formatting

If you need to use a specific format for all your files, you can try saving the format you want as a template file. and whenever a new Excel file is created, you can directly apply template rather than making changes manually. For example: if you always want gridlines to be off when you create a new workbook or want to have a standard text for header etc, you can use templates.

One of my friends in billing team spends most of his time preparing invoices in Excel and printing them. He keeps an invoice format in a file on his desktop and opens that each time. He could save some time if he can save that invoice format as a template and keep it in start-up folder so that every time he opens up an Excel file, it opens with his invoice format by default. Let’s see how to do that.

1. Create a new workbook
2. Apply all the formatting you need (cell format, styles, sheet format), data validation settings etc to this workbook
3. Press Ctrl+S
4. In the Save in field, locate XLSTART folder. This folder can be found in one of the following locations depending on your operating system.
• Windows XP: C:\Documents and Settings\<username>\Application Data\Microsoft\Excel\XLStart
• Windows Vista: C:\Users\<username>\AppData\Local\Microsoft\Excel\XLStart
• Windows 7: C:\Program Files\Microsoft Office\Office\XLStart
5.  In the File name field, type Book
6. In the Save As window, Select Excel Template (In Excel 2007 onwards) / Template (In Excel 2003 and below) in Save as type field drop down list.
7. Click OK.

That’s it. From now on, whenever you create a new workbook, it will contain all the formatting you applied in step 2 above.

The ClearType Font

Caution: Techie terms ahead

What is ClearType

ClearType is a technology of showing text in a font. ClearType is owned by Microsoft and was introduced first in 2000.

How it Works

Normally, the software in a computer treats the computer’s display screen as a rectangular array of square, indivisible pixels, each of which has an intensity and color that are determined by the blending of three primary colors: red, green, and blue. However, actual display hardware usually implements each pixel as a group of three adjacent, independent subpixels, each of which displays a different primary color. Thus, on a real computer display, each pixel is actually composed of separate red, green, and blue subpixels. 

If the computer controlling the display knows the exact position and color of all the subpixels on the screen, it can take advantage of this to improve the apparent resolution in certain situations. If each pixel on the display actually contains three rectangular subpixels of red, green, and blue, in that fixed order, then things on the screen that are smaller than one full pixel in size can be rendered by lighting only one or two of the subpixels. For example, if a diagonal line with a width smaller than a full pixel must be rendered, then this can be done by lighting only the subpixels that the line actually touches. If the line passes through the leftmost portion of the pixel, only the red subpixel is lit; if it passes through the rightmost portion of the pixel, only the blue subpixel is lit. This effectively triples the horizontal resolution of the image at normal viewing distances; the drawback is that the line thus drawn will show color fringes (at some points it might look green, at other points it might look red or blue).

ClearType uses this method to improve the smoothness of text. When the elements of a type character are smaller than a full pixel, ClearType lights only the appropriate subpixels of each full pixel in order to more closely follow the outlines of that character. Text rendered with ClearType looks “smoother” than text rendered without it, provided that the pixel layout of the display screen exactly matches what ClearType expects.

Why Are We Bothered 

Microsoft had implemented ClearType font in all MS Office products from 2007 and onwards. This is on by default. If at all you feel your font is rendered bit blurry, you may want to try switching off this feature.

According to a study at the University of Texas, ClearType "may not be universally beneficial". The study notes that maximum benefit may be seen when the information worker is spending large proportions of their time reading text (which is not necessarily the case for the majority of computer users today). Additionally, over one third of the study participants experienced some disadvantage when using ClearType. Whether ClearType, or other rendering, should be used is very subjective and it must be the choice of the individual, with the report recommending "to allow users to disable [ClearType] if they find it produces effects other than improved performance".

How to Disable / Enable ClearType 

Excel 2007:

Click the Microsoft Office button > Excel Options > Popular tab > Top options for working with Excel segment > clear the Always Use ClearType check box.

Excel 2010 and later:

File tab > Options > General > User Interface options segment > clear the Always Use ClearType check box.

Follow the same steps but check the box if you want to enable this feature.

Points to Remember

You will have to close all the Office applications that are open while changing this feature to take effect. 

Microsoft do not recommend to turn off this feature. As a workaround, you can use Microsoft’s ClearType tuning tool. More information can be found here.