excelvbauser-defined-functionsoptional-parameters# getting an error pop-up window on optional argument in UDF VBA

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

Solution

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

- Build vertical summary from Columns with Count
- Excel - Create a Unit Conversion sheet - Multiple links
- update cell when column header matches a list
- Delete text in cell subject to Worksheet_Change
- Counting the number of visible rows after autofilter
- Excel 2007: AVERAGEIF, SUMIF, COUNTIF, MAXIF, MINIF across multiple sheets, multple rows
- Import CSV data from a txt file skipping the first line and adding headers
- Simplifying SumIFs formulas for efficient excel formula
- I'd like to find out how to find the current streak of non-negative numbers in a row of data in Excel
- ValueError: Invalid character found in sheet title
- decrypt excel files
- Excel table search funcion that looks for partial string match
- Combine macros to filter on today's and tomorrow's date
- Converting line breaks to commas in excel sheet using Powershell
- Combinations of numbers arranged side by side as many as a random number (VBA)
- How can I remove ONLY leading and trailing spaces while leaving spaces in between words alone with an excel formula?
- VBA Date as integer
- Sliding Window Auto Increment Range
- Count cells with different conditional ranges
- Turning flattened pivots data into tables -Error tables can't overlap - Excel Vba
- How do I get only a specific part of a cell in Excel or Numbers?
- Calculate the Legendre symbol of two integers in Excel
- Unable to get text wrapping or vertical centering to work with xlsxwriter
- Number stored as text warning in excel using POI
- VBA Excel paste to the columns in regular intervals
- Convert filtered values from formulas to values
- Sorting Dictionary by nested class objects data [VBA]
- Events does not appear when making an Outlook query from Excel
- The script works well when I use MSXML2.XMLHTTP.6.0, but it fails miserably when I switch to MSXML2.serverXMLHTTP.6.0
- How to convert text string (d h m s) to time format in excel