New Excel Help Forums at Excelguru.ca

Hello everyone,

At the last MVP summit I had discussions with a couple of people about something that has been burning away in the back of my mind for quite some time… creating my own Excel forums.

I’ve been absent from the online forums for the last few years, and I’ve greatly missed it. With the amount of teaching I’ve done over the past few years I’ve constantly been referring people to other forums and blogs, and I’ve received several comments from those people asking about a forum on my own site. So during this time I’ve been musing it over, wondering if there is enough differentiation, or enough reason to take on the task of building and maintaining another forum out on the interweb.

Despite missing a few of my good friends at the last summit, it was one of the most exhilarating ones I’ve been too, for a variety of reasons. Some of it was technology based, of course, but there was some based on conversations with people that I never expected to have. The compounding of all of it together made me realize that I have been putting this off for too long, and that I actually needed to do it, to bring back the piece of me that I’ve put off for my job over the past few years.

To this end, I’ve begun the process of building another forum on the web, one that focuses on Excel and Excel users. I am hoping to grow it with the style of friendly community that was fostered at VBAExpress.com, but have the focus strictly on Excel users from start to finish, no matter the platform. There are forums there for Excel Client focused areas, Programming (VBA, .NET, XML, etc), PowerPivot and data needs, the recently released Excel WebApp and even Sharepoint. As I said, I want this to be a one stop portal for anything Excel related.

In addition to the Excel focused stuff, I’ve also added a simple section on the other Office apps. The reason for this is simple; every Excel user encounters the other Office apps, and runs into issues. I think it’s important that we have a place to ask/answer those too.

Oh, and naturally there is a chat forum, as no forum would be complete without it.

The forums are, of course, completely free to use. Simply sign up for an account, and post your questions. You’ll be notified immediately by email when someone replies, so you can jump right back to the forum and post further information if required, or see the solution that an expert has helped you build. You can even upload your own files to the forum so that people can see EXACTLY what you’re trying to do. After all, a picture saves 1000 words, right?

And if you’re an expert, or even if you’d just like to help, then post some answers! We love help, and encourage you to take part. Speaking from my own experience, I can tell you that there is no better way to better your own skillset than by trying to help others. You won’t always have perfect answers, but nor do we expect that. This is all about people helping people. The intentions are what is important.

You can find them at http://www.excelguru.ca/forums. (The registration button is in the top right of the screen.) Or, if you’d prefer, you can jump straight to the Registration link to create your account.

I look forward to seeing you in the forums!

Debugging RibbonX Invalidate/InvalidateControl failures…

I ran into this the other day when I was working on a file and it drove me nuts.

Background:

I always use a custom property to contain my RibbonUI object. It’s set up like this:

The following code goes in the ThisWorkbook class module:

Private pRibbonUI As IRibbonUI

Public Property Let ribbonUI(iRib As IRibbonUI)
‘Set RibbonUI to property for later use
Set pRibbonUI = iRib
End Property

Public Property Get ribbonUI() As IRibbonUI
‘Retrieve RibbonUI from property for use
Set ribbonUI = pRibbonUI
End Property

And then the onLoad statement, placed in a standard module, looks like this:

Private Sub rxIRibbonUI_OnLoad(ribbon As IRibbonUI)
‘Set the RibbonUI to a workbook property for later use
ThisWorkbook.ribbonUI = ribbon
End Sub

At that point I can invalidate the entire ribbon by using:

ThisWorkbook.ribbonUI.Invalidate

Or just a single control:

ThisWorkbook.ribbonUI.InvalidateControl “ControlName”

The Issue:

This particular problem started for me when my InvalidateControl methods weren’t working. This was irritating, but I’ve debugged a lot of RibbonX before, so I thought it may be the control name. After checking the XML I was pretty sure it wasn’t, but just for grins I switched to try to Invalidate the entire ribbon. That didn’t work either…

Next up on the debugging mission was to step through the code, at which point I discovered that I had no reference to the RibbonUI object. As you’ve no doubt figured out if you worked with the ribbon, you can’t invalidate your controls without a RibbonUI object to work with. So somewhere I thought I must be triggering an error which caused the RibbonUI to lose scope. Despite a ton of searching through the VBA though, I couldn’t find anything that would trigger this kind of error.

