En, Em, Eh, What?

There are different types of dashes: hyphen, en dash, and em dash. What is the difference between them?
Hyphen (-): This is the width of one digit. It is used to hyphenate words that break across a new line, or to join compound words. Ex. My, this professor is long-winded.
En Dash (–): This is the width of the letter ‘n’. It is used to indicate a range of values. Ex. I will be on vacation from November 6–10.
Em Dash (—): This is the width of the letter ‘w’. This is used to indicate a break in thought. Ex. I was eating pizza—and what a good pizza it was—with all my favorite toppings.
Using courier font we can get a better idea of the widths:
0 n m
– – —
[Sources: Fonts.com and Wikipedia]

Excel Macro for Background Color

I was writing an Excel Macro and I needed to set the background color of a cell based on the text value. To do this I wanted to use Conditional Formatting, and I found out that you cannot (AFAIK) setup Conditional Formatting from inside of a macro. But, you can create a macro that loops through a range and sets the formatting.
Here is how:
  1. Open the VB editor (Alt + F11)
  2. Open ThisWorkbook under Microsoft Excel Objects
  3. Add the following code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Set myRange = Range(“C4:C104”)
    For Each Cell In myRange
        If Cell.Value = “n” Then
            Cell.Interior.ColorIndex = 3
        End If
    Next
End Sub
Anytime  a change is made to the workbook, the macro is called. It checks a range of cells, and then if the text is ‘n’ it sets the background color to 3. But what is ‘3’?
Excel uses an object called ColorIndex which takes an integer as its argument. The integer is one of 56 colors, given by the following table (a link to the source is below):

[Color 0] [Color 0] [Color 15] [Color 15] [Color 30] [Color 30] [Color 45] [Color 45]
[Color 1] [Color 1] [Color 16] [Color 16] [Color 31] [Color 31] [Color 46] [Color 46]
[Color 2] [Color 2] [Color 17] [Color 17] [Color 32] [Color 32] [Color 47] [Color 47]
[Color 3] [Color 3] [Color 18] [Color 18] [Color 33] [Color 33] [Color 48] [Color 48]
[Color 4] [Color 4] [Color 19] [Color 19] [Color 34] [Color 34] [Color 49] [Color 49]
[Color 5] [Color 5] [Color 20] [Color 20] [Color 35] [Color 35] [Color 50] [Color 50]
[Color 6] [Color 6] [Color 21] [Color 21] [Color 36] [Color 36] [Color 51] [Color 51]
[Color 7] [Color 7] [Color 22] [Color 22] [Color 37] [Color 37] [Color 52] [Color 52]
[Color 8] [Color 8] [Color 23] [Color 23] [Color 38] [Color 38] [Color 53] [Color 53]
[Color 9] [Color 9] [Color 24] [Color 24] [Color 39] [Color 39] [Color 54] [Color 54]
[Color 10] [Color 10] [Color 25] [Color 25] [Color 40] [Color 40] [Color 55] [Color 55]
[Color 11] [Color 11] [Color 26] [Color 26] [Color 41] [Color 41] [Color 56] [Color 56]
[Color 12] [Color 12] [Color 27] [Color 27] [Color 42] [Color 42]
[Color 13] [Color 13] [Color 28] [Color 28] [Color 43] [Color 43]
[Color 14] [Color 14] [Color 29] [Color 29] [Color 44] [Color 44]
A more complex example is shown below. This is used in a ToDo sheet. It checks the “Done” column (C) to see if  the cell is marked ‘y’ or ‘n’ and sets the corresponding color. If the task is done (C=’y’) then it also sets the cell adjacent tothe color gray with strikethrough text.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Set myRange = Range(“C4:C104”)
    For Each Cell In myRange
   
        If Cell.Value = “n” Then
            Cell.Interior.ColorIndex = 3
        End If
        If Cell.Value = “y” Then
            Cell.Interior.ColorIndex = 10
        End If
        If Cell.Value “n” And Cell.Value “y” Then
        Cell.Interior.ColorIndex = xlNone
        End If
       
        ‘ add strikethrough
        Dim myRow As Integer
        Dim myCol As Integer
        myRow = Cell.Row
        myCol = Cell.Column – 1
       
        If Cell.Value = “y” Then
            Cells(myRow, myCol).Font.Strikethrough = True
            Cells(myRow, myCol).Interior.ColorIndex = 15
        End If
       
        If Cell.Value = “n” Then
            Cells(myRow, myCol).Font.Strikethrough = False
        End If
    Next
