VBA:将变量设置为“空".或“缺少"?处理多个可选参数? [英] VBA: Set variable to "Empty" or "Missing"? Handling multiple optional arguments?

问题描述

是否可能或希望将对象/数据设置为空"或缺少"变体? 我希望能够有条件地将可选参数传递给函数.有时我想使用可选参数，有时则不需要.

Is it possible, or desirable, to set objects/data to an "Empty" or "Missing" variant? I want to be able to conditionally pass optional arguments to a function. Sometimes I want to use an optional argument, sometimes I don't.

在Python中，通过使用** kwdargs将字典或列表解压缩到函数参数中，您可以轻松地传递所需的任何可选参数.是否有类似的东西(或在VBA中破解它的方法)，以便您可以传入Empty/Missing可选参数?

In Python, you could easily pass through whichever optional arguments you wanted by using **kwdargs to unpack a dictionary or list into your function arguments. Is there something similar (or a way to hack it in VBA) so you can pass in Empty/Missing optional arguments?

尤其是，我尝试将 Application.Run 与任意数量的参数一起使用.

In particular, I'm trying to use Application.Run with an arbitrary number of arguments.

编辑:

我基本上是想这样做:

Public Function bob(Optional arg1 = 0, Optional arg2 = 0, Optional arg3 = 0, Optional arg4 = 0)
    bob = arg1 + arg2 + arg3 + arg4
End Function

Public Function joe(Optional arg1)
    joe = arg1 * 4
End Function


Public Sub RunArbitraryFunctions()
    'Run a giant list of arbitrary functions pseudocode

    Dim flist(1 To 500)
    flist(1) = "bob"
    flist(2) = "joe"
    flist(3) = "more arbitrary functions of arbitrary names"
    flist(N) = ".... and so on"

    Dim arglist1(1 To 4)        'arguments for bob
    Dim arglist2(1 To 1)        'arguments for joe 
    Dim arglist3(1 To M number of arguments for each ith function)


    For i = 1 To N
        'Execute Application.Run,
        'making sure the right number of arguments are passed in somehow.
        'It'd also be nice if there was a way to automatically unpack arglisti
        Application.Run flist(i) arglisti(1), arglisti(2), arglisti(3), ....
    Next i

End Sub

由于每个函数调用的参数数量都发生了变化，因此可以采用什么方法来确保将正确数量的输入输入到Application.Run中?

Because the number of arguments changes for each function call, what is the acceptable way to make sure the right number of inputs are input into Application.Run?

等效的Python代码为

The equivalent Python code would be

funclist = ['bob', 'joe', 'etc']
arglists = [[1,2,3],[1,2],[1,2,3,4,5], etc]

for args, funcs in zip(arglists, funclist):
    func1 = eval(funcs)
    output = func1(*args)

推荐答案

有两种方法可以更改例程必须提供给它的参数的数量:

There are two ways in which a routine can change the number of arguments that has to be provided to it:

