Compile error: Expected Sub, Function, or Property

[Cloudfall]

I don't program very much so I am prone to silly errors. I just can't
get the following subprocedure to call my funtion. I keep getting the
"Compile error: Expected Sub, Function, or Property" error message when
the program tries to execute the line:
CheckNumber = CorrectABNDigits(CellContents).
When stepping through the code, the function is highlighted after the
call, so it seems to recognise it, but then it won't step into it,
giving me the same error message over and over. What am I doing wrong?
Why isn't my function a function even if the debugger jumps to it?

I am trying to get the program to read the contents of a cell into
"CellContents", pass this string to the function "Function
CorrectABNDigits(CellContents As String) As Boolean", and then do some
checks on the string. But my sub won't call my function.

Any further comments / criticisms regarding the way I have programmed
this are welcome!

Sub ABNtidy()
Dim CellContents As String
Dim CheckNumber As Boolean

Range("E2").Select
CellContents = Selection.Value
CheckNumber = CorrectABNDigits(CellContents)
MsgBox (CheckNumber)
End Sub

Function CorrectABNDigits(CellContents As String) As Boolean
Dim MyCheck As Boolean

If Len(CellContents) = 11 Then MyCheck '11 characters in cell
CorrectABNDigits = MyCheck
End Function

 

[Dave Peterson]

This line looks like it's missing something:

If Len(CellContents) = 11 Then MyCheck '11 characters in cell

maybe:

If Len(CellContents) = 11 Then MyCheck = True '11 characters in cell

?????

 

[mudraker]

Cloudfall

in this line of code
If Len(CellContents) = 11 Then MyCheck

Excel appears to be treating MyCheck as sub routine or a function.

Try

If Len(CellContents) = 11 Then MyCheck = True


Another way to write your code is


Sub ABNtidy()
Dim CheckNumber As Boolean

CheckNumber = CorrectABNDigits(Range("a2").Value)
MsgBox (CheckNumber)
End Sub

Function CorrectABNDigits(CellContents As String) As Boolean
Dim MyCheck As Boolean

If Len(CellContents) = 11 Then MyCheck = True '11 characters in cell
CorrectABNDigits = MyCheck
End Functio

 

[Cloudfall]

To Dave Peterson and mudraker:

You were both spot on. For some reason I had thought that a declared
boolean variable would simply default to true in an "if...then"
statement if the "if" part was correct (I thought I remembered learning
this somewhere). I won't make this mistake again. I never expected an
error of this kind to generate the "Compile error: Expected Sub,
Function, or Property" error message. I thought it was talking about
"Function CorrectABNDigits(CellContents As String) As Boolean" when it
was talking about MyCheck (as mudraker helpfully pointed out above).
That's why the debugger did indeed jump to "Function
CorrectABNDigits(CellContents As String) As Boolean" but refused to
execute any of its code.

And mudraker's suggestion regarding "CheckNumber =
CorrectABNDigits(Range("a2").V­alue) " is far more elegant. I wasn't
aware you could do this. I don't know Excel VBA very well so I am
finding this group phenomenally helpful (including past answers to
other peoples' questions). I am now faced with a major programming
chore and I am trying to use good habits from the very beginning.

Thank you for your fast responses and I wish you both all the very
best.