The Ken Puls Blog

Wow…

CNet reported in an article today that a research firm estimates that only 40% of the 320 million current PC’s around the world can run some flavour of Vista, and even some of those will need memory upgrades.  Further, they estimate that only 15% can run Vista premium editions.

Microsoft’s answer, according to the article?  Don’t upgrade, just go buy a new PC.

I’m not even going to touch on the enviornmental impacts of sending a bunch of otherwise decent machines to the landfills to make room for Vista…

While Vista is a sexy OS, I’d seriously have to question the wisdom of buying a new PC just to get it.  XP works, and will for a while yet, so if you don’t really need a new PC to do what you do, why spend the money?  Why even upgrade to Vista at all?  (This coming from the geek who couldn’t wait to try it out.)  Given my experience with it personally, I can’t think of a compelling reason to upgrade at this point… unless one of the two following points are true:

• You find software that you need which only runs on Vista
• You were in the market to upgrade to a new PC anyway

My wife and I are in the latter, and will be looking at a new PC in a couple of months time.  At that point they’ll come with Vista pre-installed, and hopefully the manufacturers will have had enough time to make sure they work well with Vista, not just that they work.

I’m curious to know how many of my readers here are planning on upgrading.  And if you are upgrading, are you buying a new PC to do it?  Are you upgrading some of your PC’s components?  Or do you think your hardware is good enough?

Over the weekend I've spent a significant amount of time working in Visual Studio 2005 with VB.net. The more I work with it, the more I like the IDE, and some of the cool things that I can do in it. This post, though, is going to focus on some of the differences between VBA and .Net that I'm playing with.

First thing to notice in importance is the existence of Option Strict. As VBA'ers, we're used to using Option Explicit (at least you should be), but Option Strict adds another dimension to your code. As I believe in following best practices, I am coding with both Option Explicit (OE) and Option Strict (OS) set to On.

It should also be noted that while I am quite proficient with VBA, I am certainly not an expert on VB.Net, and I am not trying to pass myself off as such. It's very likely that I will get some things wrong in this, and if I do, feel free to comment on the post. I'm doing this to learn, and blogging to share my experiences with those who have both gone before, and may choose to come after me on this road.

Okay, so here we go...

Early vs Late Binding

In VBA, we have the option of Early or Late binding. Generally, I develop using late binding, mainly because I work in an environment of mixed Office versions. There is nothing worse than having an Excel 2003 user open a file, have the references upgraded, and the 97 user can no longer get in. In VB.Net, with OS on, you must use Early binding.

In addition, OS demands very strict Type casting. I'm used to the following to create an instance of Word in VBA:

This does not work in VB.Net though, as "Option Strict On disallows implicit conversions from 'Object' to 'Microsoft.Office.Interop.Word.Application'."

Instead, we need to use the following to explicitly cast the Type:

Functions...

In VBA, we'd use our Right() function to return x number of characters from the right of a string. In VB.Net, we need to preface that with something more:

Type Casting

Probably the biggest issue that I've run into is casting types. VB.Net (OS on) will NOT convert data from one type to another. This is evident in the binding example above, as well as many other things. Fortunately Option Strict kicks up little error messages to tell you what's up, and after a bit you can generally make some sense of how to fix them. Usually it involves a converting something to a type that we're used to... CStr, CBool, etc... What really threw me, though, was how to type data as an Object. The secret is the Imports statement... For example, look at the MessageBox constants (more on this below).

With a msgbox, you can't just use the constants like vbYesno anymore. You need to use Microsoft.Office.Core.MsgBoxStyle.YesNo. Now that's a bit of a pain, but there is an easier way. Thanks to Mike Rosenblum, who explained this to me in great detail, you can do this:

At the top of your module, insert the following statement:

Now you can refer to that constant as:

MessageBox Constants
In addition to the issue with Type Casting above, the messagebox also changed a bit in that you don't add your options together in .Net as we did in VBA. Let's look at the differences:

Notice that it is an Or statement, not a +. This seems weird, but I have tested it, and it does work. Seems counter intuitive, I'll grant, but what are you going to do.

Final Word

Well, that's my observations so far on language differences. There's way more, obviously, and I'm sure I'll share more as time goes along.

I found this kind of interesting, but somewhat strange...

