remove references programmatically & how to prevent debug error

[IkeMike]

hi excel gurus!

i m working on an excel 2007 workbook with ms project functionality. in order to be able to use ms project from within excel i m using early binding. for those who dont know what early binding is: http://www.dicks-clicks.com/excel/olBinding.htm


this works fine if the user has ms project installed on his/her pc.

if the user does not have ms project installed i remove the broken references. this works fine.

but my problem is that when i remove the reference to ms project, my vba project will not compile correctly. thus each time when a user opens the workbook the user gets a "compile error in hidden module" , since the functions of ms project are not available.

for example i get a compile error in the following source code:

Public Function getResID(ResName As String, ActiveProject As Project) As Integer
    Dim res As Resource
    For Each res In ActiveProject.Resources
        If res.name = ResName Then
            getResID = res.ID
            Exit Function
        End If
        getResID = -1
    Next
End Function

i just want to know what i can do to prevent the compile error? would the use of late binding remove the problem? actually i dont want to use late binding since my source code is already very complex and it would be a huge effort to change it..

would be thankful for any help!

kind regards
IkeMike

 

[Norie]

Using late-binding wouldn't make any difference if the user doesn't have Project installed.

At some point you would have to create an instance of Project to work with and I think it might be difficult to do that with a non-existent app.

Do you actually need to distribute the workbook to people who don't have Project with the code in it?

What I'm thinking is that if they don't have the app then the code is sort of redundant.

Perhaps you could give them a stripped down version?

If that's not possible then you can set and check references programatically but I don't think that would help in this situation.

Plus it might raise security issues.

 

[IkeMike]

thanks for your reply!

i already thought about a stripped down version. it might be possible.

so lets think about the two version version
even if i have two versions i can not prevent that a user is opening the excel at a pc without installed ms project.

thus is there a way to catch / surpress the pretty ugly compile error, since it leaves a pretty unprofessional impression of the developer, who i am, and replace it with a msgbox or surpress it at all?

somehow i can not believe that i m the first one with this problem, since it is nested in the design of vba references.

another question i have is whether the other references below i m using are save in the way that they are installed in most windows pc!?

_Ole Automation
_Microsoft Office 12.0 Object Library
_Microsoft Excel 12.0 Object Library
_Microsoft Forms 2.0 Object Library
_Visual Basic for Applications

for example does excel automatically search for an earlier version of the object library if the 12.0 is not available?

 

[IkeMike]

i guess it is not allowed to push a thread through posting a reminder message. but nevertheless i will try it, since i posted this thread at the weekend and i hope that some other people read this at the beginning of the week and have some hints for me.

i think this problem could affect a lot of people.. punish me if i m wrong

 

[RoryA]

Late binding would indeed avoid the reference error. You would then just need an error handler on the routine(s) that create the instance of the project application so that if it can't be created you exit gracefully.

 

[Norie]

Rory

Would that avoid a compile error?

 

[RoryA]

Yep. With everything declared as Object, the compiler won't complain (assuming the reference has been removed!). You'll get a 429 error when you try and run CreateObject but that's easy enough to trap.

 

[Norie]

Rory

Isn't it the declarations using Object that avoid the compile error, not the On Error...?

And the OP does seem to want to use early binding.

 

[RoryA]

Yes, isn't that what I said? (The error handler was a different remark)

 

[IkeMike]

ok thanks. so i give it a try and change my code.. ..

i will post the result here whether late binding helps..