Office 2016 Public Preview Now Available

Microsoft has announced on its Office blog that Office 2016 is now available for public review. Here are some useful links if you want to get involved:

Advertisements
Posted in Microsoft Excel | Leave a comment

2015 Microsoft MVP Virtual Conference

Hi All – I wanted to let you know about a great, free event that Microsoft and the MVPs are putting on, May 14th & 15th.  Join Microsoft MVPs from the Americas’ region as they share their knowledge and real-world expertise during a free event, the MVP Virtual Conference.

The MVP Virtual Conference will showcase 95 sessions of content for IT Pros, Developers and Consumer experts designed to help you navigate life in a mobile-first, cloud-first world.  Microsoft’s Corporate Vice President of Developer Platform, Steve Guggenheimer, will be on hand to deliver the opening Key Note Address.

Why attend MVP V-Conf?  The conference will have 5 tracks, IT Pro English, Dev English, Consumer English, Portuguese mixed sessions & Spanish mixed sessions: there is something for everyone!  Learn from the best and brightest MVPs in the tech world today and develop some great skills!

Be sure to register quickly to hold your spot and tell your friends & colleagues.

The conference will be widely covered on social media, you can join the conversation by following @MVPAward and using the hashtag #MVPvConf.

Register now and feel the power of community!

MVP15_MicrosoftMVP_VC_EmailBanner

Posted in General | Tagged | Leave a comment

Naughty Data Validation Lists

Data validation is a wonderful thing. I know it has its shortfalls (and I always hope for improvements in each Office release)  but, nevertheless, I do use it a lot in my spreadsheets – particularly data validation lists.

The source for a data validation list comes in 2 basic flavours: a comma separated string or a range. If you play around with the user interface [Data > Validation > Allow: List > Source xxx]  you’ll quickly discover that the character limit on the comma separated string is 255 characters (tested in Excel 2013). This limitation isn’t often going to be a problem (and you could always use a range if it were) but what is interesting is that the Excel Object Model will let you bypass it.

This little piece of code creates a comma separated string of 276 characters and creates a data validation list in A1 with it.

Sub foo()
    
    Dim strarrValidation(0 To 40) As String
    Dim strValidation As String
    Dim i As Long
    
    For i = LBound(strarrValidation) To UBound(strarrValidation)
        strarrValidation(i) = "Item" & CStr(i)
    Next i
    
    strValidation = Join$(strarrValidation, ",")
    
    Debug.Print Len(strValidation) '276
    
    With Range("A1").Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=strValidation
    End With
    
End Sub

Sure enough, if you run it and then click on the data validation dropdown you’ll find it goes from Item0 to Item40.

LongList

Now let’s check if this long list can be saved. Save the file as an .xlsm, close it in Excel, browse to the file in Windows explorer and change its file extension to .zip. Then open the zip file and browse through xl > worksheets and open the sheet’s xml file. If you track the formula1 element to the right you’ll find that all 41 items were saved down in the file.

LongListXML

Now for the gotcha. Change the file extension back and try opening it in Excel. Excel will tell you it found a problem with the file and, if you choose to repair it, the data validation list will get stripped out:

Data validation repair list

Unfortunately the repairs log file doesn’t give any extra information such as why the data validation was removed – but it is because it exceeded the character limit.

Potentially one could add the long list in during the Workbook_Open event and then delete it in the Workbook_BeforeSave event to avoid the repair issues – but I haven’t found a good use for bypassing the character limit using code. Have you?

Posted in Microsoft Excel | Tagged , , , | 2 Comments

Delete Rows If All Cells In A Given Range Are Empty

Deleting rows. We all need to do it from time to time and I often find myself referring to how to delete rows using VBA. It has a bunch of handy code snippets and notes on using Range.Find(), Range.Autofilter() and Range.SpecialCells() which can be quickly adapted to do all sorts of things. However, one important scenario missing from that post is how to delete rows when all cells in a given range are empty: something which I’m going to put right now.

Here’s my data in A1:D9:

SampleData1

