Function with Optional parameter

Question

I am trying to learn and implement a Function in VBA with an optional argument. I am trying to seperate values in a string given a character or substring to seperate.

Private Function SeperateString(MainString As String, Seperator As String) As String { // Body }

Eg: I want to seperate "1-2-3-4-5" using seperator "-" or "1/2/3/4/5" using seperator "/".

I will write the logic using Instr and other functions. My doubt is I want to pass a seperator so I don't need to write a seperate function for each and want "-" to be default if nothing is passed. Can I do that?

FreeMan · Accepted Answer · 2015-05-26 19:53:02Z

Private Function SeparateString(MainString as String, Optional Separator as String = "-") As String

Oh, and this is VBA. No {} and no //, but I'm sure you know that...

I would have to suggest, however, that using Split() would likely be a better option than writing your own.

Maciej Los · Accepted Answer · 2015-05-26 19:44:04Z

Assuming, you want to set Separator as optional parameter, try this:

Private Function SeperateString(MainString As String, _ Optional Seperator As String = "-") As String 'Body SeperateString = "result of function" End Function

Usage:

result = SeperateString("whatever-it-is") 'uses default separator result = SeperateString("whatever-it-is", "*") 'uses another separator

https://msdn.microsoft.com/en-us/library/sect4ck6.aspx

Mathieu Guindon · Accepted Answer · 2015-05-26 19:53:56Z

You are working in VBA and not C# so do away with those curly braces.

The moment you type the first line and press Enter, the VBE will automatically add End Function

Private Function SeperateString(MainString As String, Seperator As String) As String End Function

To make a parameter optional, use the keyword Optional before it

Example

Private Function SeperateString(MainString As String, Optional Seperator As String = "-") As String End Function

Collectives™ on Stack Overflow

Function with Optional parameter

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related