The Ken Puls Blog

A friend just asked for some help with making a getSize callback work with some buttons. I figured this should be an easy one, and dove into the RibbonX book to check the table on page 172. Aha! There it is in the middle of the table:

So I whipped up a quick example to prove it out and… wtf? I started getting an error message:

Interesting… it returned a value that could not be converted to the expected type. What the heck is that about?

When you set up your XML code, you use normal or large in the code. So far as I know, they are the only allowed values, although I can’t say I tested that. What I did test, however is both normal and large in VBA callbacks. Both return the error listed above.

Interestingly enough, I decided to try passing a value of 0 or 1 to the callback via VBA and it worked! So then I gave it a whirl with true and false. Likewise, it worked.

So it looks like, in order to use a getSize callback with a button, you actually need to pass one of the following to the callback:

Just in case anyone wants to have a play with this, I’ve also attached a workbook here: getsize.zip. It also shows how to use getLabel on a tab, as well as getImage on a button. (It’s in a zip file, so you’ll need to unzip it first as my blog won’t accept xlsm uploads.)

As I mentioned in this post, I started working my way through the .NET Hello World example in PED 2nd Edition. I ran into a funny issue though, where it kept opening up my application in Excel 2003 instead of 2007. I fired off a quick email to Dennis who was kind enough to give me some pointers.

As it turns out, my “Current Version” of Excel on my laptop was set to 11 (Excel 2003). My attempts at running Office 2007’s Detect and Repair feature, to reset this to 12, were causing the Microsoft Bootstrapper to crash. (So it wasn’t completing.)

Today I decided to install Acronis TrueImage to take a backup before I mucked with this any further and, lo and behold, it pointed out that it couldn’t install until it killed the suspended install of Office 2007. I gave it the assent to kill the job, and then my Detect and Repair ran fine. I’m now back up to Excel.12 as my current version, and my “Hello World” application works nicely.

I’m only about ½ a chapter into PED 2^nd edition, but the .NET stuff is working for me so far. I’m looking forward to digging a little deeper into this now, and trying my hand at a full managed COM Add-in.

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!

A little background

My staff and spreadsheet users will tell you that any time I build a spreadsheet, there are always shaded cells on the grid. I preach that "Green means go", and make sure that any cell they enter data in has a green background. I also use blue backgrounds for "update these sometimes" cells, like tax rates. If cells are left with no colouring, though… everyone here knows that they should be left alone.

The case I was working on today was a development report, of which I have a few data entry cells scattered throughout the overall file. You could argue that this violates a good design principle in that you should keep the data separate from the report, but in this case I'm comfortable with the design. I have shaded my input cells in green, and know that I'm not going to lose track of them.

The problem that I decided to address today was that the green backgrounds print. While they're great for telling the user where to input data manually, our Asset Manager isn't really interested in seeing that kind of info. In fact, it's distracting, so better left off. But how do you do that?

I could create a full new report that essentially duplicates what I've got, but then it's both a maintenance headache and performance hit. Not really where I want to go. Instead, I opted to use a conditional format to hide the colour on the cells when I go to print the sheet. This would have been really easy, if it weren't for the fact that I wanted to do it automatically. That involved dipping into some VBA. If you're not comfortable working with VBA though, read on anyway. You can still do the setup for this and control it manually.

I'll get into the steps I used to solve the issue in just a sec, but I wanted to say that, to me, this article illustrates three things:

• If you aren't aware of conditional formatting, you should be
• If you don't know VBA, you should learn it
• There should be a Workbook_AfterPrint event in VBA

Step 1 – Setting up the Control Point

The first thing I decided to do was to create a cell to control what mode I'm in. i.e. Printing Mode or Working mode. There's lots of ways I could have done this, but since I have a worksheet dedicated to being a "Control Panel", I set it a cell on that worksheet to hold a "Yes" or "No" value:

