Working With Variables

You will read about Variables a lot while learning VBA and we use them to a very large extent in most of the codes we write. It’s better to know about them before starting real coding. So, let’s see.

Variable is essentially a storage location where you can store data. As the name suggests, this value can be changed during the macro execution. You can literally define any number of variables you need to work in your project but each variable will have to be differently named.

Naming a Variable

There are some rules to follow while naming a variable. Let’s see what they are below.

• Name length must be within 255 characters
• Space, Period (dot/full stop), exclamation (!), @, &, $, # cannot be used in the name
• Should not begin with a number
• Reserved names cannot be used
• Names are not case sensitive
• You cannot duplicate names in the same procedure

Remember our Named Range post? You can relate these rules to them. Few examples of valid names can be: MyName, This_Year, Year1, NewVal etc. Invalid name examples are: My.Name (dot not allowed), This Year (space), 1Year (number at the start), New&Val (& used).

Declare a Variable

If you decide to use a variable in your procedure, you will have to declare that to VBA. This is usually done with a key word Dim.  For example: if I have to use a variable named MyVal, I enter the following line in my code.

Dim MyVal

If you place this line after Sub statement, this variable can be used anywhere in the same macro until End Sub. If you place this at the start of the module code before any Sub statement, you can use this variable throughout all the macros written in that module.

Variables occupy memory to work with. Though VBA is capable of deciding the nature of the variable when it encounters the actual usage of variable in a line of code, it is usually useful to define the data type for which this variable is used. For example: you can define if you are going to use the variable to store a text string or a date or another object etc. Defining this would help VBA to reserve exact memory required and often it saves from risk of storing an unintended value. To define the data type, you will have to enter below line.

Dim MyVal As XX

You have multiple options to use in place of XX. See below to know what those are.

So if you want your variable named MyVal to store a number that is between 0 and 255, you declare it as in below line.

Dim MyVal as Byte

If you or your procedure tries to give a value to variable that is outside of 0 and 255, you will get a run time error. Look at the below picture, I declared variable as byte and tried to set its value to 2556 and executed the code. This is the error I got.

As said earlier, declaring the variable in advance is not mandatory. In the code in above picture, I can straight away enter MyVal = 2556 without declaring the variable. However this causes few problems. If you do not declare the variable and data type, VBA reserves more memory than required. One more problem is that you cannot capture correct data type in the variable. In above example: your intention is not to store any number outside 0 and 255, but if you don’t declare the data type, VBA happily accepts the number 2556. One more problem is related to misspelling. VBA might take any misspelt words as a different variable and your project may produce undesired results. If you want to mandate VBA to ask for declaration of every variable, read about Option Explicit below.

Option Explicit

This is to make sure you declare each variable before using it in the module. Enter Option Explicit at the top of module, before starting of any code with Sub. You can also click on Tools > Options > Editor tab > check or clear Require Variable Declaration.

Declaring Multiple Variables

Many times, you might have to use more than one variable in your module. You can declare all of them in 2 ways as shown below.

Method 1

Dim MyVal As Byte

Dim MyName As String

Dim Cl As Range

Dim SelOpt As Boolean

Method 2

Dim MyVal As Byte, Dim MyName As String, Dim Cl As Range, Dim SelOpt As Boolean

Finishing Touch

Once you declare variables, you can start using them to store values so that they can used somewhere else in your code. For example, copy the below code in to a VBE window and press F5.

Sub Test()
Dim UserName As String 'Declaring variable
UserName = Application.UserName 'Passing value to variable
MsgBox ("You are signed in as " & UserName) 'Using variable in another action
End Sub

Running this code on my system will show me a dialogue box as show below.

Different Colours Used in VBA Window

VBA highlights text in different colours in different situations. Understanding what they mean would help much. Let’s see about that in this post.

Font Highlighted in Black

All is well here. All text that is in black colour is normal code that will be executed by VBA.

Font Highlighted in Red

If entire line of code font is highlighted in red, that means there is a syntax error. Syntax error occurs because of various reasons. Example: can be a misspelt keyword or argument.

Font highlighted in Green

Comment text is highlighted in green. See if there is an apostrophe (‘) from where the text is appearing green. Comments are used to elaborate what the code means or generally to give more information. VBA will not execute comments.

Background highlighted in Grey

