The Ken Puls Blog

Today we began the process of migrating away from VMWare to Microsoft Hyper-V for our server farms. Something I’m sure that Microsoft would be pretty happy to hear. And yet I got burned by an issue in the process that irks me.

I keep my OS pretty current, and installed Windows 7 SP1 as soon as it was pushed out in Windows Update. Today we go to install Microsoft’s Remote Server Administration tools so that I can connect to Hyper-V to build and manage my Virtual Machines, and it won’t install. What the hell? I get a nice little error message telling me “This update is not applicable to your computer.” Like hell it’s not!

After some searching, I found out that someone has come up with a route around this issue to get it to work correctly, which you can find here.

Microsoft has acknowledged it as an issue. In their KB’s wording: “Microsoft has confirmed this to be by design, as RSAT was designed for Windows 7 RTM version. A newer version of RSAT is slated to be released in the future.” Their advice is to uninstall SP1, install RSAT, then reinstall SP1 again. To me that sounds more dangerous than the route I went to fix it.

Personally, I don’t think this is good enough. If this is truly “by design”, then someone needs a smack upside the head. Microsoft wants people to keep their software current, and these are the exact people getting smacked!

I get that software is tough to deploy, but if the route I went is all that’s needed to fix it, surely someone could roll up a quick hotfix to release in a few hours.

I was writing going to write up an article for my site tonight to show how to create a table of contents using native Excel functions, rather than resorting to VBA. Naturally, I figured that it would be great to put up an interactive example with the Excel WebApp, but I ran into an issue: I found a formula that works fine in the client, but just returns #VALUE! Errors in the Excel WebApp.

Debra did a great writeup of the =CELL function back in her 30 Excel Functions in 30 Days series. The examples work great in the client, but not the webapp. Too bad, really, as it’s a great function that can be used for a lot of things.

I don’t know how much people have played with this, but if you encounter a function that doesn’t work, post it in the comments. It would be nice to get a full list.

Since I know I have a few charting guys that follow this, I figured that I’d ask this question here. Hopefully the response will help someone…

I’m creating a food & beverage function evaluation form, and threw a chart on it. The point behind this chart is to let someone scroll through the number of customers, seeing how much profit the event will earn.

Using a trick that Mike Alexander covers in his latest dashboarding webcast, I added a second chart series. The scroll bar links to a cell and that controls a column of data that shows the value indicating the profit point for the selected customer, or #N/A for anything else. This allows for the single data point on the line chart shown below:

Okay, so this is fine, but it’s really hard to tell how many customers and how much profit (or loss) is evident at that point. So I thought I’d add a data label to it. So I selected the series from the legend (not shown on the chart here) and chose to add Data Labels. It came out like this:

Well that’s just nasty. And scrolling through the scroll bar didn’t change anything.

With a little playing though, I found that I could set the data points individually. So I tried a little VBA to set each and every data point individually:

Dim c As Long

For c = 1 To 100

ActiveChart.SeriesCollection(2).Points(c).ApplyDataLabels

Next c

 

Now this was much better, and yielded the following:

Okay, it’s still not perfect, but that’s not the point here. Why should I have to set each data point in the series manually to have it correctly recognize that it should not be plotted if the value is #N/A?

I just assumed that this would be a bug at first, but now I’m not so sure. If you have a legend on the chart and hit it with the following code…

ActiveChart.Legend.Select

ActiveChart.Legend.LegendEntries(2).Select

ActiveChart.SetElement (msoElementDataLabelTop)

 

… then the chart turns back into the first one I showed, with all the clustered nasty elements. So… does this mean that telling Excel that you’d like Data Labels on your series is not the same as “turning on” a collection of data labels? I would have assumed that it was intended to be the same.

I’m curious as to people’s thoughts here… is this a bug, or a feature? I’m still leaning towards bug, since it seems to be attempting to display the #N/A values, which are not supposed to be charted.

What do you think?

