Author Archive | Chris Page

Windows 10 creator update causes Outlook and iCloud problem

I use MS Outlook 2016 on my PC for my email and also iCloud to synchronise my diary and contacts with my iPhone.  Along came the latest Windows 10 Creator update and after installation I was unable to open my Outlook Calendar and People folders.

There are numerous suggestions on what do to do out there on the Internet some even suggesting all Apple software should be uninstalled and re-installed. I didn’t want to go to that extent, so instead just tried repairing my iCloud installation.

Firslty, make sure that Outlook is not running, then in Windows 10 select Settings, Apps & Features. Scroll down and click on the iCloud entry, then select Modify. Finally, select the Repair option  and click on Next. Follow the dialog.  Easy!

Excel Cell, Row and Column Reference Secrets in VBA Code

Code VBA to Refer to Excel Cells, Rows and Columns Using Numbers

My favourite way of referring to ranges of Excel cells in VBA code is to use the Cells property and numbers. For example, the following code refers to cell C4 in the active sheet:

Cells(4, 3)

 

As this does not require any column letters it is very easy to substitute variable names in place of any numerical values, which means I can then use loops to cycle through different cells that are adjacent to each other (a lot of VBA in Excel is repetitive looping and processing or updating cells). So I could have two variables – one for row and one for column position and substitute something like this:

Cells(lngRow, intColumn)

 

Rows Only

If I want to refer to all the cells in a specific row, then I can use this:

Rows(4)

 

That code would refer to every cell in row 4.  Again I could substitute a variable like this:

Rows(lngRow)

 

Columns Only

If I want to refer to all the cells in a specific column, then I can use this:

Columns(3)

 

That code would refer to every cell in column C.  Again I could substitute a variable like this:

Columns(intColumn)

 

Interestingly, because columns are referred to with letters in the Excel User Interface I can use that in my VBA. So another way to refer to column C in my VBA could be:

Columns("C")

 

However, in that code it would be harder to substitute a numerical variable where I could loop through multiple columns. On the other hand to refer to more than one adjacent column I could use the letters. So to refer to columns C, D and E, I could use:

Columns("C:E")

 

Again, in that code would be impossible to substitute a numerical variable. There is a way to achieve this though using numerical variables. The secret is to use a range object and then supply the column numbers as the arguments. So the code below is the equivalent of using Columns(“C:E”):

Range(Columns(3), Columns(5))

 

With the is code I can now substitute numerical variables. So I could use:

Range(Columns(intFirstColumn), Columns(intLastColumn))

 

All of the code samples above refer to actions within the ActiveSheet.  If you need to refer to a specific sheet, then every object in the code needs to be fully qualified. So in order for the code immediately above to be used to refer to a sheet named Expenses I would need to use:

Worksheets("Expenses").Range(Worksheets("Expenses").Columns(intFirstColumn), _
Worksheets("Expenses").Columns(intLastColumn))

 

Of course this I could simplify the fully qualified code using a With..End With statement, so the example below shows me changing the value of every cell in columns C to E to the text “Chris”:

intFirstColumn = 3
intLastColumn = 5
With Worksheets("Expenses")
  .Range(.Columns(intFirstColumn), .Columns(intLastColumn)).Value = "Chris"
End With

Outlook Keyboard Shortcuts

Do you want some handy keyboard shortcuts when working in Outlook? Try these:

CTRL + 1 takes you to Mail

CTRL + 2 takes you to Calendar

CTRL + 3 Takes you to People

CTRL + 4 takes you to Tasks

CTRL + N starts a new item of the folder you are in

Windows 10 Default Browser Problems

Working on a new tablet with Windows 10 installed, I recently discovered that I could not set the default web browser to Firefox – my browser of choice. No matter what I did it seemed to only accept Microsoft Edge. In fact when I went to Settings, System, Default Apps all I saw was a plus icon and the choose a default prompt. Tapping the icon gave me a choice of browsers but selecting anything other than Edge failed.

So I decided to stay old school and went via Control Panel. In the Category View you can tap on Programs and then tap on Default Programs.  In the Small or Large Icons view, tap the Default Icons option. Either way you finally tap on the Set your default programs option that is eventually displayed.  Tap the browser of your choice and tap on the Set this program as default option on the right. It worked for me!

