Go to Tools > Macro > Macros. Under “Macro Name”, type in the name of the macro, and click Create. The VBA editor will open up, with the name of the macro and the “End Sub” text filled in. You’ll need to type the macro in between these two.
To assign a keyboard shortcut to a macro, go to Tools > Customize. In the Commands tab, choose Macros, then choose your macro from the list on the right. Press “Keyboard”. Now under “Categories”, choose Macros, and choose your macro from the list on the right. Place the cursor in “Press new shortcut key”, and do just that-press the shortcut key you want to use, for example, [Ctrl] [Alt] [5]. Finally, click Assign, and OK your way out.
Assigning a toolbar button to your macro is a little harder. Go to Tools > Customize, and click the Toolbars tab. Click New and then type in your Toolbar name. Click OK. You should see a new menu listed and checked. The checkbox indicates that this menu is in use. You can also see that on your document, there is a floating toolbar. Now click the Commands tab. Click Macros in the Categories list. You will see your macro listed in the commands list as Normal.NewMacros.MacroName.
Drag this macro from the Commands list to your new toolbar. The toolbar will change its appearance. Drag the item to where you’d like it on the Standard Toolbar.
To customise the appearance of your button, on the Tools menu, click Customize. With the Customize dialog box open, do the following: click the Normal.NewMacros.MacroName button on the toolbar. Then in the Customize dialog box, click Modify Selection, click Name on the shortcut menu, and then type over the current name with a new name. Click the Normal.NewMacros. MacroName button on the toolbar. And in the Customize dialog box, click Modify Selection, point to Change Button Image on the shortcut menu, and then click an image.
Bold Italics
Aren’t so many words and phrases bold and italicised? The two often go together. To bold-italicise something requires two actions, whether on keyboard or by mouse. Wouldn’t it be nice to have a toolbar button called “BI” that does it, or a single keystroke? Here’s a macro for just that.
Sub BI1()
Selection.Font.Bold = True
Selection.Font.Italic = True
End Sub
The macro above turns on bold and italics, but it can’t do any toggling-meaning you can’t turn off bold italics using the same macro. So here’s a longer macro that toggles our bold-italics procedure.
Sub BI2()
Dim BIStatus As Integer
BIStatus = 0
If Selection.Font.Bold Then BIStatus = BIStatus 1
If Selection.Font.Italic Then BIStatus = BIStatus 1
If BIStatus = 0 Then
Selection.Font.Bold = True
Selection.Font.Italic = True
End If
If BIStatus = 1 Then
Selection.Font.Bold = True
Selection.Font.Italic = True
End If
If BIStatus = 2 Then
Selection.Font.Bold = False
Selection.Font.Italic = False
End If
End Sub
The way we’ve written this macro, it will convert text to bold italics in two cases: if neither bold nor italics is on, or if either of them is on. If both are on, it removes both the bold and the italics. Inspect the code a little and you’ll get the hang of what’s going on, and then you can change the macro to what you want.
Most ASCII files-such as those you might download off the Net, and then need to put in Word in a presentable format-have a hard return, the equivalent of an actual [Enter] keypress, at the end of every line of the file. They also have two hard returns at the end of every paragraph, so you can’t just globally replace hard returns with an empty value. The macro that follows formats a file so there are only hard returns at the end of paragraphs:
Sub RemoveHardReturns()
Remove1 “^p^p”, “{|}”
Remove1 “^p”, ” {@}”
Remove1 ” {@}”, ” ”
Remove1 “{@}”, ” ”
Remove1 “{|}”, “^p”
End Sub
Sub Remove1(FromWord$, ToWord$)
Set myRange = ActiveDocument.Content
myRange.Find.ClearFormatting
myRange.Find.Execute FindText:=FromWord$, ReplaceWith:=ToWord$, _
MatchCase:=0, Replace:=wdReplaceAll
End Sub
Parenthesise Easily
When you need to parenthesise a lot of words in a document, it’s convenient to just select the word using your mouse and then pressing a shortcut key to parenthesise the word. Here’s a simple macro that will parenthesise selected words for you.
Sub Parenthesise()
Dim iCount As Integer
iCount = 1
While Right(Selection.Text, 1) = ” ” Or
Right(Selection.Text, 1) = Chr(13)
Selection.MoveLeft Unit:=wdCharacter,
Count:=1, Extend:=wdExtend
iCount = iCount 1
Wend
Selection.InsertAfter “(”
Selection.InsertBefore “)”
Selection.MoveRight Unit:=wdCharacter,
Count:=iCount
End Sub
Note that you could just as well use square brackets or curly braces in the macro. Any spaces or paragraph marks at the end of your selection are compensated for. When you are done running the macro, the insertion point is left at the end of the original selection.
Easy Quotation Marks
Similar to the above macro, the following one inserts quote marks around selected text. The macro determines the proper type of quote marks to use, depending on whether you have Word’s SmartQuotes feature turned on or not.
Sub Quotise()
Dim sBegQ As String
Dim sEndQ As String
If Options.AutoFormatAsYouTypeReplaceQuotes Then
sBegQ = Chr(147)
sEndQ = Chr(148)
Else
sBegQ = Chr(34)
sEndQ = Chr(34)
End If
Selection.InsertBefore sBegQ
Selection.InsertAfter sEndQ
End Sub
Correcting A Document #1
In some grammatical circles, it is standard to capitalise the first letter of the word immediately following a colon. In some others, it is not. If you belong to the first school of thought, you probably wish that Word included a feature to automatically capitalise the first word after a colon. The solution? Use a macro!
Sub MakeCaps()
With ActiveDocument.Range.Find
.ClearFormatting
With .Replacement.Font
.SmallCaps = False
.AllCaps = True
End With
.MatchWildcards = True
.Text = “: ([a-z])”
.Replacement.Text = “: 1”
.Execute Replace:=wdReplaceAll
End With
End Sub
There is one thing to take note of with this macro: it does not change the first character after a colon to an actual capital letter. What it does is, it changes the formatting of the colon, space, and first character to all caps. This means that the character, even though lowercase, is displayed by Word as uppercase.
Easily Change Colours
If you are developing a document that uses colours to signal special meanings to the reader, it can be a real pain to use the menu again and again to change colours in the text. This is an invitation for a macro, and here it is: it changes the colour of the selected text to red.
Sub MarkAsRed()
If Selection.Type = wdSelectionNormal Or _
Selection.Type = wdSelectionBlock Then
Selection.Font.ColorIndex = wdRed
Else
Beep
End If
End Sub
Naturally, you can use this macro for any colour besides red, but the value above is “wdRed”; you need the values for the colour you need. Find alongside a table that gives you the values for different colours.
Text Colour What To Use
Auto wdAuto
Black wdBlack
Blue wdBlue
Cyan wdTurquoise
Green wdBrightGreen
Magenta wdPink
Red wdRed
Yellow wdYellow
White wdWhite
Dark Blue wdDarkBlue
Dark Cyan wdTeal
Dark Green wdGreen
Dark Magenta wdViolet
Dark Red wdDarkRed
Dark Yellow wdDarkYellow
Dark Grey wdGray50
Light Grey wdGray25
Did you know that there is a shortcut in Word for changing the capitalisation of text? You can quickly change between lowercase, uppercase, and initial caps: all you need to do is to select the text you wish to alter, and then press [Shift] [F3]. Pressing the key sequence cycles through the three cases.
If you are working with a large document where you want to change the case of the words in all occurrences of a particular style, you’ll need a macro. Say you have a document in which Heading Level 1 was originally intended to be in ALL CAPS, and now, you need to change it to Sentence Case. The following macro will search for all occurrences of the Heading 1 style and change it to Sentence Case.
Sub ChangeTextCase()
Selection.HomeKey Unit:=wdStory
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
Selection.Find.Style = ActiveDocument.Styles(“Heading 1”)
Selection.Find.Execute
While Selection.Find.Found
Selection.Range.Case = wdTitleWord
Selection.Find.Execute
Wend
End Sub
Correcting A Document #3
Now here’s a long and somewhat cumbersome macro, but one that’s very useful once you’re done writing it. The idea is, you want only one space after the end of every sentence. Several people, as you would have found if you work with documents, leave two or more spaces after sentences. Now this can be remedied by replacing all double spaces by single spaces, but it involves more than one keystroke. Besides, what if there are intentional double spaces in, say, a table?
You can use the following macro to get rid of unwanted spaces at the end of sentences:
Sub NoMoreThanTwo()
Call TheChange(“Normal”, “.”)
Call TheChange (“Normal”, “!”)
Call TheChange (“Normal”, “?”)
End Sub
Sub TheChange(StyName As String, PuncMark As String)
Selection.HomeKey Unit:=wdStory
Selection.Find.ClearFormatting
Selection.Find.Style = ActiveDocument.Styles(StyName)
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = PuncMark & ” ”
.Replacement.Text = PuncMark & ” ”
.Forward = True
.Wrap = wdFindContinue
.Format = True
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Text = PuncMark & ” ”
Selection.Find.Execute Replace:=wdReplaceAll
End Sub
This macro will only take extra spaces out of paragraphs formatted in the Normal style, which ensures you won’t mess up the formatting in tables or other elements where you might want extra spaces after full stops, exclamations, and question marks. You can change the type of punctuation searched for, and search in different style paragraphs-just look in the first macro, which explains itself.
Freezing Numbered Lists #1
You have, in all probability, used Word’s numbered list feature. As you’re selecting parts of a document and get to a numbered list, you’ll see that the numbers don’t get selected. In addition, as you add and delete points to the list, the existing items get renumbered appropriately. This can sometimes be an annoyance, in the case where you want a list to be set as it is with the existing numbers, and you want to add more items-say some with a new numbered beginning.
The brute-force way of dealing with this involves using the Paste Special feature. You first select the list that you want as permanent, and press [Ctrl] [C] to copy the list. Then choose Paste Special from the Edit menu. This will cause the Paste Special dialog box to pop up. If you choose “Unformatted Text” and click OK, the list should get pasted as it is, with the numbers “frozen”-Word will convert the numbering to text. However, you’ll lost any formatting you may have had in the items in the list. To not lose formatting, you’ll need to use the macro in the next tip.
If you want a numbered list frozen as above but without losing formatting (except in the numbers themselves), here’s the macro to use:
Sub NumberedList()
Dim lp As Paragraph
For Each lp In ActiveDocument.ListParagraphs
lp.Range.ListFormat.ConvertNumbersToText
Next lp
End Sub
Note that selecting text and running the macro will affect the numbering in the entire document-they will all be frozen!
Deleting A Page
If you want a quick way to delete the current page, there’s a simple macro:
Sub DeletePage()
ActiveDocument.Bookmarks(“Page”).Range.Delete
End Sub
You can now easily delete the current page, that is, the one in which the insertion point is located. Note that the macro decides what a page is based on the current pagination of your document, which is affected both by the printer driver you’re using and by other formatting properties.
Deleting All Comments
When a team is working on a document, adding text and comments, you could end up with a file with a lot of comments from different reviewers and writers. So what do you do when you want the document finalised and uncluttered, free of all comments?
The simple way to do this is to click the upside-down triangle next to the Reject Change / Delete Comment button. A drop-down list of options will come up; choose “Delete All Comments in Document”.
(If the Reviewing Toolbar is not displayed, go to View > Toolbars, and check Reviewing.)
The straightforward way, unfortunately, might not get rid of all comments in the documents. This happens if the document is complex-for example, if some reviewers have turned on Track Changes and then commented, and some others haven’t. You’ll need a macro to handle such cases:
Sub DeleteAllComments1()
For Each cmt In ActiveDocument.Comments
cmt.Delete
Next
End Sub
This will get rid of all comments in all cases.
Pretty Fractions
You’ve seen that Word only converts 1/2 and 1/4 into their nicer-looking forms, but not fractions like 2/7 and 3/10. The very helpful macro that follows will format any fraction this way-select the characters that make up the fraction and run the macro.
Sub ConvertToFraction()
Dim fractionbit As Range
Dim iSlashPlace As Integer
With Selection
iSlashPlace = InStr(.Text, “/”)
Set fractionbit = ActiveDocument.Range _
(Start:=.Start, End:=.Start iSlashPlace-1)
fractionbit.Font.Superscript = True
Set fractionbit = ActiveDocument.Range _
(Start:=.Start iSlashPlace, End:=.End)
fractionbit.Font.Subscript = True
End With
End Sub
What the macro does is, it formats the portion of what you selected before the slash as a superscript, and the portion after the selection as a subscript
Using The Numbers #1
Think of when you have values such as “3.2 GHz” in numerous cells. You obviously can’t use that “3.2” as it is-you’ll need to “extract” it from the full string. There happens to be a way to do this. Assume the entries all consist of a number followed by a space, followed by some characters. You can perform the extraction by following these steps: first, make sure there’s a blank column to the right of the entries. Then select the entries. Choose “Text to Columns” from the Data menu. Excel will display the first step of the Convert Text to Columns Wizard.
Select the “Delimited” radio button and click Next. Excel will display the second step of the wizard. Now, making sure the Space checkbox is selected, click Next. The third step of the wizard will be displayed; click Finish.
Excel splits the entries into two columns, with the numbers in the left one and the alphabetic characters in the right. You can then run any functions on the numeric values!
Using The Numbers #2
If it doesn’t seem a good idea to separate the data into columns-for example, if it might cause problems with others using the worksheet-then a different approach is needed. There are two ways you can do the extraction mentioned in the previous tip.
You can use this formula on individual cells:
=VALUE(LEFT(A1,LEN(A1)-4))
The LEFT function strips off the three rightmost characters (the space and the “GHz”) of whatever is in cell A1, and then the VALUE function converts the result to a number. You can then use this result as you would use any numeric value.
Using The Numbers #3
On the same problem, if all you want to do is sum the column containing your entries, you could use an array formula. Enter the following formula into a cell:
=SUM(VALUE(LEFT(A1:A10,LEN(A1:A10)-4)))
You need to make sure you actually enter the formula by using [Shift] [Ctrl] [Enter].
Using Names
Excel allows you to define names that can refer to formulas or constants. This can be useful in several cases: suppose you use a constant, say an interest rate equal to 12 per cent, quite often in your worksheet. To define a name for that 12 per cent, you’ll need to do the following: first, select the Name option from the Insert menu and choose Define from the sub-menu. Excel will display the Define Name dialog box. Change the Refers To field at the bottom of the dialog box so that it contains the desired formula-that is, “=12%”. Click on Add, and your name will have been defined. Click OK.
You can now use the constant in formulas. For example, if you used “digit” in the Name field, you can now say “=(50*digit)” in a cell, and you’ll get 6.25 in that cell.
Using An Input Mask…
When you’re keying in times into cells using the numeric keypad, what slows you down is the need to enter the colons. There happens to be a way to use an input mask so you can enter values such as 6:30 by typing in just “630”! Use the following procedure:
First select the cells you want to use for inputting the time. Then go to Format > Cells. Under the Number tab, in the Category list, choose Custom. Replace whatever is in the Type box with #”:”00 (a hash sign, a quote mark, a colon, another quote mark, and two zeroes). Click OK. You can now enter your times using only digits!
…And Then A Formula
The problem with the above tip, of course, is that the cells don’t really contain times. If you entered “630” for 6:30, it doesn’t contain 6:30 as a time-it contains six hundred and thirty. As a result, you can’t use the contents of the cell in time calculations.
In order to get around this, you can use a separate column to show the entered numbers converted into a time. You’ll need a formula for this. Say the time you entered is in cell B1; use the following:
=(INT(B1/100)/24) ((B1-(INT(B1/100)*100))/1440)
Now you just need to format the cell that contains this formula so it displays a chosen time format.
[Alt] [Enter] In A Formula
When you’re entering data in a cell, you can use [Alt] [Enter] to start a new line within the current cell. You may want to create a new line in a formula as well, just as if you had pressed [Alt] [Enter].
Say you’re working with the formula that concatenates text values: =A1 & A2 & A3, and you need a way to make it appear on separate lines. You can use the actual character code that Excel uses when you press [Alt] [Enter], as in the following formula:
=A1 & CHAR(10) & A2 & CHAR(10) & A3
The “CHAR(10)” inserts a line feed character. If you don’t see the results on separate lines, it’s because you haven’t turned on wrapping for the cell. You’ll be seeing a square box where the line feed character is located. To see the results of the formula on separate lines, go to Format > Cells > Alignment and check the Wrap Text box.
Numbers With Signs #1
As you work with a large set of numbers consisting of both negative and positive values, you might sometimes want to separate them into columns based on their sign. There are two ways to do this, the first of which is described in this tip.
Use a formula in the columns to the right of the column with mixed values. If the mixed column is A, place this formula in the cells of column B:
=IF(A2>0,A2,0)
The result of this will be that column B will only contain values greater than zero. Then, in column C, use this formula:
=IF(A2<0,A2,0)
Column C will only contain values less than zero. So what you have is two new columns of the same length as the original column. Column B is the same as column A with negative values replaced by zero, and column C is the same as column A with positive values showing up as zero.
Numbers With Signs #2
Starting from the above tip, say you want columns that only contain negative or positive values, with no zeroes. You’ll need to use Excel’s filtering capabilities. Say the mixed values are in column A, with the column heading in A1. You’ll need to follow these steps:
In cell E2, place the formula “>0”. Select any cell in the mixed values of column A. Choose Data > Filter, then choose Advanced Filter from the resulting sub-menu. Excel will display the Advanced Filter dialog box. The List Range should already be filled in, representing the range of mixed values in Column A.
Now, make sure the “Copy to Another Location” radio button is selected. Select the Criteria Range box, and with the mouse, select cells E1:E2. Select the “Copy To” box and click once in cell B1. Then click OK. Column B now contains cells that are greater than zero.
In E2, place the formula “<0”. Then, once again choose Data > Filter, and choose Advanced Filter from the sub-menu that comes up. Excel will display the Advanced Filter dialog box. The settings in the dialog box should be the same as the last time you used them. Select everything in the “Copy To” box, and then click once in cell C1. Then click OK. Column C will now contain cells less than zero. Of course, you can now delete the cells at E1:E2.
What If…
Suppose you want to calculate how much money you will need to deposit in a savings account for each of 12 months in order to have Rs 10,000 in it at the end of one year. You know the interest rate, but you’re not sure of the amount you need to put in. You can use a future value calculator for this (FV). In cell A4 is your deposit amount which is unknown right now. Say cell A5 contains your interest rate of 5 per cent, and cell A6 is the number of months (12). In cell A7, you can enter the formula:
= -FV(A5,A6,A4)
If you wanted to determine how much you needed to deposit in the account each month, you could repeatedly change the deposit value (A4) until you got close to the desired goal. Here’s where the goal seeking tool comes in. Here are the steps:
First select the Future Value cell (the one at A7). Choose Goal Seek from the Tools menu. This displays the Goal Seek dialog box. The Set Cell field will have already been set to A7. Now move the insertion point to the “To Value” field and change it to 10000. This is the amount you want to have at the end of two years. Then move the insertion point to the “By Changing Cell” field and click on cell A4 (the cell that will contain your regular deposit amounts). Click OK. Excel tells you it has found a solution. Click OK again. Your worksheet will reflect the solution.
Evaluating Formulas
How exactly does Excel arrive at a particular result? This can be hard to determine, particularly if the formula is complex. Thankfully, Excel provides a tool to help figure out what is going on when it evaluates a formula. To access this tool, first select the cell containing the formula you want to evaluate. Choose Tools > Formula Auditing. Excel will display a sub-menu. From the sub-menu, choose Evaluate Formula. Excel will display the Evaluate Formula dialog box.
The full formula from the cell will be shown with part of it underlined. This underlined area represents the part of the formula that will next be evaluated, and this allows you to see what intermediate steps Excel follows in arriving at a result. Every time you click the Evaluate button, Excel replaces the underlined portion of the formula with a result.
Nothing you do with the formula evaluator affects the formula itself. When you’re done using the Formula Evaluator, click Close.
Counting Non-Blank Cells
You might already know that you can use the COUNTBLANK function to return the number of blank cells in a range. If you want to count the number of non-blank cells in the range, you could use:
=COUNTA(A1:A10)
The problem here is that this doesn’t return the complementary value to what COUNTBLANK returns. This is because COUNTBLANK and COUNTA treat formulas differently: COUNTBLANK includes, as blank, formulas that return a blank value. COUNTA does not consider such cells blank (even though a blank is returned), so it includes them in its count.
If you define non-blank cells to be those that are not returned by the COUNTBLANK function, you’ll need to use the following formula:
=(ROWS(A1:B10)*COLUMNS(A1:A10))-COUNTBLANK(A1:A10)
How this works is pretty straightforward, so we’ll refrain from explaining it further.
SYMBIAN TRICKS
The Symbian OS in its Series 60 (now known as S60) avatar is by far the most popular, powering a majority of smartphones around the world. Whether you have a Nokia 6600 or an N70 or a Samsung X700, these tricks should provide a richer experience with that phone, besides demonstrating the power of the S60 platform.
Check Your Firmware Version Number
You can check your phone’s model number, the OS version and its date of release on your Symbian Series 60 phone by pressing the key combination *#0000#. This is particularly useful in case you plan a firmware upgrade and want to know what version you are currently using.
The End Call key
The red button on your Symbian cell phone that is used to end an active call can also be used to quick return to the main menu. Say you are using notepad, and need to type a quick SMS. Use the end key to be taken to the startup (home) screen. The notepad application is simply minimized to memory and not closed. Later on, when you wish to get back to the notepad where you were working all you need to do is access the task manager and switch to the notepad task. This works for all applications, and you can switch between several applications in this way.
Selecting multiple files
This tip is for all those who have a huge inbox of say 300-plus SMSes, and want to delete many at a time. Keep the edit button pressed (the one with the pencil), simply scroll through your messages, and you’ll find that they get a check/tick mark besides them. To keep a message you want, simply let go of the edit button while scrolling past it. Pressing the delete ([C]) key will delete all the messages that have been check marked. This trick works for all files.
Deep Format
Sometimes your S60 phone may boot up to the Nokia screen and nothing else. This little tip is also useful when you are unable to delete some files from your C: drive (you will need a utility like FE Explorer installed to view drive partitions) available for download at http://www.gosymbian. com/FE_download.html. There is a hard format option which works on the newer crop of S60 phones. Before following the following steps please ensure that you have at least 75 percent of battery charge left, if this format process is interrupted you’ll be compelled to make a trip to your nearest Nokia center for a complete software install.
Shutdown your S60 phone, and remove the memory card. Now press the green (call) key, your [*] (asterisk) key and the number [3]. Keep them pressed while you power on your phone. Do not release any of these three keys until you see a formatting screen appear. After the format is complete your cell phone will be back to its factory default state.
Smart memory card removal
When removing your memory card, always power down the phone and remove it. Though some S60 phones sport hot swap, you will find the themes and icon arrangement switches to default after you put the memory card back. This happens with all S60 phones simply because most themes would be stored on your card by default. When you power off the phone and remove the card, your OS cannot determine that it’s been removed. So power down and swap away, your current theme and icon settings will survive the MC swap.
Startup in Safe Mode
Starting up in safe mode is very useful for basic debugging-diagnosing whether a memory card is corrupt, or if you are getting a system error.
Safe mode does is prevents third-party applications on your phone from auto starting, making troubleshooting easier. To boot into safe mode simply power down your S60 phone and power up again with the edit (pencil) key pressed. Now you’ll be asked for your lock code if you have one. If not, you’ll boot into safe mode, and can double check this by keeping the menu button pressed and checking the running programs.
Auto Saving SMSes
This tip is for you if you have an older S60 phone say an N-Gage, or an X700. You cannot save SMSes to your MMC (this feature is factory disabled because these phones don’t support hot swapping, though it can be done on 6630s and later phones).
Backup your messages before this, because even though they’re on your phone, this trick doesn’t move current messages from your C: drive (we’re assuming you have FE explorer installed), it just changes the default path for future messages, you won’t be able to see them, unless you change the path again. In case you are doing this to save space, after backing all the SMSes up, delete them from your C: drive.
You’ll need to install MsvDriveE (available at http://www.carareok.com/ bluemint/index.php/p98).
Run this application just once, change your message storage to E: (MMC) from C: (phone mem). You can create folders as required. To switch back to the default phone memory follow these steps in order-simply switch off your phone, remove the MMC card, switch the phone back on, navigate to messaging and you’re done. Oh, you can re-insert your MMC card now!