A couple of nights ago I spent a significant amount of wasted time trying to get a callback to work in Visual Studio 2005. The problem was that I was trying to adapt my Dynamic Menu example to VB.Net, and the callback signatures for VB.Net and VBA are different. That's not really a big secret, and most of the callback signatures (in VB.Net, VBA, C# and C++) can be found in MSDN's Customizing the Office (2007) Ribbon User Interface for Developers (Part 3 of 3) article. Most, but not all currently.

To my chagrin, there was at least one callback signature missing... the getContent callback. For populating the dynamicMenu control, this is essential. That omission has been brought to Microsoft's attention, and hopefully the article will be updated soon.

Now, what's interesting about all of this... here's the signatures:

I'm fairly comfortable programming in VBA, but to me the VB.Net callback is far more intuitive to what I would have expected. In truth, despite working with the VBA signature for a while, I still scratched my head on why I was setting the value of "returnedVal" to a parameter. I knew that it was working, but was not sure why it was not looking like the VB.Net version, and why it was working.

The fact that it works makes sense, though, once you realize what is happening. I should have seen this way earlier, but I've never passed parameters like this. For my own purposes, I think I would have realized what was going on if the VBA version was written as:

Do you see it? I added the ByVal keyword to the first control. To me, this would have probably alerted me to consider this... It's the ByRef keyword that does it.

By default, all objects are passed to and from subroutines and functions ByVal, and the keyword is therefore usually omitted. The key difference between ByVal and ByRef, though, is that when you pass a parameter ByVal, a copy of the object is passed, not the original object. This means that anything you do to the object is done on the copy only, and when the procedure or function ends, the temporary object goes out of scope and the changes are lost. By contrast, when you pass a parameter ByRef, it passes the original variable. Any changes made to that variable are done on the original, and this is how the callback works... changes are made to the original "returnedVal" object that was passed as a parameter ByRef to the subroutine.

Personally, I don't really follow why the VBA callbacks would have been set up as subroutines with the "returnedVal" object passed ByRef. To me this seems like the perfect use for a function, which is obviously the route that Microsoft decided to take with VB.Net.

Why the difference? Is there a good reason why they would choose to go one route with VBA vs the other with VB.Net?

I'm trying out a new script for the site, based on the feedback from my friend Will Riley, called Snap.

For the record, I've also seen Snap in action on one of my other friends sites (Dennis Wallentin's) as well, and had to check it out on Will's site as well.

Snap is a script that you can add to your web templates that will allow a pop-up preview of links contained in the text. It's also got a Search box on it, but the instant preview is the real selling feature. If you like, just try mousing over any of the copious amounts of links that I shamelessly included in this post.

So far, it looks pretty neat, but I'm concerned about the performance a bit. Does anyone find that it slows down the site, or is it still okay? Feedback on that would be appreciated, as the last thing I want to do is kill the site performance for a cool feature.

One thing I have noticed is that my "Write Post" pages in the Wordpress control panel do sometimes have issues when I try to edit them if this script is on.

Okay, I can't stand posting any more shameless links now... like this one, to my main site. Okay, seriously, now I feel dirty...

Update on January 25, 2007

For anyone who is reading this who missed it, the Snap preview looked like the following picture when you moused over a link:

As you might have guessed, I've turned the feature off on my blog...

Earlier in the post, I'd made a comment about Snap affecting the content authoring/editing section of the blog. Because of this, I submitted a bug report to the Snap folks. In addition to what is below, I also pointed them back at this post, as I figure they appreciate the feedback they get. At any rate, here's what I submitted as my issue:

I find that when I'm editing a post in the Site Admin area, it sometimes doesn't finish loading. Instead of seeing a formatted blog post in process, I see all the {p} and {br} coding. Usually this appears briefly on the way to full load, but the full load doesn't happen with Snap installed. Ironically, clicking on the Save button completes the load, and then I have to click Save again.

I got a response from them today:

Thanks. Our tech team is aware of that. Sometimes Snap Preview Anywhere
and WYSIWYG editors do not play well together. Hopefully there'll be a
fix coming, but I'm afraid I can't say when.

I read your blog and wanted to point out that part of the speed issue
was temporary, and has been fixed, but still if there are lots of links
on a page, page loading can be a bit slow. But - happy day! - we have
some tips on how to speed that up, here:

http://www.snap.com/about/spa_faq.php#q

I'm glad they took the time to acknowledge my reply and answer it. That's very cool and much appreciated. I'm also glad they know about the editor issue and hopefully they can make a fix for it.