Week Numbers in Microsoft Project

Microsoft Project does not have a generic way of displaying week numbers, but some project managers may need to display what week in the year or week of the project a task date corresponds to.  What you have to do is create a custom text field then set it to display a formula. I’ve shown before you how to create a custom field but I’ll do this again here.

Displaying Week Numbers using a formula

Creating a Custom Text Field

To create a custom field, right-click on any of the existing column titles in the Gantt Chart grid and choose Custom Fields from the pop-up menu.

Custom Field dialog for week numbersIn the top-right drop-down selector choose Text.

Now click on the first available field in the list – here Text1 is selected – and then click on the Rename… button. Give the field a suitable name. here I’ve called it Week Number.

Next you need to select the Custom attribute of Formula  by clicking on the Formula button.Formula for week numbers

Here is a suggested formula to show the week number relative to the project’s start date:

"Week " & CStr(1+datediff("w",[Project Start],[Start]))

 

Here is an alternative formula to show the week number in relation to the calendar year of the year the project started:

"Week " & CStr(1+datediff("w",DateSerial(Year([Project Start]),1,1),[Start]))

 

Of course if you only want the week number and no text before it omit the    “Week ” &    in the formula.

Having now created the custom field, right-click on the column title where you want to display your new field and select Insert Field from the pop-up menu. Scroll down the list of field names and select your new field. As you renamed a Text field it will still appear with all the other default text fields in the listing so my example above would appear as Text1 (Week Number) in the list.

Project Calendars Renaming and Copying

How to Rename and Copy Project Calendars

The secret to renaming and copying project calendars between Microsoft Project files is to use the Organizer. This article explains how.

The Organizer is found by clicking on the File tab, then in the Info backstage area clicking on the Organizer button.

Once in the Organizer you need to click on the Calendars tab.

Listed in the left pane will be the calendars in the Global template (Global.MPT). In the right-hand pane will be the calendars in the current project file.Project calendars organizer

To rename a calendar

  1. rename project calendarsClick on the current calendar name in the respective pane.
  2. Click on the Rename button.
  3. In the displayed Rename dialog, type in the new name then click on OK.

To copy a calendar to another project.

  1. Open up both project files.
  2. Open the Organizer.
  3. In the calendars tab of the Organizer, use the two Calendars available in drop-down selectors at the bottom of the dialog to select the project file that has your calendar in one pane, and the project that you want to copy the calendar over to in the other drop-down selector.
  4. Click on the calendar name that you want to copy, then click on the Copy button. Your calendar will be copied to the other Project file. (If you copy your calendar to the Global.MPT template file it will be available for all new blank projects.)

Windows 10 Update to Version 1511

Windows 10 users have been alerted recently on starting their computers with a message displayed across the whole screen saying “All your files are exactly where you left them”. Now for me having gone to bed safely knowing that my PC was working fine, to be told this when I started the next day, was something of a surprise. To be honest I didn’t think I had come down in the night and mysteriously moved them around on the hard disk. My wife thought we’d been hacked or suffered a virus attack. The next message “We’ve got some new features to get excited about” made me realise that Microsoft with its new practice of automatically installing updates had installed a major update to Windows 10 (known as version 1511) and this was their way of announcing that the upgrade had succeeded.

There are apparently a few fixes, including an end to black tab previews in Edge, Windows no longer forgetting how you login, and “more reliable” downloads from the Windows Store.

What is nice is you can now have a coloured title bar on your programs, instead of the white one that would merge into menu bars of some apps. Go to Settings, Personalisation, Colours and scroll down to find the option to turn on Show colour on Start, taskbar, action centre and title bar. Choose an accent colour from the various ones displayed above this option and your title bars will be clearer.

What is a bit nasty (as far as I am concerned because I have multiple printers) is Windows 10 now makes the last printer you used the default printer. This setting is On after the upgrade and basically the last printer used becomes the default printer. In other words if you change to another printer all your other programs print to it until you change again – even if you restart your PC. We have a Dymo labelwriter and after printing off a label it became the default printer. Subsequently, trying to print an A4 document on many tiny labels was an interesting effect that I never want to repeat. I think there should have been third message from Microsoft saying “We have messed up your printer settings and will cover your desk in lots of wasted output”. The problem is caused by a new setting that lets Windows manage your default printer. To stop this happening go to Settings, Devices, Printers & Scanners. Scroll down the list of printers and below is the section Let Windows manage my default printer. Set this to Off.

