Are Excel VBA references made in the workbook or the client machine?

Question

I am working on an Excel VBA project that I want to distribute to other people, but I need to make sure that they have the appropriate references enabled. Specifically, the most recent Microsoft ActiveX Data Objects X.X Library so that they can use the ADODB methods to run SQL queries.

If I enable these references in the macro enabled .xlsm workbook file, will they automatically be available and working on the end user's client machine, or are those references all made specifically on the client machine? In other words, will everyone that uses my .xlsm file have to manually go into their settings and make these references?

If they have the appropriate dll, tlb or whatever installed, then it is fine. But the dll is not transported with the workbook. They don't have to tick the reference, but they have to have the same dlls installed on their machine. This is safe for very basic things like Windows\sysWOW64\scrrun.dll which contains the Scripting classes for FilesystemObject etc(ADO should be OK). Some dlls may not be installed on client, so beware. JohnyL's answer simply makes sure that the client picks whichever latest version they have default in their registry, but does not itself guarantee the dll is there. — MacroMarc, Dec 16 '18 at 17:04

score 1 · Answer 1 · answered Dec 16 '18 at 15:44

1

In order to get rid of troubles with references , you can use late binding:

Dim rs As Object
Set rs = CreateObject("ADODB.Recordset")
rs.CursorLocation = 3 'adUseClient

answered Dec 16 '18 at 15:44

JohnyL

6,894
3
22
41

Does this eliminate the problem of making references completely? – SandPiper Dec 16 '18 at 16:04
@SandPiper Yes. The VBA runtime will automatically create appropriate object. – JohnyL Dec 16 '18 at 17:34

Are Excel VBA references made in the workbook or the client machine?

1 Answers1

Linked