I’m not sure if the observation is weird, or the actual chart sizing is. I think this is a bug, but I’m curious if others see similar behavior. This is an Office 2007 thing (at least, I haven’t tested it in other versions…) Here’s the repro steps:

• Make a chart and select it
• In the VBE, find out the width. I just did the following in the immediate window:

  ? selection.width

• It came back with 400. All good
• Set the width to something (again in the immediate window):

  Selection.width = 245

• Now, check the width again:

  ? selection.width

• It comes back with 250

This strikes me as a little strange. I would have thought that the width would be the same as what I set it to. No matter the number given, it always seems to come back with a width that is 5 pixels larger than what I set it to.

The height and left properties do not seem to suffer this issue, only the width that I’ve encountered so far…

Anyone else get similar behavior?

Today I did the following:

• Created a new workbook
• Added a pivot table, based on an Access database connection
• Created it using the following setup

After doing that, I grouped my months by year, and dropped in the following code to the Sheet1 module:

Then I changed the value of B1 to one of my account numbers and… BOOM!

And when it says the Object invoked has disconnected from its clients, it really means it! What's really bizarre is that hitting end leaves me able to move around the worksheet using the mouse, as evident in the formula bar, but the active cell on the worksheet never changes. Closing and re-opening the workbook has no effect, as the issue persists. The only way to cure it is to shut down Excel and restart.

As it turns out, the problem is that I passed the pivotfilter a number, not a text string. An easy fix with CStr to convert it, and now I can quickly check the balances in my accounts over the last few years:

What really strikes me as really bizarre is that passing a number to a string variable in VBA will not cause an error. The value is accepted just fine. If I'd had to guess about this, I would have expected it to work the same way or, at the very worst, throw a trappable error. Passing a string to a numeric field shouldn't cause such as drastic automation error that forces you to close Excel to cure it.

Suspicious that this might be something to do with my setup, I also tested this on a pivot table created on data from within the workbook (I mocked up a simple three line table) and the same thing happens.

Recently I was introduced to this really cool add-in by a vendor: SQL Server 2005 Data Mining Add-in for Office 2007. It's a pretty neat tool that you can use to analyze relationships within data.

Unfortunately, as cool as this add-in is, it has wee little issue with it. It creates ghost projects in the Visual Basic Editor. What I mean is this…

When you open Excel, a new file called Book1. Go into the VBE (press Alt+F11) and you'll see VBAProject(Book1) in the list. What should happen is that when you close Book1 in Excel, it should go away in the VBE as well. With this add-in installed, it doesn't. The workbook closes in Excel, but it hangs around in the VBE. You can't do anything with it, because it doesn't really exist, but it just loiters there.

You should also never be able to see the same file name multiple times in the VBE, as you can't open the same file more than once in Excel. The picture below is a mock-up of what I saw, as I've fixed the issue, but this is what it looked like.

(Note: My issues were not with Book2, but rather with one of my own files that was listed several times.)

I've been having issues with a certain procedure I've been writing all day, and I'm not sure if this was affecting it or not. I suspect that it was a bit, although I can't prove it, as I was opening and closing the same file repeatedly in testing, and seeing some weird results… almost like it was pointing to a former copy or something. Again, I can't prove it, I just suspect it is all.

I also don't know if it would affect a normal, non-code hungry user at all. If you're a developer though, it may be something you want to know about.

This is a big irritation...

I've started getting errors every time I open an Office application.  Specifically, the error message reads:

"EXCEL.EXE - Bad Image

C:\Windows\system32\mscms.dll is either not designed to run on Windows or it contains an error.  Try installing the program again using the original installation media or contact your system administrator or the software vendor for support."

After clearing that message three times, it comes up with a final error message entitled:

"EXCEL.EXE - Unable to Locate Component

This application has failed to start because mscms.dll was not found.  Reinstalling the application may fix this problem."

Grrrr....

For reference, the application (be it Excel or Outlook, and I haven't check the others) still open and seem to work normally.

So here's what I know:

• I added an IMAP account to my Outlook settings, synching up my gmail account to Outlook
• I installed Visual Studio 2008
• I installed PefectDisk 2008 (with VMWare))

