Excel issue with Office 2013 and 2016

VinayKumar15 · Jan 15, 2018

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 !!

Marc L · Jan 15, 2018

S. Das · Jan 15, 2018

Hi, Vinay, can you tell me one thing that is only your code is not running in that version of Excel or all VBA codes are failed to run in that??

VinayKumar15 · Jan 15, 2018

Hi,

Only my code is not working. Infact, some of my code is working but some of code doesn't work at all.

S. Das · Jan 15, 2018

As per my opinion, you should upload your code here so that our respected ninjas or experts can make it work.

Debaser · Jan 15, 2018

Are you using 64 bit Office in 2013 and 2016?

Hui · Jan 15, 2018

Posting the file or at least the code will get you a long way here
It is also important to know if you are running a 64 Bit version of Excel, as that requires modification of all Functions.

p45cal · Jan 15, 2018

Is this not a duplicate of https://chandoo.org/forum/threads/excel-vba-with-office-2013.36489/
?

VinayKumar15 · Jan 16, 2018

Hi All,

I am using 32-bit excel on a 64-bit Windows 7 PC.

I am trying to attach the workbook but after compressing its size is around 3 MB and max size allowed is 1 MB so I am not able to attach it.

Please tell me how I can attach the file.

VinayKumar15 · Jan 16, 2018

Hi all,

since there is no option to upload complete workbook, I am posting a code snippet which is giving problems -

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

In this code these lines are giving issue -

Columns("B").ColumnWidth = 25

and

Cells(1, 1).Interior.Color = RGB(255, 198, 198).

Excel issue with Office 2013 and 2016

VinayKumar15

New Member

Marc L

Excel Ninja

S. Das

Active Member

VinayKumar15

New Member

S. Das

Active Member

Debaser

Well-Known Member

Hui

Excel Ninja

p45cal

Well-Known Member

VinayKumar15

New Member

VinayKumar15

New Member