Daily Dose of Excel

Hi all

VBE Bookmarks/Copy buffer and Tools>References Enlarger Com Add-ins

http://www.rondebruin.nl/win/addins/vbeaddins.htm

When I create this new page on my site about two very nice com add-ins from Jim Rech I found out that if you want to install them on a machine with Office 2013 you are missing a dll file that you need if you want to register one of the com add-ins. Read the info on the page how you can download and register the missing dl so you can install the add-ins also in 2013 .

Regards Ron

http://www.rondebruin.nl

Recently, I have had to make changes to my “computing infrastructure.” This is one of them.

For the longest time I synchronized files between my laptop and desktop with the help of a flash drive.

Then, I discovered Groove but it didn’t really fit the bill and I don’t remember why.

It was on to FolderShare at about the same time that it became a free product. And, it worked wonderfully.

What was really neat about it was that it synced folders peer-to-peer. The files were not on a server and if both computers were on the same LAN, they didn’t even touch the ‘Net!

Microsoft acquired it and it still worked. It became Live Sync and I dreaded each new version but luckily it continued to work. Though, I noticed there were serious lags in file sync. From what I could tell — and I could be wrong — the server played a critical role in deciding which files needed syncing and that introduced a bottleneck. Live Sync became Windows Live Mesh and at some point it included a cloud-based sync capability, which I never used.

This peer-to-peer sync model fit in very well when I got married and my wife delegated all “computer support” to me. So, I extended Live Sync to her computers, duplicated her files on my computer and and included her files in my backups.

Earlier this year, in Feb. 2013, Microsoft discontinued Live Sync, which by now was named Live Mesh. In researching alternatives, I narrowed the field to GoodSync and Cubby, the only two that supported peer-to-peer sync. GoodSync was priced per computer: $30/Windows and $40/Mac; Cubby works on a subscription: $84/year. Given the proliferation of devices, Cubby made more sense.

The interesting thing about Cubby is that it has a free version that includes 5GB of cloud storage but no peer-to-peer capability. Upgrade to Pro (that’s the $84/year subscription), and it includes 100GB of cloud storage. Of course, with the peer-to-peer DirectSync, I don’t care about the expanded cloud storage. Cubby has other features that I haven’t explored yet including customer specified encryption keys.

Several years back, I wrote an article on how to use multiple cells to simulate conditional formats that involved more than 3 conditions. Three versions of Excel later, I still receive requests related to this post. So, I updated it to include more screenshots and a downloadable file.

In Excel 2003 and earlier, conditional formatting works well for up to three conditions. But even when the number of conditions exceeds that limit, it is possible to do without any programming support. For example, one possible way to show twelve possible rankings through color is shown below.

For more see http://www.tushar-mehta.com/excel/newsgroups/worksheet_as_chart/

Tushar Mehta

I need this procedure automatically called from every entry point procedure I run:

When I refer to properties, I say “The x property of the y object”, as in “The Value property of the Range object”. I’m updating the DDoE style guide to reflect a change a recent editorial decision. From now on, I will refer to properties as “The y.x property”, as in “The Workbook.FullName property” or “The Collection.Add method”. Please make a note of it.

Chandoo is polling his readers to find their favorite Excel feature. There’s almost 50 comments as of this writing and a lot of good features mentioned. A couple of people, including Chandoo, named formulas as a top feature. You can’t say formulas are the best feature of Excel. That’s like saying wetness is the best feature of the Ocean. Or cheesiness is the best feature of the Moon. Formulas are Excel. They are Excel’s essence. Aren’t they?