End Sub
Happy coding 🙂

[Source MVPS site]

Ready, Set, No-Go

You were supposed to leave work 15 minutes ago but you squeezed in some last minute details and now you want to run. So, you save & close your applications, and then click shutdown on your fancy new laptop with Windows 7. You jump up and get ready to run, but then you see the dreadful message:
Do not unplug your machine
Installing update 1 of 15 ….
Now what? You sit and wait 20 minutes while it installs, looking at your watch every minute. Isn’t there some way around this? 
Actually there is. Instead of using the Shutdown from the start button, do this:
  1. Type Control-Alt-Delete
  2. In the bottom right corner then is a small power button. Click the arrow
  3. Choose Shutdown
The menu that opens gives you the option to Shutdown with Updates or just Shutdown.
[Source: MyTechGuide]

The Path

Here is a handy shortcut that I just found out about:

Under Windows 7, if you need to copy the path to a file, you just hold the Shift key, then right mouse click on the file. The menu that opens now has a new menu item in the middle called “Copy as path”. You can then use Paste to put the full path where ever you like.

What is a Lexophile?

1. lexophile – A lover of cryptic words

For those of you who like punny lists:

1. A bicycle can’t stand alone; it is two tired.
2. A will is a dead giveaway.
3. Time flies like an arrow; fruit flies like a banana.
4. A backward poet writes inverse.
5. In a democracy it’s your vote that counts; in feudalism, it’s your Count that votes.
6. A chicken crossing the road: poultry in motion.
7. If you don’t pay your exorcist you can get repossessed.
8. With her marriage she got a new name and a dress.
9. Show me a piano falling down a mine shaft and I’ll show you A-flat miner.
10. When a clock is hungry it goes back four seconds.
11. The guy who fell onto an upholstery machine was fully recovered.
12. A grenade fell onto a kitchen floor in France resulted in Linoleum Blownapart.
13. You are stuck with your debt if you can’t budge it.
14. Local Area Network in Australia : The LAN down under.
15. He broke into song because he couldn’t find the key.
16. A calendar’s days are numbered.
17. A lot of money is tainted: ‘Taint yours, and ‘taint mine.
18. A boiled egg is hard to beat.
19. He had a photographic memory which was never developed.
20. A plateau is a high form of flattery.
21. The short fortuneteller who escaped from prison: a small medium at large.
22. Those who get too big for their britches will be exposed in the end.
23. When you’ve seen one shopping center you’ve seen a mall.
24. If you jump off a Paris bridge, you are in Seine.
25. When she saw her first strands of gray hair, she thought she’d dye.
26. Bakers trade bread recipes on a knead to know basis.
27. Santa’s helpers are subordinate clauses.
28. Acupuncture: a jab well done.
29. Marathon runners with bad shoes suffer the agony of de feet.
30. The roundest knight at king Arthur’s round table was Sir Cumference. He acquired his size from too much pi.
31. I thought I saw an eye doctor on an Alaskan island, but it turned out to be an optical Aleutian.
32. She was only a whisky maker, but he loved her still.
33. A rubber band pistol was confiscated from algebra class because it was a weapon of math disruption.
34. The butcher backed into the meat grinder and got a little behind in his work.
35. No matter how much you push the envelope, it’ll still be stationery.
36. A dog gave birth to puppies near the road and was cited for littering.
37. Two silk worms had a race. They ended up in a tie.
38. A hole has been found in the nudist camp wall. The police are looking into it.
39. Atheism is a non-prophet organization.
40. Two hats were hanging on a hat rack in the hallway. One hat said to the other, ‘You stay here, I’ll go on a head.’
41. I wondered why the baseball kept getting bigger. Then it hit me.
42. A sign on the lawn at a drug rehab center said: ‘Keep off the Grass.’
43. A small boy swallowed some coins and was taken to a hospital. When his grandmother telephoned to ask how he was, a nurse said, ‘No change yet.’
44. The soldier who survived mustard gas and pepper spray is now a seasoned veteran.
45. When cannibals ate a missionary, they got a taste of religion.
46. Don’t join dangerous cults: Practice safe sects.

