2

This is a followup question to this:

VB - How do I test if optional arguments are supplied or not?

If my input type is a string (not a variant), is there any way to check if it was missing? The best I found so far is to enter a very unlikely input as my default.

Function func (Optional arg As String = "VeryUnlikelyInput")

    If arg = "VeryUnlikelyInput" Then
        MsgBox "NOT SENT"
    End If

End Function
Community
  • 1
  • 1
waternova
  • 1,472
  • 3
  • 16
  • 22

2 Answers2

4

Don't supply a default value at all. Then the missing argument will become a null pointer:

Function IfDefault(Optional s As String) As Boolean
    IfDefault = (StrPtr(s) = 0)
End Function

? IfDefault()
> True

? IfDefault("aaaa")
> False

? IfDefault("")
> False

Note that you can still fool it by passing in vbNullString, TextBox.Value of an empty TextBox on a form, or InputBox() if the dialog is cancelled. Application.InputBox() doesn't have this problem of course, since it returns False when cancelled.

Chel
  • 2,593
  • 1
  • 18
  • 24
  • 1
    Note: An `InputBox` will pass in a null pointer if you hit "Cancel". – waternova Aug 22 '14 at 23:39
  • This works in the IDE. If you put `=IfDefault()` in a cell in a Excel worksheet (the OP is explicitely tagged the question with `excel-vba`) it will return `False` as Excel passed an empty string to the function in this case. To make it work correctly you'll have to supply a default value of `= vbNullString` (at least in Excel 365) – Stef Dec 23 '19 at 10:46
0

If this is for user-entered text use characters that cannot be typed on the keyboard like "¤Þ".

Function func (Optional arg As String = "¤Þ")
    If arg = "¤Þ" Then MsgBox "NOT SENT"

End Function
Ross McConeghy
  • 874
  • 2
  • 7
  • 16