remove references programmatically & how to prevent debug error

IkeMike

New Member
Joined
Jun 19, 2009
Messages
40
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:

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
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
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

New Member
Joined
Jun 19, 2009
Messages
40
thanks for your reply!

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

so lets think about the two version version :cool:
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.:confused:

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

New Member
Joined
Jun 19, 2009
Messages
40
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 :biggrin:
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,325
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
Rory

Would that avoid a compile error?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,325
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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.
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,325
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Yes, isn't that what I said? (The error handler was a different remark)
 

IkeMike

New Member
Joined
Jun 19, 2009
Messages
40
ok thanks. so i give it a try and change my code.. :( ..

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

Forum statistics

Threads
1,081,753
Messages
5,361,093
Members
400,613
Latest member
Markdc123

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top