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

6

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.

edited May 26, 2015 at 19:53

answered May 26, 2015 at 19:39

FreeMan

5,7092 gold badges32 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Siddharth Rout Over a year ago

++ For beating me to it :)

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

5

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

edited May 26, 2015 at 19:44

answered May 26, 2015 at 19:38

Maciej Los

8,6211 gold badge22 silver badges36 bronze badges

Comments

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

4

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

edited May 26, 2015 at 19:53

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

answered May 26, 2015 at 19:40

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Collectives™ on Stack Overflow

Function with Optional parameter

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related