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

Meta Dose of Excel

Hey, did you notice this blog is significantly peppier lately? I recently moved from Digital Ocean to Linode. The transition, frankly, sucked lemons. I tried to move from Apache to Ngnix and I failed miserably. After several weeks, I finally gave up, uninstalled Nginx, reinstalled Apache, all was well.

If you’ve been visiting in the last few days, you’ve probably been treated to a different theme each time. All WordPress themes are terrible. I’ve settled on the current one because it’s the least offensive until I can find a better one. I found a super plain theme that I like, but it messes up code in the comments.

I’m sick of WordPress and ready to move on to something else. It’s a great CMS, but I just don’t need it. I want simple, secure blogging software with comments and a simple CSS. I wouldn’t mind having a go at writing my own, but the “secure” requirement might be a problem. I’d also miss Akismet for comment spam as it makes managing a blog tolerable.

That’s the update. More Excel stuff coming soon.

Paste Special Multiply

Thanks to Dennis Wallentin for another article:

Paste Special Dialog – Operating on a Range of Data

For some of you this is already known, but I know there are users who are not aware of the power of the Paste Special Dialog.

Suppose you have been asked to create a budget for next year. The budget will be based on last year’s sales figures and the budget should be 10% more than last year.

As usual, there are several ways to skin a cat. A quick method to achieve it is to use the Paste Special Dialog. The following screen shot shows the details:

  • First we enter 1.1 (which represents the 10% increase) in an empty cell in the worksheet.
  • Next we copy the source, i.e. the cell that holds the value.
  • Then we select the targeted range of data that is to be increased with 10%.
  • Next we select the command Paste Special… from the menu by Right Clicking on the targeted range.
  • In the Dialog we select the operation Multiply and then hit the OK button.

The output of the operation is showed in the following screen shot:

In my opinion this is an excellent way to quickly update a range of data.

Recently I made a suggestion to improve the operation. Instead of using a cell in a worksheet as the source I suggested to add a numeric field in the Paste Special Dialog. The following picture shows the idea:

If You think it’s a good idea, then I suggest that You vote for it at UserVoice:
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/17352001-paste-special-add-a-numeric-field-when-using-one

Enjoy!
Thanks and all the best,
Dennis

New Computer Setup

I got a new computer at work. This is how big it is.


It’s got an i7-6700T @2.8GHz and 16GB or RAM. All in that little box. It also has Windows 10, which I’m finding quite acceptable. I guess I should have upgraded my home computer when it was free. I’m opposed to Microsoft’s Windows 10 revenue model on principal and that really hasn’t changed.

The other major change is that I have Office 2010 64 bit. You can read some of my comments at the bottom of this post. To be fair, Microsoft doesn’t recommend installing 64 bit unless you have a specific reason. I don’t have a good reason, I just want Excel to address as much memory as possible. And it does. And it’s super-fast. Except when it crashes. My email to IT reads as follows:

I give up. Office 64bit sucks. When you have time, I’d like it uninstalled and 32bit installed.

Thanks,
Dick

Finally, I’m making the switch from Firefox to Chrome. It’s been an adjustment, but generally I like it. My biggest blocks to switching have been Type Ahead Find and the treatment of diverted tabs. I installed an extension called Type-ahead Find that takes care of the first problem. And I installed an extension called Inoreader Companion which takes care of the second problem within Inoreader. The vast majority of diverted tabs for me happen in Inoreader. I still don’t like how Ctrl+Click opens the tab just to the right rather than at the end, but it’s only a minor annoyance and I’ll get used to it.

Here are the rest of the settings I make when I do a clean install.