Given that my code all looked good, I added a single line into my OnLoad statement so that it read as follows:

Private Sub rxIRibbonUI_OnLoad(ribbon As IRibbonUI)
‘Set the RibbonUI to a workbook property for later use
Debug.Print “Fired!”
ThisWorkbook.ribbonUI = ribbon
End Sub

I then opened the file again, and found no message in the immediate window. Hmmm… The callback was obviously never called at all. This is a little strange, as this callback should be triggered as soon as the file was opened. So then I checked the remaining things that could cause these kind of issues:

  • I checked the Trust Center to ensure that:
    • macros were set to “Disable With Notification”
    • The folder where the file was stored (on my network) was a Trusted Location (and that subfolders were also trusted)
  • I checked that the onLoad callback matched that generated by the customUI Editor
  • I check that UI errors would be displayed (just in case my XML wasn’t valid). You can find this under OfficeàExcel OptionsàAdvanced near the bottom of the list:

None of these earned me any joy though. Things still didn’t work. I then emailed the files to a friend and guess what… they worked!

With the problem isolated to my machine, I then disabled all my other add-ins and opened Excel fresh. Lo and behold, it worked!

The Fix:

When we wrote the RibbonX book, Robert and I standardized on naming the onLoad callback rxIRibbonUI_OnLoad. I’m starting to rethink that advice a bit…

In my case, I had two add-ins with this same onLoad name, one of which had the onLoad callback declared as a Public routine, rather than private. This caused a conflict when I wrote a new add-in and used the exact same procedure/callback name. Just to make this clear:

Add-in #1 used:    Public Sub rxIRibbonUI_onLoad(ribbon as IRibbonUI)

Add-in #2 used:    Private Sub rxIRibbonUI_onLoad(ribbon as IRibbonUI)

Even though Add-in #2’s onLoad was correctly written, at load time it did not run. I didn’t actually test this to be totally sure, but I’m 99.9% confident that Add-in #1’s onLoad DID fire instead.

So some suggested rules for you if you’re building an add-in:

  • Code to avoid conflicts. Even if you are only writing one add-in, you never know if you’ll end up installing one from somewhere else. If both you and the other author followed our naming convention, and the other author screwed up their callback by declaring it as Public, YOUR add-in will not run correctly. (Nice, eh?)
  • Declare (ALL) your RibbonX callbacks as Private. This will avoid pushing this issue on someone else. By default, the customUI editor authors its callbacks as Sub blahblah(). Omitting the Private keyword by default leaves it as Public.
  • Forget about Option Private Module. If you think you can use Public callbacks and just declare the module private with “Option Private Module”, see rule #2. (Trust me. That one I did test, and is what led to this whole mess.)
  • Add a project specific name to the end of the callback. E.g. “rxIRibbonUI_onLoad_FWBudgetTools“. While I still believe in standards, you need to tag it with differentiating text to avoid conflicts with other add-ins. Oh sure, it may look long and unwieldy but let’s face it — you’re only going to write this routine once per project and forget all about it after that!


RibbonX available in Kindle edition

J-Walk posted that his Excel VBA Programming for Dummies has been Kindled.  I was a little surprised to find out that RibbonX – Customizing the Office 2007 Ribbon has as well!

I’ll be completely honest here… I have a pretty limited idea of what this even is.  From what I gather, it sounds like a palm pilot style book reader.  I can’t really see being interested in that myself, but there you go.  So if you’ve been waiting for it, now it’s here.  :)

Activating a Tab on the Ribbon

If you’re working with the Office 2007 Ribbon, you may have come to the conclusion that there are areas of, the RibbonX object model which are kind of weak. One of the areas that lacks in the RibbonX API is the ability to programatically activate a specific tab.

The good news is that there is a way to select Ribbon tabs using VBA. The bad news is that we have to do it using SendKeys. Why is that bad news? Well… SendKeys does pretty much exactly that… it sends keystrokes to the user interface. This means that it isn’t totally reliable, as users can mess up what windows may be active, send other keystrokes, or whatever. Regardless, it’s the only way, so here’s how it works:

Activating Built in Ribbon Tabs using VBA

In Excel, to activate the Home Tab using VBA, you’d use:

[code]ActivateHomeTab()
Application.SendKeys "%H%"
End Sub[/code]

