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

Thursday, July 23, 2015

Pasting Content Without Weird Looking Results


When we cut from various sources and paste into the destination document, we often end up with a mish mash of our originally formatted content and foreign content.  Here are some techniques to reduce the pain.

When you right click to paste

Right Click to Paste in Word 2013 Screenshot
Left icon - K - Keep Source Formatting
Middle icon - M - Merge Formatting
Right icon - T - Keep Text Only or
Ctrl+Shift+Y  - Paste as Plain Text

After you paste

After you paste screenshot 

Setting your preferences for repeated use

File > Options > Advanced > Cut, Copy,, and Paste

Removing Styles and Direct Formatting long after the Paste

Style Inspector and Style Pane screenshot

Note the virtual entry in the Styles Pane - Clear All - it isn't real Style, it is an menu item to clear styles and direct formatting.

Google Docs Paste Behaviour

Ctrl+V - Pastes with Source Formatting
Ctrl+Shift+V - Pastes plain text
Ctrl+\ - removes formatting of the selected text

Monday, June 22, 2015

Deleting windows.old

I don't know what method Microsoft will use to deploy free Windows 10 to participating Windows machines.

I just upgraded a Windows 8 machine to Windows 8.1 - by way of the green Windows Store tile. It took a while to download 3+ Gb of Windows 8.1, then took its time to install in-place over Windows 8.

After it all settled down after rebooting, we have a Windows.old folder on C: drive. I'm pretty confident I don't need it anymore but it won't delete using Windows Explorer - it gets to the Windows.old\System folder and stalls because of some access rights / permissions. It does not ask for an admin password so I assume that it is some system folder permissions issue with a dodgy no can do error message. I thought, "ugh! do I have to go permissions hacking?"

Turns out, that's not how to delete Windows.old According to Microsoft article How Do I remove the Windows.old folder, I can use the Disk Cleanup tool.

And then I looked at the Disk Cleanup tool and instead of ignoring the short list of boring things it clears (which often does not do enough when I am desperate), there is actually a list of interesting things it can clear off.

Wednesday, June 17, 2015

The Microsoft Access Tab Control on a Form

Psst. Tab_OnClick event does not trigger when you click on a Tab in the Tab Control. You have to use the OnChange event of the Tab Control. But how do you refer to the current tab or active tab so that you can conditionally execute code?

No point looking at the MSDN article on the Tab Change Event.

Sample code is in an Access 2000 article:  ACC2000: VBA Procedure to Determine Current Page of Tab Control

The current tab name is in TabCtrl.Value - oh, and you don't use the Page Name, you use the Page Index Number.

Monday, March 30, 2015

Microsoft Office 365 is now available to Monash University staff and students free

Monash University is a solid Google Apps user, for both Students and Staff. In 2016, Microsoft Office 365 is now available too. Microsoft Office 365 is
  • Office cloud webapps
  • installable classic Office 2013 desktop programs
  • OneDrive cloud storage (description in restricted webpage - you need to log into portal first before you go to that url.