What causes "Named Argument Already Specified" error in Excel VBA?

This is the start of the code I have written up in Excel VBA but I don't understand开发者_开发百科 why I keep getting a compile error (VBA is pointing to the last line of code as the source of error with "Named argument already specified"). There are so many smart programmers here, so could anyone please inform me what exactly is wrong (and modify the code)? I am in the early stages of learning VBA so more explanation and guidance on what I have coded up incorrectly would be really helpful to me.

Thank you very much.

''''''''''''''''''''''''''''''''''''''''''''

Dim outputbook, sourcebook As Workbook

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


      'Firstly creating new worksheet
     Set sourcebook = ActiveWorkbook
     Set outputbook = Workbooks.Add
     outputbook.SaveAs , Filename:="Output" & " " & Format(Now(), "dd_mm_yyyy_hh_mm_AMPM") & ".xlsx"



     ''''''''''''''''''''''''''''''''''''''''''''''''''''''
     Dim analysisbook As Workbook
     '''''''''''''''''''''''''''''''''''''''''


    'Create another new workbook where analysis will be performed
    Set analysisbook = Workbooks.Add
    analysisbook.SaveAs , Filename:="analysis" & " " & Format(Now(), "dd_mm_yyyy_hh_mm_AMPM") & ".xlsx"

---

You're going to think this is silly, but it will fix your problem...

You need to remove the comma after the call to the SaveAs method and before the Filename named parameter.

So the very last line of your code should look like this:

analysisbook.SaveAs Filename:="analysis" & " " & Format(Now(), "dd_mm_yyyy_hh_mm_AMPM") & ".xlsx"

You only have to use a comma if you're providing more than one named argument. Since you only provide one (Filename), there is no need for a comma, and that's causing you to get the (somewhat cryptic) "Named argument already specified" compile-time error. Excel thinks that you missed providing a named argument in the beginning, before the comma. Take that out, and it will realize that you only meant to include one.

---

EDIT: The exact meaning of parenthesis in method calls, and when they're required, is something that's confusing to even long-time VBA users. It's relatively complicated, so let me see how well I can do at explaining it. (The same rules also apply to pre-.NET versions of Visual Basic, like VB 6.)

The simple rule is that when you're assigning the result of a function to a variable or another object, you always enclose the argument list in parentheses. So, if I had a function named GetAge that returned a numeric value when I specified a first and last name, I would write the following:

Dim age As Integer
age = GetAge("John", "Smith")

Otherwise, it is never necessary to enclose the arguments in parentheses. This covers two possible cases. First, when you call a subroutine ("sub"), which never returns a value, and second, if you call a function (which always return a value), but are not assigning the value is returns to any variable or object. Essentially, when you are ignoring the value that it returns. For example:

''#Calling a subroutine
MsgBox "Hello World"

''#Calling the same function as above, but ignoring its return value
''# (this particular example is not very useful, but sometimes you'll do this)
GetAge "John", "Smith"

Alternatively, you can choose to call both subroutines and functions whose return value you are ignoring using the Call statement, which always requires wrapping the arguments in parentheses. I personally prefer this syntax, because it's more similar to other languages I work in, removes the ambiguity of when parentheses are required and when they're not, and makes calls to outside methods more explicit in your code. For the same two examples as given just above, the code would change to:

''#Calling a subroutine, but using the Call statement
Call MsgBox("Hello World")

''#Calling a function, but ignoring its return value
Call GetAge("John", "Smith")

And finally, just in case you thought you were getting all of that, there's one more possibility that you should understand. If you wrap the arguments to a subroutine or a function whose return value you ignore with parentheses and do not use the Call statement, VBA will interpret the arguments as being passed by value (ByVal) when they would otherwise normally be passed by reference (ByRef). When you write a method call this way, the compiler will automatically re-format it for you, demonstrating the difference in how it is interpreted:

''#The following line
MsgBox("Hello World")
''#will be re-formatted by the compiler to
MsgBox ("Hello World")

Notice the space that it added between the method name and the arguments enclosed in parentheses? That space indicates that the arguments are passed ByVal. Eric Lippert's blog post on a related question probably explains this particular issue better than I can.