Excel VBA Clipboard Error With DataObject: Alternative Method Without MSForms

You may get a runtime error when using the DataObject from the Microsoft Forms library to copy text to the clipboard in Excel VBA. This error often occurs because the MSForms library is not available or is blocked by security settings. The error prevents your macro from copying data for use in other applications. This article explains why this error happens and provides a reliable alternative method that does not require the MSForms library.

Why the MSForms DataObject Fails in VBA

The DataObject is part of the Microsoft Forms 2.0 Object Library, referenced as MSForms. This library is not a core part of Excel VBA. It is an external component that must be available on the user’s system. If the library is missing, damaged, or not referenced in your VBA project, your code will fail with an error like “Run-time error ‘429’: ActiveX component can’t create object.”

Even with the reference set, some corporate IT environments restrict or block the MSForms library for security reasons. This makes the DataObject method unreliable for distributing macros. The Windows API provides a direct, low-level way to interact with the system clipboard. This method is built into Windows itself, so it works on any PC where Excel runs, with no extra references needed.

Steps to Copy Text Using the Windows API

This method uses native Windows functions. You must declare these functions at the top of a standard VBA module before writing the copy procedure.

1. Open the VBA Editor and insert a module
   Press Alt + F11 to open the Visual Basic for Applications editor. Go to Insert > Module to create a new standard module.
2. Declare the required Windows API functions
   Copy and paste the following code at the very top of the new module, above any subroutines.

   Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long
Private Declare Function CloseClipboard Lib "user32" () As Long
Private Declare Function IsClipboardFormatAvailable Lib "user32" (ByVal wFormat As Long) As Long
Private Declare Function GetClipboardData Lib "user32" (ByVal wFormat As Long) As Long
Private Declare Function SetClipboardData Lib "user32" (ByVal wFormat As Long, ByVal hMem As Long) As Long
Private Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As Long

3. Create the main subroutine to copy text
   Below the declarations, create a new subroutine named CopyToClipboardAPI. This subroutine will contain the logic to place your text string onto the clipboard.
4. Write the core copy logic inside the subroutine
   Use the following code pattern inside your subroutine. Replace “Your text here” with the string or variable you want to copy.

   Sub CopyToClipboardAPI(text As String)
Dim hMem As Long, lpMem As Long, dataLen As Long
dataLen = Len(text) + 1
hMem = GlobalAlloc(&H2, dataLen)
If hMem <> 0 Then
lpMem = GlobalLock(hMem)
If lpMem <> 0 Then
lstrcpy lpMem, text
GlobalUnlock hMem
If OpenClipboard(0) <> 0 Then
EmptyClipboard
SetClipboardData 1, hMem
CloseClipboard
End If
End If
End If
End Sub

5. Call the subroutine from your main macro
   To use the function, call it from another part of your code. For example, to copy the value from cell A1, you would write: CopyToClipboardAPI Range("A1").Value.

Method for 64-bit Windows and Office

If you are using 64-bit versions of Windows and Office, you must use the PtrSafe keyword in your declarations. Replace the first block of declarations with this version.

#If VBA7 Then
Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
Private Declare PtrSafe Function EmptyClipboard Lib "user32" () As LongPtr
Private Declare PtrSafe Function CloseClipboard Lib "user32" () As LongPtr
Private Declare PtrSafe Function SetClipboardData Lib "user32" (ByVal wFormat As Long, ByVal hMem As LongPtr) As LongPtr
Private Declare PtrSafe Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As LongPtr) As LongPtr
Private Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
Private Declare PtrSafe Function GlobalUnlock Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
Private Declare PtrSafe Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As LongPtr
#End If

The subroutine code remains the same, but you should change the variable types from Long to LongPtr for compatibility.

If the API Method Does Not Work

Excel Crashes When Calling the API Functions

This is usually caused by incorrect API declarations, especially on 64-bit systems. Ensure you are using the PtrSafe declarations inside a #If VBA7 Then block. Also, verify that all variable types match the declaration. Using Long where LongPtr is required will cause a crash.

Text is Copied But Appears Garbled in Other Apps

The clipboard expects a null-terminated string. The line dataLen = Len(text) + 1 in the code adds space for this null character. If you omit the + 1, the copied string may have extra garbage characters at the end. Always calculate the length including the null terminator.

Clipboard is Locked by Another Application

The OpenClipboard function returns zero if another process is using the clipboard. Your code should handle this gracefully. You can wrap the call in a loop with a short delay to retry a few times before giving up.

DataObject vs Windows API: Key Differences

Item	MSForms DataObject	Windows API Method
Library Dependency	Requires MSForms 2.0 Object Library reference	No external references needed
Deployment Reliability	Fails if library is missing or blocked by admin policy	Works on any Windows system running Excel
Code Complexity	Simple, few lines of code	More complex, requires precise API declarations
Control Over Data	Basic text copy and paste	Full control, can set specific clipboard formats
Bitness Compatibility	Works in 32-bit and 64-bit VBA without modification	Requires different declarations for 64-bit VBA7

You can now copy text to the clipboard in Excel VBA without relying on the MSForms library. The Windows API method is a robust solution for distributed macros. For a related technique, try using the GetFromClipboard API function to read text back into Excel. An advanced tip is to wrap the core API logic in a separate class module to create a reusable Clipboard object for your projects.