I want to delete rows 5 and 8 because all the cells in columns A to C are empty on those rows. Some ways of tackling this conundrum are:

  1. Loop through each row, use WorksheetFunction.CountA() to determine if the cells in A:C are empty, remember it if they are and then delete them all at the end
  2. Loop through each column in A:C and use Range.SpecialCells() to identify the empties, and then any entirely empty rows at the end
  3. If the data has a structured layout (in this case column headers are missing), use an autofilter and apply a ‘not empty’ filter to every column and then delete the visible cells
  4. Variations of the above but avoid deleting rows by applying intelligent sorts and clearing the cell contents

Approach (2) looks pretty good to me in terms of a useful code snippet, so I’m going to try to implement it below but, to make things more interesting, I’m going to make some common mistakes which need to be fixed.

Here’s my first effort:

Sub test()

    Dim rngToCheck As Range
    Dim rngToDelete As Range
    Dim rngCol As Range

    Set rngToCheck = Range("A1:C9")
    Set rngToDelete = rngToCheck.Columns(1)

    For Each rngCol In rngToCheck.Columns

        On Error Resume Next
        Set rngToDelete = Intersect( _
            rngToDelete.EntireRow, _
            rngCol.SpecialCells(xlCellTypeBlanks))
        On Error GoTo 0

        If rngToDelete Is Nothing Then Exit Sub

     Next rngCol

    Application.ScreenUpdating = False
    rngToDelete.EntireRow.Delete
    Application.ScreenUpdating = True

End Sub

It works well on the sample data. It gives rngToDelete a reference to A1:A9 and then loops through columns A to C checking for empty cells.

  • After the 1st iteration of the loop, rngToDelete holds a reference to A2:A3,A5:A6,A8. Perfect.
  • After the 2nd iteration of the loop, rngToDelete holds a reference to B3,B5,B8. Perfect.
  • After the 3rd iteration of the loop, rngToDelete holds a reference to C5,C8 so the code then deletes rows 5 and 8. Perfect.

But wait, there are some bugs: you have to be so careful when using Range.SpecialCells().

Let’s test the code on some different data:

SampleData2

The code shouldn’t delete any of these rows but, if you run it, you’ll find that it deletes row 3! Not good. Not good at all.

The reason for this is Range.SpecialCells(xlCellTypeBlanks) throws an error when there are no empty cells. The code assumes it  returns Nothing which means that rngToDelete simply retains its reference from the previous iteration in the loop:

  • After the 1st iteration of the loop, rngToDelete holds a reference to A3. Perfect.
  • After the 2nd iteration of the loop, rngToDelete holds a reference to A3. Ouch.
  • After the 3rd iteration of the loop, rngToDelete holds a reference to A3 so the code then deletes rows 3. Ouch.

There are a couple of ways this can be fixed, one of which is to move the Range.SpecialCells() call into a separate function which will return Nothing if an error occurs:

Sub test2()

    Dim rngToCheck As Range
    Dim rngToDelete As Range
    Dim rngCol As Range

    Set rngToCheck = Range("A1:C9")
    Set rngToDelete = rngToCheck.Columns(1)

    For Each rngCol In rngToCheck.Columns

        Set rngToDelete = Intersect( _
            rngToDelete.EntireRow, _
             GetEmptyCells(rngCol))

        If rngToDelete Is Nothing Then Exit Sub

     Next rngCol

    Application.ScreenUpdating = False
    rngToDelete.EntireRow.Delete
    Application.ScreenUpdating = True

End Sub

Private Function GetEmptyCells(ByRef rngToCheck As Range) As Range
    On Error Resume Next
    Set GetEmptyCells = rngToCheck.SpecialCells(xlCellTypeBlanks)
End Function

Now that the On Error Resume Next has been tucked out-of-the-way, a second issue reveals itself. Application.Intersect() will throw a “No cells were found” error if GetEmptyCells() returns Nothing. This can be fixed by adding some defensive coding:

