Hi Jonathan
Jonathan Kaye escribió:
The mysterious "argument is not optional" error
was "solved" by not choosing to the run the macro from the IDE
Organize Macros--> select the relevant macro and click the Run button)
but rather running them from the spreadsheet. I have no explanation
for this behaviour but at least I can continue using the
run-from-spreadsheet-and-not-IDE" method. The same macro throws up the
error in the former method but not the latter.
there is no mystery in that error. Something very basic:
A procedure (subroutine or function) may perform repeated tasks like the
one you are trying, it will performs these tasks on different
information every time you call it. This information is the data that
you pass to the procedure.
To pass this data to the procedure, the procedure defines a *parameter*,
and the caller passes an *argument* to that parameter.
Sub DummyTask
Dim i%
For i=0 To 10
doRepetitiveTask( CStr(i+1) + " times called" )
Next
End Sub
Sub doRepetitiveTask(aString as String)
aString = "argument passed: " + aString
MsgBox aString
End Sub
A parameter is a value that the procedure expects you to pass when you
call it, and parameters are defined in the procedure's declaration.
When you declare a Function or Subroutine, you specify a list of
parameter[s] right after the procedure name enclosed within "(" and ")".
For each parameter you specify a name, a data type, a way in which the
argument is passed (if by reference or by value [the ByVal keyword; see
Office Help: Macros and programming - Command Reference - Using
Procedures and Functions ]).
You can also indicate that a parameter is *optional* using the Optional
keyword before the parameter name. This means that the caller does not
have to pass a value for it.
Sub Test1(aString as String)
MsgBox aString
End Sub
Sub Test2(Optional aString as String)
MsgBox aString
End Sub
If you execute Test1 directly you will get the error #449 "Argument is
not optional" [look for "debugging Basic programs" in the Help index].
Test1 declares a parameter named aString, of type String; this means it
*expects* you to pass a string argument when you call it. If you just
run it, you will get the error "Argument is not optional" because you
are not passing the expected argument, in fact you are not passing any
argument at all. So "Argument is not optional" means you *must* pass an
argument when you call it, as the parameter is not declared to be
*Optional*.
If you try to run directly Test2, you won't get this error because the
parameter is declared as *Optional*, i.e. you have the option not to
pass an argument when you call Test2.
If you debug Test2, you'll see that the IDE indicates aString=<missing
parameter>. You can check if a parameter is missing (i.e. when the
procedure is called, no argument was passed for that parameter) using
the IsMissing() function.
Sub DummyTask
Dim i%
For i=0 To 10
doRepetitiveTask( CStr(i+1) + " times called" )
Next
doRepetitiveTask( CStr(i+1) + " times called", "Now with a title" )
End Sub
Sub doRepetitiveTask(aString as String, Optional aTitle as String)
aString = "argument passed: " + aString
If IsMissing(aTitle) Then aTitle = "Missing title"
MsgBox aString, 64, aTitle
End Sub
Hope that solves the mystery
Regards,
Ariel.
--
Ariel Constenla-Haile
La Plata, Argentina
[EMAIL PROTECTED]
[EMAIL PROTECTED]
http://www.ArielConstenlaHaile.com.ar/ooo/
"Aus der Kriegsschule des Lebens
- Was mich nicht umbringt,
macht mich härter."
Nietzsche Götzendämmerung, Sprüche und Pfeile, 8.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
