Excel vba function optional input

I am calling a specific piece of code several times therefore I would like to use optional parameters. I can write something like:. Now, how can I clear the value of an optional parameter?

I will need this for some of the calculations. I'm not sure you really mean "optional". In your example, you're listing the args as optional, but you're still passing both arguments to the function.

Otherwise, if you are always passing both arguments, then it doesn't make sense to have them Optional. By posting your answer, you agree to the privacy policy and terms of service.

Stack Overflow Questions Developer Jobs Documentation beta Tags Users. Sign up or log in to customize your list. Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us.

Log In Sign Up. Join the Stack Overflow Community. Stack Overflow is a community of 7. Join them; it only takes a minute: VBA Function Optional parameters Ask Question. I can write something like: MoveFirst Do Until rs. Value End If rs.

MoveNext Loop End Sub How can I change the function to hold more than 1 optional parameters? MoveNext Loop End Sub Following Pankaj Jaju's advice, I have managed to have it run by changing it to: MoveNext Loop End Sub Now, how can I clear the value of an optional parameter?

VBA Function Optional parameters - Stack Overflow

ProtoVB 4 5 I know this is an old question now, but are you trying to pass an array? Then loop through x items in the array?

Default value parameters : Function Parameter « Language Basics « VBA / Excel / Access / Word

No, I was not; only 2 string variables. I was not familiar with the syntax at the time and Pankaj Jaju help solving that. Changing the function to a subroutine and adding ByVal enabled to perform what I was trying to do.

InputBox Function in Excel VBA | belucydyret.web.fc2.com

Change your sub and add ByVal Public Sub CalculateMe Optional ByVal strA As String, Optional ByVal strB As String. Pankaj Jaju 2, 2 8 ThrowawayAccount3Million - When you call CalculateMe , you can choose to specify which argument to pass. For example, if you want to use only strB , then call CalculateMe ,"second".

Similarly, if you want to use just the first parameter, call CalculateMe "first". If you do not wish to send any parameter, simply call CalculateMe. Since you did ignore his question, I will ask again ; At runtime, how do you tell whether the optional parameter was supplied? ThrowawayAccount3Million Use this statement which will compare your parameter with Nothing and do stuff based on that: If TheParameter Is Nothing Then [statements In any case, here you pass both arguments: You could simply pass a nullstring instead: Calculate "", strB etc Honestly it sounds like you are trying to be too fancy with your approach, without really understanding what you're doing.

Sign up or log in StackExchange. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name.

Creating Custom Functions with Optional Arguments

Stack Overflow works best with JavaScript enabled. Change your sub and add ByVal Public Sub CalculateMe Optional ByVal strA As String, Optional ByVal strB As String share improve this answer. At runtime, how do you tell whether the optional parameter was supplied? MathOverflow Mathematics Cross Validated stats Theoretical Computer Science Physics Chemistry Biology Computer Science Philosophy more 3.

Meta Stack Exchange Stack Apps Area 51 Stack Overflow Talent.