In Word:
[code]ActivateHomeTab()
WordBasic.SendKeys "%H%"
End Sub[/code]

What this does is send the ALT key (using the %), then the H key, then the ALT key again (the final %). The reason that we send the second Alt key is to clear the ALT key from being active. Alternately, you can also use {RETURN} in place of the second %, which will work as well.

You can test this yourself, by pressing those keystrokes manually. This also lets you figure out exactly which keys to use for which tabs. ;)

Activating custom Ribbon tabs at application startup

Now, for reference, the remaining portion of this post was covered in our book RibbonX – Customizing the Office 2007 Ribbon on page 404 under the heading “Activating a Tab at Startup”, and is geared towards Excel. You can sub in your own tabs and adjust the VBA for Word based on what I showed above. (It’s slightly adapted here, but only slightly.)

To activate your own tab at startup, you’d use something like the following XML. (Pay attention to the keytip.)

[code][/code]

And then the following line of VBA gets inserted into the onLoad routine:

[code]Application.SendKeys "%UN{RETURN}"[/code]

size Attributes in dynamicMenu

A user asked a question in Experts Exchange asking if it was possible to use a dynamicMenu in the officeMenu.  The answer is that yes, you can, but this person couldn’t get it to work.

The issue appears to be with the following code:

[xml]

[/xml]

Specifically, it was the size attribute causing the issue.  If this same code were used in a tab, it wouldn’t be an issue.  The officeMenu, however, will not let you control the size.

This isn’t the first time I’ve run into something like this.  It’s in this kind of situation where the CustomUI editor is key to solving issues.  By taking your XML code and validating it in the CustomUI editor, you can identify these issues immediately.

An Excerpt from RibbonX – Customizing the Office 2007 Ribbon

I’m very pleased that Wiley Publishing has encouraged me to post an excerpt from our new book RibbonX – Customizing the Office 2007 Ribbon, which can now be found at Amazon.com and other online retailers.

Chapter 2 of the book focuses on how to access the XML layer of the new Office 2007 file formats. We cover the hard way (renaming the file to a zip format), using the free CustomUI Editor tool, and also discuss XML Notepad. The excerpt that I have picked to share, from pages 41-43 of Chapter 2, actually contains two parts.

  • The first part discusses how to create an XML template in the CustomUI Editor, which can then be applied to other files later.
  • The second part provides some notes about using the CustomUI Editor, specifically exposing the “Gotchas” that are evident in this tool.

I chose this section because it was referred to throughout the book, rather than reproduce the framing XML code. I also chose this section because Wiley has generously allowed me to post the entire Chapter 7, focusing on the comboBox and dropDown elements, on my site. By reproducing this section of Chapter 2 here it means that you’ll be able to follow the entire Chapter 7, which will be up in a day or two.

The Chapter Excerpt

Storing Customization Templates in the CustomUI Editor

You may encounter scenarios in which you want to refer to a custom UI that you previously developed. Again, you can use the CustomUI Editor to store and access custom templates, as shown in Figure 2-14.

fg0214.png

Figure 2-14: CustomUI code templatesTo demonstrate the ease and benefits of setting up your own templates, use Notepad to create a new text file on your computer and enter the following code into it:

[xml]
startFromScratch="false">



[/xml]

Save the file in the folder Program Files\CustomUI Editor\Samples with the filename RibbonBase.xml.

note.png The Program Files\CustomUI Editor\Samples path assumes that you used the default installation path for the Microsoft Office 2007 Custom UI Editor. If you installed the program to a different path, you will need to modify the preceding instruction accordingly.

tip.png If you cannot access this file in Windows Vista, it is due to the User Access Control feature restricting your permissions. You will need administrative rights in order to store these templates in the specified folder.

Finally, open the CustomUI Editor and select the Samples menu. If your new file is in the right place, you should now see the RibbonBase entry on the list. Click it and voilà! There is your XML template, which you can use to start all future Ribbon customizations!

Now that you have this on your menu, how do you use it? It’s quite simple, actually. Just create your new file, open it in the CustomUI Editor, and select your RibbonBase from the menu. It copies all of the code into your file for you, and you’re good to go! What a great way to get a head start.