I wasn't sure if I was going to keep this feature on anyway, but this is serious enough for me that I cannot work with the product, as it makes editing posts virtually impossible.

This probably isn't something that everyone would do, but I figured "what the heck"!

If you recall, back in December I posted about Google Analytics, as well as adding FeedBurner to track the number of subscribers here. In this post, I'm going to share some of that info. Please keep in mind that I haven't really publicized either site much, as this is not really a commercial venture for me. I like to share info, which is the point of the main site, but the blog is really just a place for me to sound off in a less formal environment. While I have Google Adwords on both, neither is really meant to become a full time job to solicit business. I get just enough consulting jobs to earn a little extra funds, which serves me just fine.

At any rate... I added a new "widget" to the left sidebar, just under the orange icons, which tells you how many people are subscribing to the site via RSS. At the time of this writing, there are all of 19. Not many, I know, but I find it flattering that there are even that many people who want to know when I've posted more of my ramblings.

In general, I've been following the Feedburner stats since I installed it. It takes a couple of days before it starts tracking, but once it does, it spits out a report like the following:

Okay, so maybe it isn't the most impressive of all reports, but it tracks the ups and downs. It also fills in the gap for Google Analytics, which tracks basically everything else that you could want.

Again, my sites are not the busiest out there by any means. They're both growing constantly, though, which is great. Hopefully I'll remember to post the entire 2007 stats chart in December to see how it worked out.

Now, here's what I found interesting... I have Analytics installed for both my main site and the blog, and it tracks them separately. This is pretty cool, as I can get separate reports for each. This really showcases the differences that I have in my readership. Rather than let you guess about it, here's the Analytics dashboard for Excelguru.ca:

And the picture below is the dashboard for the blog:

What I found really amazing is the difference in the returning visitor stats. The blog has almost twice the rate of returning visitors that the main site does. Maybe that makes sense, given the higher volume of the main site.

Some other interesting differences:

• The blog's Google referrals are 41.55% (the google.com number is 4.93%) vs the 69.89% of the Main Site. (59% vs 30% of visitors were referred by other websites.)
• The blog visitors tend to read a higher average of pages... granted not much, but it's still higher
• The geographic disbursement is similar, although it seems like there may be slightly less blog readers in the Middle East and Indonesia.
• The blog stats are less volatile on weekends than the main site... but that could just be that the RSS subscribers turn their PC's on on weekends.

So there you have it. That's the performance of my sites in a nutshell.

Now that I've caught you with an alluring title...

This area is actually a great passion to me, as I'm not a big fan of maintenance work. I like to sit down, think my project through, build it, and then never have to worry about it again. A fantasy? Maybe... but I do have some projects at work which are exactly that. One of my databases has been in use for 4 years with very little tuning.

I will, most likely, be teaching a 3 hour course in late March on "Creating Re-Usable Spreadsheets" to an audience of people who work with Excel regularly. They are accountants, and oversee staff members who also use Excel. The point of this seminar is not necessarily to cover cutting edge concepts, but rather to drive home design points to build spreadsheets that can be used, and re-used, without breaking. In addition, I'll throw in some time saving tips throughout the session that can speed up their productivity.

I'm thinking that my approach for the course will probably be based on my Spreadsheet Design Tips article, and will start by:

• Covering the 3 types of spreadsheets
• A discussion on spreadsheet risk

The purpose of the above is to draw attention to how important solid thought out design is, and the risks that can be generated from ad-hoc creation of spreadsheets to solve business issues. I'd like these people to realize that by investing time up front to design their worksheet well, they can significantly reduce training times, error rates, and ensure that worksheets that they pass to their staff come back working, as compared to just looking like they still work.

From there, we'll start focussing on actual design work and tools by way of a case study. This will involve:

• A discussion on layout and efficiency for the data entry clerk
• Usage and implementation of styles
• Using appropriate formulas wherever possible to reduce redundant or unnecessary data entry
• Leveraging Lookup and Index/Match formulas
• Setting up data validation
• Adding form controls
• Applying protection options
• Saving files as templates, and how to determine if it is appropriate to do so

Naturally, it will end off with a question and answer period.

I figure that with 15 minutes per subtopic, (some more, some less,) I should be able to cover off about 2.5 hours, and a Q&A session should draw it out nicely.

