3

I am trying to write a VBA macro to AutoFilter a table using the users input. I need the user to indicate the columns to filter and also the Criteria for that field. Therefore I thought to define a Sub with multiple optional arguments(parameters) to take users preference. My problem is how to check if an optional argument is supplied?

I understand that I can write a test for each parameter and then write a conditional statement for each possible option. But that doesn't seem a smart way and am wondering if any one can suggest a solution. I should say that in the beginning we don't know what's the number of argument we expect to receive from the user.

Thanks for your responses.

Community
  • 1
  • 1
M.T
  • 105
  • 1
  • 7
  • Possible duplicate of [VB - How do I test if optional arguments are supplied or not?](http://stackoverflow.com/questions/1660772/vb-how-do-i-test-if-optional-arguments-are-supplied-or-not) – J. Chomel Apr 08 '16 at 12:59

2 Answers2

4

If the type of the variable is variant a solution would be to use IsMissing:

 If IsMissing(arg) Then
     MsgBox "missing parameter"
 End If

Then if you expect string or number, just check the value:

 Function func (Optional s as String, Optional n as Integer)

 If s = "" Then
     MsgBox "s is missing"
 End If

 If n = 0 Then
     MsgBox "n is missing"
 End
Jika
  • 85
  • 11
  • 1
    This is the only answer that works with an optional variant, which is required when you need the macro to be visible in the F8 menu. It is also the more correct answer as it doesn't require setting a default value. – HackSlash Oct 10 '17 at 21:34
0

A possibility would be to specify an absurd default. Then compare with the default.

Sub xyz( Optional p1 As String = "default_impossible_value")
    If p1 = "default_impossible_value" Then
       MsgBox "there_is_no_value_for_param_1"
    End If
End Sub

Then extend it to any number of parameter you wish as an entry:

Sub xyz( Optional p1 As String = "default_impossible_value"_
        ,Optional p2 As String = "default_impossible_value"_
        ,Optional p3 As String = "default_impossible_value"_
...
)
    If p1 = "default_impossible_value" Then
       MsgBox "there_is_no_value_for_param_1"
    End If
    If p2 = "default_impossible_value" Then
       MsgBox "there_is_no_value_for_param_2"
    End If
    If p3 = "default_impossible_value" Then
       MsgBox "there_is_no_value_for_param_3"
    End If
....
End Sub
J. Chomel
  • 8,193
  • 15
  • 41
  • 69
  • Thanks so much for the comment , very useful piece of code. My question is how can I know about the expected number of variables that may/may not be supplied. I try avoiding a huge range of variables ( combination of the columns and type of filters ) as P1,P2,P3,...I understand that I can resolve this issue by using VBA Forms as GUI and allow the user the make the variables that will be used for filtering but is there some way of doing it in a code with out the forms ? – M.T Apr 10 '16 at 01:17
  • I just tried answering the initial question. I am not very sure I understand what you are asking now. What do you mean _the expected number of variables_? Is this a `Sub` you wrote, or is it something related with the workbook, worksheet, anything else? – J. Chomel Apr 10 '16 at 07:37