caution.png If you open a file that has customizations in place already and select your template, all of your current customizations will be overwritten with the template’s code. You can, of course, close the file without saving and not lose your current work.

Some Notes About Using the CustomUI Editor

The CustomUI Editor is a fantastic utility that makes editing XML code much easier. As with all programs, however, it is not perfect. You should be aware of some CustomUI Editor “gotchas” before you dive right in:

  • The CustomUI Editor does check the form of your XML tags, as well as make sure that you only use attributes that are defined in the XML schema. What it does not do, however, is check that you have provided valid attributes within the quotes. (You’ll learn about attributes later, but be aware that you can still receive errors even if your XML is well formed.)
  • While writing and debugging your RibbonX code, it is very tempting to open your file in both the application and the CustomUI Editor at the same time. Trying to save the file in the CustomUI Editor while the file is open in the Office application will result in an error. In addition, even if you close the document that you are editing in the Office application and then save it in the CustomUI Editor, the editor will overwrite any changes that you made while editing your document in the application! It is much safer to close the file in each application before making changes in the other.

note.png If you forget and make changes in the application, you can preserve those changes by saving the file to a new filename. At least then it can be a reference if you want to incorporate the changes into the original document.)

  • The CustomUI Editor does not have a Find/Replace utility, so if you’re planning to do large-scale editing on your XML, you may want to copy your information to another program, edit it, and then copy it back.
  • When working on files that have more lines of XML than will fit on the screen, the CustomUI Editor has an annoying habit of refreshing the screen so that your cursor is always on the last line of the screen. If you are trying to make an edit and want to read the information three lines below, this can become a major irritation. Here, again, you may want to copy your XML data into another editor to work on it and then paste the updated copy back into the CustomUI Editor.

700 pages

A couple of days ago, Johan asked how it was possible to write almost 700 pages on the Ribbon… I figured that this was probably as good an opportunity to tell what is in the book (RibbonX – Customizing the Office 2007 Ribbon) in my own words, as well as what we were trying to accomplish with it.  Due to the fact that we weren’t really allowed to talk about it as we wrote, I didn’t really get to blog much about my experiences.  So here goes…

First off, there is an index and a table of contents that add to the bulk a bit.  I’m going to blame that for a bit of the inflation.  ;)

The book naturally starts off with an intro chapter that discusses what is needed to customize your UI in Office 2007, compared to the way we did it in previous versions.  It’s sure not as easy as importing J-Walks’ table driven menu code and filling out a short survey any more!  :)

From there we got right to the content: A full chapter just on XML, how to read it, and how to build it.  Next we described the different methods for attaching a simple CustomUI to your project using both the unzipping/zipping method, (for Excel and Word files,) as well as the CustomUI editor.  We also discussed XML Notepad as well, as it is another tool that can be used to the developer’s advantage.  Both these tools require the installation of the .NET 2.0 Framework on Windows XP, so that needed to be covered as well.

Once through the XML basics, we had to give the user the basics of VBA as well, since it’s fundamental if you want to get more than just moving Microsoft’s commands around.  So really, there was quite a lot before we even got into working with the Ribbon.

The meat of the book is broken down to cover all of the individual Ribbon elements, from the button to comboBox to dynamicMenu, and also has a dedicated chapter on using different attributes to format controls.  Every element is provided with complete (and legible) tables that detail what attributes it has, their default values (if any), acceptable parameters and callback signatures.  In many cases, images are even provided to demonstrate exactly how certain attributes display on screen.

It was really important to me that each chapter be something that you could refer back to when writing your code.  I wanted every user to be able to get the answer to this kind of scenario… “Why doesn’t my dropDown work?  Oh!  It doesn’t support the attribute that I put in!”

What really added to the page count, though, was that we chose to demonstrate by example as well.  There are full examples, rife with XML and VBA, of using each element.  In 90% of the cases, we included an example in each of Excel, Word & Access.  We walk the reader through the creation of the file, as well as hooking up the pieces of code to make things work.  We really made an attempt to teach the “how”, rather than just give a “this is the theory, now good luck”.  I can’t even begin to count the hours that I spent coming up with examples, building them, testing them, and documenting them.  There were a lot that ended up on the cutting room floor, too, some of which ended up as “CAUTION” notes in the book.

