Results 1 to 6 of 6
  1. #1
    Join Date
    May 2005
    Posts
    119

    Unhappy Unanswered: VBA paramaters throw compile error - expect line number of statement or end of stmt

    I used to be a pro at Access VBA and writing code; but haven't done it in a while... I know this is something stupid and I'm having a brain fart; but would someone please help me figure out why I'm getting this error message when I call the function? It works fine when I take out the parameters.
    Click image for larger version. 

Name:	Capture.JPG 
Views:	10 
Size:	65.8 KB 
ID:	16658
    Code:
    Option Compare Database
    Option Explicit
    
    
    Public Function addzeros(nlength As Integer, sValue As String) As String
    'Dim svlaue As String
    'Dim nlength As Integer
    Dim i As Integer
    i=1
    'sValue = "123"
    'nlength = 6
    stemp = sValue
    If Len(sValue) < nlength Then
    Do Until i = (nlength - Len(sValue))
            stemp = "0" & stemp
        i = i + 1
        Loop
    End If
    addzeros = stemp
    Debug.Print stemp
    End Function
    Last edited by Missinglinq; 12-05-15 at 15:28. Reason: Correcting Code formatting

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    should that not be
    Code:
    ? addzeros (6, "123")
    assuming you want to test the function

    if you try to assign the value then
    Code:
    myvar =  addzeros (6, "123")
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2005
    Posts
    119
    First, thank you for helping me. Much appreciated!

    Yes, I'm testing the function. It works fine if I take out the paramaters and just do =addzeros, so it's got to be something with the call or the parameter names or something..

    I changed it to both myvar = addzeros (6,"123") and ? addzeros(6,"123") and received "compile error: Expected variable or procedure, not module"

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Thats odd
    I suspect you may yave a corrupt db, or possibly other compilation errors encouraging Access to throw a wobbler.

    As you have declared option explicit it should throw an error as stemp is never declared.
    Suggest you try to compile the code BEFORE running, clear any faults and try again
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think you can simplify the function using soemthing like:-

    Code:
    Option Compare Database
    Option Explicit
    
    'function pad a string (sValue) with leading zeros IF its length is less than nLength characters
    ' so addzeros(6,"123") will return 000123, addzeros(6,"ABCDEFGHI") will return ABCDEFGHI
    Public Function addzeros(nlength As Integer, sValue As String) As String
    'not going to worry overmuch about validating the inputs
    addzeros = sValue ' can use the intrinsic string variable addzeros
    Do Until Len(addzeros) >= nlength 
        addzeros = "0" & addzeros
        Debug.Print 'addzeros is now :' & addzeros
    Loop
    Debug.Print addzeros
    End Function
    a suggestion ALWAYS, ALWAYS comment your function (what they do, what the inputs are what the expected outcomes are
    if your code does, or doesn't do something you'd expect then state so as a comment. ferrinstance Ivge stated int he above that Im not going to worry about validating the inputs.... why well the first thign I look for with user supplied values in a functions is that the inputs are sane.
    there's feck all point in commenting a line of code if by reading tghe code you can see what it does
    so I'd argue
    Code:
        addzeros = "0" & addzeros ' pad a zero in front of the existing value of addzero
    .. its doens't ell you anything you cant already read from the code

    Code:
    ' can use the intrinsic string variable addzeros
    I wouldnt;' normally include that as a comment but its here for yo0u and others to pickup on

    why comment...
    well you don't know when you (or someone else) may pick up this chunk of code in the future. by documentign wehat it does and anythign that was going on in your mind may help you work out why it isn't working now, or how to call it from another form/report/query/module etc...
    goo comments also help in debugging the code. if you explicitly write what you expect in your antiuve language as well as computer code it can often be easier to spot where the code diverges from what you say you want to do. yopu can alwasy strip out dubious comments alter on...
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could use:
    Code:
    Public Function AddZeros(nLength As Integer, sValue As String) As String
    
        Dim stemp As String
        
        stemp = sValue
        Do Until Len(stemp) >= nLength
            stemp = "0" & stemp
        Loop
        AddZeros = stemp
        Debug.Print stemp
        
    End Function
    However, there is a more efficient way of doing the same:
    Code:
    Public Function AddZeroes(nLength As Integer, sValue As String) As String
    
        AddZeroes = Format(sValue, String(nLength, "0"))
        
    End Function
    Have a nice day!

Posting Permissions

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