For what it’s worth, I’ve been using the 2010 UI for a couple of months now (formerly mostly using VBA). The thing I’m most grateful for is autocomplete of Table references. I prefix all my Table name with tbl, so I can type =SUMPRODUCT((tbl {tab} [#A {tab} and half my formula is written for me. I should make a video of that, because typing it doesn’t do it justice. Or I’ll just go find one on Contextures since Debra has created every possible Excel video.

I don’t know if that qualifies as my favorite Excel feature, but I smile a little every time I use it.

Here’s a picture of my new grill. Summer is here.

In Opening Outlook Attachments, I posted some code to open Outlook attachments using the keyboard. Now that I’m back using Outlook, I had to dig out the old code and put it to use. Of course I won’t be leaving well enough alone, but thanks for asking.

The last change I made was to refactor a lot of the code into separate procedures. When my procedure is bigger than one code pane, it’s time to refactor.

I was perfectly happy getting the last attachment on the list. If I had more than one attachment, I would resort to the mouse. I thought I could do better. I change the code so that successive calls opened each attachment in order. A static variable keeps my place. That meant that I needed to get a collection of valid attachments. I couldn’t just grab the last attachment that wasn’t hidden, I need to get them all. Here’s the main procedure:

In English: Get the path to the Temp folder, get the current item (either open email or selected in a folder), collect the valid attachments, pick the next in the cycle, display it. Getting the current item didn’t change from the last version, it’s just in it’s own procedure now.

Next, I collect the valid attachments. I have to check some MAPI property. I don’t understand MAPI properties fully, but I don’t need to to be able to copy stuff from the web. The error trapping is because this property isn’t True or False, rather it exists or it doesn’t.

Once I have a collection of attachments, I pick the next one based on the value of lAtt (the static variable). It’s a reverse order progression, so if lAtt is 0 or 1, it picks the last attachments. Otherwise it decrements lAtt.

Another minor change I made was to check for Error 70 – permission denied. That means I already opened that attachment and it’s still opened. Finally, I display the attachment. This is where things get very imcompletish. Some people send me other emails as attachments. That’s a whole other ball game, so I have to check if the Attachment.Type is olEmbeddedItem. If it is, I display the item. If it’s embedded but not a MailItem, the whole thing blows up. There can be other types of embedded items. I might be able to simply cast miNew as Object and get away with it. But for now, it’s strongly type and will strongly fail if the embedded attachments is not mail. I’ll deal with that when it happens, but for now I don’t care enough.

If it’s not embedded, it’s opened in the same manner as the prior code.

There’s one other problem with this code and that’s the static variable. It doesn’t get reset when a different email is opened. If I open one attachment of three, then use this code on a different email, it will open the second attachment rather than the last. The first email has three attachments, so the code will select the third. The next email has, say, four attachments. When the code runs again, lAtt will be 3, then changed to 2. The solution is to make lAtt a public variable and code up some event that resets it when a different email is active. Meh. It works well enough.

Not long ago, both my personal and business email was managed by Google. Those were the good old days. I had a consistent interface, and more importantly, consistent shortcut keys. Can you guess why I keep refreshing gmail? Now my work email is Outlook and Exchange.

I’ve been setting up some macros in Outlook to try to make it palatable. At least one advantage Outlook has over GMail is VBA. Mostly I can make it do whatever I want. Mostly.

Today I’m starting with an easy one. When I send an email that requires some follow up, I need to get it on GoodTodo. If you’re a GoodTodo user, you know how well it handles email. All I need to do is BCC one of several email addresses and GoodTodo handles the rest. I’m sure most GoodTodo users use the many email addresses GoodTodo offers – you can email to a specific date or a relative date in a variety of syntaxes – but I use today@goodtodo.com exclusively. I just find it easier to get it on today’s list and move it the next time I’m using the list.

To add today@goodtodo to an email I’m composing, I created this simple procedure.

Pretty simple. Then I got to thinking (sometimes that happens). Maybe I would use some of the other email syntaxes if they were as handy as “today” is. All of my todo items go in one of a few spots: the specific day it’s due, today, tomorrow, this Saturday (I’d like to get it done this week, but it’s not critical), the last day of the month (I’d like to get it done someday, but let’s face it…). Every Saturday, my list grows and I’m forced to either push the items off another week, do them, or delete them. The same happens at the end of the month. It’s nice way for me to create the list items and encourage myself to review them on a regular basis.

OK, enough of that. With only a few relevant dates, I changed the code to cycle through them. This macro is on the QAT in the second position, so Alt+2 does the deed.

Nice little arrow anti-pattern there. And the result.

As you are no doubt aware, Google Reader is going away. The public outcry hasn’t brought it back, nor open sourced it, by now so that’s not going to happen. There are some options, but I decided that I wasn’t going to switch until May 15th. That will give the smart people an opportunity to vet the options and tell me what to do. I haven’t tried the options, obviously, so I can’t comment on them. Instead, I want to reflect on what I want from an RSS reader.

But first, a word about RSS. I’ve read and listened to a lot about Reader’s demise lately. A lot of people are characterizing RSS a a two-faced beast (most notably John Gruber, but I can’t remember if it was The Talk Show or some podcast on which he was a guest). One face is the user-interface like Google Reader. It’s how I read web pages. I almost never read a website that doesn’t have a feed. In fact, I read less than a handful of sites that don’t publish the full content in their feed.

The other face is this back-end plumbing, this architecture, this infrastructure that is RSS. How you read websites is a personal choice. But whether you pop over to DDoE to see what’s new or you check a feed reader, this site still publishes an xml file. You could write some software that parses that file. You could buy some software that does it. You could do something interesting like the Spreadsheet Page’s Excel Blog Headlines Page. The XML file is out there. Use it, don’t use it, but don’t get rid of it. It has value. Someone needs to build an interface that gets the masses interested in reading pages from a single source. Just don’t call it RSS and it will be fine.

What do I want from a reader? Here’s what I love about Google Reader:

• Great syncing – Google updates quickly. Really quickly.
• No duplicates – Google does a good job figuring out which posts I’ve read and which I haven’t. There have been a few glitches along the way but all-in-all it’s been solid.
• Good rendering – For the most part, the feeds render the web pages really well. I hate the way it shows DDoE code, but other than that, it’s good.
• Shortcut keys – Google respects the mouse-a-phobes and provides keyboard shortcuts for easy navigation. I can learn new shortcuts, but if they’re not there it’s a deal breaker.
• Ubiquity – I like Reader on the web, in Chrome for iOS, and everywhere else I’ve used it.

Here’s what I don’t like:

• Screen Real Estate – Nearly 1/3 of the screen is used up by non-value added bullshit. Some of that is Firefox, but everyone has to do their part.
• No sorting – My folders are sorted alphabetically, which is the just about the worst way you could organize them. Let me assign a number to each folder that determines the order. Some feeds I read no matter what and some I read when I get the chance. I want the former at the top.

Every feed that I read falls into one of four categories.

News
News is the least important, the highest volume, the most skimmed (as opposed to read), and the least missed. I have some news feeds, but if they ever amassed too many unread entries, I have no problem blowing away large swaths. Some of my news feeds are:

• KETV.com
• AP Top Headlines
• A couple Reuters feeds
• Stack Overflow feeds
• Netflix new releases

You see that they don’t have to be news in the traditional sense of the word. In fact, I should stop calling them that. Their most defining characteristic is that I don’t care if I miss something.

Content
Content are feeds I read because they’re damn good. If I thought I missed something through one of these feeds, I would go visit that site to make sure. I’m going to read every entry posted to these feeds. That may be because I know the feed has short entries that take no time to read (web comics) or the content is just so good that it’s worth my effort to avoid missing any entries (kottke). Content feeds include:

• Family memeber’s blogs (let’s call this content ‘highly relevant’ rather than ‘good’
• Web comics like xkcd, Oatmeal, Savage Chickens, Dilbert, NIH, Scenes from a Multiverse
• Signal vs. Noise
• Street Smarts

Not only do I not want to miss a single post, I want to read these right away. When xkcd publishes, I don’t save that gem for later, I read it right now.

Obligation
Some feeds I read out of obligation. I feel it’s my job to know what’s happening in the world of Excel, so I read a whole crap load of Excel blogs. Some are great and some aren’t. If it’s not a blog that solely exists to sell a product, I’m probably reading it whether I find it currently useful or not. I also read feeds about manufacturing and petroleum. I’m not going to miss a post on these either, but I don’t need to read them today. Mike’s Data Explorer post was awesome, but if I read it five days after he posted, it would still be awesome. I’m not going to list all the Excel blogs I read, you can read my OPML file if you’re so inclined.

Saves
Saves are either long reads or things that I wish I were interested in. The Harvard Business Review has some nice articles, but I save them for a rainy day. Same with Kahn’s Corner, Lowering the Bar, and a host of other sites. I may get to them someday or I may not. But I want them there.

My reader should have two panes. The left pane holds Content feeds. The ones I want to read right now. The right pane holds News feeds. It will be pretty full, but I’ll blow through them in no time. When I have no content feeds, the left pane turns to Obligaton feeds. The Saves feeds never show up unless I specifically ask. I can put any single post into Saves while still keeping the feed in another category.

I can set an expiration on any pane. I may say, for instance, that entries from News feeds go away if I don’t read them for five days. For me, personally, every other pane will be set at ‘never expire’, but others may want their Obligation entries to fall off after some time. The panes show all of the unread entries from the feeds assigned to them. If you want to see entries you’ve already read, you can search for them. Entries can be shown in chronological order or the reverse. Feeds can be rated 1 to 5 stars. A combination of age and rating determines what’s on top in a pane. The feeds inherit all these features from their pane, but can be overridden on a feed-by-feed basis. For example, I read almost everything chronologically so that’s how my news pane will be ordered. A notable exception is Stack Overflow, where I prefer to see the newest (least likely to have been answered) first and I will set that feed to be reverse chronological. All other feeds in the News pane will inherit the chronological property from the pane.

So there you have it. Give me two big panes, keyboard shortcuts, effortless syncing, and a semi-consistent experience across web and iOS and I’ll be fine. Oh, one more thing: I’ll pay $100 per year for the service.

I’m sick of writing this

I want a new argument to the End property. I want to type

I would prefer if this new argument mirrored the code above. That is, I would prefer if it returned a range from the current cell until the last cell in that column. That’s as opposed to returning a range from the current cell to the cell just above the first blank cell, as you should get if you used Ctrl+Shift+Down on the keyboard. But I’d be happy either way.

The other thing this new argument would have to do is know when B2 is the only cell or there is nothing in B2:B?. Oh, screw it. Here’s a table of what I want.

I guess that wasn’t so complicated. It should act just like going up from the last cell except when the column is empty. In that case, it should just return the one cell.

Who’s with me?

Now that Excel 2003 is almost completely out of my life, it’s time to get my many add-ins ribbonized. Here’s what my UIHelpers ribbon looks like.

I used the Custom UI Editor and it was horrible. Did they put the VBE team on that project? I think I’ll find an alternative for the next add-in I convert. I don’t mind XML so much. The absolute worst part of creating a ribbon is finding a suitable button image. I downloaded Jim Rech’s add-in from Ron’s site. I scrolled through some button pages, but with 3,500 images, that wasn’t going to work.

Eventually, I changed the IsAddin property so I could see the list of image names. Then I filtered the names based on some keywords. Once I had a good candidate, I’d go back up to the ribbon, find that button, and look at it. This was much better than browsing through all of the pages hoping I’d find something suitable.

As you can imagine, I couldn’t leave well enough alone. I modified Jim’s code so I could filter the images based on their names directly on the ribbon. Jim had a nice system for what he wanted to do and I spent far too much time trying to shoehorn this new feature into that system. After a while, I started, more or less, from scratch. I should have done that from the start.

I started by adding a textbox to the ribbon where the filter term will go. It looks like this:

I don’t know what all that means, I just copied Jim’s and changed a few things. For the control handling part, I created two variables: mcolFiltered and mcolShown. mcolFiltered is a collection that holds all of the controls that match the filter. If there’s no filter, it simply holds all of the controls. When the ribbon loads, it sets up some module-level variables.

mlStart determines which member of the collection to show first. ResetListToUse gets the button names from a spreadsheet. msWhat is the filter term – set to nothing to start.

SetButtons handles what’s shown. First mcolFiltered is populated with all the control names that match the filter. Then mlStart is validated to make sure it’s within the range. Lastly, mcolShown is populated with which portion of mcolFiltered is on display. Most of the rest of the code just sets one or more module-level variable and calls SetButtons. Here’s some more code, just for fun.

Here’s the ribbon showing all of the controls with “filter” in their name.

As verbose as the image names are, they don’t contain enough information to do a proper search. Searching for “filter” works well enough, but if you want something related to money, you won’t find one search term that gets the job done. We need some meta data around each of the images. The first image is of a zero and is named “_0″. That image needs to turn up for searches such as zero, nil, number, digit, telephone, and probably some others. With 3,500 images, I’m not too interested in filling out all of that meta data. We need to crowd source it. Or hire some poor people.

Come to think of it, all those captchas that you have to type to prove you’re human are a lot of wasted effort. I should make a captcha system that shows a picture and asks the user to type two words that come to mind. I won’t actually check the words against anything, but I’ll use them to build my meta database for these images. Sure the captcha won’t work, but by the time people figure that out, I’ll already have my data. Brilliant.

You can download RibbonButtons.zip

Several years ago I needed to open the newest CSV file from a particular directory. Now I’m faced with a similar problem. My accounting system produces PDFs in some kind of proprietary PDF reader. It doesn’t have near the feature set of Foxit, my preferred reader. Instead of fighting it, now I immediately save the pdf and open it in Foxit.

Drawing from that previous post, I made a function to find the name of the most recently created PDF.

Once I have the name, a simple FollowHyperlink method will get me where I want to go. Oh, except that hyperlinks are bad and Excel needs to show me a warning. That’s not going to work. Instead, I take the long way around. I create a batch file to open the PDF and run that.

The file opens and there’s no warning to click through. Coincidentally, JW has been working around that same security measure, only for MP3 files. There’s an interesting approach.

It works and no message. Good one John.

Finally, I wanted a third method. fzz commented that I should use a console command because that’s what consoles are good at. I made a batch file following his example:

No warnings, obviously, and even though the VBA above is lightning quick, I think we can all appreciate that this is the quickest and most direct way. I’m having a problem running from VBA though.

It got the file name right, but says it can’t find it. As you can see, I tried ShellExecute too. Same result. It doesn’t matter. I have a batch file, so I don’t need Excel. I put a shortcut to the batch file on my desktop and set the shortcut key. Now I can open it regardless of which applications are open or have the focus.