Who Has My Workbook Open? [VBA]

A while back I posted some C# code which would find out if someone is locking an Excel workbook and identify who they are. At the time I didn’t post a VBA equivalent because I had linked to a VBA solution on StackOverflow. Well, I recently needed to do it in VBA and I found that the answer on SO didn’t work on directories on unmapped drives. Not wanting to let you down, here’s a working solution which has been adapted from a Microsoft Support article:

Option Explicit

'adapted from https://support.microsoft.com/en-us/kb/218965
'requires a reference to Microsoft Scripting Runtime (scrrun.dll)

Private Const mlngERROR_INSUFFICIENT_BUFFER As Long = 122&

#If Win64 Then
    'https://msdn.microsoft.com/en-us/library/windows/desktop/aa446639(v=vs.85).aspx
    Private Declare PtrSafe Function GetFileSecurity Lib "advapi32.dll" Alias "GetFileSecurityA" ( _
       ByVal lpFileName As String, _
       ByVal RequestedInformation As Long, _
       ByRef pSecurityDescriptor As Byte, _
       ByVal nLength As Long, _
       ByRef lpnLengthNeeded As Long _
       ) As Long

    'https://msdn.microsoft.com/en-us/library/windows/desktop/aa446651(v=vs.85).aspx
    Private Declare PtrSafe Function GetSecurityDescriptorOwner Lib "advapi32.dll" ( _
        ByRef pSecurityDescriptor As Any, _
        ByRef pOwner As LongPtr, _
        ByRef lpbOwnerDefaulted As LongPtr) As Long

    'https://msdn.microsoft.com/en-gb/library/windows/desktop/aa379166(v=vs.85).aspx
    Private Declare PtrSafe Function LookupAccountSid Lib "advapi32.dll" Alias "LookupAccountSidA" ( _
       ByVal lpSystemName As String, _
       ByVal Sid As LongPtr, _
       ByVal name As String, _
       ByRef cbName As Long, _
       ByVal ReferencedDomainName As String, _
       ByRef cbReferencedDomainName As Long, _
       ByRef peUse As LongPtr) As Long

#Else
    Private Declare Function GetFileSecurity Lib "advapi32.dll" Alias "GetFileSecurityA" ( _
       ByVal lpFileName As String, _
       ByVal RequestedInformation As Long, _
       ByRef pSecurityDescriptor As Byte, _
       ByVal nLength As Long, _
       ByRef lpnLengthNeeded As Long _
       ) As Long

    Private Declare Function GetSecurityDescriptorOwner Lib "advapi32.dll" ( _
        ByRef pSecurityDescriptor As Any, _
        ByRef pOwner As Long, _
        ByRef lpbOwnerDefaulted As Long) As Long

    Private Declare Function LookupAccountSid Lib "advapi32.dll" Alias "LookupAccountSidA" ( _
       ByVal lpSystemName As String, _
       ByVal Sid As Long, _
       ByVal name As String, _
       ByRef cbName As Long, _
       ByVal ReferencedDomainName As String, _
       ByRef cbReferencedDomainName As Long, _
       ByRef peUse As Long) As Long

#End If

Public Function GetWorkbookWriteOwner(ByRef strWorkbookFullName As String) As String
    
    'requires reference to Microsoft Scripting Runtime
    Dim fso As Scripting.FileSystemObject
    Dim strFileName As String
    Dim strFolderPath As String
    Dim strTempFilePath As String

    Set fso = CreateObject("Scripting.FileSystemObject")

    'check if a system temp file with a ~$ prefix exists in the same folder
    strFileName = fso.GetFileName(strWorkbookFullName)
    strFolderPath = fso.GetParentFolderName(strWorkbookFullName)
    strTempFilePath = fso.BuildPath(strFolderPath, "~$" & strFileName)

    'if it exists then the file is locked with write access
    'we can retrieve the creator of the temp file to determine who has
    'the write access
    If fso.FileExists(strTempFilePath) Then
        GetWorkbookWriteOwner = GetFileOwner(strTempFilePath)
    Else
        GetWorkbookWriteOwner = vbNullString
    End If

End Function

