Workbook_Open vs. Auto_Open

I’ve seen it written that Auto_Open() was superseded by Workbook_Open() and that it is therefore redundant and only still available for backward compatibility. In fact, there are subtle differences between the two which means that, in my opinion, Auto_Open() remains a distinct and useful tool in the Excel developer toolkit. Here are the differences I know about:

The Code Container

While this difference isn’t significant, it does catch out beginners.

Auto_Open() must sit in a standard code module whereas Workbook_Open() must be housed in the ThisWorkbook class module. If either one is in the wrong place then it won’t run.

Since Auto_Open() lives in a standard code module, it is possible to generate it using the macro recorder. By contrast, Workbook_Open() cannot be generated by the macro recorder in situ although you could generate it in a standard code module and copy it across [see Patrick’s comment]. For me, the best way to create the Workbook_Open() procedure stub is to use the two dropdown boxes at the top of the code pane when the ThisWorkbook code module is active and then to manually code the contents.

• Contrary to popular belief, Auto_Open() can be declared with a Private scope: it does not have to be Public. Setting the scope to Private will hide it from the Assign Macro dialog box’s available macro list and make it inaccessible to other code modules.

Application.EnableEvents

This is a really important difference. If Application.EnableEvents is set to False then Workbook_Open() does not get triggered. There’s a lot of sloppy VBA code around which sets EnableEvents to False but fails to set it back to True again, which means your Workbook_Open() code is vulnerable to other people’s mistakes.

Auto_Open() is unaffected by Application.EnableEvents so some developers use Auto_Open() if they want to ensure that a certain piece of code is executed when a user opens the workbook regardless of the Application.EnableEvents state. This is, of course, subject to user macro security settings.

• Holding down the SHIFT key when opening a workbook will disable both Workbook_Open() and Auto_Open().

End User Or Automation?

Another really important difference. If a workbook is opened programmatically then Auto_Open() does not get triggered whereas Workbook_Open() does. This means that Auto_Open() is an easy way to segregate code which should only be executed for end-users and not during automation.

• If you want to disable Workbook_Open() during automation then set Application.EnableEvents to False before opening the workbook.
• If you want to run Auto_Open() (and other auto methods) during automation then use the Workbook.RunAutoMacros() method.

Call Order

If your workbook has both Workbook_Open() and Auto_Open() procedures then it’s important to know that it is the Workbook_Open() method that gets called first.

• Workbook_Open() and Auto_Open() are both called before the customUI OnLoad() callback.

My First Custom Ribbon Using Excel-DNA And Visual Studio

For the next instalment in this “getting started with Excel-DNA for VBA’ers ” mini-series I’m going to have a look at customisation of the Office ribbon. The end goal of this post is very simple: a custom tab with a button which shows a message box.

First let’s set up a new Excel-DNA project.

In Visual Studio press CTRL+SHIFT+N, choose a Visual C# Class Library project and give it the name/solution name MyFirstRibbon, like so:

Click on OK and then type the following command into the package manager console and press ENTER:

Install-Package Excel-DNA

If the package manager console window isn’t already showing in Visual Studio then you can get to it via the View > Other Windows menu.

If you have 64-bit Office installed then you need to go into the project properties and update the debug command line arguments to "MyFirstRibbon-AddIn64.xll" as outlined in the previous post.

The project is ready now so we can start coding by adding the XML which defines the custom tab and its button. Select the MyFirstRibbon-AddIn.dna file in the solution explorer window and insert the following XML code inside the DnaLibrary tags:

<CustomUI>
 <customUI xmlns='http://schemas.microsoft.com/office/2009/07/customui'>
  <ribbon>
   <tabs>
    <tab id='myFirstTab' label='My First Tab' visible='true'>
     <group id='myFirstGroup' label='My First Group' visible='true'>
      <button id='myFirstButton' label='My First Button' size='large' imageMso='HappyFace' onAction='SayHello' visible='true'/>
     </group >
    </tab>
   </tabs>
  </ribbon>
 </customUI>
</CustomUI>

The dna file should now look like this:

If you’ve customised the ribbon in your VBA projects then the above XML should look very familiar to you except for the outer CustomUI tags (with a capital "C") which are idiosyncratic to Excel-DNA.

The dna file is the default place Excel-DNA checks for custom ribbon XML. You can store the custom XML elsewhere but, to do so, you have to override the MyRibbon.GetCustomUI() method. Overriding is a more advanced topic and something to be discussed another day.

Now we need to add the code for the myFirstButton button’s onAction callback. Since we want to show a message box when the button is clicked, we need to add a reference to System.Windows.Forms to our project:

• Project > Add Reference > Assemblies > Framework
• Tick System.Windows.Forms and click OK.

Select the Class1.cs file in the solution explorer and add the following using statements to the top of the module:

using System.Runtime.InteropServices;
using System.Windows.Forms;
using ExcelDna.Integration.CustomUI;

And the following class inside the MyFirstRibbon namespace:

    [ComVisible(true)]
    public class MyRibbon : ExcelRibbon
    {
        public void SayHello(IRibbonControl control1)
        {
            MessageBox.Show("Hello!");
        }
    }

The Class1.cs module should now look like this:

The project is now ready for debugging. When you click on the debug button, Excel should launch and you should see the “My First Tab” on the ribbon. When you click on the My First Button you should get a message box:

I really like this. In particular, having a single IDE for both the XML and the callbacks is something which is severely lacking in the VBA development world, so doing it this way makes quite a refreshing change!

Get Property Syntax Bug [VBA]

I just saw this posted by @RubberduckVBA  on Twitter and couldn’t resist sharing it on here. Did you know that this VBA code will compile and run correctly?

Public Property Get Foo() As String
    Foo = "WTF!"
End Function

And, perhaps even scarier, so will these (and variations thereof):

Public Property Get Foo2() As String
    Foo2 = "WTF!"
End Sub

Public Property Get Foo3() As String
    Foo3 = "WTF!"
    Exit Function
    Exit Sub
End Property

My First C#.Net UDF Using Excel-DNA And Visual Studio

Having recently blogged about the importance of broadening my programming horizon, I felt it was high time I put together a step-by-step guide on how to create an Excel UDF in Visual Studio using Excel-DNA. In this post I’m specifically targeting Excel VBA developers with little or no experience of .Net so following the steps below should be easy and will cost you nothing more than your time.

First of all, you need to download and install Visual Studio. Visual Studio 2015 Community can be downloaded for free from MSDN. During the installation choose Visual C# as the default language when prompted.

Next launch Visual Studio and go to File > New > Project   (or press CTRL+SHIFT+N). Choose a new Visual C# Class Library project and give it the name/solution name MyFirstUDF, like so:

And then click on OK. Visual Studio will think for a few seconds and create a new solution for you.

Next, go to the View menu at the top > Other Windows > Package Manager Console and the Package Manager Console pane should appear:

Next to the PM> prompt in the package manager console, type in Install-Package Excel-DNA and press ENTER.

A bunch of text will appear in the package manager console and hopefully you’ll see lots of successful and complete messages.

You should now also see some extra stuff in the solution explorer window:

The project’s ready now, so we can finally do some coding!

In the solution explorer, select Class1.cs. Visual Studio should open a tab for you which looks just like this:

Don’t be frightened by the curly brackets { }. I know they’re alien to us VBA’ers but all they do is mark the beginning and end of code blocks in C#.

At the top of the module, add this extra using statement:

using ExcelDna.Integration;

C# is case-sensitive so it really needs to be exactly as above and as shown on this screenshot:

If you see any small, squiggly red lines then you’ve done something wrong.

Next, copy the below code and overwrite the Class1 section.

    public static class Class1
    {
        [ExcelFunction(Name = "TOMORROW")]
        public static DateTime Tomorrow()
        {
            return DateTime.Today.AddDays(1);
        }
    }

Your code window should now look exactly like this:

Take particular note that the public static class Class1 line now has the static keyword. That’s really important: without it you won’t see the function in Excel. I don’t want to delve too much into C# specifics but, in this context, just think of a static class in the same way as you do a standard code module in VBA: you don’t have to create an instance of the class before using its members.

The line in square brackets [ ] is an attribute. It tells Excel-DNA that the function underneath, Tomorrow(), needs to be exposed in Excel as worksheet function called TOMORROW. The rest of the code defines a function called Tomorrow() which returns a DateTime object representing tomorrow’s date.

************************************************************

This next step only applies if you have 64-bit Excel installed. If you have 32-bit Excel then you can go straight on to testing the project.

 In the solution explorer, right-click on the MyFirstUDF project and choose Properties.

This will open up a tab showing the project properties.

On the left-hand side select Debug and then, in the Command line arguments box, change "MyFirstUDF-AddIn.xll" to "MyFirstUDF-AddIn64.xll".

This change means that the 64-bit version XLL will be loaded in Excel when you debug the application. If you don’t do this then you will get the below error because 32-bit binaries can’t load in 64-bit Excel:

************************************************************

We’re now ready to test our UDF. In the commandbar section at the top of Visual Studio you should have two dropdown controls next to a green triangle:

Make sure that Debug is selected and then click on the green triangle to run the project. Typically when you debug a C# Class library a dll file is created, but Excel-DNA does some magic and creates an xll file (actually several xll files). At this point Excel should be launched and, depending on your security settings, you may be asked if the MyFirstUDF add-in should be enabled. If you get the prompt then you need to choose Enable.

The TOMORROW() function should be available for use in the Excel session. Navigate to an empty cell and put it in as a formula. Hopefully you should get tomorrow’s date as the return value:

Excel’s TODAY() function is volatile so let’s put the icing on the cake and make our TOMORROW() function volatile too. In Visual Studio click on the red stop icon to stop debugging and the Excel session should close:

Making the function volatile is really easy; we just have to add an IsVolatile property (set to true) to the ExcelFunction attribute:

[ExcelFunction(IsVolatile = true, Name = "TOMORROW")]

It’s really important to emphasise again that C# is case-sensitive: the true must be all lower case (in VBA the "t" would be upper case). Here’s how your code should look:

Test it again using the green start icon (or by pressing F5).

At this point all that’s left for me to write is congratulations: you’ve now entered the magical world of C#, Excel-DNA and XLLs. I know that, in itself, TOMORROW() is a pretty useless UDF – but it’s a great starting point to learning this technology. There may seem to be a lot of steps to get to this point but with a little practice it only takes a minute.

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:

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?

 

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.

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

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:

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):

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

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