Windows

  • Pin programs to taskbar.

    That’s Outlook, Chrome, Excel, an RDP to our accounting software, SQL Server Management Studio, and Notepad++. I leave Outlook running all the time so it processes my client side rules. I start the rest of them each morning by holding down the Windows key and typing 23456. Everyone in my office closes the entire application when they’re done with it (except Outlook). That means every time they want to work in Excel, they start Excel. They don’t have 16 GB of RAM, either, so it takes a few seconds. I just don’t understand why they don’t leave it running all day.

  • Uncheck Hide Extensions for Known File Types
  • Check Show Hidden Files. I did these the old fashioned way, but have discovered that they are on the View menu in Windows Explorer. They’re called File name extensions and Hidden items. Just another little Windows 10 convenience. Although I still argue that File extensions should be on by default.
  • I use Ctrl+Alt+Down and Ctrl+Alt+Right for a couple of Excel macros so I can’t have them rotating my screen around.

Excel

  • Uncheck Show Mini Toolbar on Selection
  • Include this many sheets = 1
  • Uncheck Use GetPivotData functions for PivotTable references
  • Turn off Autocorrect – Replace as you type: Internet and network paths with hyperlinks
  • Uncheck Allow editing directly in cells
  • Uncheck Show paste options button when content is pasted
  • Show this number of Recent Documents = 50
  • Uncheck Show all windows in taskbar
  • Add Max and Min to Status bar

Visual Basic Editor

  • Tools – Options – Break in Class Modules
  • Uncheck Auto Syntax Check
  • Check Require variable declaration
  • Comments to gray and Keywords to green. I don’t write a ton of comments. I’m a proponent of writing self-documenting code and only commenting when necessary. But when I do comment, I certainly don’t want it slapping me in the face. If I need clarification, I’ll look for comments. Otherwise I prefer not to see them. I realize there are others in the VBA community who have the exact opposite opinion. I’m in favor of people having opinions about programming in VBA even if I don’t share them.

  • Add CommentBlock and Uncomment Block to Tools menu

Outlook

  • Compose messages in this format: Plain Text
  • When a new message arrives Play a sound – off
  • When a new message arrives Briefly change the mouse pointer – off
  • When a new message arrives Display a Desktop Alert – off
  • Check When replying to a message that is not in the inbox, save the reply to the same folder
  • Check Always send a read receipt. I hate that some people always request read receipts. But I also hate that prompt, so I just gave in.

SQL Prompt

  • Add space as special character
  • Put commas as start of row
  • Put space after comma
  • Add snippet gob = GROUP BY $PASTE$ ORDER BY $PASTE$ I have to copy the SELECT list for this to work. I wish I could make it work without copying. That is, it would group by and order by everything in the SELECT list that wasn’t an aggregate.
  • Add snippet ssfgross = SELECT * FROM dbo.OSASGrossMargin_vw WHERE GLYear = $DATE(yyyy)$ Just a query I start with a lot. We’re a fiscal year end, so the $DATE$ variable doesn’t quite work.

One last word about 64 bit office. You can’t program the menus in the VBE as I documented here. My temporary fix for that was going to be AutoHotKey. This is bad, so be sure to plug your nose if you choose to consider reading. Here’s my AHK script:

I’d go to the Immediate Window and type, for example, vbPrivate. It would expand into an Application Run statement and I’d press Enter to execute. I told myself that this is how I would access these procedures until I was able to rewrite this as COM add-in or a .Net thingy or however people automate the VBE these days. I’m pretty sure I would have just kept using this method and never actually rewrote it. Now that I’m switching back to 32 bit, it’s a non-issue.

I hope you’ve enjoyed this small glimpse into my computing life.

Todoist vs GoodTodo

Recently some guys I work with started using Todoist. It’s a todo list application with collaboration features. They set me up with an account and I tried it for a month. Here’s what I learned.

I’m sticking with GoodTodo. Sorry for the early spoiler, but since this is an Excel blog, some of you may not care about the details.

I meant to post this earlier, but never got around to it. Then I got an email that GoodTodo is raising their prices. It’s still a good deal to me. The email just reminded me to get off my butt and post my review. Also, my “really inexpensive” comment from six years ago needs to me modified to “moderately inexpensive”.

One thing before I get to the details: I’m only evaluating these applications based on features I care about. I don’t care about collaboration, so Todoist doesn’t get any points from me. If you care about collaboration, well, we’re different then.

Exporting
Remind me never to write mystery novels. I’ve already told you I’m sticking with GoodTodo and now I’m starting with the number one reason why. Anytime I want, I can send all my past, present, and future todos from GoodTodo to my inbox. Not so much with Todoist. Their export feature doesn’t export completed todos nor does it export the comments associated with the todos.

They do have an API. Presumably I could write my own code to get whatever I want, but that was beyond what I was willing to do.

This was a deal breaker for me. It’s my data even if it’s on your server and you shouldn’t restrict my access to it.

Point: GoodTodo

Entering Todos
GoodTodo has a feature where if you send an email to today@goodtodo.com from an email it recognizes, it will create a todo from the email. It supports all kinds of email addresses like tomorrow@, nextfriday@, and even specific dates.

As I’ve used GoodTodo over the years, I literally only send things to “today” and I move them from within the site if I need to. The fact that I don’t use this feature to it’s fullest doesn’t mean it’s not useful. I just means Mark did a lot of email parsing code that I never use. I still email todos quite a bit.

The downside to entering emails is that the subject of the email becomes the title of the todo. It’s pretty rare that I would have titled my todo like that if I were creating it from scratch, particularly if the email originated from someone else. I’m pretty careful with my email subjects, but I’m the exception, not the rule. If the subject is particularly egregious, I can just change the title in GoodTodo. If it’s good enough, I generally don’t.

Todoist does not support emailing to your list. If I had an email that I needed to put on my todo list, I copied the contents and pasted it into a new todo. That’s not as convenient as forwarding, but it honestly wasn’t that bad.

Todoist does support keyboard shortcuts in its web app. I had a twitter back-and-forth with them about how I couldn’t do certain things without the mouse, the result of which was I figured out how to do everything I need with the keyboard. It’s clunky. Things lose focus and, in one case, I have to attempt to close the todo without saving, cancel the close when I get the “save now?” message, and that returns the focus to where I need it. Not great, but workable.

Goodtodo has no keyboard shortcuts. I can navigate fairly well with my normal web page navigating tricks, but there is at least one task where I must use the mouse.

Point: A slight edge to Todoist because I’m a keyboard freak.

Editing Todos
GoodTodo provides a title field and a description field. I use the description field to record what I’ve done and what my next action is. It’s all freeform with no real structure. I have an AutoHotKey so that if I press Ctrl+; it enters the current date wherever I am, not just in Excel. So a todo might look like this:

Basically it’s a date stamp with something I’ve done (like EM (meaning I emailed someone)) and a bunch of NAs (next actions) for what I need to do next. It works and it’s fine.

Todoist has comments that are time stamped. I didn’t have to put the date next to my action, it was already done. That was pretty handy. I also like the structure of comments being a separate entity from the todo item.

Point: Slight edge to Todoist, but I can live with either one

Completing Todos
When you complete a todo in Todoist, it goes away. And it’s really hard to find after that. It’s there and you can get to it, but it’s not easy.

GoodTodo puts a checkmark next to it and moves it to the bottom of the page. If you want to know what you’ve done today, scroll down. If you want see what you did yesterday, navigate to that page and see what’s checked off.

It probably doesn’t seem like a big deal, but it turned out to be important to me.

Point: GoodTodo

Not Completing Todos
Yes, this does happen from time to time. If I don’t get something done today, GoodTodo moves it to tomorrow sometime after I’ve gone to bed (I think).

Todoist still shows me the todo, but it’s under a heading with the date it was added. It’s a lot of wasted space and clutter. Just because I added a todo on a certain day doesn’t mean it has anything to do with that day. I have four things left on my today todo list and I’m not going to get to any of them. I put them on there in case I did get to them, not because they were due today.

Point: GoodTodo

So I’m sticking with GoodTodo. I’d love to have a todo list app written just for me and maybe someday I will. It will have the rolling forward of GoodTodo, the timestamped comments of Todoist, the keyboard shortcuts of Todoist but slightly better, and most importantly I can export all my data any time.