Private Function GetFileOwner(ByRef strFullFileName As String) As String

    Dim lngResult As Long
    Dim bytarrSecDesc() As Byte  'Buffer for Security Descriptor

    #If Win64 Then
        Dim lngOwnerSid As LongPtr  'Pointer to the owner's security identifier (SID)
    #Else
        Dim lngOwnerSid As Long
    #End If

    Dim strDomainName As String
    Dim strOwnerName As String

    'get the file owner's security information
    lngResult = GetFileOwnerSecurityInfo(strFullFileName, bytarrSecDesc)

    '0 indicates failure
    If lngResult = 0 Then
        MsgBox CStr(Err.LastDllError)

    Else
        ' get the owner's SID
        lngResult = GetSecurityDescriptorOwner(bytarrSecDesc(0), lngOwnerSid, 0&)

        '0 indicates failure
        If lngResult = 0 Then
            MsgBox CStr(Err.LastDllError)
        Else

            'get the owner's 1st domain and account name
            lngResult = GetAccountNameFromSID(lngOwnerSid, strDomainName, strOwnerName)

            '0 indicates failure
            If lngResult = 0 Then
                MsgBox CStr(Err.LastDllError)
            Else
                If LenB(strOwnerName) = 0 Then
                    GetFileOwner = "unknown"
                Else
                    GetFileOwner = strDomainName & "\" & strOwnerName
                End If
            End If

        End If
    End If

End Function

Private Function GetFileOwnerSecurityInfo( _
    ByRef strFullFileName As String, _
    ByRef bytarrSecDesc() As Byte) As Long

    'SECURITY_INFORMATION:
    'https://msdn.microsoft.com/en-us/library/windows/desktop/aa379573(v=vs.85).aspx
    Const lngOWNER_SECURITY_INFORMATION As Long = &H1

    Dim lngResult As Long           ' API call result
    Dim lngSizeSID As Long          ' Buffer size to store Owner's SID

    ' get the size of the
    ' buffer required for the Security Descriptor.
    lngResult = GetFileSecurity( _
            strFullFileName, _
            lngOWNER_SECURITY_INFORMATION, _
            0, _
            0&, _
            lngSizeSID)

    '0 indicates failure
    If lngResult = 0 And Err.LastDllError <> mlngERROR_INSUFFICIENT_BUFFER Then
          MsgBox CStr(Err.LastDllError)
    Else

        ' Create a buffer of the required size and call again
        ReDim bytarrSecDesc(0 To lngSizeSID - 1) As Byte

        ' The calling process must have READ_CONTROL rights to retrieve the owner
        GetFileOwnerSecurityInfo = GetFileSecurity( _
                strFullFileName, _
                lngOWNER_SECURITY_INFORMATION, _
                bytarrSecDesc(0), _
                lngSizeSID, _
                lngSizeSID)
    End If

End Function

#If Win64 Then
    Private Function GetAccountNameFromSID(ByVal lngOwner As LongPtr, ByRef strDomainName As String, ByRef strOwnerName As String) As Long
#Else
    Private Function GetAccountNameFromSID(ByVal lngOwner As Long, ByRef strDomainName As String, ByRef strOwnerName As String) As Long
#End If

    Dim lngResult As Long           ' API call result
    Dim lngDomainLength As Long     ' Required length for the domain name
    Dim lngOwnerLength As Long      ' Required length for the owner name

    ' Pointer to a SID_NAME_USE value which indicates the type of account
    'https://msdn.microsoft.com/en-gb/library/windows/desktop/aa379601(v=vs.85).aspx
    #If Win64 Then
        Dim lngUse As LongPtr
    #Else
         Dim lngUse As Long
    #End If

    ' Call LookupAccountSid twice:
    ' the first time
    ' to obtain the required size of the owner and domain names.
    lngResult = LookupAccountSid( _
                    vbNullString, _
                    lngOwner, _
                    strOwnerName, _
                    lngOwnerLength, _
                    strDomainName, _
                    lngDomainLength, _
                    lngUse)

    '0 indicates failure
    If lngResult = 0 And Err.LastDllError <> mlngERROR_INSUFFICIENT_BUFFER Then
        MsgBox CStr(Err.LastDllError)
    Else

        'both the account and domain are null terminated strings
        'so allocate 1 byte less to avoid the appended null character.
        strOwnerName = Space$(lngOwnerLength - 1)
        strDomainName = Space$(lngDomainLength - 1)

        GetAccountNameFromSID = LookupAccountSid( _
                        vbNullString, _
                        lngOwner, _
                        strOwnerName, _
                        lngOwnerLength, _
                        strDomainName, _
                        lngDomainLength, _
                        lngUse)
    End If

End Function



To use it just call the GetWorkbookWriteOwner() procedure and pass in the full name of the workbook:

immediate

Posted in Microsoft Excel | 1 Comment

Excel Development Roadmap – Where Next?

The developer tool suite offered by Microsoft for Excel is currently a set of three complimentary technologies:

  • Visual Studio Tools for Office (Dot Net)
  • Apps for Office (Javascript)
  • Visual Basic For Applications

The VBA language is an interesting one. Often referred to as the “jewel in the crown” of the Office suite, there is no doubt that VBA is largely behind the huge success of desktop Office. Yet Microsoft seems to consider it to be an unwanted child and has left it to stagnate for 15+ years – except for the 64-bit support in VBA7 which was grudgingly conceded for Office 2010.

Whilst Microsoft has made it clear that it will continue to support VBA for now, it has been incredibly indecisive on how to replace it, leaving VBA developers such as myself in a perpetual state of uncertainty as we live out the long tail. I honestly have no idea when a decision will finally be made but, for now, Microsoft seems to be concentrating its efforts on building out the BI capabilities of Excel and support for various platform endpoints. Excel is here to stay and has even been recently praised by Microsoft’s CEO, but if you ask any developer they’ll tell you there isn’t a future in VBA development.

The recent volatility (read: Brexit) has caused me to think very carefully about how to secure my professional future. Reskilling is something I’ve delayed for far too long: it took me ages to get good with VBA and I know that it will take me equally long – or likely longer – to get to the same standard in another technology. I’ve dabbled here and there, but I haven’t really committed. The time for dallying is over.

The question then becomes: which technology should I turn to? As far as I’m concerned VSTO has long been a dead turkey and, whilst Apps for Office is potentially worth learning, I don’t see any demand for it out in the market. Perhaps that’s because it is too new – or perhaps it is another turkey. The information available to me suggests that I need to look beyond Microsoft’s Excel development tool suite and possibly even beyond the wonderful world of Excel development itself.

I have to be realistic. I can’t just declare myself to be a Haskell developer and expect the job offers to come flooding in. A more pragmatic approach is to find a hybrid role – one where the key skill is Excel/VBA so I can deliver solid results – but where there is also an opportunity to use other technologies. There’s nothing like learning a new skill on-the-job.

In London’s finance sector where I work, Excel developer job requirements have a common theme:

  • Outstanding Excel and Excel VBA knowledge
  • Good business knowledge and experience. [RAD developer roles are business facing, often on trading floors.]
  • C#.Net or Python or Java (or C++ for the more quantitative positions).
  • Good database skills: typically SQL Server or Oracle. MS Access is often useful

The number of Excel-focused developer jobs here is decreasing, but I believe there is still enough time left in the tail to pick up one of the “alternative” skills highlighted above in bold and progress it on-the-job. After a couple of years that skill should be decent enough to apply for developer roles which have nothing to do with Excel – if one needs to.

Database skills are essential but, since I already have decent SQL Server, my focus is drawn towards learning either C#.Net, Python or Java. Python and Java are both excellent options, but I seem to notice more Excel developer roles advertised with C# attached to them. What is interesting about the Excel/C# roles is there’s usually very little mention of VSTO: the experience the recruiters are looking for tends to be on third party products such as Excel-DNA, Add-In Express and Spreadsheet Gear.

Other than immediate job opportunities, C# appeals to me for a number of different reasons:

Firstly, I already have some experience with C#. Don’t get me wrong – I’m a beginner to intermediate level at best – but at least I’m already a little way along the learning curve. C#.Net is a Microsoft technology so perhaps it will feel more familiar to me than Python or Java, thereby making it easier to pick up.

