• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel VBA with Office 2013

V

VinayKumar15

New Member
I have a VBA project built with Office 2007 that works fine on Office 2007 and Office 2010. When I use the same project on Office 2013 it gives me errors like code is not compatible with the version or architecture. I tried reference checking, removal of XLSTART file, Add-ins or third party apps issues, removal of comctIlib.exd and MCComctILib.exd and everything was ok. I also used VBA code cleaner with no result. Even code cleaner entry was not created as tools menu item. I also checked for 32 bit and 64 bit libraries mismatch but, everything was okay. Now I am not able to figure out how to resolve this compatibility issue. Please help asap. Thanks in advance !!
 
Vinay

Firstly, Welcome to the Chandoo.org Forums

Can you post the file here?
 
This is the code which is giving error -

Code: 
Private Sub cmdOk_Click()
On Error GoTo ERROR

    Dim o As Integer
    Dim i As Integer, r As Integer, c As Integer
    Dim j As Integer
    Dim sname As String
    Dim sData As String
    Dim sValue As String
    Dim sNameAlise As String
    Dim sDescription As String
    Dim sModuleName As String
    Dim iCount As Integer, iStrPos As Integer
    Dim iRowNo As Integer, iDataRow As Integer, iLen As Integer, lngLstRow1 As Integer, sumRow As Integer
    Dim s1 As String
    Dim rngCell As Range
    j = 3
    iCount = 2
    o = 0
    Columns("B").ColumnWidth = 25
    If m_ListBox2.ListCount <= 0 Then
        If MsgBox("No Tags To Display, Do You Want To Close Template?...", vbYesNo) = vbYes Then
            ActiveWorkbook.Saved = True
            ActiveWorkbook.Close
            Me.Hide
            Exit Sub
        Else
            Exit Sub
        End If
    End If
    o = 1
    If ThisWorkbook.g_EditTemplate = True Then
        ThisWorkbook.myobject.xlAppSTD.Application.DisplayAlerts = False
        ThisWorkbook.myobject.xlAppSTD.ActiveWorkbook.Saved = True
        ThisWorkbook.myobject.xlAppSTD.ActiveWorkbook.Close

        If ThisWorkbook.iTempType = 0 Then
            Excel.Application.Workbooks.Open (ThisWorkbook.sAppPath & "\StdTemplate.xltx")
        Else
            Excel.Application.Workbooks.Open (ThisWorkbook.sAppPath & "\StdAlarmTemplate.xltx")
        End If
        Set ThisWorkbook.myobject.xlAppSTD = Excel.Application
        ThisWorkbook.myobject.xlAppSTD.Visible = True
        ThisWorkbook.myobject.xlAppSTD.ActiveWorkbook.Activate
        ActiveWorkbook.Sheets("Report").Activate
        TemplateMEnu.AddTemplateMenus
        ThisWorkbook.g_EditTemplate = False
    End If
    o = 2
    If ThisWorkbook.iTempType = 0 Then
        Cells(1, 1).Interior.Color = RGB(255, 198, 198)
        Cells(2, 1).Interior.Color = RGB(221, 221, 221)
        Cells(1, 2).Interior.Color = RGB(255, 198, 198)
        Cells(2, 2).Interior.Color = RGB(221, 221, 221)
        Cells(ThisWorkbook.g_Data, 1).Interior.Color = RGB(199, 254, 200)
        Cells(ThisWorkbook.g_Data, 2).Interior.Color = RGB(199, 254, 200)
        Cells(ThisWorkbook.g_Footer, 1).Interior.Color = RGB(210, 210, 255)
        Cells(ThisWorkbook.g_Footer, 2).Interior.Color = RGB(210, 210, 255)
        Cells(ThisWorkbook.g_PageEnd, 1).Interior.Color = RGB(221, 221, 221)
        Cells(ThisWorkbook.g_PageEnd, 2).Interior.Color = RGB(221, 221, 221)
        Cells(ThisWorkbook.g_wbRow1, 1).Interior.Color = RGB(255, 255, 198)
        Cells(ThisWorkbook.g_wbRow1, 2).Interior.Color = RGB(255, 255, 198)
        Cells(ThisWorkbook.g_wbRow2, 1).Interior.Color = RGB(255, 255, 198)
        Cells(ThisWorkbook.g_wbRow2, 2).Interior.Color = RGB(255, 255, 198)
    ElseIf ThisWorkbook.iTempType = 1 Then
        For i = 1 To 11
            Cells(1, i).Interior.Color = RGB(255, 198, 198)
            Cells(2, i).Interior.Color = RGB(221, 221, 221)
            Cells(ThisWorkbook.g_Data, i).Interior.Color = RGB(199, 254, 200)
            Cells(ThisWorkbook.g_Footer, i).Interior.Color = RGB(210, 210, 255)
            Cells(ThisWorkbook.g_PageEnd, i).Interior.Color = RGB(221, 221, 221)
            Cells(ThisWorkbook.g_wbRow1, i).Interior.Color = RGB(255, 255, 198)
        Next
    End If

    If FrmReportTypes.sname = FIVE_MIN_DAILY_REPORT Then
        Cells(1, 2) = FIVE_MIN_DAILY_REPORT
    ElseIf FrmReportTypes.sname = FIFTEEN_MIN_DAILY_REPORT Then
        Cells(1, 2) = FIFTEEN_MIN_DAILY_REPORT
    ElseIf FrmReportTypes.sname = HR_REPORT Then
        Cells(1, 2) = HR_REPORT
    ElseIf FrmReportTypes.sname = WEEKLY_REPORT Then
        Cells(1, 2) = WEEKLY_REPORT
    ElseIf FrmReportTypes.sname = MONTHLY_REPORT Then
        Cells(1, 2) = MONTHLY_REPORT
    ElseIf FrmReportTypes.sname = YEARLY_REPORT Then
        Cells(1, 2) = YEARLY_REPORT
    ElseIf FrmReportTypes.sname = DAILY_REPORT Then
        Cells(1, 2) = DAILY_REPORT
    ElseIf FrmReportTypes.sname = ONE_MIN_DAILY_REPORT Then
        Cells(1, 2) = ONE_MIN_DAILY_REPORT
    ElseIf ThisWorkbook.iTempType = 1 Then
        Cells(1, 2) = "ALARM"
    End If
    iRowNo = ThisWorkbook.g_wbRow1
     o = 3
    For i = 0 To m_ListBox2.ListCount - 1

        If ThisWorkbook.iTempType = 0 Then
            sname = m_ListBox2.List(i)
            iCount = iCount + 1
            Cells(2, iCount) = sname

            sNameAlise = sname
            iDataRow = ThisWorkbook.g_Data
            iLen = Len(sname)

            iStrPos = InStr(sname, ".")
            sDescription = Left(sname, iStrPos - 1)

            iStrPos = InStrRev(sname, ".", , vbTextCompare)
            sNameAlise = Left(sname, iStrPos - 1)

            iStrPos = InStrRev(sname, ".", , vbTextCompare)
            sModuleName = Right(sname, iLen - iStrPos)

            Cells(1, iCount).Interior.Color = RGB(255, 198, 198)
            Cells(2, iCount).Interior.Color = RGB(221, 221, 221)
            Cells(ThisWorkbook.g_Data, iCount).Interior.Color = RGB(199, 254, 200)
            Cells(ThisWorkbook.g_Footer, iCount).Interior.Color = RGB(210, 210, 255)
            Cells(ThisWorkbook.g_PageEnd, iCount).Interior.Color = RGB(221, 221, 221)

            Cells(ThisWorkbook.g_wbRow1, iCount).Interior.Color = RGB(255, 255, 198)
            Cells(ThisWorkbook.g_wbRow2, iCount).Interior.Color = RGB(255, 255, 198)

            Cells(3, iCount) = sDescription
            Cells(iDataRow, iCount) = sname
            Cells(4, iCount) = sModuleName
        ElseIf ThisWorkbook.iTempType = 1 Then
            sname = m_ListBox2.List(i)
            iCount = iCount + 1
            Cells(2, iCount) = sname

            sNameAlise = sname
            iDataRow = ThisWorkbook.g_Data
            iLen = Len(sname)

            iStrPos = InStr(sname, ".")
            sDescription = Left(sname, iStrPos - 1)

            iStrPos = InStrRev(sname, ".", , vbTextCompare)
            sNameAlise = Left(sname, iStrPos - 1)

            iStrPos = InStrRev(sname, ".", , vbTextCompare)
            sModuleName = Right(sname, iLen - iStrPos)

            Cells(1, iCount).Interior.Color = RGB(255, 198, 198)
            Cells(2, iCount).Interior.Color = RGB(221, 221, 221)
        End If

    Next



    Range("B" & ThisWorkbook.g_wbRow1 & ":B" & ThisWorkbook.g_wbRow1).NumberFormat = "@"  ''Range("B" & ThisWorkbook.g_wbRow1 & ":B35").NumberFormat = "@"
    Dim lngLstCol As Long, lngLstRow As Long
    lngLstRow = ActiveSheet.UsedRange.Rows.Count
    lngLstCol = ActiveSheet.UsedRange.Columns.Count

    Range(Cells(ThisWorkbook.g_Data, 1), Cells(lngLstRow, lngLstCol)).HorizontalAlignment = xlLeft
    Range(Cells(ThisWorkbook.g_Data, 1), Cells(lngLstRow, lngLstCol)).Select
    With selection.Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
       ' .WrapText = True
    End With
    o = 4
    If cboMin.Value = False Then
        Rows(Application.Match("Min", Range("A1:A" & lngLstRow), 0)).EntireRow.Hidden = True
    End If
    If cboMax.Value = False Then
        Rows(Application.Match("Max", Range("A1:A" & lngLstRow), 0)).EntireRow.Hidden = True
    End If
    If cboavg.Value = False Then
        Rows(Application.Match("Average", Range("A1:A" & lngLstRow), 0)).EntireRow.Hidden = True
    End If
    If cbosum.Value = False Then
        Rows(Application.Match("Sum", Range("A1:A" & lngLstRow), 0)).EntireRow.Hidden = True
    End If

    Range(Cells(1, 1), Cells(lngLstRow, lngLstCol)).WrapText = True
    ThisWorkbook.bNewTemplate = True
    FrmReportTypes.Hide
Exit Sub
ERROR:
    If o = 0 Then
        WriteLog "Error in FrmReportTypes_CmdOk_Click listbox error"
    ElseIf o = 1 Then
        WriteLog "Error in FrmReportTypes_CmdOk_Click edit template functionality"
    ElseIf o = 2 Then
        WriteLog "Error in FrmReportTypes_CmdOk_Click Template formatting"
    ElseIf o = 3 Then
        WriteLog "Error in FrmReportTypes_CmdOk_Click display data in Template"
    ElseIf o = 4 Then
        WriteLog "Error in FrmReportTypes_CmdOk_Click Footer Functions"
    End If

End Sub

MOD EDIT: ADDED CODE TAGS. PLEASE USE CODE TAGS TO POST CODE!
 
Last edited by a moderator:
Can you upload the file as well? Looking at this code, I'm guessing that the code resides in either Worksheet or UserForm.

Without context of how the code is run, it's very difficult to trouble shoot.

As well, this section seems bit off.
Code: 
If ThisWorkbook.g_EditTemplate = True Then
ThisWorkbook.myobject.xlAppSTD.Application.DisplayAlerts = False
ThisWorkbook.myobject.xlAppSTD.ActiveWorkbook.Saved = True
ThisWorkbook.myobject.xlAppSTD.ActiveWorkbook.Close