Sub test3()

    Dim rngToCheck As Range
    Dim rngToDelete As Range
    Dim rngCol As Range

    Set rngToCheck = Range("A1:C9")
    Set rngToDelete = rngToCheck.Columns(1)

    For Each rngCol In rngToCheck.Columns

        Set rngToDelete = GetIntersect( _
            rngToDelete.EntireRow, _
            GetEmptyCells(rngCol))

        If rngToDelete Is Nothing Then Exit Sub

     Next rngCol

    Application.ScreenUpdating = False
    rngToDelete.EntireRow.Delete
    Application.ScreenUpdating = True

End Sub

Private Function GetEmptyCells(ByRef rngToCheck As Range) As Range
    On Error Resume Next
    Set GetEmptyCells = rngToCheck.SpecialCells(xlCellTypeBlanks)
End Function

Private Function GetIntersect(ByRef rng1 As Range, _
        ByRef rng2 As Range) As Range

    If Not (rng1 Is Nothing Or rng2 Is Nothing) Then
        Set GetIntersect = Intersect(rng1, rng2)
    End If

End Function

That all seems to be working now.

But wait! Let’s change the data again and suppose we’re running this just on a single row, row 1:

SampleData3

If you change the rngToCheck variable to reference A1:C1 you’ll find that the code deletes the row even though cells A1 and B1 aren’t empty!  The reason for this is, as it loops through each column, the rngCol variable holds a reference to a single cell. I’ve got some important notes on Range.SpecialCells() from a single cell on my other blog post which explain the problem so I won’t repeat them here but, essentially, the GetEmptyCells() method needs to check if rngToCheck is a single cell, like so:

Sub test4()

    Dim rngToCheck As Range
    Dim rngToDelete As Range
    Dim rngCol As Range

    Set rngToCheck = Range("A1:C1")
    Set rngToDelete = rngToCheck.Columns(1)

    For Each rngCol In rngToCheck.Columns

        Set rngToDelete = GetIntersect( _
            rngToDelete.EntireRow, _
            GetEmptyCells(rngCol))

        If rngToDelete Is Nothing Then Exit Sub

    Next rngCol

    Application.ScreenUpdating = False
    rngToDelete.EntireRow.Delete
    Application.ScreenUpdating = True

End Sub

Private Function GetEmptyCells(ByRef rngToCheck As Range) As Range

    If rngToCheck.Cells.CountLarge = 1 Then
        If IsEmpty(rngToCheck.Value2) Then
            Set GetEmptyCells = rngToCheck
        End If
    Else
        On Error Resume Next
        Set GetEmptyCells = rngToCheck.SpecialCells(xlCellTypeBlanks)
    End If

End Function

Private Function GetIntersect(ByRef rng1 As Range, _
        ByRef rng2 As Range) As Range

    If Not (rng1 Is Nothing Or rng2 Is Nothing) Then
        Set GetIntersect = Intersect(rng1, rng2)
    End If

End Function

I’ve used Range.CountLarge which means that I’m assuming the code is for XL 2007 or later. This also means that I don’t have to worry about the Range.SpecialCells() limit of 8,192 non-contiguous cells.

Nearly there now, but there’s one more scenario we need to cater for.  If you add a new sheet to the workbook, carefully enter the data below and then run the code using A1:C9 as the rngToCheck, you’ll find that row 2 doesn’t get deleted.

SampleData4

The reason for this one is that Range.SpecialCells() intrinsically only checks cells within the sheet’s used range. In this case, the used range is A1:B3 so Range.SpecialCells() doesn’t even consider cells in column C to exist. This means that in the final iteration of the loop the GetEmptyCells() call returns Nothing. Again, there are a couple of ways we can deal with this but an easy option is to use our existing GetIntersect() function to trim down rngToCheck to fit within the used range. Calling the Worksheet.UsedRange property can be expensive so the fix can be added into the main Sub rather than in the GetIntersect() method which gets called repeatedly.

Here’s the final, tidied up version. I’ve coded it to throw an error if the rngToCheck doesn’t intersect with the used range but you could just silently exit if you prefer.

