基于参考代码开发的Excel/VBA剪贴板监控程序报错求助

Hey there! Let's dig into your clipboard monitoring VBA issue. Since you mentioned basing your code on that classic clipboard snippet, here are the most common pitfalls and actionable fixes to troubleshoot your error:

1. Incomplete or Version-Incompatible API Declarations #

Your code snippet cuts off, but one of the biggest culprits here is missing or incorrect Windows API declarations—especially if you're running 64-bit Office. 32-bit and 64-bit Office require different pointer types, so you need conditional compilation to cover both:

Option Explicit

#If VBA7 Then
    Public Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr
    Public Declare PtrSafe Function CallWindowProc Lib "user32" Alias "CallWindowProcA" (ByVal lpPrevWndFunc As LongPtr, ByVal hwnd As LongPtr, ByVal uMsg As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As LongPtr
    Public Declare PtrSafe Function SetClipboardViewer Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
    Public Declare PtrSafe Function ChangeClipboardChain Lib "user32" (ByVal hwnd As LongPtr, ByVal hWndNext As LongPtr) As Long
    Public mNextClip As LongPtr, mPrevHandle As LongPtr
#Else
    Public Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
    Public Declare Function CallWindowProc Lib "user32" Alias "CallWindowProcA" (ByVal lpPrevWndFunc As Long, ByVal hwnd As Long, ByVal uMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
    Public Declare Function SetClipboardViewer Lib "user32" (ByVal hwnd As Long) As Long
    Public Declare Function ChangeClipboardChain Lib "user32" (ByVal hwnd As Long, ByVal hWndNext As Long) As Long
    Public mNextClip As Long, mPrevHandle As Long
#End If

Const WM_DRAWCLIPBOARD = &H308
Const WM_CHANGECBCHAIN = &H30D

2. Missing Window Hook Initialization & Cleanup #

Clipboard monitoring requires hooking into Excel's window message loop. You need to set up the viewer when your workbook opens, and clean up the chain when it closes to avoid crashes:

' Put this in the ThisWorkbook module
Private Sub Workbook_Open()
    mPrevHandle = SetClipboardViewer(Application.hwnd)
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If mPrevHandle <> 0 Then
        ChangeClipboardChain Application.hwnd, mPrevHandle
        SendMessage mPrevHandle, WM_CHANGECBCHAIN, Application.hwnd, mPrevHandle
    End If
End Sub

3. Unhandled Clipboard Messages #

Your custom window procedure must properly handle clipboard-specific messages and pass unhandled ones back to Excel's original window process. Without this, you'll get unexpected errors:

' Put this in a standard module
Public Function WindowProc(ByVal hwnd As LongPtr, ByVal uMsg As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As LongPtr
    Select Case uMsg
        Case WM_DRAWCLIPBOARD
            ' Process clipboard content here (example: read text)
            Dim clipText As String
            clipText = GetClipboardText()
            If clipText <> "" Then
                Debug.Print "Clipboard updated: " & clipText
                ' Add your custom logic here (paste to sheet, etc.)
            End If
            ' Pass the message to the next viewer in the chain
            SendMessage mPrevHandle, uMsg, wParam, lParam
        
        Case WM_CHANGECBCHAIN
            ' Update the viewer chain if another app leaves it
            If wParam = mPrevHandle Then
                mPrevHandle = lParam
            Else
                SendMessage mPrevHandle, uMsg, wParam, lParam
            End If
        
        Case Else
            ' Pass unhandled messages back to Excel's original window procedure
            WindowProc = CallWindowProc(mPrevHandle, hwnd, uMsg, wParam, lParam)
    End Select
End Function

' Helper function to safely read clipboard text
Private Function GetClipboardText() As String
    On Error Resume Next
    GetClipboardText = ""
    With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") ' MSForms.DataObject
        .GetFromClipboard
        GetClipboardText = .GetText
    End With
    On Error GoTo 0
End Function

4. Unhandled Errors When Accessing the Clipboard #

Other apps can lock the clipboard temporarily, causing your code to fail. Always wrap clipboard access in error handling (like the GetClipboardText function above) to avoid runtime crashes.

If you're still hitting errors, share the exact error number and the line of code where it occurs—this will help narrow down the issue even further!

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