Vba function variable optional
Not every argument has a value every single time you call it. When you need a function that's flexible enough to handle an empty argument, use VBA's Optional keyword. By Susan Harkins in Microsoft Office , August 1, , 1: Many of Office's built-in functions have optional arguments.
excel - Optional ranges in vba function - Stack Overflow
If you don't explicitly specify a setting, the function uses a default. When you write your own code using VBA, you can mimic this functionality by using VBA's Optional keyword. Doing so adds a bit of flexibility to passed arguments, as you can choose to pass a value, or not. To take advantage of this behavior, use the following form:. When you call this function, you must supply a value for arg1 because it isn't optional; arg2, on the other hand is — you can choose to pass a value, or not.
Be careful where you place the Optional keyword in your argument list. All arguments that follow Optional are optional by default, which opens the door to logic bugs. There's a big gotcha when using Optional — just what value do you want VBA to use in the absence of a passed value?
The easiest way to handle a missing value is to assign a default value to the argument as follows:. In addition, you can use the IsMissing function in the following form to check for missing arguments:. If you choose this route, be sure to declare arg2 as a Variant because IsMissing works only with the Variant datatype. That makes the IsMissing function a little less handy than simply defining a default for the optional argument.
A default is always my first line of defense in this situation, but IsMissing comes in handy when a default isn't practical. Susan Sales Harkins is an IT consultant, specializing in desktop solutions.
Optional Arguments with VBScript
Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals. Cyberweapons are now in play: From US sabotage of a North Korean missile test to hacked emergency sirens in Dallas. Elon Musk and the cult of Tesla: How a tech startup rattled the auto industry to its core.
excel - VBA Function - Argument Not Optional - Stack Overflow
The truth about MooCs and bootcamps: Their biggest benefit isn't creating more coders. How Mark Shuttleworth became the first African in space and launched a software revolution. We deliver the top business tech news stories about the companies, the people, and the products revolutionizing the planet. Our editors highlight the TechRepublic articles, galleries, and videos that you absolutely cannot miss to stay current on the latest IT news, innovations, and tips.
Defenses, response plans, and greatest concerns about cybersecurity in an IoT and mobile world. To take advantage of this behavior, use the following form: Function name arg1 AS datatype , Optional arg2 AS datatype End Function When you call this function, you must supply a value for arg1 because it isn't optional; arg2, on the other hand is — you can choose to pass a value, or not.
The easiest way to handle a missing value is to assign a default value to the argument as follows: LOG IN TO COMMENT. My Profile Log Out. Free Newsletters, In your Inbox.
Tech News You Can Use We deliver the top business tech news stories about the companies, the people, and the products revolutionizing the planet. Delivered Daily Subscribe Best of the Week Our editors highlight the TechRepublic articles, galleries, and videos that you absolutely cannot miss to stay current on the latest IT news, innovations, and tips. Latest From Tech Pro Research.
Privacy Policy Cookies Ad Choice Terms of Use Mobile User Agreement. A ZDNet site Visit other CBS Interactive sites: Select Site CBS Cares CBS Films CBS Radio CBS.