Macro Compile Error

[David1300]

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I am new to this forum so please excuse any lack of forum etiquette. I am using excel 2004 for mac and I have a file that works fine on my other macs with a macro. On this mini mac of mine, I get a "compile error: Sub or function not defined" message after every input and subsequent return. It forces me into the macro debugger and highlights the following in yellow: "Private Sub Worksheet_Change(ByVal Target As Range)." I have to get out of the debugger in order to proceed.

I am not sure why on this mac with the same excel program I am having this issue while on other macs, the program works beautifully. The file is the same and I simply copied the file to the new mac when I first started. Any thoughts? Any settings of which I am not aware? I would like to learn why it happened and more importantly how to fix it. THX

 

[JE McGimpsey]

I am new to this forum so please excuse any lack of forum etiquette. I am
using excel 2004 for mac and I have a file that works fine on my other macs
with a macro. On this mini mac of mine, I get a "compile error: Sub or
function not defined" message after every input and subsequent return. It
forces me into the macro debugger and highlights the following in yellow:
"Private Sub Worksheet_Change(ByVal Target As Range)." I have to get out of
the debugger in order to proceed.

I am not sure why on this mac with the same excel program I am having this
issue while on other macs, the program works beautifully. The file is the
same and I simply copied the file to the new mac when I first started. Any
thoughts? Any settings of which I am not aware? I would like to learn why it
happened and more importantly how to fix it. THX

First thing I'd check is to make sure XL04 is fully updated on that
machine.

It's also possible that the module has become corrupted. You could try
selecting and copying (just) the macro, pasting it into TextEdit or
another text editor, selecting everything in the module and deleting it,
then copying the macro back.

If that doesn't work, you could try copying the sheet (after saving the
macro and deleting it from the workbook), then deleting the original and
copying the macro back to the worksheet code module.

 

[David1300]

J.E.,
Thank you for the advice. I tried everything you said and I am still having problems. I decided to go to another mac in which I am having no problems and sent it to this mac again via email. I opened the new file and this too has the same error. It is almost like the issue is related to my mac and my excel on this mac since the file works on other macs. Thoughts?

 

[JE McGimpsey]

J.E.,
Thank you for the advice. I tried everything you said and I am still having
problems. I decided to go to another mac in which I am having no problems and
sent it to this mac again via email. I opened the new file and this too has
the same error. It is almost like the issue is related to my mac and my excel
on this mac since the file works on other macs. Thoughts?

The easiest thing left to do is probably to remove and reinstall Office
on that Mac. Use the Remove Office application to remove it. Make sure
it's updated fully after reinstallation.

 

[David1300]

J.E.,
Thank you for the advice. I tried everything you said and I am still having
problems. I decided to go to another mac in which I am having no problems and
sent it to this mac again via email. I opened the new file and this too has
the same error. It is almost like the issue is related to my mac and my excel
on this mac since the file works on other macs. Thoughts?

The easiest thing left to do is probably to remove and reinstall Office
on that Mac. Use the Remove Office application to remove it. Make sure
it's updated fully after reinstallation.
[/QUOTE]

J.E,
I believe that I am missing something. now, my other mac reacts to this spreadsheet the same way that my mini does "Compile error: Sub or Function not defined"

My macros has the following:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2:I65536")) Is Nothing Then
Target.Interior.ColorIndex = 8
Cells(Target.Row, 10).Value = FormatDateTime(Date, 1)
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Is it something in my syntax that is now not working? I swear it was working for 3 months , no prob and then all of a sudden it is doing this first on one mac then on another. Thoughts?

 

[JE McGimpsey]

I believe that I am missing something. now, my other mac reacts to this
spreadsheet the same way that my mini does "Compile error: Sub or Function
not defined"

My macros has the following:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2:I65536")) Is Nothing Then
Target.Interior.ColorIndex = 8
Cells(Target.Row, 10).Value = FormatDateTime(Date, 1)
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Is it something in my syntax that is now not working? I swear it was working
for 3 months , no prob and then all of a sudden it is doing this first on one
mac then on another. Thoughts?

Ah...this is why it's always best to post your code.

FormatDateTime is a VBA6 function, used in WinXL00-07.

WInXL97 and all MacXL versions use VBA5, so FormatDateTime is undefined.

The easiest thing to do is just use something like:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2:I65536")) Is Nothing Then
With Target
.Interior.ColorIndex = 8
Me.Cells(.Row, 10).Value = Format(Date, "Long Date")
End With
End If
End Sub


Alternatively, you could build a conditionally compiled function:

#If Mac Then
Public Function FormatDateTime( _
dDate As Double, _
Optional nFormat As Long = 0) As Variant
Dim sFormat As String

If (dDate >= 0) And (dDate < DateSerial(9999, 12, 31) + 1) Then
Select Case nFormat
Case 0
sFormat = "General Date"
Case 1
sFormat = "Long Date"
Case 2
sFormat = "Short Date"
Case 3
sFormat = "Long Time"
Case 4
sFormat = "Short Time"
Case Else
'Invalid entry
End Select
End If
If Len(sFormat) = 0 Then
FormatDateTime = CVErr(xlErrNum)
Else
FormatDateTime = Format(dDate, sFormat)
End If
End Function
#End If

I keep this one in an add-in so that I can use it with any project.