Empty Nest(ed) Tables

I had a large document with many, many tables.  I needed to make sure that none of the tables contained any sub-tables.  Macros to the rescue!


Sub removeNestedTables()
‘
‘ removeNestedTables Macro
‘
‘
    Dim myTable As Table
    Dim subTable As Table
   
    Dim ttCell As Word.Cell
   
    For Each myTable In ActiveDocument.Tables
        For Each ttCell In myTable.Range.Cells
            If ttCell.Tables.Count > 0 Then
                For Each subTable In ttCell.Tables
                    subTable.ConvertToText Separator:=”, “
                Next subTable
            End If
        Next ttCell
    Next myTable
End Sub

The Incredible Shrinking Sentence

The Cannonball Run (1981)
He’s my shrink. He was committed yesterday. 

If you have a block of code with long lines that you quote in a document, the long lines can wrap around to the next line which looks really bad and makes the code harder to understand.  To fix this problem, we start with a macro found over at WordTips and add a conditional statement so that it only is executed for a certain style (in this case Code Box). The macro reduces the font by ChangeSize and then checks if the paragraph fits on one line. It then repeats this process until it fits on one line.

Sub forceParaOneLine()
    Dim objPara As Paragraph
    Const ChangeSize = 0.5
   
    For Each objPara In ActiveDocument.Paragraphs
        If objPara.Style = ActiveDocument.Styles(“Code Box”) Then
            With objPara.Range
                While .Information(
wdFirstCharacterLineNumber) .Characters(Len(.Text)).Information(wdFirstCharacterLineNumber)
                    .Font.Size = .Font.Size – ChangeSize
                Wend
            End With
        End If
    Next objPara
               
End Sub
Note that if you have really long lines, this will reduce the font so that it is impossible to read.  To avoid this, I suggest using 2 styles in your document: one for code that can be shrunk, and one that is not shrunk.

Gmail: Going Label Crazy

Marion: [laughs] What is this stuff, Rene?
Belloq: [laughing as well] I grew up on this. It’s my family label.  
This morning I noticed that you can apply custom colors to labels in Gmail. I have used labels in the past, but the labels didn’t really stand out which reduced their usefulness.  With custom labels you can easily differentiate all the emails in your Inbox (work emails in one color, mailing list emails in another color, etc.)
Here’s how to use the custom colors. First, create labels and filers.
  1. Create a label. See instructions from Google Help.
  2. Create a filter to apply the label. See instructions from Google Help.

Now we need to apply custom colors to the labels. Click the little arrow next to the label that you want to change:

 When you click the arrow, you get a popup window like this one:
The color choices were a little too dark for my taste.  When I tried them, it was hard to see the text next to them because of the contrast.  You can click on Add custom color and choose a background and text color.
Now all your labels look just the way that you want them.
Happy labeling 🙂

Selecting a Text Block

I just came across the shortcut of the year for MS Word over at TechRepublic.  Normally, when selecting text, you select it in a continuous line.  You can expand the selection forwards or backwards.
But, did you know that you can select a text block?  This is one of the powerful features of Vim for those of you who have used Unix computers.
Just hold the ALT key while you click.

For example, if I have the following text:

REM First  line
REM Second line

REM Third  line

You can hold the ALT key and then select the first three letters of each sentence:
REM First  line
REM Second line

REM Third  line

You can also cut and paste a block of text. In the above example, one could cut the selection and then paste it in the middle of the first line, resulting in:

 First REM line
 SecondREM line

 Third REM line

Note that selection of text blocks does not work inside of tables.

[Source TechRepublic]