Set a Reference from VBA

[Imdabaum]

I swear I saw a post on this somewhere but I can't find it. If anyone has a post or link to the answer feel free to post it. Or if you can maybe I can find a solution here.

I have a dll that allows me to disable the mouseWheel from moving through records. I uploaded it to the network where I thought everyone would be able to access it since they all work on the same network. The problem comes up when I found out that some people are working on laptops and are doing work at home... off the network. So is there a way to attach the reference based on whether the network is available or not? Or is that just pushing the limits of VBA?

[Ken Puls]

I wasn't sure if you need code to set the references or not. If you do, then you may want to have a read through these KB entries:

Add a VBA Reference Library via code
Remove Missing VBA Library References via code
Determine the File Path or GUID to a VBA Reference Library

Now, if you want to do different things depending on if the reference is set, you'll want to use conditional compilation:

#If NetworkPresent = True Then
'Add the code to set your reference
#Else
'Do NOT add the code to set your reference
#End If

The # signs mean that it won't compile the parts that are false. You will, of course, need to write a function to test if you have connectivity to the network, to sub in place of "NetworkPresent" above.

HTH,

[Imdabaum]

Thanks Ken. I appreciate that. Sorry I forgot about the KB entries. That's probably where I saw the original post. I'll give it a try and report back. So if I am trying to follow the steps for Access would there need to be any differences (aside from using something different than ThisWorkbook.VBAProject as a variable? I haven't done much with Excel of Word and am just starting with Access VBA.

[Ken Puls]

Hi there,

I've changed up one of my entries there to work for access. It's still using the Word 2003 library as the GUID, but you can fix that up. I'll let you work out how to put this in the conditional part as well. ;)

This will now check that all reference are intact, and remove any missing ones. It does require a reference to the "Microsoft Visual Basic For Applications Extensibilty 5.x" though.

Sub AddReference()
'Macro purpose: To add a reference to the project using the GUID for the
'reference library

Dim strGUID As String, theRef As Variant, i As Long
Dim vbProj As vbproject
Dim bLinked As Boolean

'Update the GUID you need below.
strGUID = "{00020905-0000-0000-C000-000000000046}"

'Set to continue in case of error
On Error Resume Next

'Set reference to appropriate VB Project
For i = Application.VBE.VBProjects.Count To 1 Step -1
Set vbProj = Application.VBE.VBProjects(i)
If CurrentProject.Name = vbProj.Name & ".mdb" Then
bLinked = True
Exit For
Else
bLinked = False
End If
Next i

'End if no project matching
If bLinked = False Then GoTo EarlyExit

'Remove any missing references
For i = vbProj.References.Count To 1 Step -1
Set theRef = vbProj.References.Item(i)
If IsEmpty(theRef) Then Exit For
If theRef.IsBroken = True Then
vbProj.References.Remove theRef
End If
Next i

'Clear any errors so that error trapping for GUID additions can be evaluated
Err.Clear

'Add the reference
vbProj.References.AddFromGuid _
Guid:=strGUID, Major:=1, Minor:=0

'If an error was encountered, inform the user
Select Case Err.Number
Case Is = 32813
'Reference already in use. No action necessary
Case Is = 0
'Reference added without issue
Case Else
'An unknown error was encountered, so alert the user
MsgBox "A problem was encountered trying to" & vbNewLine _
& "add or remove a reference in this file" & vbNewLine & "Please check the " _
& "references in your VBA project!", vbCritical + vbOKOnly, "Error!"
End Select
On Error GoTo 0
Exit Sub

EarlyExit:
On Error GoTo 0
'Could not find the references for this project
MsgBox "I could not find a VB Project for this database!", _
vbCritical + vbOKOnly, "Error!"
End Sub

One thing of note. I added a loop in here, just to make sure that the project we link to is correct. It's verified by the following:
If CurrentProject.Name = vbProj.Name & ".mdb" Then

I had to add the ".mdb" to the vbProj name, but this could fail if your database is "MDB", "Mdb", etc.. You could use an Lcase or Ucase statement to deal with that though.

Let me know if you need any more help with this.

:)

[Imdabaum]

Ken, do you think I could query through the sGUID? I am trying to work this on a replicated database. So there are a several sGUIDs that would need to be checked. Aren't the sGUIDs usually in hidden/system tables? Could I get that from that hidden table form a form_load or something similar?

[Ken Puls]

Hmmm...

To be honest, you're exposing my weakness here with Access. I don't really know.

The route that I went through was to check the VBProject in the Locals window. If the VBProject is being read from a table (possible I suppose), I wasn't aware of it.

[Imdabaum]

I didn't mean to expose weaknesses but rest assured that your weaknesses are probably on a superior level than mine. Thanks again for the help. I'll get plugging away.

[Ken Puls]

LOL!

Didn't figure you were. I'm not ashamed to admit when we're past my level of expertise, though. It may be just as you say, but I can't vouch for it either way. ;)

Let me know how this turns out, or if you run into any other issues. :)

[Imdabaum]

The thought just occurred to me is it possible to write the same code in a module? That way the code references are inherited in the application itself without referencing it? I'll keep working with the dll and setting the reference by vba but something to think about.

[Ken Puls]

Not sure I follow...

If you set the references, they are "inherited" each time you open the application, whether opened on your PC or someone elses.

You can certainly use the code in a module to set (and with mods remove) references at open/close.

[Imdabaum]

I was thinking that there might be a way to use the code from the dll I created in a module... for the possibility that it might be easy to just include the dll code with the project rather than referencing it...
Feel free to call me stupid anytime. :dunno I won't get offended I promise.