-1

I want to write a code with multiple parameters like this :

Set param1 = qt.Parameters.Add("City Parameter", xlParamTypeVarChar)

and be able to use either one parameter or the second. VBA obliged me to have values for both parameters, but I want to have one obligatory parameter and others optional. What should I add to the code?

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
Tanya Kravets
  • 19
  • Possible duplicate of [VBA Function Optional parameters](https://stackoverflow.com/questions/28770412/vba-function-optional-parameters) – L8n Jul 03 '19 at 15:30
  • Really, just using google would have been faster: https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/understanding-named-arguments-and-optional-arguments – L8n Jul 03 '19 at 15:33
  • 1
    You need to make both parameters optional, because `Optional` parameters must appear *at the end of the paremeter list*. That said, asking about a procedure's signature without even sharing what your procedure's signature is, is a little short. – Mathieu Guindon Jul 03 '19 at 15:46
  • Wait is this about VBA optional parameters, or query/SQL parameters? If this has nothing to do with SQL, please remove the tag, it makes the post confusing. – Mathieu Guindon Jul 03 '19 at 15:49
  • @Mathieu Guindon it seems to be about [Parameters for QueryTables in Excel](https://learn.microsoft.com/en-us/office/vba/api/excel.parameters) which routinely involve SQL. Of course, I defer to OP. – Trevor Reid Jul 03 '19 at 15:55
  • @TrevorReid I know it looks like it, but it *reads* like OP merely used the `Parameters.Add` method as an example of a function with multiple optional parameters... I initially understood the question exactly as in the below answer, but now I'm not sure any more. – Mathieu Guindon Jul 03 '19 at 15:57
  • I agree it's confusing. That wasn't my reading at first glance, but hopefully we'll get some help from OP giving OP help :-) – Trevor Reid Jul 03 '19 at 16:20

1 Answers1

1

Optional parameters are defined with the term Optional

Example:

Sub DoStuff(requiredParam as String, Optional optParam as String = "")

Adding in the = "" is helpful so you can write out the logic depending on whether a value has been passed in. "" is just an example and you should choose a value that isn't liekly to be passed in.

dasche
  • 111
  • 6
  • You can also leave the ´= something´ and use ´IsMissing()´ to check if a value was passed. – L8n Jul 03 '19 at 15:36
  • @L8n `IsMissing` will only work with a `Variant` though – Mathieu Guindon Jul 03 '19 at 15:57
  • @MathieuGuindon Thanks, good to know, somehow missed that since I usually use it with a variant and ´select case´ for fake overloading. – L8n Jul 03 '19 at 16:07
  • 1
    Anyway, in this specific case the ´= ""´ should not be necessary as the default value is ´""´ anyway. – L8n Jul 03 '19 at 16:09