1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'Ask an Excel Question' started by VinayKumar15, Nov 23, 2017.

  1. VinayKumar15

    VinayKumar15 New Member

    Messages:
    11
    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 !!
  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,266
    Vinay

    Firstly, Welcome to the Chandoo.org Forums

    Can you post the file here?
  3. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,465
  4. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,465
  5. VinayKumar15

    VinayKumar15 New Member

    Messages:
    11
    This is the code which is giving error -

    Code (vb):

    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: Nov 24, 2017
  6. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,430
    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 (vb):
    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.
  7. VinayKumar15

    VinayKumar15 New Member

    Messages:
    11
    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 (vb):
    Columns("B").ColumnWidth = 25
    Error - unable to set column width proerty of range class.

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


    Code (vb):
    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: Nov 24, 2017
  8. VinayKumar15

    VinayKumar15 New Member

    Messages:
    11
    Hello everyone,

    I am waiting for help on above issue. Please provide your help as soon as possible.
  9. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,430
    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.
  10. VinayKumar15

    VinayKumar15 New Member

    Messages:
    11
    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.

Share This Page