Thursday, September 1, 2016

Fonts and things in Microsoft Access

Microsoft Access designer tools have mostly been dormant and not improved over the years.

Query - SQL View font

Two things are cruel in SQL View. 

The SQL Text will not stay formatted and will automatically be wrapped - probably this is due to the two way interface between Query Design View and SQL View.

The font in SQL View isn't the best choice. You can change the font in SQL View but this also changes the Query Design View font.

File > Options > Object Designers > Query Design - Query design font

Expression Builder font

Put your cursor in the editable content text area of the Expression Builder. Ctrl+Mouse Scroll Wheel. Tip from Utter Access Forums

Tuesday, March 1, 2016

Insert Textbox - in Excel 2011 for the Mac

I don't have a Mac. The day will come (maybe) but in 30 years of working with Personal Computers, the only Apple I used was the Apple ][.

So once in a while, I encounter participants that bring a Mac along and the menus look different or items have different menu positions.

Today we couldn't find Insert > Textbox easily. Microsoft assures me it's there.

Also because I am so used to Windows's Right Click, that's Control + Click on the Mac.

Sunday, November 22, 2015

Registering VBEBookmarks.dll on 64 bit Windows 10 and Microsoft Office 2013

I've been using VBE Bookmarks for a long time. The VBA IDE that comes inside every Microsoft Office program (Word, Excel, Powerpoint, Access, Visio, Project) was the zenith of useability and productivity during the Visual Basic VB 6 days. And even in 2015, the year I am writing this, it is in Microsoft Office 2016. However, jumping around your code is tough because the native bookmarks are not numbered or fun.

Ron de Bruin hosts two 32 bit .dlls authored by Jim Rech (Excel MVP) - one of them is VBE Bookmarks. The thing you have to do is to download the .dll and register it (a.k.a. install it) on your machine. Before Windows 10 64 bit, I did not have difficulty doing that. But I did this time, I did. So with help from friends +Paul Pavlinovich +Greg Kerr and a Stack Overflow discussion thread, I managed to make it work.

1. To register a .dll, ensure the .dll has no dependencies on other .dlls which may be missing. In this case, vbebookmarks.dll relies on msaddndr.dll which may or may not be already registered.

2. Ensure that the .dll you want to register is in a folder where there is no uncertainty about access rights. I gave up and copied it to the %systemroot%\syswow64 folder.

3. For 64 bit .dlls use regsvr32.exe in %systemroot%\system32, for 32 bit .dlls use regsvr32 in %systemroot\sysWoW64.

4. Use an elevated command prompt by running cmd.exe as Admin.

Tuesday, September 29, 2015

Happy 30th Birthday Excel

Thanks to +Debra Dalgleish for alerting me to the date tomorrow - September 30. On that day in 1985, Excel (for the Mac) was released. That's 30 years ago. I hunted around for some old Excel and found Excel 2 for Windows (the first version for Windows). It comes with its own Windows runtime environment since Windows at that time was not commonplace. I managed to make it survive long enough to type in =NOW() - it's running in Oracle Virtualbox and the platform might not be 100% emulating well.

Wednesday, September 16, 2015

Nice tutorial on INDEX, MATCH and array formula

Making sense of Conditional Formatting in Excel

Conditional Formatting in Excel is a bit like Charting in Excel. If you start simple and don't hit traps, you get good results first time. However, if you hit a trap or try to do complex work, it catches you easily. One of those features where the software makes simple tasks easy and difficult tasks difficult.

There are several articles, of course, in the Microsoft Support knowledge base, but they don't illuminate the detail as well as this one: Manage conditional formatting rule precedence

The points to bear in mind are are:
  • There are several types of criteria. Format only cells that contain is not the same as Use a formula to determine which cells to format.  The latter allows you to format neighbouring cells (usually on the same row) that don't contain values  for the criteria. The former only formats the cells that contain values for the criteria
  • For the formula method when used in a row wise manner
    • Select the range that will be formatted. It will be an absolute range - use an Excel Table for ease of data management
    • If the formula uses criteria that refers to relative cells in the range, use cells in the first row.
    • Make the cell addresses in the formula absolute in the column and relative in the rows. 
    • The formula should evaluate to True / False
  • A rule higher in the list has greater precedence than a rule lower in the list. By default, new rules are always added to the top of the list and therefore have a higher precedence
  • When rules conflict, the rule that is applied is the one that is higher in precedence (higher in the list in the dialog box).
  • For a range of cells, if a formatting rule is true, it takes precedence over a manual format.
  • The Stop If True check box is for backwards compatibility with older versions of Excel
I've got an Excel file you can play with.

Tuesday, August 18, 2015

What's Happening With Windows 10 and Cloud Integration

Windows 10 and OneDrive

Windows 8 was the first Windows with some OneDrive integration. There was a virtual folder in the File Explorer tree called SkyDrive / OneDrive. All your OneDrive folders and files were rendered in this tree - the files  however were not fully downloaded - they were shadow place holders. When you double clicked on the file, SkyDrive services on the machine would pull down the file for use and thereafter synchronise changes.

Windows 10 doesn't do it this way. When you install it, you are asked to decide which folders you want to synchronise. After you answer, it builds the virtual branch in File Explorer. The way to control it is through right click on the OneDrive tray app.

Here are screen captures of the app.

In addition to having files in Microsoft OneDrive, Microsoft offers the ability of fetching files from your Windows PCs - go to the OneDrive Web Page and if your source Windows PC is powered on with the OneDrive service running, you can obtain files from that PC. See Fetch Files on Your PC