I believe the problem surfaced when I turned off IMAP and deleted my IMAP account from my Outlook profile.

I've tried the following steps to fix the issue:

• Running a Repaid installation on Office 2007
• Running regsvr32 on the file (it does still exist)
• Uninstalling Office and reinstalling
• Running a complete Registry cleanup (as administator) using CCleaner

So far nothing has worked, and I can't find anything remotely hopeful on the net.  In fact, the only reference I've been able to find about this issue so far has been unanswered public ng threads for Outlook.

Suggestions welcome...

As many of you know, I'm a big fan of the free PDFCreator utility since it allows us to automate PDF creation via VBA in Office 2003 and earlier.  One issue with it, however, is that it won't run on Vista yet.

It looks like PDFForge has offered a $150 reward to anyone who can solve the bug.

Hopefully they can get this sorted, as it is a great little utility.  Even if you don't want to automate the process, it's a handy little tool.

So the other day I got a complaint from one of my users:  "I really wish you'd fix the Excel tabbing issue in this file."

Now I've sent a considerable amount of time training my users to enter data in worksheets by navigating to the right by pressing the Tab key and then pressing Enter when they are done working with the row.  For those of you who don't know this, when you work in this way, Excel will return you to the cell just below the one you started tabbing from.  Try this:

• Select cell B1, press Tab 3 times and you'll be in cell E1.  Press Enter and you will be returned to cell B2.
• Select cell B1, press Tab 3 times, then press the right arrow key and you'll be in cell F1.  Pressed Enter and you'll be in cell F2.  (This is because you started using different keys to navigate, so the tab caching was lost.)

Okay, so this is pretty basic navigation, but I accidentally did something that breaks it.

As a matter of general practice, I apply worksheet protection (with no password) to all of my templates.  This is just fine if you leave the default options -- "select locked cells" and "select unlocked cells"-- checked.  If you decide to only let users select unlocked cells, however, the keys work like this:

• Select cell B1, press Tab 3 times and you'll be in cell E1.  Press Enter and you will be returned to cell E2.

That is E2, not B2 as it was before!  (Note that this does assume that at least B1:E2 is unlocked.)  Personally, I found this pretty irritating.  I've also been able to confirm that this is an issue in Excel 2003 and Excel 2007.  I haven't tested any further back than that.

Now, the big question that I'd like to know... Is this a feature or a bug?  Does anyone have a good reason for why this scenario would be different?

At any rate, here's a fix:

The following code goes in the ThisWorkbook module:

And the following code goes in a standard module:

One little note here... if you press the down arrow at the end of a string of tabs, it will be treated as if you hit the Enter key.  You'll be sent back to the beginning of the row.  I suppose that I could have also captured the Enter key's onKey event, but I elected not to bother with this.

I believe that I've uncovered a bug in the implementation of the visible attribute of the separator element. Or rather, the fact that you cannot set the visible attribute to false.

The code I used to test this was as follows:

XML code for an Excel xlsm file:

Code in the Thisworkbook module:

Code in a standard module:

Upon saving all that and opening the file, you get a really ugly little group on the Home tab that looks like this:

Now, the interesting things here are that the first separator has it's visible property set to false. So why is it showing? The second separator is hooked up to the "Show Sep 2?" checkbox to make it dynamic. Uncheck it and the separator will stay there. I only hooked both up as I wanted to check if having the attribute set to static or dynamic would make a difference.

Finally, the Debug Mode is just there to allow you to see that the code does trigger and step through it while firing if you want to.  (It also logs the value of the property to the immediate window, but this saves you looking there.)  The entire Debug Mode and debug.print section could be dropped from the example, but then the checkbox doesn't appear to do anything. (As compared to doing something but being ineffective!)

I don't know how much this attribute would even be used, but the fact is that it doesn't work as advertised.  I'm going to log this one with Microsoft.