Grey highlighted text as shown in below picture means you selected that text. It’s ready to be cut, copied or moved to other location.

Background highlighted in Yellow

This means highlighted line is the execution point. This is the line that will be executed when you proceed further. An arrow mark will be placed in the left hand side of that line. Often  you can see this when you press F8 in the macro.

Background highlighted in Maroon

This means highlighted line is the break point. When you run your code, execution will automatically stop at this line. You can place breakpoint or remove at any line of the code by pressing F9 key placing cursor on that line. A maroon colour circle will also be placed to the left of the code along with highlighting.

Font Highlighted in Blue

If you use any key word that is part of VBA build in statements, functions; that word will be highlighted with blue. Notice colour of With qualifier below. (May be difficult to see here, you may type the same statement in your VBA and notice)

Turquoise Colour Box in the Left

This box refers a bookmark. Bookmarks are used to return to that line of code quickly from anywhere at a later point of time.

Green Arrow in the Left

This is called Call Return Text. When you have a series of macros called from one another and stop the code at a break point, this arrow indicates from where the code will start running when the code is resumed.

Follow this example to see how this works. Copy and paste below code in your module.

Sub Test()
Call Test1
End Sub
Sub Test1()
Call Test2
End Sub
Sub Test2()
Call Test3
End Sub
Sub Test3()
ActiveCell.Select
End Sub

Select ActiveCell.Select line and press F9. This should add a break point on that line (maroon colour highlight). Now click on Sub Test () line and press F5, when the maroon line turns yellow (execution point), press Ctrl+L. This would show a box called Call Stack. Double click anything in the list other than first item. You should be able to see the green arrow in the module that calls your double clicked module. Bit confusing but this is a great debugging tool. You might not be using it if you are not really deep in developing. I myself haven’t used it yet.

All these colours are by default applied by VBA; however you have option to choose other colours you fancy. Click on Tools > Options > Editor Format tab. Under Code Colors, click on any item and you can change the font, size, foreground, background & indicator colour of that item. Enjoy!!!

VBA - Best Practices To Follow While Coding

Hello!!

I hope you remember our posts on VBA so far. In earlier posts, we have recorded macros & analysed the code behind them. Today, we are going to see few good practises that saves your time and make the code efficient. Let’s dig into them.

Indentation

Did you notice the code saved by Excel when it records a macro? For example, notice our MyFirstMacro code. You can see how various lines of code under With qualifier are indented.

With Selection.Borders(xlEdgeLeft)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlThin

End With

You, while writing code should ideally follow this. Excel wouldn't complain if you don’t, but your code looks cool and tidy. If you have multiple With qualifiers or If statements, keeping track of where a qualifier is starting and where it is ending will become difficult without indentation. Look at the code snippet below.

Sub Test()

    If ActiveSheet.Name = "Sheet1" Then

        If ActiveCell.Value < 10 Then

            MsgBox ("You are in Sheet1 and cell value is less than 10")

        Else

            MsgBox ("You are in Sheet1 and cell value is not less than 10")

        End If

    Else

        MsgBox ("Select Sheet1")

    End If

End Sub

 And see this.

Sub Test1()
If ActiveSheet.Name = "Sheet1" Then
If ActiveCell.Value < 10 Then
MsgBox ("You are in Sheet1 and cell value is less than 10")
Else
MsgBox ("You are in Sheet1 and cell value is not less than 10")
End If
Else
MsgBox ("Select Sheet1")
End If
End Sub

Which one is looking cool? Pressing Tab key will add indent to your code, repeated press will add more indent. You can use Shift+Tab to outdent (to remove indent).

If you select Auto Indent option, VBA auto indents the next line as per indent of previous line. You can turn this option on and off by clicking on Tools > Options > Editor tab > check or clear Auto Indent. You will also have option to enter what width your indent should have.

Think of All Possible Errors

Prevention is better than cure, right? Think of the errors that can occur while your code is used. These errors can be because of version compatibility, incorrect usage of syntax etc. If you see the possibilities, you can categorize those under 3 types.

Syntax Errors

Syntax error is where your code itself having errors. For example, you misspelled object’s name, one example could be, Application object is entered as Applicatoin (i and o have changed places). There is plethora of other scenarios where you err while coding. VBA highlights syntax errors with red colour and often with an alert message.