Not sure what ".g_EditTemplate" refers to and "myobject" as well.
 
Hello everyone,

I want to upload complete project but due to restrictions I can't do that.
I well tell you which line of above code is giving what error -


Code: 
Columns("B").ColumnWidth = 25
Error - unable to set column width proerty of range class.

Code: 
Cells(1, 1).Interior.Color = RGB(255, 198, 198)
Error - Application defined or object defined error.


Code: 
Range("B" & ThisWorkbook.g_wbRow1 & ":B" & ThisWorkbook.g_wbRow1).NumberFormat = "@" ''Range("B" & ThisWorkbook.g_wbRow1 & ":B35").NumberFormat = "@"
Error - Application defined or object defined error.

MOD EDIT: ADDED CODE TAGS. PLEASE USE CODE TAGS TO POST CODE!
 
Last edited by a moderator:
Can't really help without seeing the context (i.e. sample workbook). If there are sensitive info, sanitize it.

As well, I see that your range/cells objects are not qualified with parent sheet/workbook object. If the code runs on multiple sheet, or sheets other than target sheet can be active when the code is run, that'd be likely culprit on the error.
 
I can upload the workbook but it's tightly bounded with my project. Without project it can't be opened.

Application was built with office 2007. It works well in office 2010 but it's giving problem with office 2013 and 2016. I have checked for references and nothing is showing as missing.

I came to know that Microsoft has made some huge changes with Office 2013 onwards. If so, then if that's the reason ?

Any chance I can make it to work or I've to code the project from scratch with Office 2013.
 
You must log in or register to reply here.
Back
Top