Calling A Sub in VBA with multiple arguments [duplicate]

Question

I want to call sub with multiple argument but I got an error Syntax error

I tried it on Ms Excel 2013

Sub Withdrawal(Query As String, Savelocation As String)

    'Query Data
    Dim ReturnArray
    Dim Conn As New ADODB.Connection
    Dim mrs As New ADODB.Recordset
    Dim DBPath As String, sconnect As String
    DBPath = Application.ActiveWorkbook.Path + "\Data.xlsx"
    sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"
    Conn.Open sconnect
    mrs.Open Query, Conn
    ThisWorkbook.Worksheets(Savelocation).Range("A3").CopyFromRecordset mrs
    mrs.Close
    Conn.Close
End Sub

Sub CashWithdrawal()
    Dim CashWFYC As String
    Dim Location As String
    Location = "CashWithdrawal"
    CashWFYC = "SELECT TOP 10 * FROM [TT$]"
    sSQLSting = CashWFYC 
    Withdrawal(sSQLSting, Location)
End Sub

I expect the output that got from that sSQLSting query should be save in Location

Remove the brackets: Withdrawal sSQLSting, Location (FYI, this has come up many, many times before) — Rory
– Rory, Commented May 22, 2019 at 14:49
@Rory Thanks. Just found a dup for closure. I shouldn't have been so lazy. — QHarr
– QHarr, Commented May 22, 2019 at 14:53
Your actual code was Withdrawal (sSQLSting, Location), not Withdrawal(sSQLSting, Location). That space is the VBE telling you "this isn't an argument list, it's an expression I'm going to try to evaluate and pass ByVal as the first argument to the Withdrawal procedure". — Mathieu Guindon
– Mathieu Guindon, Commented May 22, 2019 at 16:10

QHarr · Accepted Answer · 2019-05-22 14:48:54Z

1

You should call without parentheses. Within parentheses is for function calls.

Withdrawal sSQLSting, Location

Please put Option Explicit at the top of your code and declare all your variables including with explicit type.

answered May 22, 2019 at 14:48

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kry Chulsa Over a year ago

By remove parantheses I got another error ByRef argument type mismatch

QHarr Over a year ago

Declare sSQLSting as string It means your receiving sub wants two strings so check both arguments being passed are strings. And put Option Explicit at the top of your sub.

Kry Chulsa Over a year ago

Thanks you so much. I found it I forgot it

QHarr Over a year ago

you are welcome. Sorry for closing the question abruptly on you. I should have done that immediately but wasn't really thinking.

Collectives™ on Stack Overflow

Calling A Sub in VBA with multiple arguments [duplicate]

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related