So what do you think? Are there any pet pieces that you see that I've missed? Anything that you wonder why I'd cover? I'm curious on your take.

This is not a post on a cool technological thing. It's a post on where technology needs to lend a helping hand...

As many of you know, I'm going down to Seattle in the middle of March to attend the MVP Global Summit. This is an event where Microsoft gathers as many MVP's as can afford to travel to Seattle to come for 4 days. We're then treated (for a geek) to events including keynotes, learning opportunities, networking and the chance to finally meet many of our fellow MVP's face to face for the very first time. It promises to be a great time, and also very worthwhile in my opinion.

Now, despite living just a couple of hours north of the border, and not actually needing a passport to get into the USA, (I can go in on a drivers license if I drive,) I never travel without one. I don't care if I drive, fly, sail or whatever, I just find it makes life much easier to have a passport on hand. Unfortunately mine expired last year, so it's time to do the renewal.

Now, renewing passports is a pain, and my first big complain is that we have to do it so often. To my understanding, the USA and EEC both have 10 year renewal periods on their passports... Canada's expire after only 5. Why is this? Do they honestly think that I'm going to change that much in the next 5 years? At any rate, off to the passport circus we went...

So here's what you have to do...

• Fill out a 3 page questionaire that basically tells them your name and address(es) over the last few years, where you work, and how long you've been a citizen. In addition, you need a "guarantor", who can be an accountant, doctor or police officer or a few other things, to sign your passport to say they've known you for at least 2 years. Finally, a copule of references who have also known you for at least two years.
• Submit two pictues that are signed by your guarantor (saying they actually look like you)
• Submit your original birth certificate
• Submit a copy (signed by your guarantor) of at least one other piece of ID
• Submit a cheque for about $100

Now, you have the option of mailing (or couriering) you application directly to the National passport office, or taking it in to a cough, cough "local" office. (Our closest is about 2 hours away.) If you take it to the local office, there are two lines... one for those who went to the pain of filling out their application online. They are served first all day long. The other is for the people who downloaded a form and filled it out by hand, or who picked up their application at the post office.

The Online Experience

We did not do our forms online. Sound weird for a techie guy? The reason is that I've heard of a few people who tried, and the attempt was an exercise of frustration.

The site is apparently very very very slow, and sometimes just times out. There is no save feature, so you just lose your info and have to start over. Not cool at all. So here's my first critique on the system. You force us to sign up for an account to use this system, why can't we save our info? Even if it is only for a few days, give the option to save!

You have to mess with your printer settings to print out your application. According to my boss, he was given specific instructions to set his left, right and bottom margins to print the form. The problem I have with this is that the settings are persistent. So for the less experienced computer user who can actually follow these directions, now you have screwed up their system. Why can it not be set up to convert to PDF and print it? That is the whole point of a PDF document, isn't it?

In addition, you can't do any more online than you can by writing by hand. I'm guessing that the advantage is that it comes in an easy scanning font for processing, and maybe some data validation is built in. The big advantage is that you get to jump the queue at the passport office if you go there... we didn't plan on doing that.

Getting Photos

Our first step in the process (after getting our forms, I guess,) was to get out passport photos taken. This is a part of the process that just makes me shake my head.

The passport office does not do passport photos. If they did, we would have gone there. Instead, you are sent to find a photographer to do them for you... and I guarantee that they hate it. We went to our local Autmobile Association (BCAA) to get them done. You can see it in their eyes when you say why you're there... they just cringe. (Seriously, our clerk was great, but you know it anyway.)

They sit you on the stool, concentrate for an eternity, tell you not to smile, and SNAP! They take the photo. It prints off on an inkjet printer, then there is a few minutes of measuring it. The rules are something like this:

• You may not smile
• Your face may not exceed a certain number of centimeters in height or width
• Your face may not be smaller than a certain number of centimeters in height or width (probably the same number)
• No glare
• Your head must be x cm's from the top
• A few unknown criteria, left up to the passport office's discretion.

Basically, they want you to look like a crack whore or criminal in your picture. Anything they can do to make that happen will be deemed necessary.

Why am I so bitter about this? I don't care if I can't smile in my photo. To me that actually makes sense, as probably most people don't smile as they hand their passport over to a customs official. I get that. No, what bugs me is the burden on the private businesses.

