Bug with VBA form controls transparency and Z-Order

There has been a bug introduced with the September update for Office 365 and Office 2016 perpetual which prevents controls on userforms from showing transparency or altering their Z-order properly.

 

Latest update from Microsoft is here. A fix is in the works and will hopefully be provided soon but, if you can’t wait, rolling back to an earlier build seems to be the best solution currently!

Worksheet.Select Replace:=False doesn’t work in 2013 or 2016

There is a bug in the current builds of Excel 2013 and 2016 (on Windows; Mac version actually works OK) that means that using the optional Replace:=False parameter for the Worksheet.Select method doesn’t work unless you already have more than one sheet selected.

 

The solution is to use an array of sheet names (or code names/objects/indices) instead.

When is a FormatCondition not a FormatCondition?

I was recently reminded of this quirk of the Excel object model (you guessed it – by a forum post). Suppose you have this code:

Dim fc As FormatCondition
For Each fc In ActiveSheet.UsedRange.FormatConditions
   ' do something to fc here
Next fc

you’d probably be slightly surprised if you received a Type Mismatch error on the second line. After all, you’re using a FormatCondition control variable to loop through the FormatConditions collection – what could be simpler?

Well, as it happens, the FormatConditions collection isn’t restricted to only FormatCondition objects. Yes, I know. It’s not my fault, so don’t shout at me.

It can actually contain all the following object types, which relate to the various fancy conditional formatting options available since Excel 2007 (shudder):

FormatCondition
DataBar
AboveAverage
ColorScale
UniqueValues
Top10
IconSetCondition

so in the original code, you actually need:
Dim fc as Object
and you can then test the object types using TypeName or TypeOf according to your needs/preference.

Using the same field twice in a pivot table

I answered a post this morning on MrExcel which reminded me of one of the (many) oddities I’ve come across when writing code for pivot tables.

The scenario:

You have a simple one column table:

Header1

Client a

Client b

Client c

Client d

Client e

Client a

Client b

Client c

Client d

Client e

Client a

Client v

Client f

Client a

Client d

Client a

Client c

Client b

Client v

Client f

Client a

Client d

Client c

Client v

Client b

Client f

Client a

Client d

Client f

Client v

Client b

 

You now want to create a pivot table that counts how many times each item appears. So you want the same field as both a row field and data field. This is very simple to do manually – just drag it to both areas – and you end up with what you want:

Row Labels

Count of Header1

Client a

7

Client b

5

Client c

4

Client d

5

Client e

2

Client f

4

Client v

4

Grand Total

31

 

Now, if you record a macro while doing that, you’ll end up with something like this:

Sub Macro2()
'
' Macro2 Macro
'
'
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Table1", Version:=xlPivotTableVersion14).CreatePivotTable TableDestination _
        :="Sheet5!R3C1", TableName:="PivotTable2", DefaultVersion:= _
        xlPivotTableVersion14
    Sheets("Sheet5").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Header1")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Header1"), "Count of Header1", xlCount
End Sub

 

The problem:

If you run this code again (adjusting the Sheet5 name in the code to match the next sheet number), you actually end up with this table:

Count of Header1

31

 

If you step through the code, you will see the field does get added as a row field, but then gets moved to the data area rather than added there as well.

 

The solution:

The solution is actually just to move the line that adds the data field so that it occurs before the code to add it as a row field.

The reason for this is that the data field caption is now “Count of Header1” so a reference to .PivotFields(“Header1”) is not a reference to the data field you just added, and it doesn’t get moved when you set its Orientation.

Default picture size with Shapes.AddPicture

I have often resisted using the Shapes.AddPicture method, sticking with the deprecated Pictures.Insert method, because with the former you have to specify the dimensions of the picture (i.e. both Height and Width) as well as the location and usually I don’t know them (and can’t be bothered with the code to get them). Unfortunately the Pictures.Insert method gives you no control over whether the image is linked or embedded.

 

However, this morning I realised that you can avoid this problem by simply passing -1 as the value for both Height and Width! (a real “why the hell didn’t I think of that before?” moment)

 

So the code is simply:

ActiveSheet.Shapes.AddPicture Filename:="path to pic", linktofile:=msoFalse, _

savewithdocument:=msoCTrue, Left:=0, Top:=0, Width:=-1, Height:=-1

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.

Office 2013 VBA documentation offline files available

You can now download the .chm Help files for Office 2013 VBA – one per application – from the Microsoft Download Center here.

