CODEWITHSUNDEEP
excelms-accessvba
Tyrone
Tyrone

Reputation: 77

VBA to Get List of Required Library References

I'd like to do some auditing of VBA programs in Access 2010 and Excel 2010. One of the main concerns is the library references that are required for particular programs. Is there a way to programmatically determine which references are required for a particular program?

I did a test in Access. I wrote some code which required a reference to the Excel library. I made sure that I didn't have the reference and compiled the code. I received no errors. My hope was that I would get errors and I would be able to compile programmatically and save the associated error that mentioned lacking the Excel reference.

Another thought I had to achieve the goal, is to parse the VBA in each module. This seems like it would be a pretty big task.

Upvotes: 1

Views: 2867

Answers (2)

HansUp
HansUp

Reputation: 97101

"Is there a way to programmatically determine which references are required for a particular program?"

No. Access can throw a compile error when it finds something it doesn't understand in your VBA code. But it can't identify whether that something is available in an unreferenced object or type library.

Consider this example:

Dim xlApp As Excel.Application

That statement will not throw a compile error if your project includes a reference to the Excel Object Library. But, without the reference, Access will complain "User-defined type not defined" when it encounters Excel.Application. It can't tell you "Include a reference to the Excel Object Library to resolve this problem."

The same thing happens when your code includes an Excel named constant, such as xlEdgeTop. It is OK with the reference. Without the reference, Access hasn't a clue about that name.

Incidentally, the result you reported for your test was only possible because your code module did not include Option Explicit in its Declarations. You should always include Option Explicit in all your code modules so that Access can warn you when it doesn't recognize something.

Upvotes: 2

Tom Robinson
Tom Robinson

Reputation: 1910

In your situation, I remove the references one at a time, compiling after each removal. If that is not working for you, there are a couple of reasons why:

It is important to have Option Explicit at the top of every module, including the code behind forms, reports, userforms, worksheets etc. Without Option Explicit, many undefined symbols won't be caught at compile time.

It is also important to declare object variables with the actual object type, rather than as Variant or as Object. For example, this code compiles fine but it won't run:

    Dim RS
    Set RS = CurrentDb.OpenRecordset("MyTable")
    If RS.EndOfFile Then MsgBox "No data"

Changing Dim RS to Dim RS As Recordset then causes the non-existent .EndOfFile property to be detected at compile time.

Upvotes: 1

Related Questions