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:

Code:

---

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,

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

Code:

---

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

Code:

---

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

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:

Code:

---

Dim eCompareType As VbCompareMethod

---

It looks like Win vs. Mac differences to me.

Thanks for the tutorial on Enum.

Cheers,

Hi Ron,

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

Mark

Mark,

No problema, we had to try something.

Cheers,

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:

Code:

---

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

---

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

Quote:

---

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

Code:

---

In 'OutputDifferences()', at/about line 39:
'Dim eCompareType As VbCompareMethod
Dim eCompareType As Integer

---

...at/about lines 68-75:

Code:

---

'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...:motz2:

Mark

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!

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!

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

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,

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:

Code:

---

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

---