Although, bizarrely – or not for those who are familiar with searching the MS Site 😉 – a search for “Office 2013 VBA Documentation” on the Download Center page returns no results at all…

 

Plus ça change.

VBA references and early binding vs late binding

At some point, many VBA programmers will find themselves dealing with a references problem. This usually manifests itself as an error message along the lines of “Can’t find project or library” with the debugger highlighting what appears to be a fairly innocuous line of code – often involving a native VBA function like Date, Left or Format.

The cause of the problem is usually a reference to a non-default object library – for example, if you are working in Excel but need to automate Word or Outlook, you will typically set a reference to the required library and then happily program against it. The issue arises when you come to distribute the project and it is run by someone who has a different version (usually an earlier one) of the programs in question. So for example, you may have developed using Office 2010 and then distributed the project to someone using 2007. When they try to run it, the phone calls begin…

Unfortunately, although the non-default project references will usually update themselves to match a later version of software, that does not often happen with earlier versions, and that is what causes code errors. Using the example of the Date function, unless you have explicitly written VBA.Date in the code, the compiler doesn’t know where the Date function is located and has to look through the referenced libraries to try and find it – as soon as it comes across a broken reference, the error is raised.

 

So – what to do? The most obvious options are:

  • Write code to update the references on the target machine. This is difficult to implement well and, even if you can, it will fail if the security restrictions on the target machine are not set as you need them.
  • Develop in the lowest version of the software that you need to support. This is a simple ‘fix’ and will work in many cases but by no means all. For instance, if someone saves your project in a later version of the software that will update the references and if it is then distributed to someone with an earlier version, you are back to square one.
  • Late bind your code. This often the most effective solution and what I will cover now.

 

In code terms, the difference between early and late binding is simple:

If you declare a variable as Object, you are late binding it.

There is no more to it than that in the strict definition of the term.

 

However, the real benefit of late binding – i.e. fixing the references issues – is that it allows you to not set a reference to the other library at all. If there’s no reference, there’s nothing to break – problem solved.

In order to do this in practice, we require three basic steps, rather than the one simple one mentioned above:

  1. Declare your object variables as Object, rather than as, say, Word.Document or Outlook.Application
  2. Instead of using the New operator to instantiate the objects, you must use CreateObject with the relevant class name.
  3. Either declare any constants you use from the target library, or use their literal values instead (the former I believe is better practice).

 

Here is an early bound and a late bound example of some simple code to manipulate Outlook from Excel – I have highlighted the differences with comments in the code:

Early bound version

Sub SendOLMail_EarlyBound()
    ' declare variables for the Application and Mailitem objects
    Dim oAPP                       As Outlook.Application
    Dim oItem                      As Outlook.MailItem

    ' instantiate the Application
    Set oAPP = New Outlook.Application

    ' create a new email
    Set oItem = oAPP.CreateItem(olMailItem)

    ' set basic properties and display the email
    With oItem
        .To = "foo@bar.com"
        .Subject = "this is a test"
        .Body = "nothing to see here"
        .Display
    End With

End Sub

Late bound example

Sub SendOLMail_LateBound()
    Dim oAPP                       As Object
    Dim oItem                      As Object
    ' need to declare this constant as it has no meaning without
    ' the reference set to the Outlook library
    Const olMailItem               As Long = 0

    ' instantiate the Application - cannot use New without a reference
    ' so we must use CreateObject
    Set oAPP = CreateObject("Outlook.Application")

    ' #######################################
    ' NOTE: THE REST OF THE CODE IS IDENTICAL
    ' #######################################

    ' create a new email
    Set oItem = oAPP.CreateItem(olMailItem)

    ' set basic properties and display the email
    With oItem
        .To = "foo@bar.com"
        .Subject = "this is a test"
        .Body = "nothing to see here"
        .Display
    End With

End Sub

Transposing an array using an in-memory listbox

There are many ways of transposing an array in VBA. Here’s another fairly simple one which is not subject to the size limits of application.transpose because it uses a ‘virtual’ MSForms.Listbox control

Function TransposeIt(vData)
Dim lBound2 As Long

lBound2 = -1
If IsArray(vData) Then
' test for 1D array
On Error Resume Next
lBound2 = UBound(vData, 2)
On Error GoTo 0
' create MSForms.ListBox
With CreateObject("New:{8BD21D20-EC42-11CE-9E0D-00AA006002F3}")
.Column = vData
If lBound2 = -1 Then
' for 1D, returning the Column will transpose and return 2D array
TransposeIt = .Column
Else
' for 2D array just return the List
TransposeIt = .List
End If
End With
End If
End Function