Installing RSAT on Windows 7 SP1

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.

Excel WebApp – Formulas that don’t work

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.

Data Labels on Chart Series

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?

Weird chart sizing observation

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?

Screw up Excel by setting a PivotFilter via VBA

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:

  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. Application.EnableEvents = False
  3. With ActiveSheet
  4. .PivotTables("PivotTable1").PivotFields("Account").ClearAllFilters
  5. .PivotTables("PivotTable1").PivotFields("Account").PivotFilters.Add _
  6. Type:=xlCaptionEquals, _
  7. Value1:=.Range("B1").Value
  8. End With
  9. Application.EnableEvents = True
  10. End Sub

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.

Microsoft Add-in Causes VBE Ghost Project Issues

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.

mscms.dll error

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…

PDFCreator and Vista Bug

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.

Breaking Tab and Enter key navigation

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:

  1. Private Sub Workbook_Open()
  2. ' Written By: Ken Puls (www.excelguru.ca)
  3. ' Purpose   : Capture the Tab key to a specific event
  4.     Application.OnKey "{Tab}", "OnTab"
  5. End Sub
  6.  
  7. Private Sub Workbook_BeforeClose(Cancel As Boolean)
  8. ' Written By: Ken Puls (www.excelguru.ca)
  9. ' Purpose   : Cancel the Tab key override
  10.     Application.OnKey "{Tab}"
  11. End Sub
  12.  
  13. Private Sub Workbook_SheetSelectionChange( _
  14.     ByVal Sh As Object, ByVal Target As Range)
  15. ' Written By: Ken Puls (www.excelguru.ca)
  16. ' Purpose   : Evaluate cell movement
  17.     If rngFirstTab Is Nothing Then
  18.         'Not in tabbing mode, do nothing
  19.     Else
  20.         'In tabbing mode
  21.         Application.EnableEvents = False
  22.         'Check if range below last tab cell was selected
  23.         '(assumes that user presed Enter to get there)
  24.         If Target.Offset(-1, 0).Address = rngLastTab.Address Then
  25.             'True, so return to cell below tabbing origin
  26.                 rngFirstTab.Offset(1, 0).Select
  27.         End If
  28.        
  29.         'Set tabbing origin and last tab to nothing
  30.         Set rngFirstTab = Nothing
  31.         Set rngLastTab = Nothing
  32.         Application.EnableEvents = True
  33.     End If
  34. End Sub

And the following code goes in a standard module:

  1. Public rngLastTab As Range
  2. Public rngFirstTab As Range
  3.  
  4. Public Sub OnTab()
  5. ' Written By: Ken Puls (www.excelguru.ca)
  6. ' Purpose   : Override tab movement
  7. '             For use in environements where tab returns are
  8. '             lost (Sheets protected with "Only select unlocked
  9. '             cells.)
  10.  
  11.     'Record where tabbing started
  12.     If rngFirstTab Is Nothing Then _
  13.     Set rngFirstTab = ActiveCell
  14.    
  15.     'Record where tab is going
  16.     Set rngLastTab = ActiveCell.Offset(0, 1)
  17.    
  18.     'Activate next cell
  19.     Application.EnableEvents = False
  20.     rngLastTab.Select
  21.     Application.EnableEvents = True
  22. End Sub

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.

RibbonX bug – separator visible attribute

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:

  1. <customUI onLoad="captureRibbonUI"
  2. xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  3. <ribbon>
  4. <tabs>
  5. <tab idMso="TabHome">
  6. <!--New Group on Home tab-->
  7. <group id="rxgrpCustom"
  8. insertBeforeMso="GroupClipboard">
  9. <!--Static separator-->
  10. <labelControl id="rxlctlIndicator01"
  11. label="Sep 1 -->"></labelControl>
  12. <separator id="rxsep01"
  13. visible="false"></separator>
  14. <!--Dynamic separator-->
  15. <labelControl id="rxlctlIndicator02"
  16. label="Sep 2 -->"></labelControl>
  17. <checkBox id="rxchkDynamic"
  18. label="Show Sep 2?"
  19. onAction="rxchkDynamic_click" />
  20. <separator id="rxsep02"
  21. getVisible="rxsepDynamic_getVisible" />
  22. <!--Debug Mode setting-->
  23. <checkBox id="rxchkDebug"
  24. label="Debug Mode"
  25. onAction="rxchkDebug_click" />
  26. </group>
  27. </tab>
  28. </tabs>
  29. </ribbon>
  30. </customUI>

Code in the Thisworkbook module:

  1. Private pRibbonUI As IRibbonUI
  2.  
  3. Public Property Let ribbonUI(iRib As IRibbonUI)
  4. 'Set RibbonUI to property for later use
  5. Set pRibbonUI = iRib
  6. End Property
  7.  
  8. Public Property Get ribbonUI() As IRibbonUI
  9. 'Retrieve RibbonUI from property for use
  10. Set ribbonUI = pRibbonUI
  11. End Property

Code in a standard module:

  1. Private bDebugMode As Boolean
  2. Private bDynamicSepVisible As Boolean
  3.  
  4. 'Set the RibbonUI to a workbook property for later use
  5. Private Sub captureRibbonUI(ribbon As IRibbonUI)
  6. bDynamicSepVisible = True
  7. ThisWorkbook.ribbonUI = ribbon
  8. End Sub
  9.  
  10. 'Callback for rxbtnDynamic onAction
  11. Sub rxchkDynamic_click(control As IRibbonControl, pressed As Boolean)
  12. If bDebugMode = True Then Stop
  13.  
  14. bDynamicSepVisible = pressed
  15. ThisWorkbook.ribbonUI.InvalidateControl ("rxsep02")
  16.  
  17. End Sub
  18.  
  19. 'Callback for rxsepDynamic getVisible
  20. Sub rxsepDynamic_getVisible(control As IRibbonControl, ByRef returnedVal)
  21. If bDebugMode = True Then Stop
  22.  
  23. returnedVal = bDynamicSepVisible
  24. Debug.Print "Separator visible property set to " & bDynamicSepVisible
  25. End Sub
  26.  
  27. 'Callback for rxchkDebug onAction
  28. Sub rxchkDebug_click(control As IRibbonControl, pressed As Boolean)
  29. bDebugMode = pressed
  30. End Sub

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

bug_separator.jpg

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.  :)