After discussing all the basic controls, we felt that it would be really useful to give some examples of using attributes to provide help to your users.  User interface design and efficiency is a bit of a passion of mine, and after all, this entire book is about creating a good UI.  We felt that drawing the readers attention to some tricks to make life easier for the end user would be pages well written.

And then it was time to move into more advanced VBA, the Office menu, QAT and contextual tabs as well.  These chapters are packed with stuff that developers will find useful, my favourite of which is probably Robert’s Table Driven QAT customization.

I’ll say that the hardest chapter to write of the entire book was Chapter 16, which was on deployment, but I’m really happy with the outcome.  Being an Excel geek, the Excel part was simple, and I really enjoyed it.  Tony Jollans helped me a bit with the Word side of things, and the material there is much better for his doing so.  Both of the above cover using regular files, templates and add-ins/global templates.  In addition, we discuss sharing tabs across different files using the idQ attribute here, and have some pretty cool examples in Excel and Word of doing so.

There’s much more in the deployment chapter as well, including a full subsection on deploying Access databases, written by Oliver Stohr.  While deployment of a full Access file to a user with a full Access install is a breeze, Oli covers how to do it using runtimes, locking down the UI where you don’t want owners of the full version to tinker with your product, and much more.  He did some great work there to be sure.

The final chapter is on Security, and details how to set up the Trust Center for both you and your users.  It ends with a discussion of digital certificates, walking the user through creating and installing certificates created with SELFCERT.EXE.

And finally, the appendices included table after table of tags, from the names of tabs, groups and commands in each application, to imageMso and keytip information.  Our RibbonX naming conventions are also provided there, as well as an Appendix that details RibbonX resources on the web for anything we may not have covered.  Wiley is also hosting all of our example files on their site for download as well.  All of the examples we create will be there, as well as some additional files.

As you can see, we tried to make this book as full and complete as we possibly could.  I’ll be honest in saying that I never thought we’d come close to this many pages at the outset, but once we started, the page count just seemed to keep mounting.  I’m fairly proud of the end result, and can honestly say that I was referring back to my own material constantly when trying to customize things at work.  My only regret about this book is that we couldn’t have a CD included with the PDF version of the entire thing.  J-Walk has that for some of his, and its simply an awesome bonus.  I understand the concern, though, as it could be easily passed around, but for those legitimate buyers the ability to just put the PDF on your laptop, rather than carry the book around… it’s a nice bonus.  Maybe next time.  :)

I guess to summarize it all, there are a few factors that contribute to almost 700 pages… the fact that there are so many prerequisites to customization now, the HUGE amount of examples, and the large amount of reference tables and images that you’ll find between the covers.  Overall, I really hope that people find this book a valuable resource for RibbonX customization.

The full table of contents is available on Wiley‘s site, if you’d like to know the breakdown of the chapter structure.  And, of course, you can pick it up at Amazon.com, among others.

Okay… enough about the book.  It’s time to get back to other things.  ;)

I am officially an author!

I got confirmation this morning that RibbonX – Customizing the Office 2007 Ribbon was sent to the printers on Wednesday. :) (I blogged that it was coming here.)

0470191112.jpg

The publishing of this book, which happens officially on December 17, 2007, makes me a third generation author. Even better, though, I’m a third generation author, specializing in the field of Bedtime Reading for Insomniacs!

  • Back in 1978, my Grandmother, Herta Puls, wrote a book all about the textiles of the Kuna Indians of Panama. From my understanding she was an expert on the subject, and was fascinated by Mola’s. (This involves several different layers of coloured cloth. The individual would then cut patterns into the cloth to different levels of depth, creating colourful patterns. Most of these patterns were abstract, until white man arrived on the scene and started requesting pictures. In my grandmothers eyes, this transformed the entire work of the people and ruined the style forever.) What amazes me the most about this book is that you can apparently still order it, as I found a link for it on the net. What’s even funnier, though, is that in searching I also found a rant that she wrote recently. I honestly didn’t think she’d embraced the internet, as she’s in her 90’s. Shows what I know, I guess. ;)
  • My father, Robert Puls was the world’s leading expert in animal toxicology. (He retired a few years ago, which is why I say “was”.) I like to summarize his job as analyzing blood and serum samples to see why animals died. The more detailed version is that he looked for deficiencies and toxicities of vitamins and minerals in the blood, and through his 30 odd years built up the definitive guides that many others in the field refer to. He has published three books in all; “Mineral Levels in Animal Health”, “Vitamin Levels in Animal Health” and the Bibliographies of Mineral Levels which, if you can believe it, was as good a seller as the other two. Dad’s books have even been converted into an electronic database by a company called ZinPro. If you really are an insomniac, and want something to put you to sleep, I’d highly recommend the first set. The contact information is here, and yes, the covers really are that boring! (I know, I know… the site is ugly. My parents have been bugging me to do something about that.
  • And now me. The third generation of writers that is on a topic so… well… let’s just say that many people wouldn’t be interested in our passions. ;)