As you can see, I use this approach to drive a few other things as well, such as dates for column headers, the type of data pulled from a PivotTable and more.)

Before someone asks why not True/False values, I opted to use Yes/No as it's more readable to others if they have to work with the spreadsheet.

Step 2 – Name the Range

The next thing I did is assign a name to the PrintMode value. In this case, I assigned the name rngPrintMode to the cell B11 on my control panel worksheet. While this isn't truly required to make this work, I just find it much easier to work with the named range later, rather than try to remember the cell reference. It does become key when you move to the VBA side though. VBA doesn't update its reference to the cell when a line in inserted above, while the name range does. For this reason, I always used named ranges when referring to worksheet ranges in VBA.

Step 3 – Set up the Conditional Format

The steps shown below are for Excel 2007, but with a little ingenuity, I believe you could make this work in 2003. I'm assuming that you already have a green (or other) background on your data entry cell at this point.

1. Select the data entry cell(s) that you want to hide the colour on
2. On the Home TabàConditional FormattingàNew Rule then create a formula as shown below:

   

3. Click Format
4. From the "Fill" tab, click "No Color"
5. Click OK twice

At this point, the conditional format has been set up. As an optional step, I actually also set the "Stop If True" option in 2007, as this allows me to use multiple conditional formats on the same section, yet ignore them all for printing. You won't find this in Excel 2003 though. (Also, if you want to stop all formats, make sure the rule is at the top of the list!)

As far as the setting up the conditional formatting, you're actually done. Change the value on the rngPrintMode cell to "Yes", and your conditional formats should kick in, removing any applied background colours. Now you can print without the data entry cells conspicuously marked! Change it back to "No", and the colours re-appear.

Just a hint here… if you want to leave this as a manual control, I'd suggest setting up a data validation list to control the field. Debra Dalgleish has a great article on doing that which you can find right here.

Step 4 – Adding Automation

Personally, I don't do manual if I can help it. Maybe I'm pretty lazy, but I want this to automatically kick in when I hit print, and set itself back once it's done. So to do this, I added a couple of procedures to my project.

In a STANDARD MODULE, I added the following:

You'll need to change your worksheet name for the one that holds the named range you defined earlier. Just change "Control Panel" to "Your Worksheet Name".

Then, I went to the THISWORKBOOK module, and put the following in it:

At this point, providing you had things working manually in step 3, things should just work by hitting the print button. The BeforePrint routine will call the PrintingActive routine, which will flip the rngPrintMode variable to TRUE. This will in turn trigger the conditional format rule. The Application.OnTime then schedules a call of the PrintingActive routine to run in one second. The worksheet(s) are then printed.

The part that bugs me about this approach is that I have to rely on using OnTime, which kicks off a routine based on time, not necessarily when my existing routine completes. Theoretically, if I had a really huge print job, the call to PrintingActive could be triggered before my job is complete. In my tests that didn't happen, but if it does, just add a few seconds to the TimeValue. I.e. "00:00:15" will turn it into a 15 second delay.

The ideal solution would have been to leverage a Workbook_AfterPrint event, but sadly one does not exist.

Conclusion

For my needs, this works. I now just click the print button and all of my green cells change their backgrounds to white (transparent), the file prints, and then the colour(s) are reintstated. And it all happens without me having the change the cell value manually.

I really like the new icon sets that are in Excel 2007. They're kind of a neat way to format a cell to show interpretive information at a glance.

One practical place to use these is as an alternative to the using custom number formats that I blogged about last year. I decided to use Excel's icon sets to show a green check when something was positive and a red x when negative, as shown below:

One thing that can be frustrating when trying to set this up is that Excel forces you to use the three part icon set. By default, the image above would have a yellow exclamation beside the zero. Personally, I don't need it. But with Excel forcing it on you, how do you avoid it? Here's how I did it:

• Select the cells:

  

• From the Home tab, choose Conditional FormattingàIcon Setsà More Rules

  

