VBA Tick FSO Question, will all users need to Tick "Microsoft Scripting Runtime"


I'm writing an application and was thinking about using a FileSystemObject to manipulate a file. When I use it I get a runtime error. After googling the problem, it seems I need to ..

To use FileSystemObject you have to use a special reference which tells Excel where it can find out what FileSystemObject is.
In the VBA IDE, go to Tools > References
Tick "Microsoft Scripting Runtime" and click OK

My question is, if I want to role out my application, will the end user need to mess around with the VBA IDE to get it to work?
The idea is to deploy the application as an VBA Add In

Who is Participating?
Fabrice LambertFabrice LambertCommented:

References are a big sources of troubles, espcially when you deploy an application.
If the user's computer have a newer library installer, there isn't much problems, as the references will upgrade automatically.
However, if the user's computer have an older library, references won't downgrade and you'll start pulling hairs.

The solution is to use Late binding, that mean: references are resolved at runtime.

To do this:
- Don't check on (tick) any additional reference in your application.
- Declare your variables as Object (this is a generic type).
- Instanciate your objects via the CreateObject() function.
- Provide constants replacement, so you code don't loose readability.
Public Sub testMe()
    Const ForReading = 1       '// constant replacement
    Dim fso As Object         '// Scripting.FileSystemObject
    Dim iStream As Object    '//Scripting.TextStream
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set iStream = fso.OpenTextFile("c:\myData.txt", ForReading)
        '// do whatever you want with the iStream object
        '// cleanup, close and properly destroy objects
    Set iStream = Nothing
    Set fso = Nothing
End Sub

Open in new window

But, using explicit references (AKA: Early binding) provide the adventage of intellisence when writing your code.

So a common practice is to use Early binding when developping, and switch to Late binding when deploying.
PJ0302917Author Commented:
What a fantastic answer, and it worked a treat. Thank you very much
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.