• 将某些结尾参数声明为 Optional
• 将最后一个参数声明为一个例程可以使用一个或两个.

  A single routine can use either or both.

  Optional参数可能具有严格的类型(例如Optional s As String)，但是将无法检测其是否已传递.如果没有为该参数传递值，则将使用正确的空白"字样，这与手动传递该空白值没有区别.
  因此，有了Public Sub Bob(Optional S As String)，您就无法从Bob内部检测到它是被称为Bob还是被称为Bob vbNullString.
  可选参数可能具有默认值，这会遇到相同的问题.因此，有了Public Sub Bob(Optional S As String = "Default Value")，您将无法检测到Bob是被称为Bob还是被称为Bob "Default Value".

  An Optional parameter may have a strict type (e.g. Optional s As String), but then it will be impossible to detect whether it was passed. If you don't pass a value for such argument, the correct flavour of "blank" will be used, which is indistinguishable from passing that blank value manually.
  So, having Public Sub Bob(Optional S As String), you cannot detect from inside of Bob whether it was called as Bob or as Bob vbNullString.
  An optional parameter may have a default value, which suffers from the same problem. So, having Public Sub Bob(Optional S As String = "Default Value"), you cannot detect if Bob was called as Bob or as Bob "Default Value".

  为了能够真正检测是否传递了可选参数，必须将其键入为Variant.然后是一个特殊功能， IsMissing ，可在例程内部使用以检测是否传递了参数.

  To be able to truly detect whether an optional parameter was passed, they have to be typed as Variant. Then a special function, IsMissing, can be used inside the routine to detect if a parameter was passed.

  Public Sub Bob(Optional a, Optional b, Optional c, Optional d)
      Debug.Print IsMissing(a), IsMissing(b), IsMissing(c), IsMissing(d)
  End Sub
  

  Bob 1, , 3  ' Prints False, True, False, True
  

  ---

  ParamArray只能是最后一个参数，它允许从此位置开始传递无限数量的参数.所有这些参数都打包在单个Variant数组中(此处没有用于静态键入的选项).

  ---

  ParamArray can only be the last argument, and it allows an infinite* number of arguments to be passed starting from this position. All these arguments arrive packed in a single Variant array (no option for static typing here).

  IsMissing函数不适用于ParamArray参数(始终返回False).知道传递了多少参数的方法是将UBound(args)与LBound(args)进行比较.请注意，这仅告诉您使用了多少个参数插槽"，但实际上其中一些可能会丢失！

  The IsMissing function does not work on the ParamArray argument (always returns False). The way to know how many arguments were passed is to compare UBound(args) with LBound(args). Note that this only tells you how many argument "slots" were used, but some of them can be in fact missing!

  Public Sub BobArray(ParamArray a())
      Dim i As Long
      For i = LBound(a) To UBound(a)
          Debug.Print IsMissing(a(i)), ;
      Next
      Debug.Print
  End Sub
  

  BobArray                 ' Prints empty line (the For loop is not entered due to UBound < LBound)
  Sheet1.BobArray 1, 2, 3  ' Prints False, False, False
  Sheet1.BobArray 1, , 3   ' Prints False, True, False
  

  请注意，您不能为ParamArray的尾随参数传递丢失"值，即，这是非法的:

  Note that you cannot pass "missing" value for the trailing arguments of the ParamArray, i.e. this is illegal:

  Sheet1.BobArray 1, , 3,   ' Does not compile
  

  不过，您可以使用以下介绍的技巧来解决此问题.

  However, you can work around this using the trick described below.

  您在问题中碰到的一个有趣的用例是，预先准备所有参数的数组，将其传递给函数，填充所有参数"placeholders"，但仍希望函数检测到某些参数是丢失(未通过).

  An interesting use case that you touch in your question is preparing an array of all arguments in advance, passing it to the function, filling all the arguments "placeholders", but still expecting the function to detect that some of the arguments are missing (not passed).

  通常这是不可能的，因为如果传递了任何内容(即使是空白"值，例如Empty，Null，vbNullString的Nothing)，那么它仍将被视为已传递，而将会返回False.

  Normally this is not possible, because if anything is passed (even "blank" values, such as Empty, Null, Nothing of vbNullString), then it still counts as passed, and IsMissing() will return False.

  幸运的是，特殊的Missing值不过是特殊构造的Variant，即使不知道如何手动构造该值，我们也可以欺骗编译器将其释放:

  Fortunately, the special Missing value is nothing but a specially constructed Variant, and even without knowing how to construct that value manually, we can trick the compiler to give it away:

  Public Function GetMissingValue(Optional ByVal IgnoreMe As Variant) As Variant
      If IsMissing(IgnoreMe) Then
          GetMissingValue = IgnoreMe
      Else
          Err.Raise 5, , "I told you to ignore me, didn't I"
      End If
  End Function
  

  Dim missing As Variant
  missing = GetMissingValue()
  
  Dim arglist1(1 To 4) As Variant
  arglist1(1) = 42
  arglist1(2) = missing
  arglist1(3) = missing
  arglist1(4) = "!"
  
  Bob arglist1(1), arglist1(2), arglist1(3), arglist1(4)   ' Prints False, True, True, False
  

  现在，我们可以解决无法将"missing"传递到ParamArray的尾随"slots"的问题:

  Now, we can work around the inability to pass "missing" to the trailing "slots" of ParamArray:

  Dim arglist1(1 To 4) As Variant
  arglist1(1) = 42
  arglist1(2) = missing
  arglist1(3) = missing
  arglist1(4) = missing
  
  BobArray arglist1(1), arglist1(2), arglist1(3), arglist1(4) ' Prints False, True, True, True
  

  但是，请注意，仅当您直接调用BobArray时，此解决方法才有效.如果使用Application.Run，它将不起作用，因为Run方法将在将任何尾随的缺失"参数传递给被调用的例程之前将它们丢弃:

  Note, however, that this workaround will only work if you call BobArray directly. If you use Application.Run, it will not work because the Run method will discard any trailing "missing" arguments before passing them onto the called routine:

  Dim arglist1(1 To 4) As Variant
  arglist1(1) = 42
  arglist1(2) = missing
  arglist1(3) = missing
  arglist1(4) = missing
  
  Application.Run "BobArray", arglist1(1), arglist1(2), arglist1(3), arglist1(4)
  ' Prints False, because only one argument is passed
  

  这篇关于VBA:将变量设置为“空".或“缺少"?处理多个可选参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！