• Using the default of "Format All Cells Based On Their Values", set up the rule as follows:

  

• You'll need to change:
  • The "Type" fields to "Number"
  • The Icon Style
• Apply the rule

Now you have the cells looking as shown below, but how do you hide the yellow exclamation on the zero?

The answer is to set up a conditional format using another of Excel 2007's conditional format tools; Stop if True

• Select the cells again
• This time choose "New Rule" from the Conditional Formatting menu (not "More Rules" from the icon set area)
• Choose to "Format only cells that contain" and set up the rule as shown below:

  

Applying the rule now won't have any effect, as it is essentially a blank rule. (See the message about "No Format Set"?)

• Click Format
• On the "Fill" tab, click "No Color" for the background and click OK twice

We now have our second rule set up, but nothing appears different. Let's fix that:

• With the cells still selected, go back and choose "Manage Rules" from the Conditional Formatting menu
• Check the "Stop if True" box beside your latest rule as shown:

  

Click Apply and it should the yellow exclamation disappears from the scenario.

The "Stop if True" feature is a great addition to Excel's conditional formatting as it allows us to decide just how many of our formats we wish to overlay in 2007. Depending on how many you apply to one cell though, you may have issues with trying to figure out inheritance issues, so use them carefully.

In a blog post last week, I was looking at the image formats to use when I snapped a picture of a chart. I'm kind of glad that Jon agreed with my assessment on EMF being the best image format for this, as it at least means I might be on the right track.

Jon also asked a question though… "Why not just use charts".

Good question, and sorry I didn't respond earlier, Jon. I got wrapped up in the course I was teaching.

Main Purpose:

Just to get it out there, the main purpose of me building these charts was to show them as micro charts on a dashboard. Some inspirational dashboards I was modeling on can be found at Charely Kyd's site. Here's just one of them.

Now, it's ENTIRELY possible that I'm going about this the wrong way, and am about to get schooled. I'm totally okay with that if it's the case. I haven't done any dash-boarding in Excel before, so this is a big experimentation for me. With that in mind, please feel free to offer suggestions or critique. I'm really trying to find the best mix of results and maintainability here.

I think the best approach to explain why not just use charts is to look at what I did along the way… So here's the history of why I went where I did.

Original Chart

I created my original chart and got it looking exactly as I wanted. I got the colours the way I wanted, set up my scales to work for me, and I basically got it to where I like the overall display. What I ended up with is the chart below (shown at actual size):

Scaling Attempt 1:

From there, I tried to scale the original chart down to micro chart size, and ended up with this nasty looking thing:

I had a quick look through all the options on the chart, but couldn't find anything that dealt with the scale preservation when shrinking. At least, I did find the "Size" on the Chart ToolsàFormat contextual tab on the ribbon, but it didn't seem to help. I could have tried to build it smaller, I guess, but then it would be hard to read on screen to see if it looked okay. In addition, I don't know if I could get everything to the right size. (Font sizes of 1.5, 1.2 or lower maybe?)

Basically, since the scale went all nasty, I gave up and reached toward the camera tool.

Scaling Attempt 2:

I reached towards the camera tool to create snapshots of the charts. Actually, this had another bonus in that I need to create different dashboards from the same data, so I will end up using the same chart more than once. I figured that the camera tool would be a great solution for that, since I would only have to maintain the source chart once.

But the camera tool is flaky. About 6 different charges into the project, I ended up with all the charts suddenly turning grey, and I couldn't get them to come back. Naturally, if you can't rely on it, you really can't use it, so I ditched it and moved on (after wasting a bunch of time trying to make it work.)

Scaling Attempt 3:

This is where I opted to go with copy the chart and doing a PasteSpecialàPicture. Using the EMF version I got a nicely scaled micro chart:

This added another benefit as well, actually, in that the dashboard page could be built on a separate sheet from the original data. Maybe it was the way that I set it up, but this seemed difficult to do with the camera tool. (In all fairness, I did NOT try to create a chart with source data on another sheet, but I can't see that being an issue.)

Potential/Known Issues:

I'm not going to say that this is all roses, either though. There are issues here, obviously, maintenance probably being the biggest of them. The image is static, so I had to knock up some VBA code to remove all the existing pictures on the sheet and rebuild them. That actually wasn't a big deal in the grand scheme of things. And at least the charts look consistent with the originals and print well.

In the process, I discovered that you can name charts in Excel 2007 through the UI. (Whooppee, you'd expect that…) But I also discovered that you can name multiple charts the exact same thing. That sucks. Once I'd set up one series of charts, I copied them all to use with my next set of stuff. (You see Food above, but I also have about 9 other product lines to deal with.) I figured I'd just change the source data, but I also had to be very careful to make sure I got the chart names, or I'd have some weird things happening when I referred to those names in code. (I'm floored that we'd be able to name multiple charts on the same sheet with the same name, but there you go.)

I'm also not happy with the difficultly this presents in creating the dashboard for the first time. How do you get the code to recognize the insertion point of the picture on the sheet? The dashboard has numeric data as well, with some (basic) conditional formatting, and the column widths are not consistent. So how do you line up the images with the grid? The short answer is that you can't. So then we get into issues of how to get the image to the correct place.

Thoughts that are currently still cooking…

I'm still trying to work my way through this, but I'm starting to wonder if I won't have to force consistent column widths. If I did, I can see a couple of potential solutions to my issues:

• I could then set up a table of chart names and cell references. A little code could then retrieve the chart and place it at the coordinate of the cell specified.
• Rob van Gelder made spark lines by creating a shape via a UDF. This might work if I could feed in the chart name as a parameter… but I think it's a non-starter. While you seem to be able to create simple line shapes, I'm running into issues trying to create get the full chart into a (new) shape.

I think I'd actually prefer the latter, but this still kind of sucks as it would probably need consistent column widths to avoid overlapping charts. While it would work well there, I'm not sure I want the rest of my report bound to having extra whitespace.

At any rate, that's what led me into pictures from real charts.

Suggestions, thoughts and comments all welcome.

*Note:* The charts in this post are based on sample data and do not reflect any real financial performance.

In setting up my first dashboard, I took a page out of Charley Kyd's book and placed a bunch of little graphs on my report. I tried to use the Camera tool first, but after I had about 7 on the page, the graphs all went to grey backgrounds, and you couldn't see anything anymore. I could not, for the life of me, get them to revert to live images. Very irritating. I suspect that it has something to do with Excel 2007, but I've never used the camera tool in earnest before, so can't be sure.

Just to get this out of the way first, I set up a bunch of charts on a worksheet. I ultimately wanted to show a smaller version of these charts in my dashboard. Unfortunately charts don't scale well when you try to shrink them down, or at least, mine are poorly set up to do so. This is why I wanted to use the camera tool, as it deals with this well.

Since the camera tool was giving me issues, I elected to go with copying my charts and pasting them as images. Adding a little VBA to it, I can wipe all the existing charts on the page, and re-create current chart images. This gives me semi-live chart updates vs the live updates that would have been available if I could have made the camera tool to work for me.

During the process, I found that some of the Paste As Image choices scaled better than others. Here's what I started and ended with:

The formats that you can paste into are Png, Jpeg, Gif, Enhanced Metafile, and Bitmap. I tried all of them.

The ironic thing is that print preview makes the Enhanced Metafile look absolutely awful, as you can see here:

In reality though, once printed out I found that the Enhanced Metafile gave the truest representation to the original of all of the formats. That surprised me a little, as I was kind of expecting png to be the best. My thoughts on this, for reference:

Png: Looked like certain lines were "clipped". The bottom of the number 1 characters, for example, seemed to miss part of the bottom line.

Jpeg: Everything looked like it's been bolded, and there seems to be a smattering of extra pixels on the graph. It looks just… "dirty" I guess.

Gif: Numbers are blurred and clipped. Far from professional looking

Bitmap: The second best of the lot, but still looks dirty

The Enhanced Metafile, on the other hand, was crisp, clean and sharp. Despite being scaled out of proportion, it still looks very good.

Just to prove that they do look better, here's a PDF of the printout: imgsample.pdf

I'm curious to other thoughts on this, as this is the first major effort I've concentrated in this area. If you're using pictures, does what I'm looking at hold true for you as well?

I'm working on writing up a course, and it always drives me nuts when the pictures I put in the document are different shapes. Height is fine, but I always want the width to be the same as the document looks weird with margins that change. Resizing manually is not practical as it takes time and eyeballing is not really reliable.

So I knocked up a quick piece of code to resize all the images in my word document. I don't claim to be a Word VBA expert, so this can probably be improved, but here it is:

Sub ResizeImages()

'Date Created : 4/1/2009 21:36

'Author : Ken Puls (www.excelguru.ca)

'Macro Purpose: Resize an image in Word

 

Dim shp As InlineShape

For Each shp In ActiveDocument.InlineShapes

shp.Width = 300

Next shp

 

End Sub

 

The size is measured in points, and 300 equates to about 4.75" on the page (at least here, anyway.)

I seem to be tripping on some odd things with Excel lately. I was trying to take a list of stuff from Word and turn it into a table of data when I tripped on this one. It's a bit bizarre and a bit amusing… Try this:

Create a new Word document with the following:

(The bullets are important)

Copy the data and paste it in Excel. You should now get something that looks like this:

Now, go into cell A1, and copy the bullet and the spaces from the formula bar:

Highlight column A and choose Find/Replace. (On the Ribbon this is on the Home TabàFind & SelectàReplace)

In the box that comes up, paste the text you just copied into the "Find" section and click "Replace All"

Here's the result:

Wow! That's kind of funky! Yet when you click in A2, the formula bar shows the correct text:

Go figure! What happened is that the bullet character is actually in the Symbol font, but the rest of the text in the cell is in whatever your default font was in Word. Replacing the leading text applies that font to the rest of the cell. It was particularly frightening in my first view (on real data):

I thought I'd moved to another country!

So during my infatuation with converting my workbook to use IFERROR statements throughout, I suddenly realized that the reports I had to send to head office needed to work in Excel 2003. After a bit of… um… motivational speaking… I elected to do the following:

• Create a new workbook
• Copy each of the required worksheets to the new workbook
• Break the links

This method would allow me to take full advantage of Excel 2007's additional functionality, speed up my calc time by using more efficient formulas, and also still provide head office with the reports they need. And easy enough to do as well, since it's just a bit of VBA code to automate it all… right?

Yeah, right. Nothing is ever as easy as it should be.

Copying the worksheets to a new workbook was simple, but the code to break the links is bizarre. I used the code that I'd posted on this blog a while back, but I had to do it in a weird way. Here's the method I ended up using:

• A loop to copy the worksheets to the new workbook
• A call to break links within the loop
• Save and close the workbook
• Re-open the workbook and break the links
• Save and close the workbook

Seems a little redundant, doesn't it?

The reason I ran the link breaking within the loop that I was using to copy the worksheets to a new workbook is because is dramatically improved the speed of the routine. I can only surmise that it did break some links, as it certainly didn't do them all.

After that I tried to break the links with the workbook still open (saved or not), and it didn't have any effect. Basically the only way I found to reliably kill those links was to save, close and re-open the workbook. Very irritating and slow, but effective.

Of course, as trying to work this out wasn't hard enough, I was also battling this issue, which was leaving me with phantom VBA projects. I'm virtually certain that this was affecting some of the code, although I still had to resort to the above method once I had uninstalled that add-in.

My conclusion… breaking links on an unsaved workbook is hokey. This should work.

And just in case anyone wants it, here's the routine I used to break the links: