Hey there! Let’s tackle your VBA reference questions head-on—this is such a common hurdle when sharing workbooks that rely on controls like the Common Controls 6.0, so it’s smart you’re locking down the best approach.

1. 四种方式哪个适配你的场景？手动加了引用还要编码吗？ #

Let’s break down each option to find the best fit for your drag-and-drop control scenario:

• Early Binding（早期绑定）: This is what you’re doing now by manually adding the reference. The upside is great IntelliSense and compile-time error checks, but it’s terrible for shared workbooks—if a user’s machine has the OCX in a different path, an outdated version, or it’s not registered at all, they’ll get a nasty missing reference error (and maybe even can’t open the workbook). So this alone isn’t enough for shared use.
• AddFromGUI: This means asking every user to manually navigate to Tools > References and select the OCX. Let’s be real—most users won’t know how to do this, and it’s not scalable. Skip this.
• AddFromFile: Using code to add the reference at runtime by pointing to the OCX file. This is better because it can auto-fix missing references, but you have to handle variable file paths (more on that later). The catch? If the OCX isn’t registered on the user’s machine, this will fail.
• Late Binding（后期绑定）: Perfect for non-visual objects (like regex) since you don’t need a pre-added reference—you just use CreateObject(). But here’s the kicker: this won’t work for the drag-and-drop visual controls you’ve already added to your workbook/forms. Those controls are tied to the design-time reference, so late binding can’t replace them. If you were creating controls dynamically in code, late binding would work, but not for pre-dragged ones.

最优方案 #

Go with a combination of runtime reference checks + dynamic-path AddFromFile. This will automatically try to fix missing references when the workbook opens, while still supporting your existing drag-and-drop controls.

And yes—even if you manually added the reference, you still need this code. User environments vary way more than you’d think, and a reference that works on your machine can break instantly on someone else’s.

2. 如何获取引用的.Name属性（比如正则表达式的VBScript_RegExp_55）？ #

The easiest way is to run a quick script on your machine (where you already have the reference added) to list all active references and their properties. Here’s the code:

Sub ListAllReferences()
    Dim ref As Reference
    For Each ref In ThisWorkbook.VBProject.References
        Debug.Print "Reference Name: " & ref.Name & vbCrLf & _
                    "File Path: " & ref.FullPath & vbCrLf & _
                    "-------------------------"
    Next ref
End Sub

Run this, then open the Immediate Window (press Ctrl+G in the VBA editor) — you’ll see the Name property for every reference you’ve added (like VBScript_RegExp_55 for the regex library).

If your earlier attempts failed, it’s probably because you were trying to access the name without having the reference added first. Run this script on a machine where the reference is working, and you’ll get the exact name you need.

3. 引用文件路径固定吗？如何处理不同路径的情况？ #

Nope, the path isn’t fixed—64-bit Windows stores 32-bit OCX files in C:\Windows\SysWOW64 instead of system32, which trips up a lot of people. Your initial code is on the right track, but we need to add system-bit detection to cover all cases.

Here’s an improved version that handles 32/64-bit systems and checks if the file exists:

Sub EnsureCommonControlsReference()
    Dim sRef As String, sPath As String
    sRef = "MSCOMCTL.OCX"
    
    ' Detect if we're on a 64-bit system
    If Environ("PROCESSOR_ARCHITECTURE") = "AMD64" Or Environ("PROCESSOR_ARCHITEW6432") = "AMD64" Then
        ' 64-bit Windows: 32-bit OCX lives in SysWOW64
        sPath = Environ("Windir") & "\SysWOW64\" & sRef
    Else
        ' 32-bit Windows: OCX is in system32
        sPath = Environ("Windir") & "\system32\" & sRef
    End If
    
    ' Check if the OCX file exists
    If Dir(sPath) = "" Then
        MsgBox "MSCOMCTL.OCX was not found at: " & sPath & vbCrLf & _
               "Please make sure the control is installed and registered.", vbExclamation
        Exit Sub
    End If
    
    ' Try to add the reference (skip if it's already there)
    On Error Resume Next
    ThisWorkbook.VBProject.References.AddFromFile sPath
    If Err.Number <> 0 Then
        If Err.Number = 32813 Then
            ' Reference already exists
            Exit Sub
        Else
            MsgBox "Failed to add reference: " & Err.Description & vbCrLf & _
                   "You may need to register the OCX manually using regsvr32.exe.", vbCritical
        End If
    End If
    On Error GoTo 0
    
    MsgBox "Microsoft Windows Common Controls reference is now active!", vbInformation
End Sub

Bonus: Handling unregistered OCX #

If the file exists but the reference still fails, the OCX isn’t registered. Users can register it via Command Prompt (run as Administrator):

• For 64-bit systems: regsvr32.exe /s C:\Windows\SysWOW64\MSCOMCTL.OCX
• For 32-bit systems: regsvr32.exe /s C:\Windows\system32\MSCOMCTL.OCX

Just remind users they’ll need admin rights to do this, or have their IT team help if they don’t have permissions.

内容的提问来源于stack exchange，提问作者Jeanjean