Outlook 2016 Blue Bar Vanishes

Posted on by July 20, 2018
1

A client recently told me how much she hated Outlook 2016 because it made new emails too hard to distinguish from older ones she had read. I was surprised since on my copy, this is not a problem.

I found two settings that made a difference.

Setting the Font Color for unread messages helps to make them stand out:

  1. On the View menu, select View Settings on the Current View group.
  2. Select Conditional Formatting
  3. Select which message types to change. Unread messages is what you want.
  4. Click the Font button and select a font/font color from the Font Menu.
  5. Click OK til you are done.

Changing the width of the Unread Messages pane determines whether the blue bar for unread messages appears:

  1. In the Messages Window, put your cursor on the scroll bar to the right of your messages.
  2. Move it to the right until the double bar with arrows appears.
  3. Drag this to make the messages window wider or smaller.
  4. Voila! At some point, the blue bar vanishes. Making the width smaller makes it reappear. Who knew?

Floating Point Errors in Excel VBA

Posted on by December 1, 2017
2

I recently had a project where I had to total dollar figures in two columns and compare that total to the reported total in a third. Easy, right?

The VBA code was pretty simple:

If Cells(ActiveCell.Row, 4).Value + Cells(ActiveCell.Row, 6).Value <> _
     Cells(ActiveCell.Row, 8).Value Then
        Cells(ActiveCell.Row, 10).Value = “Totals don’t match”
        '(e.g. if D2 + F2 <> H2 Then J2 contains an error message)
End If

In a set of 70 rows, I kept getting two rows where the totals were equal, but reported as not equal.

I tested the total and match in a separate cell. Correct answer.

I tested the total and match in VBA and got the wrong answer.

I checked for stray decimals. I checked for numbers as strings. I checked for hidden values. I rounded each number in code. I found one column had Wrapped Text and fixed that. Still, all wrong answers.

I copied/pasted values only to remove any formatting issues. Wrong answer.

I deleted the numbers and inserted two other numbers and their total. Correct answer.

I retyped the actual numbers and ran the code. Wrong answer.

