Displaying Formulas and Column Width

To display formulas in Excel, as opposed to displaying the formula results, you press the Ctrl+`┬ákey combination (that’s an accent grave and it’s to the left of the 1 on most US keyboards). When you do that, Excel helpfully increases the column width to show you more of the formula.

Excel increases the column width, but it doesn’t increase the ColumnWidth. That is, the ColumnWidth property is still the same even though you can see more characters than before. I’m not sure what exactly is changing here, but that’s not really the point of this post. The increase in width is about double. The percent of times that Excel makes the column wide enough to show me what I want to see is 0%.

If I have a 20 character formula, I’m probably not going to use Ctrl+` to check it out. It’s the longer formulas where I need to ensure consistency. I need to increase the column width even more, but that creates another problem. When I change the column width I also change the ColumnWidth. And when I go back to displaying formula results, my column width is now wider than it was. I’m working on a solution, but there’s a lot of moving pieces, so this isn’t fully baked yet.

First I need to store the ColumnWidth before any changes. Then I need to increase the column width so I can see the whole formula. Then I need to set the ColumnWidth back to what it was. For the above formula, that’s pretty straightforward. It’s only about 150 characters. But the one below is about 280 characters and that strategy will make the column wider than the screen – no good.

Round one of my attempt to replace the Ctrl+` behavior is below.

To combat really long formulas, I AutoFit the formula, then reduce the column width until it fits on the screen. It’s a bit more indirect than converting points to characters and all that funny business associated with ColumnWidth, but I like it. I haven’t tried to break it with hidden columns or anything like, so there’s probably some fragility there.

Five Minute Dashboards

Mike at Data Pig Technologies must have got some Christmas present he didn’t want because he’s having a giveaway.

The task is simple. Start with some prepared data and create a fully functional dashboard/report in 5 minutes or less.

To participate in this contest, you can submit a video of you building out a report or dashboard. All videos will be loaded to an official 5-Minute Challenge YouTube Playlist.

I think there are more prizes than there were a few days ago when I first read that post. Here’s a taste:

First Prize

XBox One

Second Prize

Fitbit Charge Heart Fitness Wristband

Third Prize

Amazon Fire Tablet

And much more. Visit http://datapigtechnologies.com/blog/index.php/new-contest-for-2017-the-excel-5-minute-challenge/ for all the details and updates.

Good VBA Dates and Bad Excel Dates

No, I’m not launching a match making service for Excel nerds. I’m talking about calendar dates. I was importing some data from a system that doesn’t seem to care what dates you might enter. Here’s the offender

Instead of 2016, the user entered 1206. VBA doesn’t care.

But Excel cares. As you know, Excel stores dates as the number of days since December 31, 1899. Anything before 1900-01-01 isn’t considered a date. The way this manifested was strange to me. I got the error (Application-defined or object-defined error) on this line

When I filled the class, and specifically the TranDate property, no problem as VBA recognizes it as a date. When I fill the array vaWrite, no problem – the array contains text, numbers, and dates so it’s typed as a Variant. It’s only when I try to write it to a range that it complains. But why? I can type 12/13/1206 into a cell with no problem. It won’t recognize it as a date, but it doesn’t throw an error either.

A user (who is not me) got the error and clicked End. They’ve learned that clicking Debug only makes things worse – not that there are ever bugs in my code. When he clicked End, the code filled up the range all the way up to the bad date, line 1103.

That’s unexpected behavior. I would think the whole write operation would fail, but apparently not. The good news is that this partial writing of data led me to the root of the problem very quickly. The bad news is that the user was perfectly happy that clicking End produced data and he didn’t realize that the data wasn’t complete. He went on about his day until things just weren’t tying out properly. As much as I like the debugging help of a partial write, I think I would prefer if it didn’t write anything at all.

In any case, it’s an interesting insight into writing an array to a range all at once. It’s long known that filling an array and plopping it into a range is way faster than writing out cell-by-cell. But whatever you do in VBA to avoid looping, you’re not really avoiding looping. All you’re doing is moving the loop to a faster place. For instance, when you Join an array, something in VBA is looping, just not you. In this case, VBA is looping through vaWrite and filling up cells and it’s doing it faster than you or I could. The interesting thing to me is that it’s really a screen refresh that’s hiding the loop (maybe). As opposed to, say, VBA building a bunch of XML in the background and replacing part of the sheet.

But back to the error. Why an error anyway? As I said, I can type that non-date into Excel without error. I can even type that non-date in a cell and format the cell as a date without error. If I enter the formula =DATE(1206,12,13) in a cell, it returns 12/13/3106. Not a good result, but not an error either.

I think it all comes down to the fact that VBA has cast this data as a date and Excel won’t accept a date outside of its range. At least it won’t accept it from VBA. If I look at the locals window, I can see that my Variant Array has cast that value as a Variant/Date.

And this command in the Immediate Window fails with the same error as above

but if I override the cast by casting it as a String, it works

If you’ve followed me down this rabbit hole and are still reading, then may god have mercy on your soul. Here’s my fix

Instead of assigning the recordset date field to the property (I commented out that line), I assign it to a date variable and test the year. If the year is less than 1900, then I change it to 1900. I considered changing it to the current year, but I think having a different incorrect year that doesn’t cause an error is better than trying to guess what it should have been.

You might have noticed that I prefixed the Year function with VBA. I have a property in this class called Year, so when I try to use it, VBA thinks I’m referring the class property and not the VBA function. Prefixing the function call with the library name ensures that it uses the right one.

I wrote that Nz function back in 2007 but it’s been updated since. So here’s the new and improved version.

This is great example of a function that needs some comments.

Standing Desk Upgrade

I upgraded my standing desk from paper boxes to something a little fancier.

The three components are a monitor pole, a desk for the top of my desk, and a mat.

VIVO Dual Monitor Stand Up Desk Mount

Adjustable height standing desk.

Imprint CumulusPRO Anti-Fatigue Mat

I tried this laptop stand for my keyboard and mouse, but it was a little too unstable. A comment on Amazon said it was designed to hold seven pound laptops and a lightweight keyboard may not work as well. It wasn’t super wobbly, but enough that I didn’t like it.

Portable Laptop-Table-Stand with Mouse Pad

The whole getup ran about $170. Compare that to almost $400 for a real stand up desk without the floor mat.

VARIDESK Height Adjustable Standing Desk Black

The downside is that I have to raise and lower the monitors manually. I loosen the allen screw, slide the monitors down the pole, and re-tighten. That’s not as easy as it sounds. The weight of the monitors makes the sleeve that holds them bind against the pole. Sliding them down means first lifting them up to unbind the sleeve. If my desk backed up to a wall this would be impossible. You really have to be dedicated to frugality to want this set up.

The next step is moving the floor mat out of the way and moving the desktop desk over to my credenza. I don’t really have a lot of paper or other stuff on my credenza, so there’s plenty of room. But again, this is hardly a universally appropriate set up. If I decide I hate it, I’m only out $170, so there’s that.

Grouped Sheets Warning

We’ve all been there. I group a few sheets, change several things at once, and pat myself on the back for being so efficient. A few changes later, I realize that the sheets are still grouped and that I’m an idiot. I finally decided to do something about it and I happened on this old post from Contextures. Debra asks

What would you like Excel to do, to make grouped sheets more noticeable?

I’d like a warning, but I’d like it to be non-modal. That is, I don’t want it to interrupt me.

What about a hideous Ribbon tab that appears when you group sheets?

First, I used the CustomUI editor to add some XML to my workbook.

It’s a couple dozen buttons with alternating colors. It appears whenever a sheet is activated and sheets are grouped. Here’s the code in a standard module

The onLoad procedure sets up a global Ribbon variable. The getVisible procedure controls whether you can see the custom tab. If the count of SelectedSheets is greater than one, returnedVal is set to True and that makes the tab visible. The If block shows the tab if it’s visible using the ActivateTab method.

In the ThisWorkbook module:

When a sheet is activated, the Ribbon is invalidated and the getVisible procedure is forced to run again.

The next step would be to put this code in an add-in with a class module and application level events to monitor all workbooks.

You can download GroupSheetAlert.zip

Comparing Worksheet Sizes

My friend and colleague, John Miller, taught me a nice trick the other day.

My compression software of choice is 7-Zip. As you know, modern Excel files are nothing more the zipped XML packages. If you change the extension of your file from xlsx to zip, you can unpack the xml using any compression software that supports zip (which is probably all of them).

7-Zip has a menu item that I’ve never used. It’s the first on the list and it’s called Open archive.

When you use Open archive, 7-Zip doesn’t care what the file extension is. It looks at the file contents, determines how it was compressed, and displays its contents.

From here you can navigate to xl/worksheets, for example, to see all the sheet XML files and their sizes.

That’s much easier that changing file extensions. Of course you can use this for inspecting anything in the file, not just worksheets.

Getting SQL Server Data into Excel

Let’s start with a simple query

It returns 10 rows containing an invoice number and an invoice date. When I hover over InvoiceDate, you can see its data type is date. What you can’t see, but trust me it’s there, is that Invoice is a varchar(50) (that’s a String in VBA parlance).

Copy and Paste

Once I run the query in SSMS, I can F6 down to the Query Grid, Ctrl+A to select everything, and Ctrl+C to copy it (Ctrl+Shift+C to copy the headers too). Then over to Excel where a Ctrl+V finishes the job.

That’s pretty good except for one problem. My string invoice numbers were converted to numbers. I can tell because they’re right-aligned. That’s not a particular problem here because they didn’t happen to have any leading zeros. But if they had, the leading zeros would have been lost.

I could have formatted the column for Text before I pasted.

Those little green triangles means everything is right with the world. My invoice numbers are text and my dates are dates. Percentage of the time I will remember to format the column before I paste: 0%.

External Data Query

If I want my varchars to remain varchars then one option is to use an External Data Query in Excel.

My invoice numbers look good and there’s no green triangles. But something is not quite right with those dates. I’m sure you noticed. I’ll get back to that in a minute. But first, here’s how you create an External Data Query.

Follow the wizard by first connecting to the server.

If you’re using SQL Server Express, you can use the command line to find the names of local servers: sqlcmd -L (that’s a capital L).

I have no idea what that is as I don’t have SQL Server Express on my machine. If you’re using regular old SQL Server then ask your DBA what the server name is. If you are the DBA, then you’re in trouble.

Next I tell it what database I want to use. In this case, I uncheck Connect to a specific table because I want to write a query.

On the last screen, I change the Friendly Name

In the next step, I select which table I want to use. In a previous step, we said don’t connect to a specific table. There are at least two kinds of OLEDB connections to SQL Server data: Table and SQL. If you check the box to connect to a specific table, it creates a Table connection and you get the whole table. If you uncheck the box, you get to pick the table and write a query, as we’ll see in a moment. For my purposes, I’m selected the view I used in the SQL statement at the start of this post.

On the Import Data dialog, you can just hit OK and bring in the whole table. But if you we’re going to do that, you might as well have checked the box a couple of steps ago. Because I want to write a query, I’m going to select Properties here.

On the Definition tab, I change the Command Type from Table to SQL and put my query in the Command Text box.

Click through the rest and you’re home. Wow, that’s a lot of work.

Automating External Data

Most of the queries I want come from one database. So I created this little gem to get me started

This puts a one column, one row External Data query into cell A1 that’s already connected to my favorite database.

I write my query in SSMS, copy the SQL statement, and head over to Excel. From a cell within the External Data Query range, I press Alt+D+D+E to get the Edit OLE DB Query box. From here I can change my Initial Catalog in the Connection in case I’m using a different database and I can paste my SQL statement into Command Text.

That’s-a nice-a doughnut.

Stupid Dates

Using an External Data Query, we solved our strings-that-look-like-numbers conversion problem, but introduced another problem. My dates no longer look like dates. They’re all left justified and weirdly formatted. Also, those strings-that-look-like-dates are pretty tenuous. If you F2 and Enter, you convert them to numbers. So if you’re making something permanent (as opposed to some quick and dirty analysis), you still want to format the column as Text.

The problem with the dates is that SQL Server uses one kind of data format and Excel uses a different, incompatible type. Basically Excel doesn’t know it’s a date. If you copy and paste, the trip through the clipboard forces Excel to analyze the data to determine it’s data type (that’s why my varchars turn to numbers), so the dates get converted because at least they look like dates. But through OLEDB, not so much. The data types stay true throughout the process. Hooray for varchars, too bad for dates.

SQL Server has a ton of date formats (well, five actually). The SMALLDATETIME is a pretty snazzy data type that just so happens to work in Excel.

Oh, if you’ve ever tried to convert an Access database to SQL Server, this date business is old news to you. Just know that you are not alone. Others are hurting too.

I’m not going to dumb down my SQL data types just for Excel. But I am willing to convert.

When I put that into Excel, my dates are dates.

Well, almost. They still need a little formatting love. Now all you have to do is remember to convert all your dates before you bring them over. The good news is that if you forget, you can simply edit the SQL to add the CONVERT function. If you have RedGate SQLPrompt, there’s even better news. I created this snippet.

You select your date field, press and release Ctrl, type xld (that stands for Excel Date) and it converts


SQL Prompt

I don’t know how much SQL Prompt costs because my employer pays for it. But I can’t imagine working in SSMS without it. In a recent update, they added a new right-click menu item to the Query Grid right-click menu: Open in Excel

As you might have guessed, this is the best of both worlds. You get varchars and dates acting as they should. You still need a little date formatting.

And that’s everything you ever wanted to know about getting data from SSMS to Excel. And then some.