none
Access 2013 -VBA Function - Error - Expected Line number or statement at end of statement

    Question

  • Hi all,  I am getting the error that is in the thread title and cannot work out what the issue is.

    I am just re-entering the world of vba after about 10 years of absence so this may be an obvious fix I am overlooking.

    I am writing a custom function for an Access database.  I am using Access 2013 on a windows 7 64 bit OS.

    here is the function code in its entirety

    Public Function Q308A_Agg_Calc(M1 As Double, M2 As Double, B As Double, M As Double)    
        ma = ((M2 - M1) * (100 - B)) / 100    
        Q308A_Agg_Calc = 100 - ((100 * M) / ma)
    End Function

    When I run it in the immediate window I get the error message

    compile Error:

    Expected: Line number or statement at end of statement

    What am I doing wrong?

    Thankyou

    kezzla

    Wednesday, February 06, 2013 1:29 AM

Answers

  • You didn't declare the variable ma. But this should result in another error not the one you mention. Maybe your statement in the immediate window was not correct. You have to proceed it with a ?, for example:

    ? Q308A_Agg_Calc(10, 5, 1, 1)

    To declare the variable add following line to your code:

    Dim ma As Double

    The code then should look like this:

    Public Function Q308A_Agg_Calc(M1 As Double, M2 As Double, B As Double, M As Double)
      Dim ma As Double
      ma = ((M2 - M1) * (100 - B)) / 100
      Q308A_Agg_Calc = 100 - ((100 * M) / ma)
    End Function

    Also ensure you have the

    Option Explicit

    in the header of your module.

    If this doesn't solve your error then it is originated from somewhere else not from this function. In this case try Menu Debug - Complile to find where the error is generated. It looks for me more like a Compiler error with a statement somewhere else that is generated by the Just-In-Time Compiler. In this case the line drawing the error should be marked in the VBA editor.

    Henry

    • Marked as answer by Kezzla Wednesday, February 06, 2013 5:17 AM
    Wednesday, February 06, 2013 3:22 AM

All replies

  • You didn't declare the variable ma. But this should result in another error not the one you mention. Maybe your statement in the immediate window was not correct. You have to proceed it with a ?, for example:

    ? Q308A_Agg_Calc(10, 5, 1, 1)

    To declare the variable add following line to your code:

    Dim ma As Double

    The code then should look like this:

    Public Function Q308A_Agg_Calc(M1 As Double, M2 As Double, B As Double, M As Double)
      Dim ma As Double
      ma = ((M2 - M1) * (100 - B)) / 100
      Q308A_Agg_Calc = 100 - ((100 * M) / ma)
    End Function

    Also ensure you have the

    Option Explicit

    in the header of your module.

    If this doesn't solve your error then it is originated from somewhere else not from this function. In this case try Menu Debug - Complile to find where the error is generated. It looks for me more like a Compiler error with a statement somewhere else that is generated by the Just-In-Time Compiler. In this case the line drawing the error should be marked in the VBA editor.

    Henry

    • Marked as answer by Kezzla Wednesday, February 06, 2013 5:17 AM
    Wednesday, February 06, 2013 3:22 AM
  • You should also declare what type of value the function should return. I think it defaults to Variant, but it's better practice (less bugs get created) if you declare properly, so use:

    Public Function Q308A_Agg_Calc(M1 As Double, M2 As Double, B As Double, M As Double) as Double


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Wednesday, February 06, 2013 3:49 AM
  • Thanks mate!  I've been programming in another variant of basic that allows local variable declaration in the manner I had written in my function.

    I was also using the incorrect statement in the Immediate window '=' instead of '?' which was the cause of the specific error message in the thread title.

    I declared it correctly and used the correct statement in the Immediate window and the function now works.

    I have also added the return data type to my function as suggested and will continue to do so.

    Thankyou for your help guys.  It is greatly appreciated.

    Kezzla

    Wednesday, February 06, 2013 5:26 AM
  • Ok, thanks for the advice, I will Declare my function return data type from here on in.

    cheers mate.

    kezzla

    Wednesday, February 06, 2013 5:27 AM
  • Just to ensure you are always using variable declaration:

    Open the VBA editor, open the Tools menu and in there open the open the Options submenu. Then tick the option "Require Variable Declaration". This will add "Option Explicit" to all new moduls. In all the existing modules add this option on the top just below "Option Compare Database"

    And another hint. Your function will draw a Devide by Zero error if the parameters are unfortunate. You should catch therefore the errors in your function and eiter return NULL or a value, that you can accept in this case. This also means you should declare your functions datatype not to Double but to Variant.

    Finally: If you want to use this function as user defined function in a query where the parameters are based on fields of a table then they may be NULL and the function call will result in #ERROR# as you can't pass Null to a Double variable. You in this case would have to use Variant as input parameters and ensure in your calculation it's converted to Double by using 100# instead of 100 (or use 100.0 which is a double and will become converted automatically to 100# in the VBA editor). This way you ensure the return value is a Variant of Subtype Double with the required precision.

    For me the function would look like this:

    Public Function Q308A_Agg_Calc(M1 As Variant, M2 As Variant, B As Variant, M As Variant) As Variant
    On Error GoTo PROC_ERR
      Dim ma As Double
      If Not IsNumeric(M1) _
        Or Not IsNumeric(M2) _
        Or Not IsNumeric(B) _
        Or Not IsNumeric(M) Then
        Q308A_Agg_Calc = Null
      Else
        ma = ((M2 - M1) * (100# - B)) / 100#
        Q308A_Agg_Calc = 100# - ((100# * M) / ma)
      End If
    PROC_EXIT:
      Exit Function
    PROC_ERR:
      Q308A_Agg_Calc = Null
      Resume PROC_EXIT
    End Function

    HTH

    Henry

    Wednesday, February 06, 2013 6:52 AM