Problem using Oorang's worksheet compare on a Mac

[RonMcK]

I am attempting to run Oorang's worksheet compare program (See KB Article 966 (http://vbaexpress.com/kb/getarticle.php?kb_id=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,

[GTO]

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

[RonMcK]

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,

[GTO]

Hi Ron,

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

Mark

[RonMcK]

Mark,

No problema, we had to try something.

Cheers,

[Oorang]

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

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

Mark

[RonMcK]

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!

[RonMcK]

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!

[Oorang]

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

[RonMcK]

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,

[Oorang]

I think that is a partial path. Try doing this:

Set a reference to "Microsoft Visual Basic for Applications Extensibility"
Go to tools>macro>security and check "Trust Access to Visual Basic Project"
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