Secondly, and very significantly, the contract I currently have does have some scope for C# projects which I can leverage. I’m happy where I am and it can help get me where I need to get to.

Thirdly, it’s a great, central language to have under one’s belt. Once you’re good at C#, the Java language syntax is very similar and shouldn’t be too hard to pick up (or so I’m told). Knowledge of the .Net framework is transferable to other .Net languages such as VB.Net. The OOP principles and concepts are shared by many other languages. It can target many different endpoints. It certainly seems to open up a lot of doors.

Fourthly, I live with the ever-diminishing hope that Microsoft will decide to replace VBA with .Net. I’d love to be able to jump on that bandwagon. And, if by some miracle, VSTO is revitalised, then I would be in a strong position to leverage that too.

Fifthly, the C# language is still evolving. It’s an exciting and dynamic space to be involved in. No tails in sight here.

And finally, getting started with C# is free.

  • Microsoft’s Visual Studio Community is a very rich IDE and is free to download from MSDN for individual developers. There are other free IDEs available out there – such as SharpDevelop – but I’ve never used them.
  • Channel9 on MSDN offers hundreds of hours of free C# training videos.
  • There are plenty of free e-books and online forums.
  • Oh, did I mention, it’s free?

So that’s my current line of thinking. How about you? Are you an Excel developer? Where do you see yourself in 2-5 years’ time?

 

Posted in .Net, Microsoft Excel, Visual C# | Tagged , , , | 6 Comments

UK Has Its Pie And Eats It

Here are the Brexit vote results displayed using the doughnut chart style which seems to have been popular on some of the media websites.

brexit vote doughnut

And here are exactly the same numbers displayed using our old favourite: the 3D pie chart.

Brexit 3D Pie

Posted in Microsoft Excel | 5 Comments

Getting A Handle On Userforms [VBA]

In a previous post I wrote about how to add minimize and maximize buttons to a VBA userform. A necessary part of that process is to get the handle to the userform’s window, but the focus of that post was how to correctly declare the GetWindowLong() and SetWindowLong() API calls across 32-bit and 64-bit environments. Today I’m going to look at getting the hWnd of a userform in more detail.

A handle to a window is also called an hWnd. We quite often need the userform’s hWnd so we can use Windows API functions to give the userform features which aren’t available out-of-the-box – such as the minimize and maximize buttons illustrated on that other post.

If you use a tool such as Spy++ you’ll find that a bare userform is actually comprised of two windows:

UserformWindowsSpy

The red, “wrapper” window has a ThunderDFrame class (ThunderXFrame pre Excel 2000). When we talk about getting a userform’s hWnd, this is the window handle we’re looking for.

Option 1 – Get The hWnd From Within A Userform Class Module

The way I get a userform’s hWnd from within its own class module is:

  • Retrieve it in the userform’s Initialize() event handler using the FindWindowA() function
  • Store it in a private, module level variable
  • Expose it as a read-only property should external code need to use it

Userform Class Module called Userform1

Option Explicit

#If Win64 Then

    Private Declare PtrSafe Function FindWindowA _
        Lib "user32.dll" ( _
        ByVal lpClassName As String, _
        ByVal lpWindowName As String) As LongPtr

    Private mlnghWnd As LongPtr

    Public Property Get hWnd() As LongPtr
        hWnd = mlnghWnd
    End Property

#Else

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

    Private mlnghWnd As Long

    Public Property Get hWnd() As Long
        hWnd = mlnghWnd
    End Property

#End If

Private Sub UserForm_Initialize()

    StorehWnd

End Sub

Private Sub StorehWnd()

    Dim strCaption As String
    Dim strClass As String

    'class name changed in Office 2000
    If Val(Application.Version)>=9 Then
        strClass = "ThunderDFrame"
    Else
        strClass = "ThunderXFrame"
    End If

    'remember the caption so we can 
    'restore it when we're done
    strCaption = Me.Caption

    'give the userform a random
    'unique caption so we can reliably
    'get a handle to its window
    Randomize
    Me.Caption = CStr(Rnd)

    'store the handle so we can use 
    'it for the userform's lifetime
    mlnghWnd = FindWindowA(strClass , Me.Caption)

    'set the caption back again
    Me.Caption = strCaption

End Sub

Standard Code Module

Option Explicit

Sub Example()

    Dim ufmExample As Userform1

    Set ufmExample = New Userform1

    Debug.Print ufmExample.hWnd

End Sub

The code gives the userform a temporary, random (and hopefully unique) caption to ensure that the FindWindowA() function gets the correct window. You could go to town with ways of guaranteeing it is unique but, for me, just using the Rnd() function is more than sufficient.

A nice feature about storing it in a module level variable is that you don’t need to worry about state loss clearing the variable: if a state loss event occurs then the entire userform object will be destroyed anyway. The hWnd property naturally lends itself to being read-only because it is set internally and will never change.

Option 2 – Get The hWnd From Within A Standard Code Module

The above code will work perfectly fine and you can implement it in every userform that needs it. However, it would also be nice to have a generic function in a standard code module that accepts a reference to a userform instance and returns back the hWnd.

Getting the hWnd in a generic function is a much trickier business. If you search the internet you’ll find some pretty complicated code which tries to do it, but I haven’t seen anything out there which is truly robust. I do, however, have a hacky approach which is relatively simple and will work:

Option Explicit

#If Win64 Then
    Private Declare PtrSafe Function GetParent _
        Lib "user32.dll" (ByVal hwnd As LongPtr) As LongPtr
#Else
    Private Declare Function GetParent _
        Lib "user32.dll" (ByVal hwnd As Long) As Long
#End If

#If Win64 Then
    Public Function GetUserformHwnd( _
        ByRef ufmTarget As MSForms.UserForm) As LongPtr
    
    Dim lngHwndControl As LongPtr
        
#Else
    Public Function GetUserformHwnd( _
        ByRef ufmTarget As MSForms.UserForm) As Long
    
    Dim lngHwndControl As Long
    
#End If

    Dim objControl As MSForms.Control
    Dim strRandomName As String
    
    Randomize
    strRandomName = CStr(Rnd)
    
    Set objControl = ufmTarget.Controls.Add( _
        "Forms.Frame.1", strRandomName, False)
    
    objControl.Left = ufmTarget.ScrollLeft + ufmTarget.InsideWidth
    objControl.Visible = True
    lngHwndControl = objControl.[_GethWnd]
    
    GetUserformHwnd = GetParent(GetParent(lngHwndControl))
    ufmTarget.Controls.Remove strRandomName
    
End Function

The code looks more complicated than it really is due to the conditional compilation sections. The function adds an MSForms.Frame control to the userform. MSForms.Frame controls have an hWnd (it’s important to note that most MSForms controls do not have their own window), so I can reference it using a general MSFroms.Control variable type and retrieve its handle using the hidden MSForms.Control.GethWnd() method (saves an extra API call):

ControlGetHwnd

I then use the GetParent() API call twice to hop up to the userform’s wrapper window:

UserformWindowsFrameSpy

For all of this to work the frame control must be visible, so I slip it off to the right of the visible area of the userform before making it visible to avoid it flashing up in front of the user. Finally I clean up and delete the temporary frame control by removing it from the userform’s Controls collection.

Since "Userform1" is a TypeOf MSForms.Userform, I can call the function from within a userform class module or from a standard code module – for example, looping through the VBA.Userforms collection:

Option Explicit

'example 1 - call from a userform class module
'using the Me keyword
Private Sub UserForm_Initialize()
    
    Debug.Print GetUserformHwnd(Me)
    
End Sub
Option Explicit

'example 2 - code in a standard code module
Sub test()
    
    Dim ufm1 As UserForm1
    Dim ufm2 As UserForm1
    Dim lngUserform As Long
    
    Set ufm1 = New UserForm1
    Set ufm2 = New UserForm1
    
    For lngUserform = 0 To UserForms.Count - 1
        Debug.Print GetUserformHwnd(UserForms(lngUserform))
    Next lngUserform
    
End Sub
Posted in Microsoft Excel | Tagged , , , , | Leave a comment

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