Autofilter bug with Excel 2016/365 build 1706

It appears that there is a problem with Autofilters in the latest build (1706) where some values are not displayed in the filter dropdowns if you have a lot of data (say more than 20k rows). The only fix I am aware of currently is to revert back to the previous build, which didn’t have the same problem.

For instructions on reverting, see here.

Passing arguments ByRef using Run

I know what you’re thinking: you can’t do that – at least not without a convoluted workaround like public variables or passing memory locations and copying memory blocks. I thought the same too. As did everyone else, including Microsoft, as far as I can tell. However, the intrepid Jaafar Tribak at MrExcel discovered an insanely simple solution to this problem – just late bind the application object.

Yep, you heard. That’s all it takes.

So, as we know, code like this demonstrates that Run passes everything ByVal no matter what you tell it:

Sub runByVal()
    Dim sTemp As String
   
    sTemp = "Hello"
   
    Application.Run "PassByVal", sTemp
   
    ' unchanged as you would expect
    Debug.Print sTemp
   
    Application.Run "PassByRef", sTemp
   
    ' still unchanged, per documentation as the arg is passed ByVal regardless
    Debug.Print sTemp
   
End Sub

Sub PassByVal(ByVal s As String)
    s = "ByVal"
End Sub
Sub passByRef(ByRef s As String)
    s = "ByRef"
End Sub

The output of both Debug.Print statements will be “Hello”.

But with a simple change, we can pass the argument ByRef:

Sub runByRef()
    Dim sTemp As String
    Dim o ' just so we can late bind the Application: Variant or Object will work
   
    sTemp = "Hello"
   
    Set o = Application
    o.Run "PassByVal", sTemp
   
    ' unchanged as you would expect
    Debug.Print sTemp
   
    o.Run "PassByRef", sTemp
   
    ' this is now changed! Mind. Blown.
    Debug.Print sTemp
   
End Sub

Sub PassByVal(ByVal s As String)
    s = "ByVal"
End Sub
Sub passByRef(ByRef s As String)
    s = "ByRef"
End Sub

Now the output will be “Hello” followed by “ByRef”. As far as I can tell it works with all variable types including objects.

I don’t really know why it works here (late binding a class won’t make a property routine work ByRef) but it does and is really easy to implement!

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.

Transpose bug in 2013 and 2016

Many of us will be used to using Application.Transpose (or WorksheetFunction.Transpose) when manipulating ranges or arrays in VBA. And, up to and including Excel 2010, we are probably all aware that you will get a run-time error if you pass an array (not range) that is over 65536 ‘rows’.

 

The good news is that this error doesn’t occur in 2013 or 2016. The bad news is that the resulting array is not the size it should be and it gets truncated with no warning at all!

 

Essentially, what happens with an array that is over 65536 rows, the last whole multiple of 65536 rows are simply removed – i.e. you lose (n \ 65000) * 65000 rows.

So if your array is 65537 rows by 1 column, the resulting array will have 1 item. If it’s 85000 rows by 1 column, the result will have 19464 items (85000-65536*1). If you have 147000 rows, the result will have 15928  (being 147000-65536*2) and so on.

 

I’ve reported this but had no feedback as yet.

Super secret SpecialCells

I’m sure we’re all familiar with the Range.Specialcells method. If you’re not, you need to be, because it’s incredibly useful, so go and check it out now. Go on, we’ll wait.

 

OK, now that we’re all on the same page, you will be aware that there are 2 arguments you can supply to the SpecialCells method:

  • Type (required).
    • This is, naturally, the type of cells you are interested in and the available values are shown as those of the XlCellType Enum. More on this in a minute.

     

  • Value (Optional).

    When the Type argument is xlCellTypeConstants or xlCellTypeFormulas, you can specify which type(s) of values you are interested in:

Enum name

Literal value

xlNumbers

1

xlTextValues

2

xlLogical

4

xlErrors

16

 

Note: these values are additive (like the buttons for a MsgBox call), so if you want numbers and text, you can specify xlNumbers + xlTextValues

As far as I can tell, you can supply any number between 1 and 255 for the Value argument but it will be ignored if the Type argument is not xlCellTypeFormulas or xlCellTypeConstants, or if it’s not a valid combination of the values in the table above.

 

So, back to point 1 as promised. According to the documentation, the valid Type values are those of the XlCellType enumeration, namely:

 

Name

Literal value

