Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
menu search
person
Welcome To Ask or Share your Answers For Others

Categories

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.

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?

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

EDIT:

I'm basically trying to do this:

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

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?

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)
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
1.1k views
Welcome To Ask or Share your Answers For Others

1 Answer

in VBA you use ParamArray to enter option inputs to functions.

See Pearson Material


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
...