Ariel Constenla-Haile wrote:
> 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.
>
>
That's brilliant Ariel. The explanation is crystal clear. So when I run the
macro from the IDE the argument from the speadsheet, (say, Cell A2 is
highlighted in the SS with the value =fixsort(A1)), the value of A1 doesn't
get passed to the function fixsort when that function is called in the IDE
and the Parameter is not Optional error appears. It's so clear when you
explain it like that :-). I hope you write technical manuals for a living
because you have a very obvious talent in that area.
Thanks again. I learn slowly but I do learn.
Jonathan
--
Registerd Linux user #445917 at http://counter.li.org/
Please do not send me copies of list mail. I read the lists. Thanks!
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
