Consulting

Results 1 to 12 of 12

Thread: Problem using Oorang's worksheet compare on a Mac

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location

    Problem using Oorang's worksheet compare on a Mac

    I am attempting to run Oorang's worksheet compare program (See KB Article 966). When I run the code, I get a 'Compile Error: Expected: Identifier' with the following code, highlighted as shown:
    Private Enum abOutputColumns
        abRange1Address = 1
        abRange1Value
        abRange2Address
        abRange2Value
    End Enum
    The notes indicate that this program was tested on XL 2003; I'm running XL 2004 on a Mac; searching Help for 'Enum' returns nothing. Have I found yet one more Windows vs. Mac difference?

    Is there a work-around so I can get this program up and flying?

    Thanks,
    Ron
    Windermere, FL

  2. #2
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,366
    Location
    Howdy Ron and nice to "meet",

    Not sure about being a Mac issue, though it runs fine in Excel 2000 for Win.

    2000 help file says...
    ===========================================================
    Enum Statement

    Declares a type for an enumeration.

    Syntax
    [Public | Private] Enum name
     
    membername [= constantexpression]
    membername [= constantexpression]
    . . .
     
    End Enum
    The Enum statement has these parts:

    Part Description
    Public Optional. Specifies that the Enum type is visible throughout the project. Enum types are Public by default.

    Private Optional. Specifies that the Enum type is visible only within the module in which it appears.

    name Required. The name of the Enum type. The name must be a valid Visual Basic identifier and is specified as the type when declaring variables or parameters of the Enum type.

    membername Required. A valid Visual Basic identifier specifying the name by which a constituent element of the Enum type will be known.
    constantexpression Optional. Value of the element (evaluates to a Long). If no constantexpression is specified, the value assigned is either zero (if it is the first membername), or 1 greater than the value of the immediately preceding membername.

    Remarks
    Enumeration variables are variables declared with an Enum type. Both variables and parameters can be declared with an Enum type. The elements of the Enum type are initialized to constant values within the Enum statement. The assigned values can't be modified at run time and can include both positive and negative numbers. For example:

    Enum SecurityLevel
    IllegalEntry = -1
    SecurityLevel1 = 0
    SecurityLevel2 = 1
    End Enum

    An Enum statement can appear only at module level. Once the Enum type is defined, it can be used to declare variables, parameters, or procedures returning its type. You can't qualify an Enum type name with a module name. Public Enum types in a class module are not members of the class; however, they are written to the type library. Enum types defined in standard modules aren?t written to type libraries. Public Enum types of the same name can't be defined in both standard modules and class modules, since they share the same name space. When two Enum types in different type libraries have the same name, but different elements, a reference to a variable of the type depends on which type library has higher priority in the References.

    You can't use an Enum type as the target in a With block.
    ===========================================================
    ...so not any real help there; just included if your help seems like a lot of topics in 2003 (ehmmm... less helpful than 2000 verbiage).

    Anyways, you asked for a workaround. Just for giggles, first try specifying the presumed values and see if that changes anything, though it shouldn't per the err descript.

    Private Enum abOutputColumns
    abRange1Address = 1
    abRange1Value = 2
    abRange2Address = 3
    abRange2Value = 4
    End Enum
    Assuming that does not work, we can just get rid of the stinky enum by edit/replace: Replace "abOutputColumns.abRange1Address" with "1" and so on...

    Seeing as how I wanted to test this before opening my yap, here's the file.

    Mark
    Last edited by Aussiebear; 04-14-2023 at 02:14 AM. Reason: Added the code tags

  3. #3
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location
    Mark,

    I'll try this at home, where I have 2002 on a Wintel box. Below is the screen shot from running it on my Mac, here at work. Error highlighted the line:

    Dim eCompareType As VbCompareMethod
    It looks like Win vs. Mac differences to me.

    Thanks for the tutorial on Enum.

    Cheers,
    Last edited by Aussiebear; 04-14-2023 at 02:15 AM. Reason: Adjusted the code tags
    Ron
    Windermere, FL

  4. #4
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,366
    Location
    Quote Originally Posted by GTO
    Howdy Ron and nice to "meet",

    ...

    Anyways, you asked for a workaround. Just for giggles, first try specifying the presumed values and see if that changes anything, though it shouldn't per the err descript.

    Private Enum abOutputColumns
    abRange1Address = 1
    abRange1Value = 2
    abRange2Address = 3
    abRange2Value = 4
    End Enum

    Assuming that does not work, we can just get rid of the stinky enum by edit/replace: Replace "abOutputColumns.abRange1Address" with "1" and so on...

    Seeing as how I wanted to test this before opening my yap, here's the file.

    Mark
    @Oorang:

    Hey Aaron - I'm plenty shot (tired), but I think I matched this in the attached, which Ron ran at work. (Did I goof it somewheres?) Unfortunately, she balked and stuck 'er hooves at another point...

    ...And if (as in this code example) the coder prefixes his enum with the name...
    BTW the coder was you, ya Mac hater (kidding, jus' kidding!). For real, very nice example, thank you :-)


    @Ron:

    While certainly not "gifted" at coding, I'm nothing if not a bit obstinate... I doubt its the problem, but might get a step closer or reveal source problem.

    You probably already did this, but just in case not... how about:

    In 'OutputDifferences()', at/about line 39:
    'Dim eCompareType As VbCompareMethod
    Dim eCompareType As Integer
    ...at/about lines 68-75:

    'Prompt User regarding case sensitivity:
    Select Case VBA.MsgBox("Do you want a case-sensitive comparison?", _
    vbYesNoCancel + vbQuestion + vbSystemModal + vbDefaultButton2 + _
    vbMsgBoxSetForeground, "Decide Comparison Type")
    Case VbMsgBoxResult.vbCancel
    VBA.Err.Raise lngErrCncl_c, strProcedureName_c, strErrCncl_c
    Case VbMsgBoxResult.vbYes
    eCompareType = 0 'vbBinaryCompare
    Case VbMsgBoxResult.vbNo
    eCompareType = 1 'vbTextCompare
    End Select
    If you don't want to pursue, no probs; I just hate the 'black box' beating me...

    Mark
    Last edited by Aussiebear; 04-14-2023 at 02:20 AM. Reason: Adjusted the code tags

  5. #5
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location
    Aaron,

    Thanks for the revised 'Mac' version. I'm getting the same error I noted in #3 above 'Automation type not supported by Visual Basic' on the same line.

    Mark,

    I'll try your suggestions and see how far forward they move me.

    Thanks!
    Ron
    Windermere, FL

  6. #6
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location
    Mark and Aaron,

    A very big THANK YOU! We're cookin' now! I have the prompt for selecting the source range with my mouse. More reports as I proceed with my work.

    Hallelujah!
    Ron
    Windermere, FL

  7. #7
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,366
    Location
    Hi Ron,

    Sorry replacing the enums w/vals just ended up jamming elsewheres, thats a shame.

    Mark

  8. #8
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location
    Mark,

    No problema, we had to try something.

    Cheers,
    Ron
    Windermere, FL

  9. #9
    It's pretty probable that enums aren't implemented in Mac, I know they were not in Excel 97. The easiest way to convert code the uses enums is to just replace the enums with long constants. And if (as in this code example) the coder prefixes his enum with the name (Ex:MyEnum.SoEnum1), remove the enum by doing a replace all on the enum prefix and replacing it with nothing (empty box) thereby deleting it (unless you'd just like to do it manually).

    Here is a non-enum version of what I just described:

    Option Explicit
     
    Private Type Cell
        Value As String
        Address As String
    End Type
     
    Private Const abRange1Address As Long = 1
    Private Const abRange1Value As Long = 2
    Private Const abRange2Address As Long = 3
    Private Const abRange2Value As Long = 4
     
    Public Sub OutputDifferences()
        On Error GoTo Err_Hnd
        Const strProcedureName_c As String = "AnalyzeDifferences"
        Const strTitleSelectRange_c As String = "Select Range"
        Const strTitleError_c As String = "Error: "
        Const lngErrRngMismatch_c As Long = vbObjectError + 513
        Const lngErrCncl_c As Long = vbObjectError + 777
        Const lngErrIntrpt_c As Long = 18
        Const strErrRngMismatch_c As String = _
        "The ranges you have selected are not equivilant. Selected ranges must have the same number of rows and the same number of columns."
        Const strErrCncl_c As String = "Procedure cancelled."
        Const lngMatch_c As Long = 0
        Const lngLwrBnd_c As Long = 1
        Const strFrcTxt_c As String = "'"
        Const lngIncrement_c As Long = lngLwrBnd_c
        Const strBang_c As String = "!"
        Dim rng1 As Excel.Range
        Dim rng2 As Excel.Range
        Dim wbOutput As Excel.Workbook
        Dim wsOutput As Excel.Worksheet
        Dim lngRow As Long
        Dim lngClmn As Long
        Dim lngUprBndRow As Long
        Dim lngUprBndClmn As Long
        Dim lngOutputRow As Long
        Dim strWs1Name As String
        Dim strWs2Name As String
        Dim eCompareType As Long
        Dim tVal1 As Cell
        Dim tVal2 As Cell
         ''-----------------------------------------------------------------------------------
         ''Select Ranges to Compare:----------------------------------------------------------
         ''-----------------------------------------------------------------------------------
        Set rng1 = GetRange("Please use mouse to select First Range:", _
        strTitleSelectRange_c)
        If rng1 Is Nothing Then
            Err.Raise lngErrCncl_c, strProcedureName_c, strErrCncl_c
        End If
        Set rng2 = GetRange("Please use mouse to select the Second Range:", _
        strTitleSelectRange_c)
        If rng2 Is Nothing Then
            Err.Raise lngErrCncl_c, strProcedureName_c, strErrCncl_c
        End If
        lngUprBndRow = rng1.Rows.Count
        If lngUprBndRow <> rng2.Rows.Count Then
            Err.Raise lngErrRngMismatch_c, strProcedureName_c, strErrRngMismatch_c
        Else
            lngUprBndClmn = rng1.Columns.Count
            If lngUprBndClmn <> rng2.Columns.Count Then
                Err.Raise lngErrRngMismatch_c, strProcedureName_c, _
                strErrRngMismatch_c
            End If
        End If
         ''-----------------------------------------------------------------------------------
         ''Prompt User regarding case sensitivity:--------------------------------------------
         ''-----------------------------------------------------------------------------------
        Select Case MsgBox("Do you want a case-sensitive comparison?", _
            vbYesNoCancel + vbQuestion + vbSystemModal + vbDefaultButton2 + _
            vbMsgBoxSetForeground, "Decide Comparison Type")
        Case vbCancel
            Err.Raise lngErrCncl_c, strProcedureName_c, strErrCncl_c
        Case vbYes
            eCompareType = vbBinaryCompare
        Case vbNo
            eCompareType = vbTextCompare
        End Select
         ''===================================================================================
         
         ''-----------------------------------------------------------------------------------
         'All dialogs have concluded, disable interface for quicker execution:----------------
         ''-----------------------------------------------------------------------------------
        ToggleInterface False
         ''===================================================================================
         
         ''-----------------------------------------------------------------------------------
         ''Create Output worksheet:-----------------------------------------------------------
         ''-----------------------------------------------------------------------------------
        Set wbOutput = Excel.Application.Workbooks.Add
        Set wsOutput = GetOutputSheet(wbOutput)
         ''===================================================================================
         
         ''-----------------------------------------------------------------------------------
         ''Create Output worksheet:-----------------------------------------------------------
         ''-----------------------------------------------------------------------------------
        strWs1Name = rng1.Parent.Name & strBang_c
        strWs2Name = rng2.Parent.Name & strBang_c
        lngOutputRow = lngIncrement_c
         ''===================================================================================
         
         
        For lngRow = lngLwrBnd_c To lngUprBndRow
            For lngClmn = lngLwrBnd_c To lngUprBndClmn
                tVal1.Value = CStr(rng1.Cells(lngRow, lngClmn).Value)
                tVal1.Address = CStr(rng1.Cells(lngRow, lngClmn).Address)
                tVal2.Value = CStr(rng2.Cells(lngRow, lngClmn).Value)
                tVal2.Address = CStr(rng2.Cells(lngRow, lngClmn).Address)
                If LenB(tVal1.Value) = LenB(tVal2.Value) Then
                     'If the lengths are the same, then values may still be different.
                    If StrComp(tVal1.Value, tVal2.Value, eCompareType) <> _
                    lngMatch_c Then
                        lngOutputRow = lngOutputRow + lngIncrement_c
                        wsOutput.Cells(lngOutputRow, abRange1Address).Value = _
                        strFrcTxt_c & strWs1Name & tVal1.Address
                        wsOutput.Cells(lngOutputRow, abRange1Value).Value = _
                        strFrcTxt_c & tVal1.Value
                        wsOutput.Cells(lngOutputRow, abRange2Address).Value = _
                        strFrcTxt_c & strWs2Name & tVal2.Address
                        wsOutput.Cells(lngOutputRow, abRange2Value).Value = _
                        strFrcTxt_c & tVal2.Value
                    End If
                Else
                     'If the lengths are not the same, then it is a given the values are different.
                    lngOutputRow = lngOutputRow + lngIncrement_c
                    wsOutput.Cells(lngOutputRow, abRange1Address).Value = _
                    strFrcTxt_c & strWs1Name & tVal1.Address
                    wsOutput.Cells(lngOutputRow, abRange1Value).Value = strFrcTxt_c _
                    & tVal1.Value
                    wsOutput.Cells(lngOutputRow, abRange2Address).Value = _
                    strFrcTxt_c & strWs2Name & tVal2.Address
                    wsOutput.Cells(lngOutputRow, abRange2Value).Value = strFrcTxt_c _
                    & tVal2.Value
                End If
            Next
        Next
        wsOutput.Columns.AutoFit
    Exit_Proc:
        On Error Resume Next
        ToggleInterface True
        Exit Sub
    Err_Hnd:
        If Err.Number = lngErrCncl_c Then
            Resume Exit_Proc
        ElseIf Err.Number = lngErrIntrpt_c Then
            MsgBox "Operation Cancelled", vbOKOnly + vbMsgBoxSetForeground + _
            vbSystemModal
        Else
            MsgBox Err.Description, vbCritical + vbMsgBoxHelpButton + _
            vbMsgBoxSetForeground + vbSystemModal, strTitleError_c & Err.Number, _
            Err.HelpFile, Err.HelpContext
        End If
        On Error Resume Next
        If Not wbOutput Is Nothing Then
            wbOutput.Close False
        End If
        GoTo Exit_Proc
    End Sub
     
    Private Function GetRange(Prompt As String, Title As String) As Excel.Range
        On Error Resume Next
        Const lngRange_c As Long = 8
        Set GetRange = Excel.Application.InputBox(Prompt, Title, Type:=lngRange_c)
    End Function
     
    Private Function GetOutputSheet(TargetWorkbook As Excel.Workbook) As _
        Excel.Worksheet
        Const lngOne_c As Long = 1
        Dim wsOutput As Excel.Worksheet
        Do Until TargetWorkbook.Worksheets.Count = lngOne_c
            TargetWorkbook.Worksheets(lngOne_c).Delete
        Loop
        Set wsOutput = TargetWorkbook.Worksheets(lngOne_c)
        wsOutput.Name = "Mismatched Cells"
        wsOutput.Cells(lngOne_c, abRange1Address) = "Range1 Address"
        wsOutput.Cells(lngOne_c, abRange1Value) = "Range1 Value"
        wsOutput.Cells(lngOne_c, abRange2Address) = "Range2 Address"
        wsOutput.Cells(lngOne_c, abRange2Value) = "Range2 Value"
        Set GetOutputSheet = wsOutput
    End Function
    Private Sub ToggleInterface(InterfaceEnabled As Boolean)
        Dim oApp As Excel.Application
        Set oApp = Excel.Application
        If InterfaceEnabled Then
            oApp.Cursor = xlDefault
        Else
            oApp.Cursor = xlWait
        End If
        oApp.DisplayAlerts = InterfaceEnabled
        oApp.ScreenUpdating = InterfaceEnabled
        oApp.EnableEvents = InterfaceEnabled
        If InterfaceEnabled Then
            oApp.EnableCancelKey = xlInterrupt
        Else
            oApp.EnableCancelKey = xlErrorHandler
        End If
    End Sub
    Last edited by Aussiebear; 04-14-2023 at 02:17 AM. Reason: Adjusted the code tags
    Cordially,
    Aaron



    Keep Our Board Clean!
    • Please Mark your thread "Solved" if you get an acceptable response (under thread tools).
    • Enclose your code in VBA tags then it will be formatted as per the VBIDE to improve readability.

  10. #10
    Updated code post with non-enum message box syntax. Just as a side note, if one of you mac types wouldn't mind, in the VBE, go to Tools>References and tell me what dll is referenced for the entry that says "Visual Basic for Applications". (Just out of curiosity.)
    Cordially,
    Aaron



    Keep Our Board Clean!
    • Please Mark your thread "Solved" if you get an acceptable response (under thread tools).
    • Enclose your code in VBA tags then it will be formatted as per the VBIDE to improve readability.

  11. #11
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location
    Aaron,

    What I found is:
    ORLMcKenzieR-M:/Applications/Microsoft Office 2004/Office ronald$ ls -l "Visual Basic for Applications"
    -rwxrwxr-x 1 admin admin 5715840 Feb 12 2006 Visual Basic for Applications

    HTH,
    Ron
    Windermere, FL

  12. #12
    I think that is a partial path. Try doing this:
    1. Set a reference to "Microsoft Visual Basic for Applications Extensibility"
    2. Go to tools>macro>security and check "Trust Access to Visual Basic Project"
    3. Run the following:

    Public Sub PrintRefs()
        Dim r As VBIDE.Reference
        For Each r In Excel.Application.VBE.ActiveVBProject.References
            Debug.Print r.Name & vbNewLine & r.FullPath & vbNewLine
        Next
    End Sub
    Last edited by Aussiebear; 04-14-2023 at 02:21 AM. Reason: Adjusted the code tags
    Cordially,
    Aaron



    Keep Our Board Clean!
    • Please Mark your thread "Solved" if you get an acceptable response (under thread tools).
    • Enclose your code in VBA tags then it will be formatted as per the VBIDE to improve readability.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •