Author: Dick Kusleika

Storing Stuff in VBA Lists

Posted on by Dick Kusleika

You no doubt recall when snb wrote about Scripting.Dictionaires. Well, there’s more.

I use Collection objects in my custom class modules almost exclusively. It’s the only object, that I know of, that I can enumerate using For Each.

Outside of custom class modules, I use Dictionary objects. I used to avoid them because they weren’t built in to the language. I was always afraid of some dependency problem. But I’ve never seen one in all my years, so I’m over that now. The advantage of the Exists property and the ability to produce an array of keys or items is awesome. it’s probably more awesome than For Each, but I just haven’t made that leap yet.

And I never use ArrayLists because I never remember them. That’s not totally true. When I’m writing a procedure with a Dictionary and I need to sort, I kick myself for not using an ArrayList.

Here’s some features of Collections, Dictionaries, and ArrayLists.

Feature Collection Dictionary ArrayList
New Enum in class Yes No No
Exists No .Exists .Contains
Key Value paradigm Yes Yes No
Unique keys Yes Yes NA
Key data types String Any NA
Get keys No Yes NA
Auto create items No Yes No
Insert anywhere .Add(,,before,after) No .Insert
Output to array No .Keys or .Items .ToArray

There are other differences. Those are just the ones that are important to me. If there’s a difference that’s important to you, leave a comment. You can read everything you ever wanted to know about these objects at one of the pages below:

Collections: http://www.snb-vba.eu/VBA_Collection_en.html
Dictionaries: http://www.snb-vba.eu/VBA_Dictionary_en.html
ArrayLists: http://www.snb-vba.eu/VBA_Arraylist_en.html

New Computer Setup

Posted on by Dick Kusleika

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.

Deleting Pivot Table Drilldown Sheets

Posted on by Dick Kusleika

I tried to make drilling into pivot tables better once upon a time. I failed. Earlier this week, I read Debra’s blog post about showing details and deleting the sheets later. It got me thinking.

The problem I have is that her solution (and many others) rely on the Before_DoubleClick event. As you might imagine, I don’t double click to show pivot table details. I press the context menu key and choose Show Details from the menu. I need a different event or to capture that context menu item. I don’t think there’s any event that will allow me to identify new sheets only when they come from showing details of a pivot table. It doesn’t matter. The better answer is create my own shortcut.

In my Auto_Open and Auto_Close procedures in my PMW:

That’s Ctrl+Shift+D for the uninitiated. That will now run PTDrillDown

Lot’s of

in there. That’s the sign of really tight code, you know. This determines if the ActiveCell is in a pivot table by trying to set a PivotTable variable. If it’s in a pivot table, it next checks to see if it’s in the body (as opposed to row or column headers or filters). If it’s in the body, the code shows the detail, deletes any sheet with my special name, and names the resulting sheet with my special name. The special name lives in my MGlobals module.

And for the coup de grace, I have a class module that defines an Application variable WithEvents. I added this event procedure to it.

Whenever I switch off of the details sheet, it goes away. Now that’s keeping things tidy.

KwikOpen Update

Posted on by Dick Kusleika

A year and a half ago, I decided that I was going to make a change to my KwikOpen add-in to get rid of recent files that no longer exist. Well, I finally got it done. No, it didn’t take that long to implement. The performance of the add-in has been fine so there wasn’t a pressing need. The other day, the addin seemed a little less peppy than usual and I thought it was time for a look.

I had 2,368 files in my MRU and 465 of them are dead links. That’s about 20% and it’s similar to the proportion I saw back in February 2015. Of the three options I listed at the bottom of my previous post, I chose none of them. Instead, I weeded out some files as I wrote them back out to disk.

The file names are written to the file with the most recent at the top – sort of. Because I’m using the built-in MRU as well as my own, it’s not exactly that way, but it’s close enough for government work. Instead of time stamping the entries, I decided to dump any nonexistent files that were near the bottom of the list. If a file is in the top 90% of the list, it stays regardless of whether it exists. If it’s in the bottom 10%, it only stays if it’s still where it was.

Iteration Total Files Orphaned Files
Beg. 2,368 465
1 2,250 345
2 2,226 321
3 2,225 320

Looking at the last 100 or so files, they’re mostly from 2014. I could cap this at 2,000 and probably not notice.

Moving Sheet Groups within a Workbook

Posted on by Dick Kusleika

Last week I created a keyboard shortcut to move a sheet within a workbook. This week I’m changing it to work with groups of sheets rather than just the active sheet. Not because I need it. I rarely work with grouped sheets as it is. But sometimes you have to program just for the fun of it.

This will require a change to NextVisibleSheetIndex function. I tried to determine if the ActiveSheet was in a group and where it was in the group. That resulted in some inelegant code and I could tell I was doing it wrong. Then I realized that I should stop futzing with the ActiveSheet and just pass a sheet into the function where I want to start. That made things much simpler.

That kept the function code cleaner, but I still had to figure out what sheet to pass in. Well, that turned out to be really easy. If It was moving left, I pass in the first sheet in the group.

And if I’m moving right, I pass in the last sheet.

Moving Sheets within the Workbook

Posted on by Dick Kusleika

Moving sheets within a workbook is one of those things that’s just easier with a mouse. You can click on a sheet, drag it, and the little arrow tells you where it will land. If you hold down the Ctrl key while you do it, you’re copying.

If you’re more of a keyboard guy (ahem), you probably already know that you can use Alt+HOM (Home > Format > Move or Copy Sheet…) to get the Move or Copy dialog. Of course it’s under Format. I mean, really, that’s the natural place for it. Home > Format is the Insert Tab of dropdowns, if you get my meaning.

That dialog defaults to the same workbook, so you’re moving within the workbook by default. Back in 2009, I took over the Move or Copy dialog as I am wont to do. That uses Alt+EV – the 2003 method for displaying the Move or Copy dialog. Back then, I settled on using my mouse to move sheets within a workbook and using my simpler dialog for moving them between workbooks. I’m very happy with my dialog box and use it all the time.

Today I decided to make a keyboard shortcut for moving within a workbook. I already have code to wrap while switching worksheets. I decided on Ctrl+Alt+PgUp/PgDn. Just Ctrl is how you navigate from one worksheet to the next. Ctrl+Shift groups worksheets together. So that left me with Ctrl+Alt. After one use, I can honestly say that I’m in love.

First I add my shortcuts to Auto_Open

and to turn them off in Auto_Close

The code is pretty darn similar to the Wrap Sheets code except that it moves the sheet rather than activating it.

If you don’t want to add this code to your PMW (YET!), you can try it out here.

You can download MoveSheetsKeyboard.zip

Todoist vs GoodTodo

Posted on by Dick Kusleika

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.

This Theme Stinks

Posted on by Dick Kusleika

I found this nice plugin called wp-markdown. It lets you write DDoE comments like Stackoverflow answers, that is, markdown with preview. But it doesn’t work with my 10 year old theme.

I’m sure I’ve needed a new theme for a while. But if you’re like me, and you probably are if you’re reading this, you don’t like change. Now get off my lawn.

So if I switch to a fancy new theme, wp-markdown works. Seriously, try leaving a comment at the end of this post and see how awesome it is.

I don’t know why themes don’t take up the whole screen. I hate it when the words are all squished. I paid for a 22″ monitor and I want to use it!

I’ll leave this new theme up for a few days and see how it goes. In the meantime, I’ll still be looking for a new theme that looks and acts more like the old one.