0

I am struggling with references in my VBA project. In order to use my Drag and Drop function, I need to add the following reference to my workbook: Microsoft Windows Common Controls 6.0 (SP6). This reference has got the following location : C:\Windows\system32\MSCOMCTL.OCX.

I would like to make sure that for any user using my specific workbook, the reference won't be missing. I have seen many tutorials about it and could "easily" add a reference, but I would like to understand precisely what I am doing and choose the best solution. I have several questions:

  1. Late binding, Early Biding, AddFromFile, AddFromGUI

    In those two topics (1 and 2), I read many things about references. The problem is I really do not know which option fits the best to my particular case.

    1. My reference has already been added manually. Should I code something in order to be sure it is not missing when the code runs?

    2. About adding a reference : Which option between those four is the best? I am hesitating between AddFromFile and Late Binding.

    3. Let's suppose I choose AddFromFile or Late Binding. I cannot manage to Print my reference's name! How do you know that Microsoft VBScript Regular Expressions 5.5's .Name is VBScript_RegExp_55? I tried the code in this topic but it does not work (Must have done something wrong...).

  2. "Universal" Path to my reference

    I am very pessimistic and I was wondering: are references always stored at the same location? Let's suppose a user uses my specific workbook and hasn't got the reference it needs for the Drag and Drop function. He/She would need to add MSCOMCTL.OCX from C:\Windows\system32\.

    1. Are references always stored at the same location?

    2. If not, how can I overcome that? Changing the path to something like the following?

      Sub mySub()
    Dim sRef, sPath As String

    sRef = "MSCOMCTL.OCX"
    sPath = Environ("Windir") & "\system32\" & sRef
End Sub

Thank you in advance for your answers and sorry if things are crystal clear and already answered in other posts, I may have missed it.

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
Jeanjean
  • 723
  • 2
  • 12
  • 22
  • Users would need to have the same version of Excel for your specification to have a chance of working. Is that what you intended? You may also need to give consideration to removing missing references. – QHarr Jan 17 '18 at 11:20
  • In order to add a reference you must have access to the VBA project which MS has long taught users not to allow, for good reasons. Therefore, even if you write code to install the reference the code won't run unless the user has granted access to VBA projects on his PC which is unlikely and not recommended. – Variatus Jan 17 '18 at 12:10
  • @QHarr they do have the same version. What if not? What do you mean by removing missing references and what do I need to know about this, please? – Jeanjean Jan 17 '18 at 15:17
  • @Variatus so what's your advice, please? Does that mean noboddy uses references? How do Professional programmers do then? – Jeanjean Jan 17 '18 at 15:18
  • Use early binding for testing and convert to late binding for release – QHarr Jan 17 '18 at 15:22
  • You need to get users to set the required references themselves. Professional programmers make do with what they find on the machines where their programs run or spit out messages like "Please contact your IT department." – Variatus Jan 17 '18 at 15:24

0 Answers0