Distributing a program with references

[Jaymond Flurrie]

So, I'm maybe getting ahead of myself a bit, but my thesis is all the time getting closer to finish and I'm supposed to give it to my teacher and for some people to test.

Now ok, I can just give an XLS (or XLSM)-file, but what do I do with references? I mean, I'm using at least two references in my VBA code, "Microsoft Scripting Runtime" is one of those. Do I tell everyone that "turn on VBE and press tools and blah blah blah", or can I actually make this "a real application" by automatizing those references? How do I do that?

Is there some way that everytime my workbook starts, my application checks if references to needed libraries are set? If, then how do I make that check. I'd like an example with for example this "Microsoft Scripting Runtime", it seems to be located in "C:\WINDOWS.0\system32\scrrun.dll" on my computer.

 

[pgc01]

Hi Jaymond

You can enable the reference. If it's already enabled it will raise an error that you can ignore.

This works in my computer (adjust the pathname):

Sub EnableReference()
 
On Error Resume Next
ThisWorkbook.VBProject.References.AddFromFile "C:\WINDOWS\system32\scrrun.dll"
On Error GoTo 0
End Sub

Notice that the pathname of the file is not the same in your computer and mine.

An alternative is to use the reference's Globally Unique Identifier. This should work in both my computer and yours:

Sub EnableReference()
 
On Error Resume Next
ThisWorkbook.VBProject.References.AddFromGUID "{420B2830-E718-11CF-893D-00A0C9054228}", 1, 0
On Error GoTo 0
End Sub

Remark: if you are setting the reference the vbe may not let you use single-step mode. Just run the code.

 

[RoryA]

You can also late bind everything and avoid the referencing issue altogether.

 

[Jaymond Flurrie]

You can also late bind everything and avoid the referencing issue altogether.

Once I get my code to work with early binding, how big translating it is to make code use only late binding? I have less than 50 lines where I need references (one need is for getting a page from Internet to a text file, other is to get that text file to a string variable - I think that's it)

 

[Jaymond Flurrie]

Hi Jaymond

You can enable the reference. If it's already enabled it will raise an error that you can ignore.

This works in my computer (adjust the pathname):

Sub EnableReference()
 
On Error Resume Next
ThisWorkbook.VBProject.References.AddFromFile "C:\WINDOWS\system32\scrrun.dll"
On Error GoTo 0
End Sub

Notice that the pathname of the file is not the same in your computer and mine.

An alternative is to use the reference's Globally Unique Identifier. This should work in both my computer and yours:

Sub EnableReference()
 
On Error Resume Next
ThisWorkbook.VBProject.References.AddFromGUID "{420B2830-E718-11CF-893D-00A0C9054228}", 1, 0
On Error GoTo 0
End Sub

Remark: if you are setting the reference the vbe may not let you use single-step mode. Just run the code.

Let's say I want to use GUID. How do I know the GUID for reference X?

 

[RoryA]

You need to declare everything as Object, replace any constants with literal values (or define them yourself) and use CreateObject rather than New.

 

[pgc01]

Let's say I want to use GUID. How do I know the GUID for reference X?

Set the reference to "Microsoft Visual Basic For Applications Extensibility" and enable all the references you want information about.

This code writes the information in columns A:F

Sub ListReferences()
Dim ref As Reference
Dim lrow As Long
 
For Each ref In ThisWorkbook.VBProject.References
    lrow = lrow + 1
    Range("A" & lrow) = ref.Name
    Range("B" & lrow) = ref.Description
    Range("C" & lrow) = ref.FullPath
    Range("D" & lrow) = ref.GUID
    Range("E" & lrow) = ref.Major
    Range("F" & lrow) = ref.Minor
Next ref
End Sub

About the Late Binding vs. Early Binding.

As Rory said you could just use late binding and forget about the references problem when distributing the application.

I prefer early binding, I have advantages like intellisense and improved readability.

Late binding is practical but also has drawbacks:

- You either don't have intellisense or you have 2 versions of the code, the one you use to develop, with early binding and the one you distribute with late binding.In this case when you have a version that you want to distribute you'll have to make a copy of the code and change the declarations of the objects to Object or Variant.

- remember to reference the library in the code when you create an object if you use late binding.

Ex.:

With early binding:

Dim dic As Dictionary
 
Set dic = New Dictionary

With late binding:

Dim dic As Object
 
Set dic = CreateObject("Scripting.Dictionary")

- it may degrade the readability of the code, declaring an object as a dictionary tells me something. Declaring it as an Object or as a variant tells me nothing. The code in the previous point is an example

- in some cases there may be also performance issues, although in many cases it may be negligible

Last, but not least, I'm personally all for strong typing, always use Option Explicit and declare all variables with the appropriate type when possible.

I don't mean that you should never use late binding, these are just points to take into considerations when taking the decison for a specific case.