VBA Code Compile error: Sub undefined whenever run on a different PC

Hello,

I have an issue with a code that actually comes from the internet thatI want to use for my project.he projectI'm working on is a employee database with an image box on the right side. The code below is working when I'm running it at home however, when I finished it and about to present it to my boss and run the vba in my boss PC it's suddenly give me an error say: Compile error: Sub undefined whenever run on a different PC. Seems that both PC has the same references so I really need help on this. TIA.

This is the code that having the issue:

Public Sub prClipboardData2Image()
Dim hCopy&: OpenClipboard 0&
hCopy = CopyImage(GetClipboardData(2), 0, 0, 0, &H4)
CloseClipboard
If hCopy = 0 Then Exit Sub
Const IPictureIID = "{7BF80981-BF32-101A-8BBB-00AA00300CAB}"
Dim iPic As IPicture, tIID As GUID, tPICTDEST As PICTDESC, Ret&
Ret = IIDFromString(StrConv(IPictureIID, vbUnicode), tIID)
If Ret Then Exit Sub
With tPICTDEST
.cbSize = Len(tPICTDEST)
.picType = 1
.hImage = hCopy
End With
Ret = OleCreatePictureIndirect(tPICTDEST, tIID, 1, iPic)
If Ret Then Exit Sub

frmEmpDetails.imgEmp.Picture = LoadPicture("")
frmEmpDetails.imgEmp.Picture = iPic

Set iPic = Nothing
End Sub

Welcome to the forum! Please paste code between code tags. Click the # icon on toolbar to insert the tags.

As posted, you left out the API routines.

Even if both have the code, one may be 32bit Excel and the other 64bit Excel. API code can be coded to handled both cases sometimes.

Please Login or Register  to view this content.

Originally Posted by Kenneth Hobson

Welcome to the forum! Please paste code between code tags. Click the # icon on toolbar to insert the tags.

As posted, you left out the API routines.

Even if both have the code, one may be 32bit Excel and the other 64bit Excel. API code can be coded to handled both cases sometimes.

Please Login or Register  to view this content.

Well, I hope this is it. I saw some code that has 32bit maybe if I changed it to 64 bit or with this code everything will works fine. Thank you. Will update you after if I can make it work.

Hi Kenneth,

It seems that code didn't work either. Here's my full code in the module that having the issue:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''' OM ''''''''''''''''''''''''''''''''''''''''''''''
''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Option Explicit
Private Type GUID
Data1 As Long
Data2 As Integer
Data3 As Integer
Data4(8) As Byte
End Type

Private Type PICTDESC
cbSize As Long
picType As Long
hImage As Long
End Type
#If VBA7 And Win64 Then
Private Declare PtrSafe Function OpenClipboard& Lib _
"user32" (ByVal hwnd&)

Private Declare PtrSafe Function EmptyClipboard& Lib "user32" ()
Private Declare PtrSafe Function GetClipboardData& Lib _
"user32" (ByVal wFormat%)
Private Declare PtrSafe Function SetClipboardData& Lib _
"user32" (ByVal wFormat&, ByVal hMem&)
Private Declare PtrSafe Function CloseClipboard& Lib "user32" ()
Private Declare PtrSafe Function CopyImage& Lib "user32" (ByVal handle& _
, ByVal un1&, ByVal n1&, ByVal n2&, ByVal un2&)
Private Declare PtrSafe Function IIDFromString Lib "ole32" _
(ByVal lpsz As String, ByRef lpiid As GUID) As Long
' Private Declare PtrSafe Function OleCreatePictureIndirect Lib "olepro32" _
' (pPictDesc As PICTDESC, ByRef riid As GUID _
' , ByVal fOwn As Long, ByRef ppvObj As IPicture) As Long

Private Declare PtrSafe Function OleCreatePictureIndirect Lib "oleaut32" _
(pPictDesc As PICTDESC, ByRef riid As GUID _
, ByVal fOwn As Long, ByRef ppvObj As IPicture) As Long

#Else
' Private Declare Function OpenClipboard& Lib _
' "user32" (ByVal hwnd&)
' Private Declare Function EmptyClipboard& Lib "user32" ()
' Private Declare Function GetClipboardData& Lib _
' "user32" (ByVal wFormat%)
' Private Declare Function SetClipboardData& Lib _
' "user32" (ByVal wFormat&, ByVal hMem&)
' Private Declare Function CloseClipboard& Lib "user32" ()
' Private Declare Function CopyImage& Lib "user32" (ByVal handle& _
' , ByVal un1&, ByVal n1&, ByVal n2&, ByVal un2&)
' Private Declare Function IIDFromString Lib "ole32" _
' (ByVal lpsz As String, ByRef lpiid As GUID) As Long
' Private Declare Function OleCreatePictureIndirect Lib "olepro32" _
' (pPictDesc As PICTDESC, ByRef riid As GUID _
' , ByVal fOwn As Long, ByRef ppvObj As IPicture) As Long
' Private Declare Function DestroyIcon& Lib "user32" (ByVal hIcon&)
#End If



Public Sub prImage2Print()
Dim hCopy&: OpenClipboard 0&
hCopy = CopyImage(GetClipboardData(2), 0, 0, 0, &H4)
CloseClipboard
If hCopy = 0 Then Exit Sub
Const IPictureIID = "{7BF80981-BF32-101A-8BBB-00AA00300CAB}"
Dim iPic As IPicture, tIID As GUID, tPICTDEST As PICTDESC, Ret&
Ret = IIDFromString(StrConv(IPictureIID, vbUnicode), tIID)
If Ret Then Exit Sub
With tPICTDEST
.cbSize = Len(tPICTDEST)
.picType = 1
.hImage = hCopy
End With
Ret = OleCreatePictureIndirect(tPICTDEST, tIID, 1, iPic)
If Ret Then Exit Sub
SavePicture iPic, prTmpPath & "outputImage.jpg"

Set iPic = Nothing
End Sub

Public Sub prClipboardData2Image()
Dim hCopy&: OpenClipboard 0&
hCopy = CopyImage(GetClipboardData(2), 0, 0, 0, &H4)
CloseClipboard
If hCopy = 0 Then Exit Sub
Const IPictureIID = "{7BF80981-BF32-101A-8BBB-00AA00300CAB}"
Dim iPic As IPicture, tIID As GUID, tPICTDEST As PICTDESC, Ret&
Ret = IIDFromString(StrConv(IPictureIID, vbUnicode), tIID)
If Ret Then Exit Sub
With tPICTDEST
.cbSize = Len(tPICTDEST)
.picType = 1
.hImage = hCopy
End With
Ret = OleCreatePictureIndirect(tPICTDEST, tIID, 1, iPic)
If Ret Then Exit Sub

frmEmpDetails.imgEmp.Picture = LoadPicture("")
frmEmpDetails.imgEmp.Picture = iPic

Set iPic = Nothing
End Sub

Run the code that I posted on both machines. Then you will know if Excel bit version is an issue.

In the #4 code, there is probably no reason to comment out the routines. The purpose of #If in #4 is to let either version work. e.g. OleCreatePictureIndirect was called in #1 but both bit versions were commented out in #4. A VBE > Debug > Compile should have showed that issue though.