Tuesday, February 27, 2018

Talking about Backup and Sync for Google Drive

I've been meaning to write notes on Google Drive and using Backup & Sync - that will be for another day, in the meantime +Lee Sapara has made the following instructional videos.


Choosing Which Folders to Sync

Handling Deletion of Files

Letting Backup and Sync work with removable USB storage / devices

Friday, February 23, 2018

Move on to Excel Tables as soon as you can

I was just reading +Bill Jelen 's prolific blog on Excel - the article I came across was the VLOOKUP bug - seems if you add a new column in the middle of your reference range, the first cell using VLOOKUP re-calculated properly but subsequent cells are "lazy" and need to be woken up, with Ctrl+Alt+Shift+F9.

I tested out Bill's Bug and yes, it is a bug. I then put the whole reference range (including headers) into an Excel Table, changed the VLOOKUP to reference that Table and it just works without extra steps.

Another reason why I keep pushing the idea that Excel Tables are not mandatory but many mundane Excel features now work properly with Tables but not with old timey ranges. 

Monday, February 5, 2018

Getting frustrated with the Microsoft Word keyboard cursor?

Microsoft Word has progressively been modified over time to make text editing easier for people. Unfortunately, every so often what the Word developers think is a good feature doesn't quite jive with the end users. One case in point is when you start selecting part of a paragraph and the keyboard cursor jumps to include the invisible paragraph mark or worse, the whole paragraph. Do you want to turn it off?

See Smart Paragraph Selection and Smart Cursoring

Like what you read?

Buy Me A Coffee

Get VBA out of maintenance mode

Microsoft planned and delivered the .NET framework in 2000 and VB.NET in 2002. It's 2018, and that's a long time ago. .NET is now the main framework that Microsoft uses for the cloud apps and even for desktop.

Poor old Visual Basic for Applications (VBA), the "internal" and bundled programming language for Microsoft Office - Access, Word, Excel, Powerpoint, Project, Visio etc... was put on maintenance status - it would be updated to cater for operating system changes here and there but it has no new enhancements. Yet, VBA for Microsoft Office desktop has not died and passed on - as long as Microsoft Office desktop lives and runs, VBA will provide a programming facility to people.

VB.NET (or offshoot) shows no signs of replacing VBA Classic. And the two platforms do not compete in the same arena at all, they are complementary instead of being antagonistic. Why not enhance VBA. With features like Linq , String Interpolation, constructs to manage large chunks of text and so on.

Tell Microsoft you want it this way

Tuesday, December 5, 2017

Linked Transpose Formula

I've known about the static
Paste > Special > Transpose

Today, a class participant wanted a transpose but not static, she wanted dynamically linked formula.

Seems the =TRANSPOSE() function has been there for a long time. It's a CSE array function - so, select a range of blank cells in the shape of a transposed destination, use the expression {=TRANSPOSE(A1:B4)}

Remember you don't type the { } - they are bookended when you press Ctrl+Shift+Enter instead of Enter

See the TRANSPOSE() description

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.