none
VBA Code compiling problem in Excel 2010 with code generated with Excel 2007

    Question

  • I wrote several vba code using Excel 2007, some of it originating from Excel 2003. The code opens specific INI files for reading settings and other Excel Sheets for doing some sorting and copying between the different sheets as well as displaying forms. The change from Excel 2003 to 2007 was no pain, but now with 2010, the vba code won't compile. There only comes an error when trying to compile it:

    Object library is invalid or links to object definitions can not be found.

    I checked the activated References and they look identical to me:

    • Visual Basic for Applications
    • Microsoft Excel 14.0 Object Library
    • OLE Automation
    • Microsoft Office 14.0 Object Library
    • Microsoft Forms 2.0 Object Library
    • Microsoft Windows Common Controls 6.0 (SP6)

    Same are also active in Excel 2007, of course there is Version 12.0 of the Excel and Office Libraries. The files are .xlsm files and not .xls files running in compatibility mode.

    I looked in the Help files and checked the changes made since Excel 2003 and Excel 2007 on the object model, but none of these objects is used by my VBA code. I manly use the "Workbook" and "Worksheet" objects.

    Is there a option where I can get details from the compiler and get the functions or objects that is missing or can not be found?

     

    Best Regards, Gerhard

    Monday, October 17, 2011 1:09 PM

Answers

  • Okay, I found the Problem.

    It was the Microsoft ProgressBar Control I used in a status form.

    Both Versions, the 5.0 SP6 (comctl32.ocx) and the 6.0 (mscomctl.ocx) are not working with my installations of Office 2010.

    I removed the Control from the form and now it works.

    What controls are you using now for displaying a progress bar with Office 2010?

     

    • Marked as answer by Cme4help Friday, October 21, 2011 12:02 PM
    Friday, October 21, 2011 12:02 PM

All replies

  • Gerhard, Would it be possible for us to have a peek at the "culprit" code :)


    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.
    Monday, October 17, 2011 2:24 PM
    Moderator
  • Hi Gerhard,

    Thanks for your post.

    For your issue that VBA code from Excel 2003 doesn't work in Excel 2010, you can consider using this tool to help you to check the code compatibility:

    http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=15001

    I hope this helps.


    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, October 18, 2011 2:54 AM
    Moderator
  • Hello Sid,

    I did not paste any code, because I do not know which part of it is creating the problem. There are hundreds of lines in it and I know me, I would paste the part with no problems in it. :-)

     

    Regards, Gerhard


    • Edited by Cme4help Tuesday, October 18, 2011 11:31 AM
    Tuesday, October 18, 2011 11:27 AM
  • Hi Calvin,

     

    thank you for the link, I did not know this tool.

    I installed it and tried to run the analysis, but the code is going to be compiled first and there the tool ends with the same error message from the compiler pointing to missing object definitions.

    I used the OEAT tool to analyze the Add-Ins loaded when executing the Code using Excel 2007:

    1 Microsoft Access Outlook-Add-In für die Erfassung und Veröffentlichung von Daten outlook
    1 Microsoft Office SharePoint Server-Kollegenimport-Add-In outlook
    1 Microsoft Exchange Unified Messaging outlook
    1 Microsoft Outlook Mobile Service outlook
    1 Microsoft VBA für Outlook-Add-In outlook
    1 OneNote-Notizen zu Outlook-Elementen outlook
    1 Windows Search Email Indexer outlook
    1 Microsoft Word East European Fonts Tool word
    1 Kalenderfeature für Windows SideShow outlook
    1 Enterprise Vault Add-in outlook

    These are all Add-Ins the Tool found in a background scan, and none of them is realted to Excel.

    Other VBA code I wrote and maintain since Office XP does not make any problems, so I tried to figure out, what code I only used here. And this are some parts I use only in the troublesome sheets:

    Access a plain text file:

    ReDim PriceListSpecs(0)
    INIFileCounter = 0
    
    Open IniFileFullName For Input Access Read As #1
    
    Do
        Input #1, ReadBuffer
        Select Case Left$(ReadBuffer, 3)
            Case "PL=": ReDim Preserve PriceListSpecs(INIFileCounter)
                        PriceListSpecs(INIFileCounter).CompanyName = Right(ReadBuffer, Len(ReadBuffer) - 3)
                        INIFileCounter = INIFileCounter + 1
            Case "VO=": PriceListSpecs(INIFileCounter - 1).PLVorlage = Right(ReadBuffer, Len(ReadBuffer) - 3)
            Case "MA=": PriceListSpecs(INIFileCounter - 1).EmployeeName = Right(ReadBuffer, Len(ReadBuffer) - 3)
            Case "EM=": PriceListSpecs(INIFileCounter - 1).eMailAddress = Right(ReadBuffer, Len(ReadBuffer) - 3)
            Case "NR=": PriceListSpecs(INIFileCounter - 1).PhoneNumber = Right(ReadBuffer, Len(ReadBuffer) - 3)
            Case "EX=": PriceListSpecs(INIFileCounter - 1).PriceLists = Right(ReadBuffer, Len(ReadBuffer) - 3)
        End Select
        
    Loop Until EOF(1)
    
    Close #1
    
    

     

     

    Add Hyperlinks:

        WB_Template.Activate
        WB_Template.Worksheets(SourceSheet).Activate
        Worksheets(SourceSheet).Range(SourceRange).Select
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=TargetRange, TextToDisplay:=DisplayText
    
    


    Open other Excel Sheets:

        Set WB_DLPricelist1 = Application.Workbooks.Open(DLPricelist1, False, True)
    
    


    Save a new Excel Sheet:

    WB_Vorlage.SaveAs Filename:= HomeLocation & "Batch-Out\Vendor_" & Replace(Date, ".", "-", , , vbTextCompare) & ".xlsx", FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", ReadOnlyRecommended:= False, CreateBackup:=False

    And finally some rather long option lists:

    Sub Generate_MCTPricelist(ByVal WB_MakroBook As String, ByRef WB_Template, ByVal WB_DLPricelist1, ByVal WB_DLPricelist2 As Workbook, ByVal VendorName As String, ByVal IniFilePos As Integer, Optional ByVal MarketName As String = "MCT", Optional ByVal FilterEnabled As Integer = 0)
    

    All looks to me like default VB code, but maybe I am wrong and some things are no longer supported. I am a bit puzzled.

     

    Regards, Gerhard

     

    Tuesday, October 18, 2011 3:40 PM
  • Okay, I found the Problem.

    It was the Microsoft ProgressBar Control I used in a status form.

    Both Versions, the 5.0 SP6 (comctl32.ocx) and the 6.0 (mscomctl.ocx) are not working with my installations of Office 2010.

    I removed the Control from the form and now it works.

    What controls are you using now for displaying a progress bar with Office 2010?

     

    • Marked as answer by Cme4help Friday, October 21, 2011 12:02 PM
    Friday, October 21, 2011 12:02 PM