2

I have implemented a DragDrop functionality to my Excel database using TreeView control, using this code:

Private Sub TreeView1_OLEDragDrop(Data As MSComctlLib.DataObject, Effect As Long, Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim StrPath As String

StrPath = Data.Files(1)
'path saved in UserForm label named "FilePathLB"
FilePathLB = StrPath

End Sub

It works perfectly fine on most of the machines I distributed the file to, however some machines with older versions of MS Office fire an error on the very first line (Private Sub ...) due to not being able to find Microsoft Windows Common Control library.

My question: is it possible to late bind this library and thereby preventing the error from happening?

Or at least, is it possible to add a debugger to prevent the error from showing, something like On Error Resume Next for the whole Sub? I understand that in this case the DragDrop function would not work, but it is better than an error.

Tim Stack
  • 3,209
  • 3
  • 18
  • 39
Gexas
  • 648
  • 4
  • 17
  • 1
    Check this thread. It’s old, but maybe can point you in the right direction. https://www.excelforum.com/excel-programming-vba-macros/1039410-distributing-excel-files-with-vba-treeview-active-x-controls-in.html – Ricardo Diaz Feb 06 '19 at 08:28
  • Thanks for the link. It offers to late bound the code, I figured it out myself, but have no idea how to do it. Another solution it offers is to ad this to my code `strGUID = "{0D452EE1-E08F-101A-852E-02608C4D0BB4}" 'Add the reference ThisWorkbook.VBProject.References.AddFromGuid GUID:=strGUID, Major:=1, Minor:=0` , however I do not understand where to add this lines? To my sub? Or somewhere else? – Gexas Feb 06 '19 at 08:39
  • In order to be efficient, I’d add it at opening of the workbook event – Ricardo Diaz Feb 06 '19 at 08:42
  • 1
    https://stackoverflow.com/questions/9879825/how-to-add-a-reference-programmatically/9880276#9880276 – Siddharth Rout Feb 06 '19 at 10:27
  • Thanks `Siddhart Rout` for very clear explanation in provided link. So, it turns out, that in order to avoid users from manually turning on reference to `MSComctlLib` I need then to manually turn on another reference and check `Trust Access To Visual Basic Project` in options. This approach is way harder and more complicated than initial one... My other idea is to turn off UserForm with TreeView completely if reference is missing, but is it possible to check active references without checking `Trust Access To Visual Basic Project` ? – Gexas Feb 06 '19 at 11:49

1 Answers1

0

For your last question:

Sub ()...
On Error GoTo ErrorHandler
    'Your code
Exit Sub
ErrorHandler:
Msgbox "Could not load DragDrop function. Program execution has been terminated.", vbExclamation, "Error"
End Sub

If you want to, you could also just drop the MsgBox.

EDIT:

will not work as the code breaks on the first line. Code below to support my comment. If an error occurs in the sub-macro, then the PassedSub variable won't be set to True, thus indicating an error.

Public PassedSub As Boolean
Sub test1()

On Error Resume Next

Call test2
If PassedSub = False Then GoTo ErrorHandler
On Error GoTo 0

Exit Sub
ErrorHandler:
MsgBox "Could not load DragDrop function. Program execution has been terminated.", vbExclamation, "Error"
End Sub

Sub test2()

Debug.Print 2 / 0
PassedSub = True
End Sub
Tim Stack
  • 3,209
  • 3
  • 18
  • 39
  • ErrorHandler will work despite the fact that error is generated on `Private Sub ...()` line? That's great I thought that code execution won't even reach ErrorHandler. – Gexas Feb 06 '19 at 08:25
  • You're right, have to think out of the box to solve that issue. From the top of my head you could also call that sub from another sub. This will enable the code to go through error checking lines before entering the sub. E.g. having the `On Error` line right before you call the sub, or populating a variable in the TreeView Sub and as it exits, checking in the main sub whether that value is populated. Hope that helps. – Tim Stack Feb 06 '19 at 08:29
  • I have added code to support my alternative error handler from my previous comment – Tim Stack Feb 06 '19 at 08:38