I found several articles about precision issues. (https://blogs.office.com/en-us/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/) but my numbers were way smaller. I remembered how you had to round numbers without a Round function: Multiply by 100 to turn into an integer, then divide by 100 to get the original value.

I changed the code to convert the numbers to long integers and tested the long integers. (They had to be Longs not Integers due to the 32,767 limit on Integer values.) Success.

Dim lngFirst, lngSecond, lngTotal as Long

lngFirst =  Cells(ActiveCell.Row, 4).Value * 100
lngSecond = Cells(ActiveCell.Row, 6).Value * 100
lngTotal = Cells(ActiveCell.Row, 8).Value * 100

If lngFirst + lngSecond <> lngTotal Then
   Cells(ActiveCell.Row, 10).Value = “Totals don’t match”
End If

If you want to try it, these numbers contain the floating point error. Note that this will not give an error in Excel, only in VBA code. The first row is the Column Letters.

D E F G H I J
1407.94 1.66 1409.60 “Error Message”

Dates, Macs and PCs

Posted on by October 13, 2017
Reply

I had a question from a friend about dates today. She had 2 spreadsheets, both showing 1/1/2017 as a date. When she copied one to another, the dates were all wrong–they were off by 4 or more years.

Here is an example of what her problem was:

The problem is that 1900 is not a leap year, according to the standard rules:

A normal year is defined as 365 days. (Plus a quarter day which is resolved by having a leap year and adding a day (Feb 29) every four years.) A year is a leap year if:

  1. it is divisible by 4.
  2. It is not divisible by 100.
  3. It is divisible by 400 if it is a centurial year. (1900, 2000, 2100, etc.)
  4. So 1700, 1800 and 1900 were not leap years, but 2000 was.

Steve Jobs insisted on the Excel numbering system starting on 1/1/1904 to avoid this since the PC started numbering from 1/1/1900 and included 2/29/1900 as a valid date.

The PC Excel File | Options | Advanced section on Excel lets you choose Use 1904 numbering system. I assume that the opposite is true on a Mac.

See Neal deGrasse Tyson on the subject: https://www.quora.com/Why-are-years-such-as-1700-1800-1900-not-leap-years-even-though-they-are-divisible-by-4-but-1600-and-2000-are

The RAND Function

Posted on by September 25, 2017
Reply

In a class, someone asked about a random number generator. I replied with information about previous iterations of the RAND() function built into Excel. In researching it, I realized that Excel has moved on. Hence this post.

=RAND() in a cell will display a random number between 0 and 1. The function is volatile, meaning that each time you press Enter to edit a cell or press F9 to recalculate the workbook, the RAND() function displays a different random number.

If you want the number between 0 and 100, use the formula: =RAND()*100. This will display the number, but it will contain a fraction. You can either embed it in a ROUND() function (=ROUND(RAND()*100),0) or use the RANDBETWEEN function, which is preferable.

RANDBETWEEN lets you enter a upper and lower number and generates random numbers between and including those two numbers. The formula =RANDBETWEEN(6,12) will generate whole numbers between 6 and 12.

For a more complex application, suppose you have a list of Employees, including their Employee ID number and want to select a person randomly from the list. If no one can be selected twice, i.e., once you’re selected, you cannot be selected again, you might combine a VLOOKUP with RAND and a SELECTED column as shown above.

In the example above, cells A1:D30 contain a list of employees, with their ID number in A and a Selected column in Y. Press F9 to select a winner.

The function in G1 selects a random number between 1003 and 1030. (The formulas appear in G5 and G6.) If your list contains holes (i.e., numbers not used, such as 1000, 1001, 1002 in this list), the message “Press F9 again” will appear and will instruct the user to try again. If the person has already won, the message “Already Won. Try Again.” will appear. Keep pressing F9 until you have a winner.

Important: once you have a winner, write the number down because if you do any editing of the spreadsheet (such as marking the winner), the RAND function will recalculate and you’ll lose the first winner.

The formula in G1 selects a random number between 1000 and 1030.

The formula in G2 looks complex, but it breaks down simply:

First, you look up the ID number. If it is not found

“ISERROR(VLOOKUP(G1,$A$2:$D$29,3,FALSE),“Press F9 Again”)”

display the message “Press F9 Again”.

Otherwise, it is found so you look for a Y in Column D:

IF(VLOOKUP(G1,$A$2:$D$29,4,FALSE)=”Y”,”Already Won. Try Again”

If the “Y” is found, display the message ”Already Won. Try Again”.

If there is no “Y”, display the winner’s last name: “VLOOKUP(G1,$A$2:$D$29,3,FALSE)”

Fix Gene Names Turned into Dates Problem

Posted on by August 30, 2016
Reply

I became aware of this issue in http://www.sciencemag.org/news/sifter/one-five-genetics-papers-contains-errors-thanks-microsoft-excel?utm_source=newsfromscience&utm_medium=facebook-text&utm_campaign=excel-6929.

The following user-defined Function is a start. You can add to the Case statement to add whatever gene names you like, with the caveat that once Excel finds a match, it will jump out of the Case statement.

Add this to your Personal Macro Workbook and save it.

You can then find the function in the User Defined Functions category of the Function Wizard. Add the function to a separate column, reference the first cell with the problem, copy it down the column, then Copy | Paste | Values into the same column to convert the function into text. It should ignore any name that has not been turned into a date. (For example, if cell B2 contains the first Gene name, add the function =FixGeneName(B2) into the next blank column at row 2 and copy it down.)

This is a start at solving the problem. It should work internationally, using the Language settings for the PC. It should work on a Mac, assuming you have a version of Office that includes Visual Basic.

Function FixGeneName(GeneText As String) As String
Dim intMonth As Integer
Dim intDay As Integer

 On Error Resume Next
 intMonth = month(GeneText)
 intDay = day(GeneText)
 
 Select Case intMonth
 Case 1: FixGeneName = "JA" & intDay
 Case 2: FixGeneName = "FE" & intDay
 Case 3: FixGeneName = "MA" & intDay
 Case 4: FixGeneName = "AP" & intDay
 Case 5: FixGeneName = "MA" & intDay
 Case 6: FixGeneName = "JU" & intDay
 Case 7: FixGeneName = "JU" & intDay
 Case 8: FixGeneName = "AU" & intDay
 Case 9: FixGeneName = "SEPT" & intDay
 Case 10: FixGeneName = "OC" & intDay
 Case 11: FixGeneName = "NO" & intDay
 Case 12: FixGeneName = "DE" & intDay
 Case Else:
 FixGeneName = GeneText
 End Select

 On Error GoTo 0
 
End Function

View Documents Side-by-Side in Windows 10

Posted on by August 1, 2016
Reply

Windows 10 makes it easy to view two documents side-by-side.

Click into one of the docs, and press the Windows key + left arrow.

index

The windows will slide to the left side, taking up exactly half of the screen. All other open applications will appear as thumbnails on the right.  Click once on the document you want to fill the right side of the screen.

Both documents are now side-by-side.

To Avoid Embarrassment, Create a Rule in Outlook to Delay Sending All Messages for 2 Minutes

Posted on by April 28, 2016
Reply

We have all hit the Send button a little too soon, and then wished we could somehow retrieve the message.

If you create a rule in Outlook that delays sending all email by 2 minutes, you can simply delete the message from your Outbox if you make a mistake.  Here’s how:

  1. File tab / Manage Rules and Alerts
  2. New Rule
  3. Start from a blank rule / Apply on messages I send
  4. Don’t select anything in the Select Conditions window.  When you hit OK, it will warn you that the rule will affect all messages, which is OK.
  5. In the Select Actions window, select defer delivery by number of minutes.
  6. In the lower part of the window, click a number of, and fill in the number you want.
  7. Give the rule a name, if you like, and click Finish.

If you want to include a way to bypass the rule, in case you want to send something immediately, just include an exception. When you get to the exception window, select except if the subject or body contains specific words, and then click the lower part of the window to select a symbol.  I use an exclamation point. If I want to bypass the 2-minute delay and send something immediately, I just start the subject with an exclamation point.

 

Hiding Data on an Excel Spreadsheet

Posted on by April 16, 2015
Reply

Suppose you need to hide a small section of data in Excel, but you still want to calculate with it.

  1. Highlight the cells, right-click, and select Format Cells.
  2. Click the Number tab, and select the Custom category on the left.
  3. Type three semicolons (;;;) in the Type: field, and click OK.

The numbers are invisible, but you can still use them.

Save an Excel Chart as a Template

Posted on by April 16, 2015
1

To save a chart as a template:

  1. Create your chart, and then right click it.
  2. Select Save as Template… You’ll save a file with a CRTX extension in your default Microsoft Excel Templates folder.

To create a new chart based on the template:

  1. Select the data you want to chart.
  2. Click the Insert tab / Recommended Charts, the All Charts tab, and the Templates folder.
  3. In the My Templates box, pick the one to apply, then click OK. Some elements, like the actual text in the legends and titles, won’t translate unless they’re part of the data selected. However, all fonts, colors, graphics and series options will be applied to the new chart.