Sub Example()

    'deletes the rows in sheet1 1:9
    'if all the corresponding cells in columns A:C are empty
    DeleteRowsIfAllCellsEmpty Sheet1.Range("A1:C9")

End Sub

Public Sub DeleteRowsIfAllCellsEmpty(ByVal rngToCheck As Range)

    Dim rngToDelete As Range
    Dim rngCol As Range
    
    Set rngToCheck = GetIntersect( _
        rngToCheck, _
        rngToCheck.Worksheet.UsedRange)
    
    If rngToCheck Is Nothing Then
        Err.Raise _
          Number:=1004, _
          Description:="rngToCheck does not intersect with the UsedRange"

        Exit Sub
    End If

    Set rngToDelete = rngToCheck.Columns(1)

    For Each rngCol In rngToCheck.Columns
    
        Set rngToDelete = GetIntersect( _
            rngToDelete.EntireRow, _
            GetEmptyCells(rngCol))
    
        If rngToDelete Is Nothing Then Exit Sub
    
    Next rngCol

    Application.ScreenUpdating = False
    rngToDelete.EntireRow.Delete
    Application.ScreenUpdating = True

End Sub

Private Function GetEmptyCells(ByRef rngToCheck As Range) As Range

    If rngToCheck.Cells.CountLarge = 1 Then
        If IsEmpty(rngToCheck.Value2) Then
            Set GetEmptyCells = rngToCheck
        End If
    Else
        On Error Resume Next
        Set GetEmptyCells = rngToCheck.SpecialCells(xlCellTypeBlanks)
    End If

End Function

Private Function GetIntersect(ByRef rng1 As Range, _
        ByRef rng2 As Range) As Range

    If Not (rng1 Is Nothing Or rng2 Is Nothing) Then
        Set GetIntersect = Intersect(rng1, rng2)
    End If

End Function

Posted in Microsoft Excel | Tagged , , , | 1 Comment

Who Has My Workbook Open? [C#.Net]

I had to update a C# project this week so it can check if someone has opened (and locked) an Excel workbook. Identifying the actual culprit is surprisingly hard to do – there’s nothing useful in the Excel object model itself –  but there’s a really neat solution posted by Steven over at StackOverflow.

When a user opens an Excel file in Windows a hidden, temporary backup file is created in the same directory as the Excel file. It has the same name as the Excel file but with a ~$ prefix. When the user closes the Excel file the backup file is deleted. The backup file does not get created if the user opens the file as read only.

Armed with this knowledge we can cobble together some code which checks for the backup file and then, if it exists, find out who created it. Steven’s already posted some VBA on the above link so I won’t regurgitate a VBA solution on here, but this is the equivalent implementation in C# goodness:

using System;
using System.IO;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            //workbook to test
            const string fullFileName = @"C:\Temp\MyWorkbook.xlsx";

            string tempFullFileName = GetXlTempFullFileName(fullFileName);

            string user = "No one";

            if (File.Exists(tempFullFileName))
            {
                user = GetFileOwner(tempFullFileName);
            }

            Console.WriteLine("{0} has workbook {1} open"
                , user, fullFileName);

            Console.ReadKey();
        }

        private static string GetFileOwner(string fullFileName)
        {
            FileInfo info = new FileInfo(fullFileName);
            return info.GetAccessControl().GetOwner(
                typeof(System.Security.Principal.NTAccount)).ToString();
        }

        private static string GetXlTempFullFileName(string fullFileName)
        {
            string fileName = Path.GetFileName(fullFileName);
            string directory = Path.GetDirectoryName(fullFileName);
            string tempFileName = "~$" + fileName;
            return Path.Combine(directory, tempFileName);
        }
    }
}

I’ve assumed that the account running the code has permissions to the relevant directory but, other than that, it should be good. Can anyone suggest when this general methodology might fail?

Posted in Microsoft Excel, Visual C# | 1 Comment

ActiveX Controls Not Working On Worksheets

The online forums have been busy the last month with posts about Microsoft security update MS14-082 which breaks ActiveX controls embedded on worksheets. It finally reared its ugly head at my workplace this week which has prompted me to mention it on here. My employer is a large, financial institution with offices around the world. Update MS14-082 was rolled out in its US offices but not in Europe and Asia which meant that the problem manifested itself in two ways.

Firstly, users in the US found that any ActiveX controls on worksheets stopped working once the update had been installed. They would click on the controls but nothing would happen. This symptom resembles the one I blogged about when you give an ActiveX control embedded on a worksheet a name which is too long. To fix it we simply had to close down all Office applications and delete any files with an .exd extension from the C:\.

Secondly, we found that, if a user in US saved a workbook with ActiveX controls on worksheets, users elsewhere in the world (who did not have the update installed) would not be able to run VBA code in it. The ActiveX controls wouldn’t work and if they tried to compile the code they would get the compile error: “Object library invalid or contains references to object definitions that could not be found”. The easiest way to fix this one is to install the update and then delete .exd files as described above. However, that wasn’t an immediate option in the corporate environment so, for any critical workbooks, we had to delete the ActiveX controls from the worksheets and replace them with their Forms controls counterparts. The caveat to this workaround is that the ActiveX controls must be deleted by a user with the update installed: if a user without the update tries to do it then it immediately crashes Excel.

If you’re experiencing problems in relation to this security update then you can refer to The MS Excel Support Team Blog and knowledge base article KB3025036 which describe all known issues and corresponding resolutions.

Posted in Microsoft Excel, Microsoft Office | Tagged , , , | 1 Comment

2014 In Review

Here’s the usual RAD Excel annual summary report which WordPress.com provides each year. We had 180,000 views in 2014, which is up from the 130,000 views we had in 2013. Thanks again to everyone for reading and I’ll see you all in the new year.

Posted in Microsoft Excel | 2 Comments

Conditional Sum On #N/A Text Values

Today’s post is inspired by a question which came up over at the MrExcel.com forums.

Here’s my spreadsheet:

NASpreadSheet

It’s pretty simple. In cells A1:A2 I have the formula ="#N/A" and in cells A3:A4 I have the formula =NA(). Whilst the outputs of these formulas look similar, they are fundamentally different: A1 and A2 are text types whilst A3 and A4 are error types. The respective left and middle alignments in the cells hint at this.

The challenge is how to use SUMIF() to get a sum of the values in column B which correspond to the text "#N/A" values – and it isn’t quite as easy as you’d think. We want an answer of 3 but all of these formulas return 12:

=SUMIF(A1:A4,"#N/A",B1:B4)

=SUMIF(A1:A4,"=#N/A",B1:B4)

=SUMIF(A1:A4,A1,B1:B4)

It appears that, under the hood, SUMIF() coerces each of these criteria arguments from text types into error types before it does its comparisons. Before you read on have a go at finding the solution.

After a bit of playing around, here’s the best I could come up with in terms of pure SUMIF() formulae:

=SUMIF(A1:A4,"*#N/A",B1:B4)

Or variations of:

=SUMIF(A1:A4,"?N/A",B1:B4)

=SUMIF(A1:A4,"#?/A",B1:B4)

etc…

The * and ? wildcards convince SUMIF() that we want it to work with text types. If you’re not familiar with using wildcards in SUMIF() then check out this blog post I wrote a couple of years ago.

Of course, these formulae aren’t foolproof. For example, if A1 contained the formula ="abc#N/A" then it would be included in the * wildcard sum. We only want pure "#N/A" values to be included. To cater for those somewhat unlikely scenarios I ended up tweaking the formula to a SUMIFS():

=SUMIFS(B1:B4,A1:A4,"?N/A",A1:A4,"#???")

I think that one’s solid. Another option, which I’m not nearly as keen on, is this array formula (CTRL + SHIFT + ENTER):

=SUM((IFNA(A1:A4,"")="#N/A")*B1:B4)

So, can you improve on the SUMIFS() formula or, even better, find a robust way of doing it with SUMIF()?

Posted in Microsoft Excel | Tagged , , | 1 Comment