Monday, August 3, 2015

Migrating to Windows 10

Something Happened  - Updating to Windows 10

Updated: 19th August 2015

It's been a while, since Windows absorbed so much of my time - it's actually a tribute to how Windows has been worry free from Windows 7 onwards. Yes, Windows 8 was initially a bit painful in user interface but Windows itself wasn't causing much distress.

Windows 10 is a new direction for Microsoft in some ways. I'll highlight some articles later.

But this weekend has been absorbed getting experience with Windows 10 installations on my computers. I've made various posts on Google+ - but with the incremental way things are happening, you might have trouble following the thread of thought. Instead, keep your eye on this blog post, I'll progressively update it as events unfold.

How to get Windows 10 Update

The well publicised way of getting Windows 10, is to make the white icon appear in your status tray, "book" a place and wait for Microsoft and your machine to download Windows 10. It will be fine for disinterested parties but most eager people want more control, more immediacy and lower overheads in repeat installation if the first attempt fails.

Instead, here's the way I got my downloads.
  1. Go to a Windows machine that is connected to the internet.  It does not have to be the machine you want to update. Download the small file that is the Media Creation Tool.
  2. In the Media Creation Tool, choose the Windows version that will match the Windows of the target machine. For me, the most common choice is Windows Home or Windows Professional. You can choose 32 bit or 64 bit Windows or both, but you cannot choose both Home and Pro at the same time for one USB flash drive
  3. You need a bootable USB. Of course you need blank USB Flash drive - an 8 Gb USB drive is fine. 
  4. Proceed and then after a while your USB drive will be ready.

Installing the Windows 10 Update

  1. Take the USB drive to the machine you want to update.
  2. Boot your current Windows and log in as an admin equivalent user.
  3. Check in your Regional Language Settings that you have selected US English as the default language. You can change it later, after installation.
  4. Open a command line window, running as admin.
  5. Look a the contents of the USB drive and run the setup program. 
  6. A relatively small blue window will open and you will be interviewed about keeping your current Windows settings and files or just files. You will also be asked whether you want to download updates or skip until later. The installation progresses. Presumably this phase copies the files to your machine.
  7. The screen then gets occupied by a full screen blue window. More copying.
  8. The machine reboots and a black and white screen with a large circular progress meter appears. More copying
  9. The machine reboots and drivers are installed
  10. The machine reboots and Windows carries our settings and configuration.
  11. The machine reboots and you are welcomed to Windows 10, with a bit more time to complete configuration.
  12. Have the machine on for a while, logged in. The machine automatically downloads the relevant graphic card driver - Windows 10 often uses the generic Microsoft driver - it doesn't come prepared with specific Intel graphics drivers even though that is one of the most common chip sets in use.

Something could Happen

This phrase "Something Happened" seems to be an in-joke. It displays when the installation process in point 6 and 7 hits an unexpected problem.

Things that happened to me:
  • During the automatic download process (before I chose to take the Media Creation Tool route), the overloaded bandwidth / servers did not / could not complete downloading.
  • In point 6, one of the computers needed the Media Creation Tool to be run as admin. 
  • In point 7, one of the computers had an issue with the UEFI system partition of the hard disk not being accessible.
  • If you want to play detective and troubleshoot setup failures, look at the Windows Setup Log Files
  • After completing installation, I could not invoke any Settings dialog

After the successful update

O&O have released a utility that allows you to choose from a whole list of privacy options called ShutUp 10

See Also:


A Clean Install (only if you want)

Microsoft is unwilling to give you a new licence key gratis - this is why you have to start a working Windows 7, 8.0 or 8.1 machine, run the Media Creation Tool to Update. Once you have accomplished the update, Windows 10 would have activated your machine with Microsoft. Once that has occurred, you now allowed to install Windows 10, fresh, clean without any legacy.

Prelude To A Clean Install

If you are going to carry out a Clean Install, your system drive containing Windows, your classic programs and Windows 8 tiled apps will be purged. Also, if you are not in luck or careful, there is danger to your documents and data (especially if your data is in the C:\Users\username hive which contains My Documents, Pictures, Videos.

  1. Copy your data to a removable USB drive or a network drive.
  2. If you are very cautious and want a rollback, in case your venture fails, back up your system using the built in Windows System Backup in Windows 7 and 8 or a third party backup utility. One I just tried is Macrium Reflect Free
  3. Find out whether you have the Activation Codes for each software that you value. If you don"t have a record of the code, try Belarc Advisor. Be aware some modern software doesn't only rely on Activation Codes, they count and id your activations on their server and you might have to get them to release their count so that they can allow a new activation. By the way, Belarc will also detect your new Windows 10 Activation Code.
More reading: Redmond Pie's article

After A Clean Install

Installing into a VHD

Windows 10 Pro will happily install into a VHD, and add a boot entry to the BCD. But of course, that machine must first have been updated from old Windows to Windows 10 first. Neowin has a VHD installation how-to.

Can't Find Safe Mode?

Safe Mode is now a legacy mode - it isn't automatically available in Windows 10.

Just Bugs

  • Windows 10 has an bad update which locks some registry keys and subsequent updates will cause a reboot loop.  You can get a utility from Microsoft to hide (suppress the automatic download) of some updates if you deem those updates problematical. If you can get to Control Panel, you can choose to View Installed Updates and uninstall specific ones.
See also the worst bugs

A Few Tips