Who’s With me?

I frequently see people get confused by, and misuse, the With ... End With construction. The most common misconception seems to be that anything put inside one of these clauses automatically refers back to the object used on the With line – so in other words, the belief is that this works:

With Sheets("Sheet1")
Range("A1").Value = "Hello"
Range("A2").Value = "Magical With block"
End With

and, indeed, while debugging it may seem to work – because usually Sheet1 is active so that you can see what is happening. But in fact the With block is doing absolutely nothing here – the code would do exactly the same thing if it weren’t there at all. (Feel free to remove it and see)

The simplest way I can think of to explain the way this clause should be constructed is like this:

Imagine your code written out in full, repeating the object you want to use in the With block wherever it is necessary. So, for the example above, we would start with this code:

Sheets("Sheet1").Range("A1").Value = "Hello"
Sheets("Sheet1").Range("A2").Value = "Magical With block"

Nothing too tricky there, I hope you’ll agree.

Now to convert this to a with block, we add back the With and End With lines we had before:

With Sheets("Sheet1")
Sheets("Sheet1").Range("A1").Value = "Hello"
Sheets("Sheet1").Range("A2").Value = "Magical With block"
End With

and then remove any occurrence of the object we have on the With line – but do not remove anything else:

With Sheets("Sheet1")
.Range("A1").Value = "Hello"
.Range("A2").Value = "Magical With block"
End With

Note the two differences between this version and the first version that didn’t work? Correct – the dots before the two Range calls. Those dots are what tell the compiler that the properties or methods relate back to the object referred to on the With line. Without them, the With block may as well not exist.

Now, where it can seem a little confusing is if you start to nest With blocks:

With Sheets("Sheet1")
    .Name = "New sheet"
    With .Range("A1")
        .Value = "Hello"
        .Font.Bold = True
    End With
.Range("A2").Value = "Magical With block"
End With

In truth though, all you need to remember is that any property or method that you prefix with a dot refers back to the nearest enclosing With block. So the second line refers back to Sheets(“Sheet1”), as does the third line. The fact that the third line is also a With block only affects the lines that follow, until the next End With line. After that, the next line refers back to Sheets(“Sheet1”) again.

So that’s the basics of writing these clauses, but there is one very important thing to remember about the way they work at run time:

The With line sets and holds a reference to the object in question, so any changes made to that object will be reflected in any code inside the with block. What that means is that this:

With Range("A1")
    .EntireRow.Insert
    MsgBox .Address
End With

is not the same as this:

    Range("A1").EntireRow.Insert
    MsgBox Range("A1").Address

The first version gets a reference to A1, then inserts a row, so the address of the object is now A2. In the second, the address is still A1. This is quite a trivial example purely to demonstrate the point.

The one that used to catch me out regularly was changing the location of a chart inside a With block because the Location method of a Chart actually returns a reference to a new Chart object making the old one (the one held by the With block) invalid.

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 ByRef not ByRef?

Somewhat related to my last post here, I neglected to mention that ByRef is the default setting in VBA – so if you don’t specify ByVal, you get ByRef.

However, a recent thread at MrExcel reminded me that this is not actually always the case.

A Property Let or Property Set procedure can be written with ByRef parameters but when the code actually runs, the variables will be passed ByVal regardless of what you specified. Equally interestingly, this applies to arrays too, even though you can’t pass an array ByVal according to all the documentation. I assume that a copy of the array is made under the covers and that is passed ByRef. I should also note (with thanks to Pedro at MrExcel) that you can only pass an array variable to a Property Let procedure if it was declared as a dynamic array.

Property Get procedures on the other hand obey the usual rules – ByRef is used unless otherwise specified.

ByVal or ByRef – what’s the difference?

This is a question I see increasingly frequently in forums for some reason, so I thought I’d cobble together a brief synopsis.

ByRef passes a pointer to the variable, so any changes are reflected everywhere that variable is used.

ByVal passes a copy of a variable to a routine, so any changes to that variable will not be reflected in the original routine. In the case of objects, they are always passed as references (pointers) but if you pass an object ByVal you pass a copy of the pointer to the object rather than the original pointer (note: not a copy of the object)

Note: Array variables are always passed ByRef.

(This does not apply to Variants that happen to contain arrays, only to variables that are actually declared as an array.)

 

So here are some examples that will hopefully serve to illustrate the above:

  1. Using a simple data type:
Sub foo()

Dim i As Long
i = 1
Call Change_ByRef(i)
' i is changed by the Change_ByRef procedure and that change IS reflected here too because it was passed by reference
MsgBox "i is now: " & i
Call Change_ByVal(i)
' i is changed only within the Change_ByVal procedure because a COPY of it was passed
MsgBox "i is still: " & i

End Sub

Sub Change_ByRef(ByRef lInput As Long)
lInput = 14
End Sub

Sub Change_ByVal(ByVal lInput As Long)
lInput = 21
End Sub

2. Using an object variable, showing that when passed ByVal, you cannot change the orginal variable to make it point to a different object:

Sub Object_foo()

Dim r As Range
Set r = Range("A1")
Call Change_object_ByRef(r)
' r is changed to a different cell by the Change_ByRef procedure and
' that change IS reflected here too because it was passed by reference
MsgBox "r is now: " & r.Address
Call Change_object_ByVal(r)
' r is changed to a different cell by the Change_ByVal procedure but
' that change IS NOT reflected here because it was passed by value.
MsgBox "r is still: " & r.Address

End Sub

Sub Change_object_ByRef(ByRef rInput As Range)
' change the range variable to one row down
Set rInput = rInput.Offset(1)
End Sub

Sub Change_object_ByVal(ByVal rInput As Range)
' change the range variable to one row down
Set rInput = rInput.Offset(1)
End Sub

3. Again using an object variable, but this time showing that, even when passing ByVal, because you are still passing a pointer to the original object, you can change the properties of the object (you just can’t change which object the variable refers to):

Sub Object_foo2()

Dim r As Range
Set r = Range("A1")
Call Change_object_property_ByRef(r)
' r is given a different value by the Change_ByRef procedure and
' that change IS reflected here because an object reference is passed
MsgBox "r is now: " & r.Value
Call Change_object_property_ByVal(r)
' r is given a different value by the Change_ByVal procedure and
' that change IS reflected here because an object reference is still passed
MsgBox "r is now: " & r.Value

End Sub

Sub Change_object_property_ByRef(ByRef rInput As Range)
' change its value
rInput.Value = "changed byref"
End Sub

Sub Change_object_property_ByVal(ByVal rInput As Range)
' change its value
rInput.Value = "changed byval"
End Sub

For details of an exception to this rule, see the next post.

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