(Solved) – Irregular #1004 error on Worksheet.[Pictures./Range.]Paste

  • by
(solved)-–-irregular-#1004-error-on-worksheet[pictures/range.]paste

Macro goal: screenshots making of windows desktop application.

Macro fails irregularly in last shown line of sub:

Sub MakeScreenShot()
    ...
    ClearClipboard
    AltPrintScreen
    wsShots.Activate
    wsShots.Range("B2").Select
    wsShots.Pictures.Paste
    ...
End sub

Found that:

  • 1004 error happens due to empty clipboard
  • in most cases right after error is occurred (when VBE is highlighting the error line) on switching to Excel UI can see that clipboard is not empty
  • on pasting clipboard then can see screenshot of VBE window with highlighted yellow line
  • error happening is highly correlated with PC CPU usage over 40-50% (re-searched by Task Manager)

Assumption:
due to clipboard content (VBE screenshot) on error happening, it looks like in some cases WinAPI calls (keybd_event) stuck (by what reason?) in some queue and OS sends it back to VBE.
Tried to add Sleep and/or DoEvents right after AltPrintScreen, didnt find the difference.

Definition for AltPrintScreen:

Public Declare PtrSafe Sub KeybdEvent Lib "user32" Alias "keybd_event" (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
Enum KeybdEventEnum
    KEYEVENTF_KEYUP = &H2
    VK_SNAPSHOT = &H2C
    VK_MENU = &H12
End Enum

Sub AltPrintScreen()
    KeybdEvent VK_MENU, 0, 0, 0
    KeybdEvent VK_SNAPSHOT, 0, 0, 0
    KeybdEvent VK_SNAPSHOT, 0, KEYEVENTF_KEYUP, 0
    KeybdEvent VK_MENU, 0, KEYEVENTF_KEYUP, 0
    Sleep 50
End Sub

Please assist; this makes me crazy, it takes several days to localize the issue.

Leave a Reply

Your email address will not be published. Required fields are marked *