Optional Parameter

[KenWK]

I've written a function that uses an optional parameter defined as VbMsgBoxStyle but can't find a way to detect when the parameter is NOT used.

I thought simply doing a "if param = vbEmpty" would work but vbEmpty seems to be the same thing as zero, which is a valid value for that parameter!

Seems like there should be a way to do detect whether the parameter was used at all.

What am I missing?

Thanks for the help,
Ken

 

[gmmastros]

You can use the IsMissing function to test for missing parameters. Unfortunately, the data type of the parameter MUST be a variant.

To see what I mean, start a new vb project. Put a button on the form named Command1. Then, copy paste this code.

Option Explicit

Private Sub Command1_Click()

    Call TestEmptyParameter("Blah")
    Call TestEmptyParameter [green]' Only this one reports missing parameter[/green]

    Call TestEmptyLongParameter(1)
    Call TestEmptyLongParameter

End Sub

Private Sub TestEmptyParameter(Optional ByVal ParamValue As [!]Variant[/!])
    
    If IsMissing(ParamValue) Then
        MsgBox "Parameter is not passed in"
    Else
        MsgBox "Parameter exists"
    End If
    
End Sub

Private Sub TestEmptyLongParameter(Optional ByVal ParamValue As [!]Long[/!])
    
    If IsMissing(ParamValue) Then
        MsgBox "Parameter is not passed in"
    Else
        MsgBox "Parameter exists"
    End If
    
End Sub

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[KenWK]

Thanks very much George, hadn't heard of that little ditty.

I was using the VbMsgBoxStyle definition just as an easy way of getting the values while writing the code. I can easily make it a variant and just hit Ctrl + J to bring up the options.

Thanks again,
Ken

 

[gmmastros]

There is something else you could do...

Public Sub Blah(Optional ByVal MessageBoxStyle As VbMsgBoxStyle [!]= -1[/!])
    
    If MessageBoxStyle [!]= -1[/!] Then
        MsgBox "Parameter missing"
    Else
        MsgBox "Parameter exists"
    End If
    
End Sub

All of the vbMsgBoxStyle's are positive numbers. By setting a default value = -1, you can check to see if the parameter was passed in. There are pro's and con's here.

The benefit is that you will get intellisense when calling the procedure. The problem is... even though there is intellisense, you can still code in any number and the code will compile, but may not run correctly.

Personally, I love intellisense, and would be willing to live with the 'con' in this case.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom