Unwanted Worksheet Events? Maybe SpecialCells() Is To Blame!

I’ve written a lot on this blog about Range.SpecialCells() and how careful you have to be when using it. Here’s yet another caveat which I didn’t know about until another developer brought it to my attention.

Put some data in a worksheet (to avoid runtime errors for this test) and then put this code in that worksheet’s class module:

Option Explicit

Sub TestSpecialCells1()
    Cells.SpecialCells xlCellTypeBlanks
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox Target.Address
End Sub

If you run TestSpecialCells1() you’ll find that, provided a blank cell was found, the Worksheet_SelectionChange() event is raised – which is immensely annoying. The code clearly doesn’t select any cells so why should that event get raised? The answer is that the code behind Range.SpecialCells() is shoddy.

Range.SpecialCells() mirrors using many of the options in the  [F5 > Special] ‘Go To Special’ dialog box (shown below). When a user tries to access special cells via the interface it is quite reasonable that those cells are selected but, when we’re accessing them via code, it shouldn’t happen. To put it simply: Range.SpecialCells() is too tightly coupled to the user interface:

GoToSpecial

If you test your way through these options you’ll find that Range.SpecialCells() isn’t the only Range class member which exhibits this behaviour. Watch out for any of these:

  • Range.ColumnDifferences()
  • Range.CurrentArray
  • Range.Dependents
  • Range.DirectDependents
  • Range.DirectPrecedents
  • Range.Precedents
  • Range.RowDifferences()
  • Range.SpecialCells()

For some reason Range.CurrentRegion doesn’t – which is a good thing – but note that its Range.SpecialCells() equivalent, Range.SpecialCells(5), does. You’ll also find some more interesting behaviour which gives us an insight as to how it works. For example:

Sub TestSpecialCells2()
    Cells.SpecialCells xlCellTypeLastCell
End Sub

Range.SpecialCells(xlCellTypeLastCell) causes the Worksheet_SelectionChange() event to be raised twice! Why? Once to find the last row and once to find the last column so that it can then intersect them to get the last cell.

Until Microsoft fixes this event issue (and I seriously doubt it ever will), the workaround is to set Application.EnableEvents to False before using it and setting it back to True again after using it. Proper error handling needs to be in place to ensure Application.EnableEvents is reset correctly because a whole host of situations can cause these calls to throw a runtime error.

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

#REF! Means Your INDIRECT() Formula May Be Slow As A Dog

A user recently handed me a spreadsheet and asked me to investigate some bizarre behaviour. For some people the spreadsheet’s calculation was fast, for others quite slow and, for a few, extremely slow. After a bit of playing around I found what the problem was and, since I couldn’t find it documented anywhere, I thought it would be worth a quick blog post.

The workbook in question had many sheets and a lot of those sheets had several thousand INDIRECT() formulas. The workbook had been structured with INDIRECT() formulas so the users could easily redirect ‘lookup’ formulas to pull data from different sheets as and when they needed to. This sort of design isn’t uncommon.

The workbook was really slow to calculate (1 minute) on my machine. There were no array formulas or complex calculations anywhere and there were very few downstream dependencies on the INDIRECT()formulas. However, I found that, if I deleted one particular sheet, the workbook calculations suddenly became ‘instantaneous’ (2 seconds). When I noticed that the INDIRECT() formulas on that sheet were all returning #REF! errors things began to make sense.

Let’s take a step back and think about what INDIRECT() does. It takes a string (or a cell containing a string), resolves what that string represents and then returns a reference to the corresponding range. The string may be an A1 or R1C1 style reference or the name of a named range. INDIRECT() has to go through a number of steps to determine what the reference may be; if it can’t find a match then it returns a #REF! error.

It’s a logical conclusion that the function will start with a local scope and then broaden its search through other open workbooks and add-ins to try to find a match if it needs to. If you have an invalid string reference, INDIRECT() is going to go through every possibility it can to try to find a corresponding range before it finally spits back an #REF! error: it is doing more work which means it will be slower.

This can also cause some pretty insidious behaviour: the speed of the #REF! INDIRECT() function will be affected by the number of workbooks and add-ins you have open and how many sheets and names they have. If you test the workbook in a “light” Excel instance it could recalculate quite quickly but for someone else with a “heavy” session it could be painfully slow. This behaviour is compounded by the fact that INDIRECT() is volatile which means it recalculates on every calculation event. In extreme cases where lots of #REF! INDIRECT() formulas are involved it can quite literally cripple the spreadsheet.

So, dear reader, manage your string references carefully and beware INDIRECT() formulas which return #REF! errors.

Posted in Microsoft Excel | Tagged , , | 5 Comments

VBA: Understanding ByVal In An ActiveX Textbox KeyPress Event Handler

I was posting on one of the online forums a while back and helped a user understand some code which he had found on the web and, in particular, how it restricted what could be typed into a textbox on a userform. The code looked like this:

Private Sub TextBox1_KeyPress( _
    ByVal KeyAscii As MSForms.ReturnInteger)

    Select Case KeyAscii
        Case vbKey0 To vbKey9, vbKeyBack
            'OK
        Case Else
            KeyAscii = 0
            Beep
    End Select
End Sub

The question revolved around the highlighted line of code. The user thought that, in theory, that line of code should do nothing because:

  1. KeyAscii isn’t used at a later point in the procedure.
  2. KeyAscii is passed ByVal, so the change won’t propogate back to the caller of the event handler (incorrect).

However, he knew from testing that, if he removed that line of code, the restricted entry was no longer enforced. The code clearly works – but how?

The most important point to this answer is that MSForms.ReturnInteger is not a literal: it looks like an Integer because it has Integer in the name – but it isn’t one. If you look in the object browser you’ll see that it is a class and it has a default Value property.

ReturnInteger

I think things become clearer if we re-write the code so that it explicitly uses that Value property:

Private Sub TextBox1_KeyPress( _
    ByVal KeyAscii As MSForms.ReturnInteger)
    
    Select Case KeyAscii.Value
        Case vbKey0 To vbKey9, vbKeyBack
            'OK
        Case Else
            KeyAscii.Value = 0
            Beep
    End Select
End Sub

This code is exactly the same but now KeyAscii looks more like an object (an instance of a class). The question now becomes: what happens when an object is passed ByVal? Let’s do a test with our own custom class:

Class1

Option Explicit

Private mlngMyValue As Long

Public Property Let MyValue(value As Long)
     mlngMyValue = value 
End Property

Public Property Get MyValue() As Long
     MyValue = mlngMyValue 
End Property

Module1

Option Explicit

Sub test()

    Dim c1 As Class1         

    Set c1 = New Class1         
    c1.MyValue = 1         

    Debug.Print c1.MyValue     'gives 1

    TryToChangeProperty c1         
   
    Debug.Print c1.MyValue     'gives 2

End Sub

Sub TryToChangeProperty(ByVal c As Class1)
     c.MyValue = 2 
End Sub

That’s right. As Chip explains on his site, when an object is passed ByVal, we can’t change the reference to the object itself upstream – but we can change its properties. If it were passed ByRef then we’d be able to change the object reference itself:

Module2


Option Explicit

Sub test2()

    Dim c1 As Class1

    Set c1 = New Class1
    c1.MyValue = 1

    Debug.Print c1.MyValue      'gives 1
    TryToChangeValue c1
    Debug.Print c1.MyValue      'gives 2

    TryToChangeObjectByVal c1
    Debug.Print c1.MyValue      'gives 2

    TryToChangeObjectByRef c1
    Debug.Print c1.MyValue      'gives 0

End Sub

Sub TryToChangeValue(ByVal c As Class1)
    c.MyValue = 2
End Sub

Sub TryToChangeObjectByVal(ByVal c As Class1)

    Dim c2 As Class1

    Set c2 = New Class1
    Set c = c2
End Sub

Sub TryToChangeObjectByRef(ByRef c As Class1)
    
    Dim c2 As Class1

    Set c2 = New Class1
    Set c = c2
End Sub

So now we have the answer: KeyAscii = 0 works because KeyAscii is an object and this sets the KeyAscii.Value property to 0. Although the KeyAscii object is passed ByVal, changes we make to its properties will have an impact on any code further up the call stack which references the same object.

Posted in Microsoft Excel | Tagged , , , , , | Leave a comment

Minimize And Maximize Userform Across 32-bit and 64-bit Environments

If you scour the internet you’ll find plenty of examples of how to give your VBA userform minimize and maximize buttons. The exact code will vary depending on the trinkets the programmer wants, but a typical snippet will look something like this:

Option Explicit

Private Declare Function GetWindowLong _
    Lib "user32.dll" Alias "GetWindowLongA" ( _
    ByVal hwnd As Long, _
    ByVal nIndex As Long) As Long
    
Private Declare Function SetWindowLong _
    Lib "user32.dll" Alias "SetWindowLongA" ( _
    ByVal hwnd As Long, _
    ByVal nIndex As Long, _
    ByVal dwNewLong As Long) As Long

Private Declare Function FindWindowA _
    Lib "user32.dll" ( _
    ByVal lpClassName As String, _
    ByVal lpWindowName As String) As Long
        
Private Declare Function DrawMenuBar _
    Lib "user32.dll" ( _
    ByVal hwnd As Long) As Long

Private Sub UserForm_Initialize()
    CreateMenu
End Sub

Private Sub CreateMenu()
    
    Const GWL_STYLE As Long = -16
    Const WS_SYSMENU As Long = &H80000
    Const WS_MINIMIZEBOX As Long = &H20000
    Const WS_MAXIMIZEBOX As Long = &H10000
    
    Dim lngFrmWndHdl As Long
    Dim lngStyle As Long
    
    lngFrmWndHdl = FindWindowA(vbNullString, Me.Caption)
    
    lngStyle = GetWindowLong(lngFrmWndHdl, GWL_STYLE)
    lngStyle = lngStyle Or WS_SYSMENU       'Add SystemMenu
    lngStyle = lngStyle Or WS_MINIMIZEBOX   'Add MinimizeBox
    lngStyle = lngStyle Or WS_MAXIMIZEBOX   'Add MaximizeBox

    SetWindowLong lngFrmWndHdl, GWL_STYLE, lngStyle
    
    DrawMenuBar lngFrmWndHdl

End Sub

The code is fine but it needs to be updated to run in 64-bit environments. In short, the way do to this is to include the PtrSafe keyword after each Declare keyword and to look up each function on MSDN and then use LongPtr instead of Long for each parameter and output if it represents a handle or a pointer. If you want the functions to work across both 32-bit and 64-bit Office then you may have to declare them multiple times inside a conditional compilation block. There are a couple of useful articles on MSDN which go into this in more depth:

The MSDN documentation for GetWindowLong and SetWindowLong states that they’ve been superseded by GetWindowLongPtr and SetWindowLongPtr, so it sounds like a good idea to use the new ones instead:

Note  To write code that is compatible with both 32-bit and 64-bit versions of Windows, use GetWindowLongPtr. When compiling for 32-bit Windows, GetWindowLongPtr is defined as a call to the GetWindowLong function.

To declare these new functions correctly in VBA, consideration needs to be given to the possibility that you’re using 32-bit Office – even if it is on 64-bit Windows. On 64-bit Windows, 32-bit Excel will try to use the 32-bit User32.dll in the C:\Windows\SYSWOW64 directory. This makes perfect sense but if you don’t believe me you can see it by using Process Explorer:

procmonex

The 32-bit User32.dll doesn’t contain the GetWindowLongPtr and SetWindowLongPtr functions. You can see this by using Dependency Walker:

DepWalkerUser32

In other words, if you try to call them from 32-bit Excel you will get the run-time error 453: Can’t Find DLL entry point [method name] in User32.dll. This means that the Win64 compilation constant needs to be used rather than VBA7 (I will talk more about these constants in a later blog post), so the API declarations should be written as follows:

Option Explicit

#If Win64 Then
    Private Declare PtrSafe Function GetWindowLongPtr _
        Lib "user32.dll" Alias "GetWindowLongPtrA" ( _
        ByVal hwnd As LongPtr, _
        ByVal nIndex As Long) As LongPtr

    Private Declare PtrSafe Function SetWindowLongPtr _
        Lib "user32.dll" Alias "SetWindowLongPtrA" ( _
        ByVal hwnd As LongPtr, _
        ByVal nIndex As Long, _
        ByVal dwNewLong As LongPtr) As LongPtr

    Private Declare PtrSafe Function FindWindowA _
        Lib "user32.dll" ( _
        ByVal lpClassName As String, _
        ByVal lpWindowName As String) As LongPtr
        
    Private Declare PtrSafe Function DrawMenuBar _
        Lib "user32.dll" ( _
        ByVal hwnd As LongPtr) As Long
#Else
    Private Declare Function GetWindowLongPtr _
        Lib "user32.dll" Alias "GetWindowLongA" ( _
        ByVal hwnd As Long, _
        ByVal nIndex As Long) As Long
    
    Private Declare Function SetWindowLongPtr _
        Lib "user32.dll" Alias "SetWindowLongA" ( _
        ByVal hwnd As Long, _
        ByVal nIndex As Long, _
        ByVal dwNewLong As Long) As Long

    Private Declare Function FindWindowA _
        Lib "user32.dll" ( _
        ByVal lpClassName As String, _
        ByVal lpWindowName As String) As Long
        
    Private Declare Function DrawMenuBar _
        Lib "user32.dll" ( _
        ByVal hwnd As Long) As Long
#End If

Private Sub UserForm_Initialize()
    CreateMenu
End Sub

Private Sub CreateMenu()
    
    Const GWL_STYLE As Long = -16
    Const WS_SYSMENU As Long = &H80000
    Const WS_MINIMIZEBOX As Long = &H20000
    Const WS_MAXIMIZEBOX As Long = &H10000
    
    #If Win64 Then
        Dim lngFrmWndHdl As LongPtr
        Dim lngStyle As LongPtr
    #Else
        Dim lngFrmWndHdl As Long
        Dim lngStyle As Long
    #End If
    
    lngFrmWndHdl = FindWindowA(vbNullString, Me.Caption)
    
    lngStyle = GetWindowLongPtr(lngFrmWndHdl, GWL_STYLE)
    lngStyle = lngStyle Or WS_SYSMENU       'Add SystemMenu
    lngStyle = lngStyle Or WS_MINIMIZEBOX   'Add MinimizeBox
    lngStyle = lngStyle Or WS_MAXIMIZEBOX   'Add MaximizeBox

    SetWindowLongPtr lngFrmWndHdl, GWL_STYLE, lngStyle
    
    DrawMenuBar lngFrmWndHdl

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

Stop MSForms.Frame Scrollbars From Resetting On Focus

I’ve been doing some more work on my dynamic grid control – which mostly comprises of MSForms Comboboxes in a Frame. A feature of a Frame control with scrollbars is that when it gets the focus the scrollbars’ positions reset themselves. Here’s some quick and dirty code you can put in a userform’s code module to see what I mean:

Option Explicit

Private Sub UserForm_Initialize()
    
    Const sngFRAME_SPACING As Single = 5
    
    Dim objFrame1 As MSForms.Frame
    Dim objFrame2 As MSForms.Frame
    
    Width = 400
    Height = 200
    
    Set objFrame1 = Controls.Add("Forms.Frame.1")
        
    With objFrame1
        .Width = (InsideWidth - sngFRAME_SPACING * 3) / 2
        .Left = sngFRAME_SPACING
        .ScrollBars = fmScrollBarsVertical
        
        AddTextBoxes objFrame1
        
        With .Controls(.Controls.Count - 1)
            objFrame1.ScrollHeight = .Top + .Height
        End With
    End With
    
    Set objFrame2 = Controls.Add("Forms.Frame.1")
        
    With objFrame2
        .Width = (InsideWidth - sngFRAME_SPACING * 3) / 2
        .Left = sngFRAME_SPACING * 2 + .Width
        .ScrollBars = fmScrollBarsVertical
        
        AddTextBoxes objFrame2
        
        With .Controls(.Controls.Count - 1)
            objFrame2.ScrollHeight = .Top + .Height
        End With
    End With

End Sub

Private Sub AddTextBoxes(ByRef objFrame As MSForms.Frame)
    
    Const sngTEXTBOX_SPACING As Single = 10
    
    Dim objTextBox As MSForms.TextBox
    Dim t As Long

    For t = 0 To 20
    
        Set objTextBox = objFrame.Controls.Add("Forms.TextBox.1")
        
        With objTextBox
            .Left = sngTEXTBOX_SPACING
            .Width = objFrame.InsideWidth - sngTEXTBOX_SPACING * 2
            .Top = sngTEXTBOX_SPACING + t * (.Height + sngTEXTBOX_SPACING)
            .Text = CStr(t)
        End With
    
    Next t

End Sub

AnnoyingScrollbar

I couldn’t have this annoying behaviour happening with my grid control so it was something that had to be dealt with. I couldn’t find any documentation on how to stop it but, since one can intuitively expect a Frame.Scroll() event to be raised when the scrollbars are reset, the first thing I did was refactor the code so it subscribed to that event:

Option Explicit

Private WithEvents mobjFrame1 As MSForms.Frame
Private WithEvents mobjFrame2 As MSForms.Frame

Private Sub mobjFrame1_Scroll( _
    ByVal ActionX As MSForms.fmScrollAction, _
    ByVal ActionY As MSForms.fmScrollAction, _
    ByVal RequestDx As Single, _
    ByVal RequestDy As Single, _
    ByVal ActualDx As MSForms.ReturnSingle, _
    ByVal ActualDy As MSForms.ReturnSingle)

End Sub

Private Sub mobjFrame2_Scroll( _
    ByVal ActionX As MSForms.fmScrollAction, _
    ByVal ActionY As MSForms.fmScrollAction, _
    ByVal RequestDx As Single, _
    ByVal RequestDy As Single, _
    ByVal ActualDx As MSForms.ReturnSingle, _
    ByVal ActualDy As MSForms.ReturnSingle)

End Sub

Private Sub UserForm_Initialize()
    
    Const sngFRAME_SPACING As Single = 5
    
    Width = 400
    Height = 200
    
    Set mobjFrame1 = Controls.Add("Forms.Frame.1")
        
    With mobjFrame1
        .Width = (InsideWidth - sngFRAME_SPACING * 3) / 2
        .Left = sngFRAME_SPACING
        .ScrollBars = fmScrollBarsVertical
        
        AddTextBoxes mobjFrame1
        
        With .Controls(.Controls.Count - 1)
            mobjFrame1.ScrollHeight = .Top + .Height
        End With
    End With
    
    Set mobjFrame2 = Controls.Add("Forms.Frame.1")
        
    With mobjFrame2
        .Width = (InsideWidth - sngFRAME_SPACING * 3) / 2
        .Left = sngFRAME_SPACING * 2 + .Width
        .ScrollBars = fmScrollBarsVertical
        
        AddTextBoxes mobjFrame2
        
        With .Controls(.Controls.Count - 1)
            mobjFrame2.ScrollHeight = .Top + .Height
        End With
    End With

End Sub

Private Sub AddTextBoxes(ByRef objFrame As MSForms.Frame)
    
    Const sngTEXTBOX_SPACING As Single = 10
    
    Dim objTextBox As MSForms.TextBox
    Dim t As Long

    For t = 0 To 20
    
        Set objTextBox = objFrame.Controls.Add("Forms.TextBox.1")
        
        With objTextBox
            .Left = sngTEXTBOX_SPACING
            .Width = objFrame.InsideWidth - sngTEXTBOX_SPACING * 2
            .Top = sngTEXTBOX_SPACING + t * (.Height + sngTEXTBOX_SPACING)
            .Text = CStr(t)
        End With
    
    Next t

End Sub

In this case I wanted to override the behaviour of the vertical scrollbar so I was interested in the Scroll() event’s "Y" parameters. As it turns out, the ActionY parameter will have a value of fmScrollActionFocusRequest when the Frame is given the focus, so it let me easily pick out the “auto reset” I wanted to block. The ActualDy.Value is how much the vertical scrollbar is going to be scrolled. ActualDy is passed ByVal but, because it is an object, I could just set its Value to 0 and that revised value would be recognised from where the event was raised from. If you run the below code you’ll find that the scrollbars don’t reset when each frame gets the focus:

Option Explicit

Private WithEvents mobjFrame1 As MSForms.Frame
Private WithEvents mobjFrame2 As MSForms.Frame

Private Sub mobjFrame1_Scroll( _
    ByVal ActionX As MSForms.fmScrollAction, _
    ByVal ActionY As MSForms.fmScrollAction, _
    ByVal RequestDx As Single, _
    ByVal RequestDy As Single, _
    ByVal ActualDx As MSForms.ReturnSingle, _
    ByVal ActualDy As MSForms.ReturnSingle)

    If ActionY = fmScrollActionFocusRequest Then
        ActualDy.Value = 0
    End If
    
End Sub

Private Sub mobjFrame2_Scroll( _
    ByVal ActionX As MSForms.fmScrollAction, _
    ByVal ActionY As MSForms.fmScrollAction, _
    ByVal RequestDx As Single, _
    ByVal RequestDy As Single, _
    ByVal ActualDx As MSForms.ReturnSingle, _
    ByVal ActualDy As MSForms.ReturnSingle)

    If ActionY = fmScrollActionFocusRequest Then
        ActualDy.Value = 0
    End If

End Sub

Private Sub UserForm_Initialize()
    
    Const sngFRAME_SPACING As Single = 5
    
    Width = 400
    Height = 200
    
    Set mobjFrame1 = Controls.Add("Forms.Frame.1")
        
    With mobjFrame1
        .Width = (InsideWidth - sngFRAME_SPACING * 3) / 2
        .Left = sngFRAME_SPACING
        .ScrollBars = fmScrollBarsVertical
        
        AddTextBoxes mobjFrame1
        
        With .Controls(.Controls.Count - 1)
            mobjFrame1.ScrollHeight = .Top + .Height
        End With
    End With
    
    Set mobjFrame2 = Controls.Add("Forms.Frame.1")
        
    With mobjFrame2
        .Width = (InsideWidth - sngFRAME_SPACING * 3) / 2
        .Left = sngFRAME_SPACING * 2 + .Width
        .ScrollBars = fmScrollBarsVertical
        
        AddTextBoxes mobjFrame2
        
        With .Controls(.Controls.Count - 1)
            mobjFrame2.ScrollHeight = .Top + .Height
        End With
    End With

End Sub

Private Sub AddTextBoxes(ByRef objFrame As MSForms.Frame)
    
    Const sngTEXTBOX_SPACING As Single = 10
    
    Dim objTextBox As MSForms.TextBox
    Dim t As Long

    For t = 0 To 20
    
        Set objTextBox = objFrame.Controls.Add("Forms.TextBox.1")
        
        With objTextBox
            .Left = sngTEXTBOX_SPACING
            .Width = objFrame.InsideWidth - sngTEXTBOX_SPACING * 2
            .Top = sngTEXTBOX_SPACING + t * (.Height + sngTEXTBOX_SPACING)
            .Text = CStr(t)
        End With
    
    Next t

End Sub

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

Deceptive Appearances: MSForms Textboxes And Comboboxes

The Microsoft Forms 2.0 Object Library (FM20.dll) really lacks a number of controls. One which I seem to miss the most often is a “grid” control which will let users select rows and edit data in multiple columns. For this sort of functionality I normally use a ListView control which comes as part of the Windows Common Controls (MSCOMCTL.OCX). It takes some VBA plumbing to get it to work but does the job in a clunky sort of way.

The problem with the Windows Common Controls OLE Control Extension is that it’s only available as a 32-bit binary. You quite simply cannot use it in 64-bit Excel – and 64-bit Excel is becoming an ever-increasing consideration for my clients. If you search around sites such as TechNet you’ll find that Microsoft helpfully suggests that, for 64-bit Office, you either obtain 64-bit compatible versions of ActiveX controls or create your own. Since Microsoft does not provide a 64-bit version of its ListView, I decided I would make my own grid control – not from scratch, but rather using TextBoxes and Labels in a Frame to create a grid effect. There’s nothing super clever here: code to manage groups of dynamic controls, wrapped up in an add-in with some simple methods and properties to make it easy to use.

Once I’d finished the first version and started to use it, I decided it would be really nice if I could put “data validation” in the grid – which would probably mean putting in ComboBoxes instead of TextBoxes for grid items which need a dropdown. Having a mixture of both would be a slight pain (but not impossible) to implement because my Collections were strongly typed, so I had a hunt around the Object Browser and I noticed that the MSForms.TextBox class exposes both a DropButtonClick() event and a hidden DropButtonStyle property:

MSFormsTextbox

Furthermore, to show or hide the dropdown button it has a hidden ShowDropButtonWhen property:

MSFormsTextbox2

If I could give some of the TextBoxes in my grid the appearance of a ComboBox then that would be pretty handy because it would be a very simple change to my add-in.

The DropButtonStyle and ShowDropButtonWhen properties can’t be configured in the Properties Window so you have to set them at runtime. Not a problem for me because my TextBoxes are all created at runtime anyway. When the DropButton is clicked and the DropButtonClick() event is raised you don’t get a nice dropdown pane supplied for you, so you have to create your own. That isn’t hard to do. For a simple example, create a UserForm and add a TextBox called TextBox1 to it. Then add the following code to the userform’s module and run it.

Option Explicit

Private WithEvents mobjListBox As MSForms.ListBox


Private Sub UserForm_Initialize()

    TextBox1.ShowDropButtonWhen = fmShowDropButtonWhenAlways

End Sub


Private Sub mobjListBox_Change()
    
    TextBox1.Text = mobjListBox.List(mobjListBox.ListIndex)
    mobjListBox.Visible = False
    
End Sub

Private Sub TextBox1_DropButtonClick()
    
    If mobjListBox Is Nothing Then
    
        Set mobjListBox = Controls.Add("Forms.ListBox.1")
        
        With mobjListBox
            .Top = TextBox1.Top + TextBox1.Height
            .Left = TextBox1.Left
            .Width = TextBox1.Width
            .SpecialEffect = fmSpecialEffectFlat
            .BorderStyle = fmBorderStyleSingle
            
            With .Font
                .Size = TextBox1.Font.Size
                .Name = TextBox1.Font.Name
            End With
            
            .AddItem "a"
            .AddItem "b"
            .AddItem "c"
            .AddItem "d"
            .AddItem "e"
            .AddItem "f"
            .AddItem "g"
            
        End With
    
    Else
        mobjListBox.Visible = Not mobjListBox.Visible
    End If
    
End Sub

TextboxAndListbox

Of course one could take the implementation further, but hopefully that’s enough to give you the idea. As it turns out, I didn’t take it any further because I then realised it would be even easier to use ComboBoxes for all my grid items and give them the appearance of TextBoxes when “data validation” isn’t needed. All that needs is one line of code:

    ComboBox1.ShowDropButtonWhen = fmShowDropButtonWhenNever

Sure, the ComboBox doesn’t have as many text formatting properties as the TextBox, but I’ll worry about that if I ever need them. For now, I’d much rather keep things simple.

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

Error Accessing The System Registry

As the resident Excel expert at work, I have the privilege of being landed with some weird and wonderful errors which have to be fixed. Last week I developed an Excel spreadsheet for a user but when he went to test it he said he was getting an error when he tried to check the project references. Specifically when he went to Tools > References in the VBA IDE he got the message ‘Error accessing the system registry’:

ErrorAccessingTheSystemRegistry

This error had nothing to do with the spreadsheet I’d built for him: obviously he hadn’t tried to reach the project references menu for a little while. When you try to pull up the project references, Excel goes off and reads a bunch of keys from the registry and this error occurs when the you don’t have permission to read one or more of those keys. But how do you figure out which keys are causing the problem?

The answer is to run Process Monitor (which must be run under an admin account) because it will show you every key which Excel tries to read and whether it did so successfully. ProcMon has saved my bacon several times over the years so it’s worth a look if you haven’t come across it before.

It’s extremely easy to use: simply add a Process Name filter for EXCEL.EXE, clear the display and then, with Excel running under the user’s account, go to Tools > References and watch ProcMon burst into a flurry of activity. Once it’s calmed down you can stop capturing events and examine the output. You’re interested in any results which are ACCESS DENIED so add another filter on the Result column. In this case I have one key, HKCR\TypeLib\{57A0E746-3863-4D20-A811-950C84F1DB9B},  which is causing the problem:

procmon

A quick Google search on that key reveals it pertains to a Flash Player update which has affected a number of people. The VBA IDE error message will go away once the admin uses the Registry Editor to grant the user read access to the access denied keys listed by Process Monitor.

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

Naughty Sorts

Continuing the theme of naughty code which can cause Excel XML corruption, let’s have a look at some code which sorts some data in a range but then causes XML errors when the file is saved and re-opened. Here’s my sheet:

BeforeSort

And here is some VBA code generated by the macro recorder which sorts it descending by ColA:

Sub Macro1()
    '
    ' Macro1 Macro
    '
    '

    Range("A1:C6").Select

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add _
        Key:=Range("A2:A6"), _
        SortOn:=xlSortOnValues, _
        Order:=xlDescending, _
        DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:C6")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

It runs, it works and I’m happy:

AfterSort1

Next I decide to make a couple of small modifications because I want to sort by three columns instead of one. Being a copy and paste cowboy, I copy the code which adds the SortField and update it so it applies to the 2nd and 3rd columns.

Sub Macro1()
    '
    ' Macro1 Macro
    '
    '

    Range("A1:C6").Select

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add _
        Key:=Range("A2:A6"), _
        SortOn:=xlSortOnValues, _
        Order:=xlDescending, _
        DataOption:=xlSortNormal

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add _
        Key:=Range("B2:B6"), _
        SortOn:=xlSortOnValues, _
        Order:=xlDescending, _
        DataOption:=xlSortNormal

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add _
        Key:=Range("B2:B6"), _
        SortOn:=xlSortOnValues, _
        Order:=xlDescending, _
        DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:C6")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

It also runs without an error so I happily save and close the file and go and make myself a nice cup to tea:

AfterSort2

When I return to my desk and open the workbook what do I get? A faceful of error messages. Thanks Excel.

Error1

Error2

Error3

So what was my mistake here? Well, actually there were two.

Firstly, the bit of code which caused the XML error is the duplicate SortField with Key range B2:B6. I didn’t edit it properly when I copied and pasted it.

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add _
        Key:=Range("B2:B6"), _
        SortOn:=xlSortOnValues, _
        Order:=xlDescending, _
        DataOption:=xlSortNormal

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add _
        Key:=Range("B2:B6"), _
        SortOn:=xlSortOnValues, _
        Order:=xlDescending, _
        DataOption:=xlSortNormal

Secondly, I didn’t test it properly: I should’ve tried it out on sample data where the third SortField would’ve come into visual effect.

Those whoopsies aside, the macro recorder itself tends to generate dodgy code. In this case it didn’t properly qualify the SortField key ranges and the Sort range. Here’s the corrected version:

Sub Macro1()

    Dim wksTarget As Worksheet

    Set wksTarget = Worksheets("Sheet1")

    With wksTarget.Sort

        With .SortFields

            .Clear

            .Add _
                Key:=wksTarget.Range("A2:A6"), _
                SortOn:=xlSortOnValues, _
                Order:=xlDescending, _
                DataOption:=xlSortNormal

            .Add _
                Key:=wksTarget.Range("B2:B6"), _
                SortOn:=xlSortOnValues, _
                Order:=xlDescending, _
                DataOption:=xlSortNormal

            .Add _
                Key:=wksTarget.Range("C2:C6"), _
                SortOn:=xlSortOnValues, _
                Order:=xlDescending, _
                DataOption:=xlSortNormal

        End With

        .SetRange wksTarget.Range("A1:C6")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub
Posted in Microsoft Excel | Tagged , , , | Leave a comment