Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to test optional parameter ?

    How do I determine whether an optional parameter was passed to a VBA/Excel 97 function ? The function below is supposed to look up a value in the previous sheet (default=same address). This works and will allow me to make a "worksheet chain" with little to no adaptations.

    However, the second parameter is causing problems, see code. How to solve this ? Or is there a much simpler work-around than this, kind of "Offset accross worksheets" ?


    Function ValPrevSheet(Optional SourceAddr As String, Optional InitVal As Variant) As Variant

    Dim SheetNo As Integer, TargetAddr As String

    With Application.Caller 'cell which called function
    TargetAddr = .Address
    SheetNo = .Parent.Index 'determine its worksheet _number_
    If SourceAddr = "" Then SourceAddr = TargetAddr 'define default
    End With

    'Debugging only
    MsgBox "Sheet " & SheetNo & " at " & TargetAddr _
    & " to have value of previous sheet at " & SourceAddr

    If SheetNo = 1 Then 'No previous sheet
    'See whether the default is given in the callup, if not ask for it
    'If IsEmpty(InitVal) = True Then 'does not work, why ??
    'If Len(InitVal) = 0 Or Len(InitVal) = Null Then 'does not work either
    If InitVal = "" Then 'does work but "" has to be passed, arghhhh...
    MsgBox "Killroy was here" 'Debugging only,
    ValPrevSheet = InputBox("Indicate init value for first sheet:", "ValPrevSheet")
    Else
    ValPrevSheet = InitVal
    End If
    Else
    ValPrevSheet = Worksheets.Item(SheetNo - 1).Range(SourceAddr).Value
    End If

    End Function

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to test optional parameter ?

    Sorry, the indents where not taken

  3. #3
    Star Lounger
    Join Date
    Dec 2000
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to test optional parameter ?

    The way I've always tested for the existence of optional arguments (parameters) is to use the IsMissing function.

    If IsMissing(NameofArg) = True Then ...

    The only drawback to using IsMissing is that you must make all the optional arguments Variants because it *only* works with the Variant data type.

    Stephan

  4. #4
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to test optional parameter ?

    There's a workarounf d for this in the help for "ismissing":

    Note IsMissing does not work on simple data types (such as Integer or Double) because, unlike Variants, they don't have a provision for a "missing" flag bit. Because of this, the syntax for typed optional arguments allows you to specify a default value. If the argument is omitted when the procedure is called, then the argument will have this default value, as in the example below:

    Sub MySub(Optional MyVar As String = "specialvalue")
    If MyVar = "specialvalue" Then
    ' MyVar was omitted.
    Else
    ...
    End Sub

    In many cases you can omit the If MyVar test entirely by making the default value equal to the value you want MyVar to contain if the user omits it from the function call. This makes your code more concise and efficient.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to test optional parameter ?

    To keep indents, use the "pre" tags.

    The trouble with those though, is that if you copy code from the forum straight into VBA, the "soft returns" which the PRE tags give you are not inserted.

    You can put code into a ".txt" and attach it to the message- that would give you the best of both worlds

    Or you could go through and change leanding spaces with the non-breaking space character
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  6. #6
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 28 Times in 27 Posts

    Re: How to test optional parameter ?

    Just on the "pre" tags topic:

    I found the same problem with copying pretagged code into the VBE, but found a simple workaround was to paste it into a plain Word document first, then copy that, and then paste it into the VBE - all the indents etc. then come in fine.

    The most frequent problem I get with the pre tags are they sometimes omit plus symbols "+" - kind of a problem!

    Attaching the .txt files seems like a good idea - will have to try that one.

  7. #7
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to test optional parameter ?

    That was Elaine's idea to attach a text file- see my post in the Word forum for an example.

    Aother way might be like this:
    Option Explicit
    Sub main()
    Dim lPos As Long
    Dim sText As String
    Dim dlg As Dialog
    sText = ActiveDocument.Sections(2).Range.Text
    lPos = InStr(sText, vbCr)
    If lPos = 0 Then
    lPos = InStr(sText, vbLf)
    End If
    If lPos > 0 Then
    sText = Mid$(sText, 1, lPos - 1)
    End If

    sText = ActiveDocument.BuiltInDocumentProperties(1) & "- " & sText
    If Len(sText) > 60 Then
    sText = Mid$(sText, 1, 60)
    End If
    Set dlg = Dialogs(wdDialogFileSaveAs)
    With dlg
    .Name = Trim$(sText)
    .Show
    End With
    End Sub


    Where I used Word to change all occurrences of 2 adjacenet spaces with 2 non-breaking spaces before pasting in.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  8. #8
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to test optional parameter ?

    Thanks to all who responded up so far. I could use the IsMissing but, as it was pointed out to me, there is the limitation of having make all optional parameters a variant. IMO comparing to a "specialvalue" i.e. a default can not be used _here_ because how do I set the default to "Zilch ?" (i.e. nothing at all, not even a "").

  9. #9
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to test optional parameter ?

    cri,

    As far a I can tell, you can't. If you have string parameter, and it's not specified, it gives you a blank string, not a null.

    Can you use the variant though? You can use the syntax:

    "if Typeof varName is String then"

    after having checked the IsMissing, to check you have the datatype.

    Not being aware of what the problem you're trying to solve, I can't see if I'm really addressing your problem- sorry if I've missed your point
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to test optional parameter ?

    It's perfectly acceptable to set the default of a variant argument to Null like this:

    Optional varNew As Variant = Null

    In answer to your original post, IsEmpty only works when a variable hasn't been initialized. With a variant, if you don't specify the default, you have to test for IsMissing AND IsNull, since they could have either ignored the argument or passed a Null. All other variable types return their inherent default value or the value you set (i.e., "SpecialValue") if the user doesn't pass a value.

    BTW, another way to test for null values is like this:

    If Len(varNew & "")>0 Then
    Charlotte

  11. #11
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to test optional parameter ?

    Thanks to your help the whistles and bells of my code now work too:

    'See whether the default is given in the callup, if not ask for it
    If IsMissing(InitVal) = True Then
    ValPrevSheet = InputBox("Indicate init value for first sheet:", "ValPrevSheet")
    Else

    My code is intended as workaround for a missing "Offset accross worksheets" or a kind of "Relative link". Reason is I have a workbook with 12 worksheets, one for each month, which pass several values to the next month. To make the annual update easier and safer I want to be able to copy the revised sheet 11 times. Regular external links would have to be adapted or updated by VBA.

    gwhitfield, sorry if my last remark came across as complaint, it was not meant this way.

  12. #12
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to test optional parameter ?

    cri,

    I'm glad it's working as you intended.

    Thanks- but no, it didn't come across as a complaint at all. I just had a feeling I was missing your point, that's all. Sorry.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  13. #13
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to test optional parameter ?

    One thing to add:

    Should you use this function, you _might_ need to add

    Application.Volatile

    to at the beginning of the code above to enforce the recalculation of the function calls. As rule of thumb check whether the target cell also is dependent from cells with calculated values and/or manual input, otherwise nothing short of Ctrl+Alt+F9 will cause the cell to refresh. As I was burnt by volatile functions once, I made a complementary Sub (triggered by the Workbook_SheetChange event) which _forwards_ the value (option links) of any monitored cell to the next worksheet.

    Again, my thanks to all who responded.

Posting Permissions

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