The passport office is able to reject the photo out of hand, for whatever reason the choose (or not) to disclose. Most businesses are good enough to offer a reshoot for free if it is rejected, and they do their best to make sure that won't happen. But they are guessing as to what is good enough. The rules are subject to the discretion of the Passport office. This hurts the citizen, in their time and passport delay, and the business who bear the cost of labour and materials to shoot and re-shoot the photos. In the intial shot, it took one for me, two for my daughter, and three for my wife to get everything to where "they hoped" they would be accpeted.

If the photo is that important, then why is it not done by the passport office themselves? Why make the citizen, and the business, suffer under this air of uncertainty? Convenience? Personally, I'd rather have to go 8 hours out of my way and KNOW it's accepted than to be sitting where I am now: 1 1/2 months till I travel and hoping that my passport comes through.

The Paperwork

The paper application is actually fairly easy. I am an accountant, so I've signed a few of these as guarantor, but it's still a bit unnerving doing your own. The worst part is taking it in to your guarantor, and asking them to sign in the box, making sure that your signature (which is used for what exactly?) does not touch the edges of the box.

There are a couple of things that bug me about the entire appplication process:

1. What in there can actually tell them that I am NOT worthy of a passport? It doesn't ask about criminal history. Only where I've lived, what year I got my nationality (and how), and a couple of references. There is nothing I see that would flag me as "suspect"
2. I have to send my original birth certificate to them, and they mail it back to me with my passport. Gee, that seems like a real safe thing to do in this era of identity theft, doesn't it?

So...

I would assume that during the passport review process that some kind of criminal record checks and such are done. But does this really need to be done every five years in Canada, where other countries only do it every ten?

In addition, I really question what the application process actually tells Passport Canada that they don't have on file already? And if they don't, then what the heck is the purpose of it in the first place, and what prevents forgeries? Why, if I've had a passport in the past, can I not submit it with updated info, and updated picture, and have the thing renewed? Do they think that the guarantor would say... "No. I don't think that's him anymore. I was sure 5 years ago, but I think that he might be someone else now..."?

If the picture is that vital (which I'm sure it is), why put the burden on private business for it? Sure, they can make money on it, but I'd be surprised to find out that passport photos are a significant part of anyone's business. More of a hassle than a benefit, I would think.

In the end, the biggest beef I have with the system is the uncertainty, and the fact that my official documents are mailed back to me. That is not cool in this day and age. Given my choice, the renewal process would be 99% online: update some info, visit a passport office for an updated photo, and have it couriered back to me (and signed for after producing ID).

I've been moving around in Visual Studio for the last hour, trying to get familiar with it.  As always, I feel that the best way to learn to use a program is to start with a project, so I decided to re-write my templates add-in for Excel 2007.  In truth, I would prefer to re-write my Favourites add-in, but it will be more difficult, so I'm using the templates one as a learning ground.

So here are my first impressions of Visual Studio 2005 from the point of view of someone who is pretty comfortable in the Excel VBE:

• Overwhelming
• Powerful
• Exciting

That is exactly the order that my feelings went, with about 20 minutes between each...

There are so many commands in here, that it's hard to figure out what I'm trying to do.  It took me 10 minutes to figure out how to change the name of my project (right click on it and say "rename"... don't I feel like an idiot now!)   I may have complicated things though, as I installed MZ Tools immediately, just because I didn't have enough tools to get lost in right away. 

To further complicate things, (as if the new UI wasn't enough to deal with,) the code is all VB.Net.  This means that I'm going to have to convert some of my code (how much remains to be seen) to VB.Net from VBA.  The first example I came across was a Property statement.  In VBA, we'd have a Property Let and Property Get routine.  In VB.Net though, it's all in one routine:

Now I haven't even got to the point where I can attempt to debug this yet, so it could be totally wrong, but it does look like the help file example.    If it is right, I actually like this better.  It  sure makes it easier to keep straight, and keeps the code collected better.

Now, back to the Visual Studio IDE... here's some of the things that strike me as really cool:

• You can collapse blocks of code.  In the example above, I can collapse the Get portion, the Set portion, the comment portion, or the whole routine.  Very cool!
• Adding code to deal with the Ribbon is as easy as choosing Project|Add Module|Ribbon support.  (Actually, this is a VSTO template, not strictly VS alone.)  As soon as you do, *poof!*, you have a module set up with the code to create a sample ribbonUI.  In fact, the RibbonX code is in another module, so you can edit it there.  All the info in one place, rather than flipping back and forth between Excel and the CustomUI editor.
• An error list at the bottom of the screen highlights all the errors in your code.  (I have a ton right now!)  This is how I knew that my Property routines were at issue.
• The help files seem to have a lot of information and it's actually helpful.  That may sound a little cheeky, but I always found the help files in Excel 2002 and prior quite frustrating.  2003 was okay, but 2007 seems better from what I've seen.  I may change my opinion on this, but the Property help page was quite easy to use.

At any rate, that's about as far as I've got with it so far, but I like what I see.  I do understand why Microsoft is pushing developers to use this product, but I still feel that they should give the tools to those developing within Excel from start to finish.  The reason I'm in this program at all is because the only hope I have of populating a RibbonX group on the fly is by writing a COM add-in, and that shouldn't be the case.

Just my 2 cents on the matter. 

Well, I've finally done it. I've just finished installing Visual Studio 2005 Professional, complete with patches, the MSDN Library files and VSTO 2005 SE Beta.
I figured that I might as well just bite the bullet on it, as I've been getting ready to re-write my XLG Favourites add-in for Office 2007. News I got today seems to point to the fact that I can't design it the way I want from VBA alone, so a COM add-in will be necessary. I can't say that I'm crazy about needing another program to do this, as it means that I'm staring up from the bottom of a big learning curve, but if we stayed in our comfort zone all the time we'd learn very little.

The install actually went pretty easy, except for one small thing. When I installed Office 2007, I didn't have the .Net framework installed. (I'm running on Win XP now, and hadn't needed it to this point.) Because the .Net framework wasn't installed, all the options for .Net programming compatibility were unavailable. I really didn't think anything of it at the time, as I wasn't really planning on jumping in to this just yet. The problem I ran into was, when I started an Excel project, it referenced certain Primary Interop Assemblies. The issue, though, was that I had an error telling me that the "Primary Interop Assemblies are not installed".

So here I am, looking at the UI of a program that is completely new to me, trying to figure out what to do. I did a full install of every component, and I'd forgotten about my .Net issue. Fortunately Google is my friend, and I found a nice little MSDN article on How to: Install Office Primary Interop Assemblies. (Someone please tell me why Microsoft would create a component with the acronym PIA? Don't they realize that it stands for something else?)

At any rate, I didn't bother to download the redistributable package, as that article somehow triggered my memory on the .Net stuff I didn't install. I hate installing extra stuff that I can avoid (yes, I know I did a full install that I'll probably never use all of, thanks), so I just went back to Office and modified the install. There were about 8 places that I had to check to install .Net programability support.

Now, here's what I don't get. Visual Studio, during the install, checks to see if you have the .Net framework installed, and will install it if you don't. Why does VSTO, when you install it, not check to see if you have the .Net PIA's installed for the installed Office apps and install them if you don't? It would seem to make logical sense, wouldn't it?

At any rate, it's time to start a new journey.

The history of posts on the Ribbon can be found at the Ribbon Example Table of Contents.

In this blogpost, I thought it might be nice to try a real example of something that someone may actually want to to. This example adds a menu to my XLG group, and populates it with all Excel files in a directory specified in a worksheet cell. Clicking the menu item will launch the file, and an additional button control will rebuild the menu if the file path in the worksheet cell is changed. One thing to be aware of, since this is an example only, is that no checking of the directory is done.

This example differs from the others I've blogged about, as it makes a truly dynamic menu. The name of the directory can be changed, and the number of files will vary from directory to directory. This creates a challenge, as we need to feed back XML to the Ribbon on demand, as well as record the details of each of the files since we have no collection to access later. I'm going to make an attempt to explain it all, so hopefully I hit the mark with it.

Base XML Required

The first thing that we need to do is add some XML to our file in order to set up a dynamic menu. This XML will start with an onLoad statement to capture the RibbonUI object (to allow menu updates), and also sets up the correct XML tags for the dynamic menu:

Notice that the rest of the details, namely the tab and group information is still the same as in past posts. We'll still have an "XLG" tab with a "TestTab" group on that tab. In addition, we'll have a menu item on that tab called "My Menu"

Capturing the RibbonUI object when the Workbook is opened
The first thing I do now is add a custom property to the ThisWorkbook module, allowing us to capture the RibbonUI object. Capturing this object will allow us to "invalidate" the RibbonX code, forcing it to rebuild the menu when we ask it to. The code for this (to go in the ThisWorkbook module) is:

Next, we need to add the routine we specified in the XML onLoad statement. It's this code that will actually be triggered when the workbook loads, and passes the RibbonUI object to our property for storage. This routine looks as follows, and goes in a Standard module:

How do we hold our file paths?
What we're going to do in this project is identify all the Excel files in a directory, and add the name of each one to a menu item. In order to open the files later, though, we'll need to be able to call up the file path somehow. In the old commandbar objects, we we could assign that path to the Tag property, or even the ToolTipText property, then retrieve it from there. Not so with the new UI, unfortunately. There is no collection to reference, and all you can get is the ID of the button clicked.

So how do we do it? I can think of two ways.

The first would be to build an index in a worksheet with the primary key (if I can call it that) being the control's ID. You'd then leverage a lookup to get back what you need. It would work, but I'd rather avoid using the worksheet like this if I could. (Just personal preference.)

The method that I've elected to use is a little more complicated, but keeps everything in code and out of the users' way. It involves creating a custom collection in order to store our items for use later. This rest of the article will be based on using this method.

To set up our Collection, we first need to create a new class module called "clsFilePaths", to which we'll add the following code:

(Believe it or not, that's it for the Class Module. )

To complete the setup for our Collection, we then go back to our Standard module, and insert the following line at the top of the module (after any Option lines):

Populating the Dynamic Menu

Now it's time to focus on adding the dynamic content to the menu. In our XML, we specified a routine called "GetContent" to populate the dynamic menu. We've got our Collection set up and ready, so we need to work out how to do this. Our complication is that we need to feed the XML back to the Ribbon to be executed. What I've done to deal with this is:

• Made the GetContent routine start the process of building the XML
• It then looks in the directory and figures out what files need to be added
• For each file it sends the details to a custom function to create the required XML
• That XML is added to the XML string
• The details of the file are then added to the Collection
• Once all files have been dealt with the XML is then passed back to the Ribbon for inclusion

Please note that each button is set up to call the btnCentral macro when clicked. This central macro handler will be important later.

The idea behind the AddButtonXML routine is that it will eventually hold all possible parameters to return dynamic vs static content to return the string. For now, however, I've only dealt with the tags that I need for the example. (To be clear, I'm not using the dynamic portion within, but felt while I was working on each tag that I might as well give all options possible for them.) I tried to comment them appropriately so that you can follow what they do. Both of the routines below go in the Standard module:

With all of the above code in place, your menu should now generate providing that you put a valid path, which holds at least one Excel file, in Cell B1.

Reacting to the Button Click
It's time to make sure that we can open our file. Remember that we used a collection to capture the file path earlier? Well now the trick is to get that out.

Since you cannot use just numbers as your ID for the XML code, we need to convert the ID returned by clicking the button into the index of our collection. This is why we started our Button ID's at 1 and kept growing; it matches the index of the Collection exactly. We can get the ID when a button is clicked, so we just need to extract the number from it, and use it to query our collection. The code below is the central button handler, and does exactly that. It also goes in the standard module:

Updating the menu for a new directory

The final part of this article deals with updating to a new directory, and getting the new files to show on the Ribbon.

This portion is actually done by leveraging the Invalidate method of the RibbonUI object. Remember way back in the beginning how we captured the RibbonUI to a custom workbook property? This is the reason. The routine I use does two things:

1. Invalidates the RibbonX, forcing a rebuild of the dynamic control
2. Clears the collection in preparation for the new menu items

The code, once again to be placed in the Standard module, looks like this:

And finally, we need to give the user a way to trigger this once they put their file path in cell B1. While we could have gone more complicated, such as adding a button to the menu or group, I elected to go the simple route. I dropped a form control on the worksheet and linked it to the following macro:

Conclusion

Adding a dynamic menu is much different than it was in Excel version prior to 2007, since we have to create our own collection objects and properties to hold the information we need. With a little organization, however, it's not impossible.

I have prepared a copy of a workbook that includes all the code used above, as the post may be a bit choppy to follow. Please feel free to download it and comment away. It is a Zip file, as I can't upload xlsm files to my blog yet.
The only instructions for use are:

• Enable macros
• Change the path to a valid file path, which holds at least one excel file, in cell B1 (you can include or ignore the trailing \)
• Press the button to update
• Try the menu on the XLG Tab's "Test Tab" group