You can enable or disable auto syntax check by clicking on Tools > Options > Editor tab > check or clear Auto Syntax Check.

Runtime Errors

These are the errors that can occur while executing your macro. You will have to think of all the possible run time errors while coding. For example, if you are writing a code that enters some value in a cell, you will have to anticipate a situation where the sheet is protected and you will have to write code to have a workaround. Another example is, if your code is using a newly introduced object which was not available in earlier version of Excel, executing your code in other machines which uses older version will throw errors.

For run time errors, Excel displays an alert and stops executing the macro unless you provide a workaround in your code.

Logical Errors

These errors occur if you fail to draft your code logically. Logical errors may not stop macro execution all the times but will give incorrect results. For example; if you were to enter some value in currently selected cell of Sheet1 but you used ActiveCell object to do this, your macro will enter the values in currently selected cell of currently active sheet which might not be Sheet1.

Test and Retest Your Code

As a good developer always do, test and retest your macro every time you do a change to your code. Your testing should include all possible scenarios you can think of. Use VBAs debugger tool extensively for this purpose. There is a whole menu dedicated to debugging in VBA (look at Debug menu). We will see about each option available in this menu but in one of future posts.

Use Recorder

We saw about recording a macro in one of our previous posts. Use this feature and you will for sure get a way around the code. Though recorded code is not perfect, at least you get an idea on what object can be used.

Use Comments

While analysing our recorded code in our previous post, we saw that comments can be entered to clearly mention what the code is trying to do for someone looking at the code that could be even for writer of the code. Looking at a code I wrote 6 months ago makes no sense to me in terms of logic I used; comments in this situation will be a saviour. Go ahead and use comments extensively to brief about your code.

Keep it Simple Silly

If you are trying to achieve a mammoth task or various tasks, try to break your code into multiple modules, each module aimed at achieving something. Calling a macro within other macro is very easy. Your code looks less clumsy this way and it will be easier to find any errors.

Provide Error Trapping

Run time error causes the macro to stop executing. If you want your macro to run without interruption, you will code your macro to tell what to do if an error occurred. This is called errortrapping. You can do this by using statements like On Error Resume Next or On Error Goto. So, make provision for error trapping in your code.

Use Option Explicit

You can use variables in VBA to capture some values or facilitate looping (we will discuss all these as we proceed deeper). Though VBA facilitates variables usage without declaring them first, it is a good choice to declare all the variables explicitly. This not only makes your code run faster and memory saving but also saves for risk of misspelling.

Use Help

Last but not the least in anyway, use built-in help in any case you are in need of help. VBA help is documented in a fantastic way; you will probably get all that you need from here. Else, there is a fair chance that one of the online documents already has a solution for your problem.

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!!

Change Comment's Default Formatting

You can insert comments to a cell in Excel. If you don’t like the format of it, you can change it by selecting the comment and right click, choose Format Comment option. But if you want to change the default format of the comment so that you need not change the format every time you insert a comment, follow the steps below.

Excel's comment uses Windows ToolTip settings for its own formatting. So, you have to change the ToolTip settings in order to set comment's default format. This is the sequence in my Windows XP. Similar commands should be available in other OS as well. Try any one of them.

Start > Control Panel > Appearance and Themes > Display (in category view)

Start > Control Panel > double click Display (in classic view)
Right click in any empty space on desktop > Properties

In the Display Properties dialogue box, click on Appearance tab and click Advanced button! In Advanced Appearance dialogue box, under Item dropdown, select Tooltip, adjust the background color, font color, font size, Italic, Bold properties etc here.

Changing Windows ToolTip format will effect all other Windows based programs too.
You may want to read how to insert picture background in comments. 

Always Open a Specific Excel File

Do you work on a specific Excel file every day? Or you have a master file that must be opened every time you work in Excel? It is generally good idea to let Excel open up this file instead of manually opening every time as this will save little effort and time. Let’s see how?

When you open Excel, it will always open the files saved under a particular folder named XLStart. So, save any workbook as you normally save, in this 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

Once saved here, you will get this workbook opened every time you start a fresh instance of Excel.

Tip: To quickly know your start up file path, in VBA Immediate Window, type below line and press Enter.

? Application.StartupPath 

You may want to read about maintaining an alternate startup folder. 

