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.

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