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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PJ0302917Author Commented:
What a fantastic answer, and it worked a treat. Thank you very much
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.