+ Reply to Thread
Results 1 to 7 of 7

Optional declared argument is not optional

  1. #1
    Forum Contributor
    Join Date
    09-20-2012
    Location
    Norge
    MS-Off Ver
    Excel 2007
    Posts
    114

    Optional declared argument is not optional

    Hi again guys,

    Here´s one for you:

    I declared 4 arguments and 3 and 4 are declared as optional but still they are required. The function will not run without them:

    Please Login or Register  to view this content.
    Other functions built the same way really do functional as optional and are not required.


    I keep getting the Run-time error '1004': Application-defined or object-defined error.

    My call is as follows:

    Please Login or Register  to view this content.

    Any ideas?

    P.S. The debug will stop at the What3 line in the function and point out that the What3 value is missing....

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Optional declared argument is not optional

    You need to check if 3 & 4 are empty before they are used.
    If what3 is empty then
    Sheets("Sheet1").range(What3).Copy ---> Sheets("Sheet1").range("").Copy --> =error
    Ben Van Johnson

  3. #3
    Forum Contributor
    Join Date
    09-20-2012
    Location
    Norge
    MS-Off Ver
    Excel 2007
    Posts
    114

    Re: Optional declared argument is not optional

    Hi protonLeah,

    I´m not following you here. Isn´t it supposed to be empty when it is optional and not being used?

    I have this functional with many more optional arguments and this one does work if I do not use the optional arguments. I just don´t see the difference....

    Please Login or Register  to view this content.
    And I call it with for example:

    Please Login or Register  to view this content.

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Optional declared argument is not optional

    In the line:
    Sheets("Sheet1").range(What3).Copy , Range() requires an address, it cannot be empty. Therefore you must modify your code to handle conditions where what3 & 4 are missing. You can use an IF..Then condition or, you could use an ON ERROR RESUME NEXT before those two lines.

    In your second example:
    Please Login or Register  to view this content.
    You are using Cel2 three different times. The function (as posted) never uses Cel3 & 4.
    If you had:
    Please Login or Register  to view this content.
    And called it with:
    Please Login or Register  to view this content.
    The last two lines would error out because, again you would have .Range("")

  5. #5
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Optional declared argument is not optional

    You could fix it by making all the arguments optional

    Please Login or Register  to view this content.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Optional declared argument is not optional

    Please Login or Register  to view this content.
    Cheers
    Andy
    www.andypope.info

  7. #7
    Forum Contributor
    Join Date
    09-20-2012
    Location
    Norge
    MS-Off Ver
    Excel 2007
    Posts
    114

    Re: Optional declared argument is not optional

    Thanks guys, that solved it

    protonLeah: That did the trick, but I noticed that the insert row statement above the What3 would off course run before the error, eventually causing many empty rows in my destination sheet.

    Both mikerickson and Andy Pope´s solutions fixed that last part too.


    Thank you all guys, you´re the best!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. 'Argument not optional'
    By swoop99 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-15-2011, 03:27 PM
  2. Please help with 'Argument Not Optional' error.
    By rkjudy in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-09-2011, 01:49 AM
  3. Argument Not Optional
    By mccrimmon in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-14-2011, 12:59 PM
  4. Don't understand why I'm getting an argument not optional
    By Brett Smith in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-08-2006, 10:10 AM
  5. [SOLVED] optional argument in a function
    By visitor in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-13-2005, 03:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1