Use an Alternate Startup Folder

We know that Excel always opens up all the files in its XLStartup folder. What if you want to open all the files in some other folder along with start-up folder files? Yes. You guessed it correct, Excel provides that functionality too. Follow the sequence below to get this.

Excel 2010 and later:

File tab > Options > Advanced tab > scroll all the way down to see General segment > Enter path of the folder you want in At startup, open all files in > OK.

Excel 2007:

Click the Microsoft Office button > Excel Options > Advanced tab > scroll all the way down to see General segment > Enter path of the folder you want in At startup, open all files in > OK.

Excel 2003:

Tools menu > Options > General tab > Enter path of the folder you want in At startup, open all files in > OK.

Points to remember:

• Excel will attempt to open every file in the alternate startup folder, make sure you specify an empty folder or a folder that contains only files that Excel can open.
• If a file with the same name is in both the XLStart folder and the alternate startup folder, the file in the XLStart folder opens.

Change Default File Format (.xlsx to .xls)

In my organisation, most of the people use Excel 2003 (you read it correct). I am one of the few privileged (canny??) to get Excel 2010 installed. So I got a version that is better in look and performance but a frequent problem I face is with the file formats. Any workbook I create in Excel 2010 will be by default contain extension “.xlsx” (recent file format). When I share these files with my colleagues, none of them will be able to modify that file in Excel 2003 and very few who is wise and installed compatibility pack will be able to read. I get a return mail saying they can’t open and I have to resave the file with “.xls” extension (Excel 2003 file format). I can do it for couple of files but if I have to do for every workbook, it would be good idea to change the default format to “.xls”. Let’s see how.

Excel 2010 and later:

File tab > Options > Save tab > Save workbooks segment > Select Excel 97-2003 Workbook in Save files in this format drop down > click OK.

Excel 2007:

Click the Microsoft Office button > Excel Options > Save tab > Save workbooks segment > Select Excel 97-2003 Workbook in Save files in this format drop down > click OK.

Convert Text Formatted Numbers to Actual Numbers

When I download reports from other programs such as SAP, often numbers are imported as text. Such numbers will be aligned left (common for actual text) and often indicated by error indicator (small green triangle on top left of those cells). These cells cannot be referenced in calculation formulas as Excel thinks it is actual text. Sometimes formatting the cells as Numbers works fine. Few times, it doesn't work. If I do F2 (edit the cell) and press Enter, then the cell value will be recognized as number. But doing this over thousands of cells manually will be irksome. If it sounds familiar to you, read on to see what we can do about it.

Excel 2002 and later versions are programmed to identify such cases and automatically convert these values to numbers. But if this hasn't happened by any reasons, we have few solutions below.

Solution 1: Try Formatting

Changing the format of the cells might work sometimes. So let’s try it. Click Home tab > Number group, select Number in the drop-down (in Excel 2007 or later) or Format menu > Cells > Number tab > under category, select Number (in Excel 2003 or earlier).

Does this solve the problem? If not, read on.

Solution 2: Use Error Checking

If those cells containing numbers as text are showing an error indicator, you may try this solution. Select all those cells. You will be able to see an error button (a square type of button with exclamation mark in it) next to the selection. Click on that and in the menu that appears, select Convert to Number.This action converts the numbers that are stored as text back to numbers.

Solution 3: Multiply by 1

1. Select any empty cell anywhere
2. Enter the value 1 into that cell
3. Copy that cell (Ctrl+C)
4. Select all the cells which needs to be converted
5. Click Paste Special (one of the option in the right click menu)
6. In the Paste Special dialogue box, select the Multiply option, then click OK.

This method forces a multiplication on the existing cells, while doing so Excel converts numbers in disguise to actual numbers.

Solution 4: Use TRIM function

Sometimes space before and after the actual number might cause Excel thinking that value is text. To clean up those spaces, use Excel’s built-in TRIM function.

Solution 5: Use SUBSTITUTE function

Some programs inserts spaces in between two digits in the same number (in one of the report I use, rupees and paise are separated by a space instead of dot). In this case, you can use SUBSTITUTE function to replace that space with a dot.

Solution 6: Use VALUE function

Value function converts any number, date or time formats entered as text to a number. Value function can also clean up the apostrophe normally entered in the start of the cell to show number as text.

Look at those numbers in disguise; they should be back as numbers by now.

Insert Picture as Background in Comments

We insert comments to a cell for many reasons like to give additional information regarding the contents of the cell. But wherever I do that, I feel that Excel comment background is boring. I try to apply different backgrounds to see which one fits, in the process I discovered that you can also set a picture as a background to a comment. Interesting? Here is the way.

Once you are done inserting a comment, click on the border of it. Make sure you don’t have the cursor inside the comment. You can check that by the design across the borders. Comments outline appears like below when you are in edit comment mode or selection of comment.

When Comment is Selected

While Editing Comment

So diagonal lines indicates that you are in comment editing mode! Series of small dots is the proof that you just selected the comment. Right click on the dots part now and select Format Comment option. This should bring up Format Comment dialogue box; notice the tabs in this box, there should be more than 1 tab (if there is only Font tab, you might have right clicked in editing mode. So close the dialogue box and try again).

Click on Colors and Lines tab, in the color dropdown, select Fill Effects. In the new dialogue, click Picture tab and Select picture… button. Browse to your picture and select it. Click on Insert. Click OK in all the open boxes. You should be able to see selected picture in the comment background. (Look at the below pic, beautiful, isn't it? I selected blue hills which is available as a sample picture in MS Windows)

You can also change font, color, size, italic, bold and many other formatting optoins using Format Comment dialogue box.

Bored With Default Blue Colour??

Microsoft Office programs have a default colour scheme, Blue. Appearance of ribbons and menus will depend on this scheme. If you are bored with this colour, Office programs provide additional colours to play with. It’s often refreshing to see a different colour, you can check out below pictures to see how Excel looks under each colour.

Default Blue scheme

 Silver Scheme

Black Scheme

How to Change the Interface Colour Scheme

Excel 2007:

Click the Microsoft Office button > Excel Options > Popular tab > Top options for working with Excel segment > In Color Scheme drop down, select the colour you want from available options i.e. Blue, Silver & Blac.> click OK.

Excel 2010 and later:

File tab > Options > General > User Interface options segment > In Color Scheme drop down, select the colour you want from available options i.e. Blue, Silver & Black > click OK.

Excel 2003 menu colours depend on desktop theme. Read below to know how to change it.

How to Change the Windows Desktop Colour Scheme

Microsoft Windows theme determines the colour and appearance of Excel 2003 interface. Even in later versions of Excel, dialogue boxes use Windows theme settings. So even if you change interface colour scheme as above, you still have to match desktop colour scheme if you want a perfect sync look.

Right click in any empty space of desktop > Properties > Appearance tab > Under Windows and buttons, select Windows XP Style > Under Color Scheme drop down, select one of the available options i.e. Blue, Olive Green & Silver > click OK. Note that this will change the theme of desktop. You may also want to try other options available in these two drop down lists.

Note

Same Interface colour is used by all the Office programs (Except InfoPath 2007, OneNote 2007, Project 2007, Publisher 2007 and Visio 2007). Changing colour in one Office program will change the interface of all other Office programs.

The Mini Toolbar

Starting Excel 2007 for Windows, there is a cute mini tool bar introduced to quickly format your selection. You can see this tool bar by right clicking on any cell(s), shape or chart etc. It appears as below in my system when I right click on a cell.

Did you see on the top of right click menu? That’s the one I am talking about. If my Home tab is not displayed (where all formatting options are available  and I urgently want to apply quick formatting, mini tool bar is my friend.

Points to note:

1. Contents of this menu depend on the object you right clicked. So, you will see a different menu if you click on a range and a different one if you click on chart etc.
2. You cannot customize this menu based on your needs.
3. Applies to Excel 2007 and later versions only (Not available in Mac 2008 & 2011)!

Though this is great to apply quick format, many users feel it annoying (I am not one of them). For those, you can turn this feature off. Let’s see how.

Excel 2007:

Click the Microsoft Office button > Excel Options > Popular tab > Top options for working with Excel segment > clear the Show Mini Toolbar on selection check box.

Excel 2010 and later:

File tab > Options > General > User Interface options segment > clear the Show Mini Toolbar on selection check box.

Show / Hide ScreenTips

Did you notice the information window that appears when you place the mouse pointer on any of the command button or control? These small windows are called ScreenTips. More descriptive information on the button is called Enhanced ScreenTip. Below is the picture of Format Painter ScreenTip.

As a starter, this is a great feature to know which button is used for what. But as you know in and out of all the buttons, this may be a bit distractive at times. You can turn off this feature partly or fully (will explain) by following the procedure below.

Excel 2010 and later:

File tab > Options > General > User Interface options segment > In the ScreenTip style drop down, select the appropriate option

• Show feature descriptions in ScreenTips: This option turns on ScreenTips and Enhanced ScreenTips. This is the default setting.
• Don't show feature descriptions in ScreenTips: This option turns off Enhanced ScreenTips. You still see ScreenTips.
• Don't show ScreenTips: This option turns off ScreenTips and Enhanced ScreenTips

Excel 2007:

Click the Microsoft Office button > Excel Options > Popular tab > Top options for working with Excel segment > In the ScreenTip style drop down, select the appropriate option (options are same as 2010 options, see above)

Excel 2003

Enhanced ScreenTips are not available in Excel 2003. Only ScreenTips are displayed, if you want to turn it off, below is the sequence.

Tools menu > Customize > Options tab > Under Other segment, select or clear the Show ScreenTips on toolbars checkbox.

Note:

Changing this setting effects all MS Office programs.

Debug Formulas Right in The Formula Bar

While you are creating complex formulas, it is often required to pause for a minute and see what is to come as a result of that formula. When you refer Sheet2!A1:A3 in a SUM function, It is often good idea to see what these cells A1:A3 contains and what is the result that SUM function fetches. Relevance of this becomes more when you build complex and sensitive formulas. Let’s see what Excel has to provide regarding this.

We have formula debugging tool in Excel that you can use to attain just the same. To simplify our example, I am going to use a simple SUM formula and show you what I mean.

Let’s assume I entered values 1, 2, 3 in cell A1, A2 and A3 respectively. I need to fetch sum of A1:A3 in cell A4.

We enter a formula in A4 which is “=SUM(A1:A3)”, after entering this formula, instead of pressing Enter, pause for a minute and notice the formula bar. Is it showing the formula you just entered? Click on the formula bar and select A1:A3 part.

Now press function key F9 and see what happens.

Saw the magic? Reference A1:A3 actually turned into the values that reference contains. If you want to show the entire formula result, you can press F9 without selecting any part of the formula.

Doing this action actually replaces the existing values with result. So, once you press F9 and are satisfied that displayed result is what you are expecting, press Ctrl+Z to undo your last action and restore the cell references. Otherwise, you can just press Enter if you don’t want formula anymore but only the result.

If you have made any error in cell reference or formula construction is incorrect, Excel throws up an error alert and you have to rectify the formula.

I deliberately gave incorrect reference as shown below and see the error.

This way you can use F9 in Formula bar as a calculator.

Let Excel Read Back What You Type

You can let the Excel talk to you by following below sequence. Excel’s text to speech conversion commands are “hidden” from normal user for the reason best known to Microsoft.

In Excel 2007 or later

1. Next to the Quick Access Toolbar (on top of menu tab), click Customize Quick Access Toolbar (circled arrow below).
   
2. Click More Commands.
3. In the Choose commands from list, select All Commands.
4. Scroll down, click the Speak Cells command, and then click Add. You can try out other options available too.
   
5. Click OK.
6. When you want to use a text-to-speech command, select the cells you want and click the button you added on the Quick Access Toolbar. (Keep speakers / headphones un-muted)

In Excel 2003 and 2002

1. On the Tools menu, point to Speech, and then click Show Text to Speech Toolbar.
2. On the Text To Speech toolbar, click the command that you want to use.

Format Numbers to Display Thousands / Millions

Sometimes you might want to display your numbers in multiples of thousands while retaining the actual values. For example: you have a number 10000 (ten thousand) in a cell. Keeping this, you want to show this number as 10. I do this all the time while presenting financial statements since numbers are huge. Do the following to achieve this.

Select the cell(s) you want to display in thousands, right click and select Format Cells. In the Format Cells dialogue box > Number tab > Select Custom in category > enter 0, (zero followed by a comma) in the Type box, click OK. Now see the cell value, it should have already become 10. Note that cell value 10000 is still there (you can notice in formula bar) but only displayed as 10 (in thousands)

Similarly, if you want in millions, Type box should contain 0,, (zero followed by two commas)

Combine Text and Formula In a Cell

We know how to enter values in cells. We also know how to enter formulas to play with numbers. But what if I have to combine text and formulas in one cell? For example: I want to show “Total bill value is 1000” where in 1000 comes from summing of cell A1:A10. So essentially we have to combine some text and a formula. Let’s see how to do it.

We will use Excel’s concatenation operator “&” and quotation marks to achieve this. In functions, text can be entered between two quotation marks. In the cell where you want the result, type the formula.

="Total bill value is " & SUM(A1:A10)

Easy? Yes it is.

I have another situation, I want to display “Today is DD:MMM:YYYY” wherein DD:MMM:YYYY is the date returned by Excel built-in function TODAY. How do I do it? I enter the below formula in any cell I want the result in.

="Today is " & TODAY()

That’s it? Actually no, if you tried entering this formula result will be something like this (on the day I am writing this post): Today is 41716. I wanted date but what is 41716? Actually this number is the date but since Excel considers entire cell to have text, you cannot format this number to date using Excel’s formatting options. You will have to do it in the function itself using another Excel built-in function TEXT. Let’s see the modified formula.

="Today is " & TEXT(TODAY(),"DD-MMM-YYYY")

This show up a result: Today is 18-Mar-2014. Just what I wanted! 

Enter Text Over Multiple Lines in a Cell

You can enter multiple lines of text in a cell by pressing Alt+Enter at the end of each line in the same cell. Excel automatically moves the cursor to one line below. Excel also automatically applies text wrapping to that cell.

One more thing that can help is, text entered this way can be easily copied and spread over multiple cells vertically in a way that each line occupies one cell. Try entering 3 lines (or any number you wish) of text with Alt+Enter at the end of every line and press Enter once done with all lines. Now select that cell again and press F2. Select all the text entered and copy it (Ctrl+c). Select any empty cell now and just paste (Ctrl+v). Your text will now occupy 3 cells, one for each line you entered.

Avoid Automatic Hyperlinks

When you type a value that resembles an email Id or a web address, Excel tries to be smart and formats the value to a hyper-link and change the font colour to blue with an underline. While most of the times this is pleasant, few times when I don’t want a hyper-link to be created or what I typed is just not a mail ID, this puts me off.

So, what options do we have?

Turn Off the Auto Formatting

If you don’t want Excel to format any value to hyper-link  you can just turn off this feature. Follow the steps below to do this.

In Excel 2010 or later

File Tab > Options > Proofing tab > AutoCorrect options > AutoFormat as you type tab > clear the check box below Replace as you type (i.e. next to Internet and network paths with hyperlinks).

Excel 2007

Click the Microsoft office button > Excel Options > Proofing tab > AutoCorrect options > AutoFormat as you type tab > clear the check box below Replace as you type (i.e. next to Internet and network paths with hyperlinks).

In Excel 2003 or earlier

Tools menu > AutoCorrect options > > AutoFormat as you type tab > clear the check box below Replace as you type (i.e. next to Internet and network paths with hyperlinks).

Follow the same steps but check this box if you want to enable this feature. This feature was introduced in Excel 2000 but there is no way to turn off this in that version. Above steps can be done only in Excel 2002 or later.

Override the Formatting

This is a quick workaround. Whenever Excel changes the format, just press Ctrl+Z (undo command). You will get the value you entered without hyper-link.

Enter Value with Apostrophe

You can start typing the text in to the cell with an apostrophe (‘). Then Excel treats this value as absolute text and will not try to include hyper-links.

Resize Scrollbars

Excel’s scrollbars size is not adjustable. By some reason if you want to increase or decrease these scrollbar size, your only hope is to change the scrollbar size settings at operating system level. Read on if you still want to go ahead as following steps will change scrollbar setting across all the programs.

This is the sequence in my Windows XP. Similar commands should be available in other OS as well.

Start > Control Panel > Appearance and Themes > Display (in category view)

Start > Control Panel > double click Display (in classic view)

In the Display Properties dialogue box, click on Appearance tab and click Advanced button! In Advanced Appearance dialogue box, under Item drop-down, select Scrollbar, adjust the size as per your needs to click OK in all open dialogue boxes.