Windows 10 Upgrade Stops Outlook and Visual Studio

Last week after the long wait, Windows 10 was finally released and the upgrade download appeared magically on my PC ready to run.

The installation took about an hour and appeared to run smoothly; however, my Visual Studio 2015 installation required me to run the Repair option. After that I tried Office 2013 and it seemed OK.

Unfortunately, I kept getting this error message when trying to send emails

Sending error 0x800CCC13 Cannot connect to the network.
Verify your network connection or modem.

 

When I looked at my account configuration and tested the Outlook account the test worked perfectly, sending and receiving the test email. Yet, whenever Outlook tried to send emails normally I received the error message.

Loads of suggestions included restarting the Computer in clean boot to make sure no third-party applications are interfering with Outlook sending emails (no good), turning off the Exchange Add-In (no good), removing and deleting the stuck emails (no good and then I had to re-write the emails), and creating a new Outlook profile to remove any corruption (no good either).

Eventually I decided to run the System File Checker (what after a fresh upgrade? Surely not you say!)

I had to open the Run menu by right-clicking on the start button and choosing Run. Then I entered the following command to run the System File Checker tool:

sfc /scannow

 

The sfc /scannow command scans all protected system files, and replaces corrupted files with a cached copy that is located in a compressed folder at %WinDir% \System32\dllcache. The %WinDir% placeholder represents the Windows operating system folder.

Lo and behold, it found corrupted system files and repaired them. After that, Outlook was back on form and working correctly.

I still like Windows 10 though.

Create a memory-efficient Data Model using Excel 2013 and the PowerPivot add-in

In Excel 2013, you can create data models containing millions of rows, and then perform powerful data analysis against these models. Data models can be created with or without the PowerPivot add-in to support any number of Pivot Tables, Pivot Charts, and Power View visualizations in the same workbook.

The data models can contain a huge amount of data that can use up computer memory which in turn could affect other programs that use the same system resources. If you use any Office or Excel online cloud applications, files are limited to 10MB which can be reached very quickly.

There is a good article here to read at on Microsoft’s Office Support website that will explain what you can do in detail.

You could also download and run Microsoft’s Workbook Size Optimizer. It analyses your Excel workbook and if possible, compresses it further.

How to place text in Word after the Endnotes

There is a problem when using bibliographies (or indexes or appendices) and endnotes together in Word. The problem arises when you want to place the text at the end of the document, after the endnotes. Word interprets end of document very strictly, and unfortunately, your endnotes appear after everything else.

Thankfully, using a little trickery we can get our bibliography, or appendix, or index, or whatever text is necessary to appear after the endnotes. The secret is to use sections. Briefly, you need to insert a section break at the end of the document. It’s possible that you are using multiple sections in your document for other reasons. So for each section in the document you need to change the Endnotes settings to display at the end of the section rather than the end of the document.  After this is done stop endnotes appearing in every section. You do this by suppressing the endnotes for all sections, except the one where the endnotes are to appear. As soon as you suppress endnotes in a section, the ones for that section appear in the following section. So you suppress them for all sections apart from the one where you want them to appear. Finally you place the bibliography, or appendix, or index, or whatever text is necessary, after the final section break.

The detailed instructions are:

  1. EndnoteDialogInsert a section break at the end of the document.
  2. In the References tab of the Ribbon, in the Footnotes group, click on the Dialog Box Launcher. The Footnote and Endnote dialog here is displayed.
  3. Select the Endnotes location option, then click on the drop-down selector to the right and choose End of Section.
  4. Click on Apply.
  5. Now position in each section where you do not want the endnotes displayed. You need to suppress the endnotes for that section. To do this:
    1. In the Page Layout tab of the Ribbon, in the Page Setup group, click on the Dialog Box Launcher. The Page Setup dialog is displayed.
    2. PageLayoutClick on the Layout tab, and click on the Suppress endnotes check box to place a tick mark in it.
    3. Click on OK.
    4. Repeat Step 5 for every section where you do not want endnotes displayed.
  6. Finally, move to the end of the document (using CTRL + End) and place the bibliography, or appendix, or index, or whatever text is necessary, after the final section break.