Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

excel - 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.

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

See Pearson Material


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...