At any rate, if you ARE into my world, an insomniac, know someone who fits into either category, or just don’t know what to get someone for Christmas, pre-orders for RibbonX are already being accepted at Wiley, Amazon.ca, Amazon.com, Chapters.ca and many others resellers. If you need to have it under the tree on the 25th, though, you may have to visit a local store within the last week before Christmas.

Where I’ve been…

Some of you may have noticed that it’s been a bit quiet around here lately.

Shortly after the MVP Summit back in April, fellow Excel MVP Robert Martin invited me to co-author a book on customizing the Ribbon in Excel, Access and Word. Since that time, I’ve been spending virtually all of my free time working on this project, which means that I haven’t been seen too much in the forums or on my own site. I’m happy to report that we’re very close to the end of this project now, and that the book should be out early in December 2007/January 2008. You can find it at Wiley‘s site, among others. I’ve also noticed that pre-sales are being accepted at Amazon now. :)

It’s an All MVP team that’s been involved with the project, which is kind of cool too. The team consists of:

I’m hoping that this makes a useful read for (more than) a few people. It’s geared at fairly wide target audience; from beginners who know no code at all through experienced developers who have not been exposed to the Ribbon. We’ve packed it full of tables and practical examples, and discuss the full development cycle from creation through to deployment of the created tools in Excel, Access and Word.

One thing that was important to me is that Office developers like myself, those who migrated into code after learning their program, had a book geared towards them. Therefore this book is PURELY Office and VBA. There is no VB.Net/VSTO here. I’m certainly not saying that there is no need for a book like this geared towards the VB.Net/VSTO crowd. There is, but I’m leaving that for someone else at this stage, as I’m not experienced enough with .Net to be able to write it. I will probably get back into fooling with .Net again once this is done, though.
So that was the first thing that’s been keeping me busy… but there is more!

On September 18th, I received at promotion at work. Our General Manager stepped down, my boss accepted the role of Acting GM and I became Controller of Fairwinds. (For those who don’t know, a Controller is the person responsible for all of the accounting, financial statements, and generally making sure that the company is being run in a financially sound manner.) I have hired someone to take over some of my old tasks, and she started this past week. And then, as only Murphy could predict, one of my other staff was offered a great job at another company, so tendered her resignation Friday morning. Thankfully it is October, and our business cycle is slowing down at the moment!

All in all, the Controllership is a huge opportunity for me, and is already involving a lot of learning, which I am very happy about. I’ve been with the company for over 8 years now, and am looking forward to being part of the team that really pushes the company forward to some solid success. What this means to my posting abilities remains to be seen, of course. I am working longer hours than I used to, so that cuts in a bit already. My community contributions are an important part of my life though, and one that I miss greatly. I do have intentions of getting back to form in the next few weeks, as soon as the book is finally put to bed for good.

One funny thing about the promotion is that I celebrated by teaching a Beginning Excel course to a forestry company in the area the very next day. (Okay, it had been planned for a while, and some of you contributed to the ideas for it.) The course went very well, and I really enjoyed doing it.

Now, just because that isn’t enough to talk about, this article was published about me in the August/September issue of CMA Magazine. The magazine is the bi-monthly magazine that goes out to about 44,000 CMA’s and students in Canada. The interview was back in July, which is why it bears my old title at work, but still pretty darn cool. :)

And finally, I should also mention that I was re-awarded my MVP award for the 2007-2008 year. I’m very excited about that, as I really felt shy on public contributions from the summit forward. So a big thanks to Microsoft for that.

It’s been a busy fall for me so far… ;)