Office Update breaks ActiveX controls

UPDATE: 10 March 2015: Microsoft have now released fixes for this!

For Excel 2007 click here.

For Excel 2010 click here.

For Office 2013 click here. (note there doesn’t appear to be a separate Excel patch)

 

Note: I have not actually been able to verify whether these patches work, but some early reports suggest they don’t work for everyone (perhaps for the same reason that the fixes below didn’t).

I am also unsure at this time as to whether Office 2003 was affected, but have not seen any mention of a patch if it was.


 

Update: 15 Dec 2014: There is now an MSKB article about this problem with suggested solutions and a FixIt tool here: KB3025036.

 

It seems that a recent Office update (Tuesday 9th Dec) has broken ActiveX controls on worksheets. The symptoms include (but are probably not limited to):

  • Being unable to use or change properties of any active controls on worksheets
  • Error messages saying “Can’t insert object”
  • Error 438 when trying to refer to an activex control as a member of a worksheet in code

To fix it, do this:

  1. Close all Office applications.
  2. Do a search in Windows Explorer – make sure to include hidden and system files and folders – for *.exd files (note: that’s not *.exe !!) and delete any you find.
    Make sure you get these:
    C:\users\username\AppData\Local\Temp\Excel8.0\MSForms.exd
    C:\users\username\AppData\Local\Temp\VBE\MSForms.exd
  3. Reboot the computer (this is not always necessary, but probably better safe than sorry)
  4. Restart your Office apps and test the controls again.

Please note that the .exd files will be recreated when you next use a workbook with an embedded active control – this is quite normal and should not cause you a problem!

Hopefully that will resolve the problem for you. 🙂

 

Note: if you are having problems locating the .exd files, you can press Win+R to open the Run box, enter %temp% and press Enter. That should open explorer to the Temp folder and you then need to look in the Excel8.0 and VBE folders for the .exd files. (if your issue is with a program other than Excel, look for the folder related to that application – e.g. Word8.0)

 

Update: 11-Dec: It seems that for a few cases so far, this solution doesn’t resolve the issue. I will post back if and when I discover a solution for those cases too, though simply uninstalling the update mentioned at the beginning of this post may be a solution until a fix is released.

Trust me, Microsoft is aware that this is an issue – see their blog post here.

 

Update: 12 Dec 2014: I have seen mentions that if none of the above fixes the issue in Office 2013, removing this update may work: http://support.microsoft.com/kb/2920734

 

Advanced filters. Who knew?

I had a real face palm moment the other day which, having no shame, I thought I’d share.

 

I imagine we all know that when using an advanced filter you can use standard criteria with a criteria range (headers match the column names and you enter values as the criteria for each column) and you can also use formulas as criteria when things are more complicated (in this case the headers should NOT match a column header. I usually just leave them blank).

 

Remarkably, it had never occurred to me that you could combine the two at once. Yes, I know – I’m an idiot. But there you go. You can. It’s actually really useful – especially if you want to have multiple options on one or more fields while using several criteria fields, where with regular criteria you would have to have a row for every combination of options you wanted. This is particularly useful if you are using code to filter data and want to allow users to select any combination of options.

For example:

Raw data file:

 
 
Simple advanced filter:
Note that Item1 filter only applies to Jim

 
 
Corrected the criteria so Item1 applies to both:
 

 
 
Adding more options and you can see how the criteria rows start to multiply:
 

 
 
So let’s use a formula as criteria (note row 9 is the first row of data, NOT the header row):
 

 
 
And finally, let’s combine the two methods:
 

 
 
I leave it to you to work out what the criteria range would look like for this permutation if you didn’t use formulas… 🙂

VBA to change pivot source data

This morning’s post was prompted by a question posted at MrExcel (here) regarding changing the source data for multiple pivot tables to refer to a different worksheet range. The stumbling block turned out to be that when you have multiple pivot tables that use the same pivot cache, you cannot simply change the SourceData property of the existing cache as you can if there’s only one pivot table. The workaround is to create a new cache and then assign that to all the pivot tables:

Sub ChangeCaches(sNewSource As String)
    ' sample to change multiple pivots based off the same Excel Range-based pivotcache
    Dim pc                         As PivotCache
    Dim ws                         As Worksheet
    Dim pt                         As PivotTable
    Dim bCreated                   As Boolean

    For Each ws In ActiveWorkbook.Worksheets
        For Each pt In ws.PivotTables
            If Not bCreated Then
                ' this only adds a new cache on the first run through
                ' on subsequent passes, the pivot tables are simply assigned to the new cache
                ' if multiple caches are desired, simply repeat this part for each pivot table.
                pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                          SourceData:=sNewSource, Version:=xlPivotTableVersion14)
                Set pc = pt.PivotCache
                bCreated = True
            Else
                If pt.CacheIndex <> pc.Index Then pt.CacheIndex = pc.Index
            End If
        Next pt
    Next ws
End Sub

Hopefully that may prove useful to others.

 

Note: trying to create the PivotCache before the loop and then assigning it to the pivot tables doesn’t work for some reason. The cache ends up with an Index of zero and is unusable.

Customising GETPIVOTDATA formulas

A typical GETPIVOTDATA formula in Excel might look like this:

=GETPIVOTDATA("Sum of ValueField",$A$3,"field1","a","field2",1)

Of course, all of these references are fixed so not much use if you want to copy the formula using different values.

Linking the criteria (the “a” and 1 in the original formula) to input cells is straightforward – let’s assume those values have been entered into J1 and J2 respectively; we can then adjust the formula to:

=GETPIVOTDATA("Sum of ValueField",$A$3,"field1",J1,"field2",J2)

Similarly the field names to which those criteria apply can also be made into cell references – let’s assume K1 and K2 contain field1 and field2 respectively:

=GETPIVOTDATA("Sum of ValueField",$A$3,K1,J1,K2,J2)

Simple – and makes it easy create summary tables of sections of the pivot, or to pick out different values using input criteria.

Now you would think that you could do the same with the “Sum of ValueField” but you will find that if you enter that text into cell L1 and try:

=GETPIVOTDATA(L1,$A$3,K1,J1,K2,J2)

you end up with a #REF! error. The solution is simple but not really obvious – you need to prepend (or append) quote marks:

=GETPIVOTDATA(""&L1,$A$3,K1,J1,K2,J2)