+ Reply to Thread
Results 1 to 4 of 4

Compile error: Expected Sub, Function, or Property

  1. #1
    Cloudfall
    Guest

    Compile error: Expected Sub, Function, or Property

    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


  2. #2
    Dave Peterson
    Guest

    Re: Compile error: Expected Sub, Function, or Property

    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

    ?????


    Cloudfall wrote:
    >
    > 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

  3. #3
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    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 Function

  4. #4
    Cloudfall
    Guest

    Re: Compile error: Expected Sub, Function, or Property

    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 =3D
    CorrectABNDigits(Range("a2").V=ADalue) " 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.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1