Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts

    Why is this giving me a compile error?

    I have a module which I call from "workbook_Open". If it has one passed parameter, it compiles and works well. If it has 2, it gives me a compile error saying ":= expected".

    Any clues as to why?

    So, this works:
    copyfile_if_new_day(myfilename)

    and the subroutine is:

    Sub copyfile_if_new_day(Filetocopy)
    ...
    end sub

    and this fails:

    copyfile_if_new_day(myfilename, myfile_extension)


    Sub copyfile_if_new_day(Filetocopy, file_extension)
    '***
    '*** this routine copies the current file to one with todays date if one is not already there
    '***
    ...
    end sub

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Alan,

    Since this is a sub routine and not a function call it this way:
    copyfile_if_new_day myfilename, myfile_extension
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    You mean without any brackets?

    Why? because it's a subroutine? But why does it work OK with one passed parameterm but not two?

    Anyway, I've done it a different way now - I parse for the extension and extract the two params from the full filename (but I am still curious)

    Alan

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Alan,

    My guess, and it is only that, is that because using the () usually indicates you are calling a Function in VBA and since there is more than one parameter and parameters in Functions don't have to be in order VBA wants to know which you are passing first. I've attached my test file where only the last item in the Immediate Window caused the error you mentioned. HTH
    VBA-EXCEL-Parameters.JPG

    PassingParametersTest.xlsm
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Alan,

    When using a sub, use the parentheses only when using the call word. Both will call the sub and pass the parameters

    One parameter:
    copyfile_if_new_day myfilename
    Call copyfile_if_new_day (myfilename)

    Multiple parameters:
    copyfile_if_new_day myfilename, myfile_extension
    Call copyfile_if_new_day (myfilename, myfile_extension)

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You only use parentheses in three situations when calling a routine in VBA:
    1. When using the Call keyword
    2. When you are returning a value from a function
    3. When you are explicitly trying to de-reference a variable (and then the parentheses should only go round that variable)
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rory,

    Thanks for the clarification. However, could you please explain the term "de-reference a variable"? It has me stumped.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Sure - if you are passing a variable that is a data type, you pass a copy of the variable (equivalent to passing ByVal rather than ByRef). For example:
    Code:
    Sub testcall()
       Dim sOut As String
       sOut = vbNullString
       RunMe sOut
       MsgBox sOut
       sOut = vbNullString
       ' call same routine but de-reference the variable so we pass a COPY (i.e. byval)
       RunMe (sOut)
       MsgBox sOut
    End Sub
    Sub RunMe(sIn As String)
       sIn = "test"
    End Sub
    If you are passing an Object however, you pass the default property of the Object, assuming it has one, otherwise you get an error.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rory,

    Very interesting... I think I'll stick with using the explicit ByVal in the function/sub definition when that is what is intended as things get confusing enough w/o adding another thing to remember that isn't explicitly stated in the code...I'm just sayin'
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    It's not something I really use to be honest (though I suppose you could have a function that usually needs a ByRef parameter but for a particular case you don't want the input(s) altered) - I just thought I'd mention it as it's the reason for the errors that usually confuse people when they are passing objects using parentheses.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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