xlCellTypeAllFormatConditions

-4172

xlCellTypeAllValidation

-4174

xlCellTypeBlanks

4

xlCellTypeComments

-4144

xlCellTypeConstants

2

xlCellTypeFormulas

-4123

rxlCellTypeLastCell

11

xlCellTypeSameFormatConditions

-4173

xlCellTypeSameValidation

-4175

xlCellTypeVisible

12

 

However, this is not in fact a complete list of the possible values you can pass. In addition, you can pass values which correspond (roughly) to the order in which the items appear in the Goto – Special… dialog (below)

 

 

Type number

Equivalent

Same as XlCellTypeEnum value

Range.SpecialCells(1)

Range.SpecialCells(xlCellTypeComments)

No

Range.SpecialCells(2)

Range.SpecialCells(xlCellTypeConstants)

Yes

Range.SpecialCells(3)

Range.SpecialCells(xlCellTypeFormulas)

No

Range.SpecialCells(4)

Range.SpecialCells(xlCellTypeBlanks)

Yes

Range.SpecialCells(5)

Range.CurrentRegion

No enum value

Range.SpecialCells(6)

Range.CurrentArray

No enum value

Range.SpecialCells(7)

Range.RowDifferences

No enum value

Range.SpecialCells(8)

Range.ColumnDifferences

No enum value

Range.SpecialCells(9)

Range.Precedents

No enum value

Range.SpecialCells(10)

Range.Dependents

No enum value

Range.SpecialCells(11)

Range.SpecialCells(xlCellTypeLastCell)

Yes

Range.SpecialCells(12)

Range.SpecialCells(xlCellTypeVisible)

Yes

Range.SpecialCells(13)

Range.SpecialCells(xlCellTypeAllFormatConditions)

No

Range.SpecialCells(14)

Range.SpecialCells(xlCellTypeAllValidation)

No

 

Hopefully this will prove useful to someone. J

 

A few key things to note:

If you use the SpecialCells method applied to one cell, it typically works as if applied to the entire worksheet. However, this is not true if you use 5, 6, 9 or 10 as the Type argument.

Prior to Excel 2010, specialcells can return a maximum of 8192 areas (discrete blocks of cells). If more areas than that apply, the returned range will be the whole of the original range.

I can at present find no way using 7, 8, 13 or 14 to provide the additional options available in the Goto – Special dialog since the Values parameter seems to simply be ignored.

TechDays Online – A Microsoft Azure special


 

Please help spread the word to as much of the technical community as possible about the Tech Days Online Microsoft Azure Special event; a three day digital event happening on the 2nd,3rd,4th of June and is part of the hugely popular Tech Days Online series. 

 

IT Professionals and Developers joining will get:

 

  • Community keynote sessions with respected industry figures showcasing what others are doing with Microsoft Azure – including Scott Hanselman, Acclaimed Developer, Microsoft
  • Over 15 deep technical sessions spanning Azure 101, Apps & Architecture, Data & Machine Learning and Cloud Infrastructure & DevOps delivered by Microsoft UK experts and Gold Learning Partner QA Training to help IT Pros and Devs get started TODAY
  • The opportunity to ask questions to all of our experts though live Q&A
  • A live mystery Microsoft Azure challenge and a competition to win a drone
  • Information on the offers available across MSDN/BizSpark etc and how to take a free trial of Microsoft Azure

 

Registration Link:

https://msdn.microsoft.com/en-gb/dn973263?ls=Inbound+Phone&lsd=MVP-Site&WT.mc_id=WBNR+MVP-comms+MAY15 

 

 

Feel free to use any of the following sample tweets and posts to encourage your followers & friends to register:

IT Pro

https://twitter.com/TechNetUK/status/598813781859258368

https://twitter.com/TechNetUK/status/598813725739483136 

https://twitter.com/TechNetUK/status/598813669808480256

 

Dev

https://twitter.com/msdevUK/status/598810060685316096

https://twitter.com/msdevUK/status/598810151081021441 

https://twitter.com/msdevUK/status/598810243494117376

 

Facebook ads here:

https://facebook.com/271725486227028/posts/892574690808768

https://facebook.com/271725486227028/posts/892575537475350

https://facebook.com/271725486227028/posts/892586637474240

https://facebook.com/613969575296338/posts/1132057673487523

https://facebook.com/613969575296338/posts/1132058216820802

https://facebook.com/613969575296338/posts/1132076460152311