getting an error pop-up window on optional argument in UDF VBA

Question

I'm getting a pop-up error window while trying to use an optional argument on a user defined function. The UDF have 3 arguments, first and third arguments are required while the second one is the optional one. the code is similar to this

function test (p1 as string, optional p2 as string="", p3 as string) as boolean
...
test=true
End function

The moment I finish the function statement an error window pops-up with the following message

Compilation error:

It was expected: Optional

Pointing to the p3 argument.

Although the fast way is to move the optional argument to the third one it is not very user friendly for my udf and I am guessing if there is any other approach to solve this issue or if I am getting this error because there is something I have to set up in my configuration

Thank you

Answer 1

Change the function declaration to

Function test (p1 As String, p3 As String, Optional p2 As String="") As Boolean

You can't have compulsory arguments following optional arguments