[[20180618150930]]　『文字列で指定した関数を実行する関数』（通りすがり）　ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

　

『文字列で指定した関数を実行する関数』（通りすがり）

Excelに関数について質問です。

VBAの場合、任意の文字列の名前の関数（プロシャージャー）を呼び出すことの出来る命令が多数存在しますよね。
Application.Run()とか、OnTimeとかOnKeyとか・・・

これと同じようなことをExcel関数で行う方法は無いでしょうか。
具体例としては
A1=1、A2=2、A3=3
と入っているときに、
C1=謎の関数("SUM",A1,A2,A3)としたら、SUM(A1,A2,A3)が実行されて、C1=6
C2=謎の関数("AVERAGE",A1,A2,A3)としたら、AVERAGE(A1,A2,A3)が実行されて、C2=3
となるわけです。

このような関数は標準関数には存在しない・・ですよね？
アドインとかでも良いので、もしご存知の方いましたらよろしくお願いいたします。

何故このような関数が必要なのか？というツッコミは無しでよろしくお願いいたします。

＜ 使用 Excel：unknown、使用 OS：unknown ＞

平均取ったら2では？

まぁ、そんな関数は無いので、無理矢理ユーザー定義関数として作ってしまう例なぞ。 標準モジュールに以下を貼りつけると、謎の関数「aaa」が使えるようになります。
が、素直に数式でSUMすれば良いものを、わざわざマクロを呼び出し、マクロの中ではワークシート関数を呼び出しているわけで、なんともまぁ無駄な事をやっています。 

 Public Function aaa(cCommand As String, ParamArray Args())
    Dim i As Long
    Dim cw As String


    For i = 0 To UBound(Args)
        If cw <> "" Then
            cw = cw & ","
        End If
        cw = cw & Args(i).Address
    Next i


    Select Case LCase(cCommand)
    Case "sum"
        aaa = WorksheetFunction.Sum(Range(cw))
    Case "average"
        aaa = WorksheetFunction.Average(Range(cw))
    End Select
 End Function
（???） 2018/06/18(月) 16:11

 ひどい手抜きだが。
 Function TST(SIKI As String, ParamArray PARA())
    SIKI = SIKI & "(" & Join(PARA, ",") & ")"
    TST = Evaluate(SIKI)
 End Function

（ねむねむ） 2018/06/18(月) 16:19

＞C1=謎の関数("SUM",A1,A2,A3)としたら、SUM(A1,A2,A3)が実行されて、C1=6
＞C2=謎の関数("AVERAGE",A1,A2,A3)としたら、AVERAGE(A1,A2,A3)が実行されて、C2=3

SUBTOTAL関数とか　AGGREGATE関数でできるのでは。

（メジロ） 2018/06/18(月) 16:29

＞何故このような関数が必要なのか？というツッコミは無しでよろしくお願いいたします。
いやいや、おかしな話ですよ＾＾；

SUMという文字列が、エクセルでは「合計しなさい」という命令だとわかっているなら、
素直に＝Sum(A1,A2,A3)と書きましょうよ。

せめて作るなら、
SUBTOTAL関数のような仕様にしましょうよ。
って、書いていて思ったけど、
SUBTOTAL関数で、なんとなく要望を満たしてませんか？？？？
https://dekiru.net/article/4366/
（まっつわん） 2018/06/18(月) 16:31

無いなら作ればいいじゃない！の精神で自作してみました
Function RunWorksheetFunction(FuncName As String, ParamArray Args() As Variant) As Variant 
    RunWorksheetFunction = Application.Run("WorksheetFunction." & FuncName)
End Function
テスト：?RunWorksheetFunction("SUM", 1,2,3)→「マクロ〜〜〜を実行できません。このブックでマクロが使用できないか、またはすべてのマクロが無効になっている可能性があります」

ならばと、VBA上に関数を再定義したら、
Function Run(FuncName As String, ParamArray Args() As Variant) As Variant 

    Run = Application.Run(FuncName, Args)
End Function
Function Sum(ParamArray Args() As Variant) As Variant
    Sum = WorksheetFunction.Sum(Args)
End Function
テスト：?Run("SUM", 1,2,3)→6　一応は動きましたが、この方法では必要な関数を片っ端から登録しなければなりません。

何とかならないかと、質問を書きながら調べていたらCallByNameで動きました！
せっかくなので、Excel標準関数とユーザー定義関数の両方に対応出来るようにしたいと思い書いているのですが・・・暫定版ができました。
Function Run(FuncName As String, ParamArray Args() As Variant) As Variant 

    On Error GoTo RunProc:
    Run = CallByName(WorksheetFunction, FuncName, VbMethod, Args)
    Exit Function
RunProc:
    Select Case UBound(Args)
        Case 0
            Run = Application.Run(FuncName, Args(0))
        Case 1
            Run = Application.Run(FuncName, Args(0), Args(1))
        Case 2
            Run = Application.Run(FuncName, Args(0), Args(1), Args(2))
        Case 3
            Run = Application.Run(FuncName, Args(0), Args(1), Args(2), Args(3))
    End Select
End Function

Function hoge(a As String, b As String) As String 

    hoge = a & b
End Function
テスト：?Run("SUM",1,2,3)　→　6
テスト：?Run("hoge","a","b")　→　ab

Q.Application.Runに可変長引数をそのまま渡すと「引数は省略できません」となりますが、上記の様にゴリゴリ書くしかないですよね？
（通りすがり） 2018/06/18(月) 16:39

調べているうちに沢山回答を頂きありがとうございます。

＞???さん
平均値、間違えてました。ご指摘ありがとうございます。

＞ねむねむさん
Evaluate関数！初めて知りました。こんな方法もあったのですね。

＞???さん、メジロさん、まっつわんさん
＞SUBTOTAL関数
関数が限られている場合は、頂いたソースコードで出来ますね。
具体例としてSumやAverageを上げましたが、実際には何が入るかわからないのです。

まっつわんさん
＞いやいや、おかしな話ですよ＾＾；
説明し辛いのですが、入力パラメータ、関数名をひたすら（ランダムに）入力していってデバッグするための関数を作りたかった、と言えば分かるでしょうか・・・。
（通りすがり） 2018/06/18(月) 16:48

 細かいようだがEvaluateは関数ではなくメソッドだそうだ。
 なので正確に記述すると
 Worksheet.Evaluate
 Application.Evaluate 
 となる。
 ただ、この二つがどう違うかは私にはよくわからない。
（ねむねむ） 2018/06/18(月) 16:56
テストケースのシナリオ化をしようとしているのですかね？ テキストまたはセルに関数名と引数を並べて、これを1行目から順に処理することで、提供しようとしているライブラリ群のテストにする、とか。

必要な命令は羅列してしまって良いと思いますよ。 そうしないと、どういう組合わせを試験したのか、網羅度を説明しにくいでしょうから。
（???） 2018/06/18(月) 17:08

ねむねむ　さん
＞細かいようだがEvaluateは関数ではなくメソッドだそうだ。
以後気をつけます。

???　さん
＞テストケースのシナリオ化をしようとしているのですかね？
そんなガッチリしたものではないのですが、そのような感じです。
（通りすがり） 2018/06/18(月) 17:35

CallByNameの第4引数は配列指定できるので、以下で良いようですよ。（余談ながら、On Error Goto 文の使い方が間違ってます。Gotoと書きますが、Gotoではありません） 
 Function Run(FuncName As String, ParamArray Args() As Variant) As Variant
    On Error Resume Next
    Run = CallByName(WorksheetFunction, FuncName, VbMethod, Args())
    On Error GoTo 0
 End Function
（???） 2018/06/19(火) 10:13
???さん
すいません、On Error GoTo RunProc:としているのは、WorksheetFunctionではないもの（VBEで定義しているユーザー定義のファンクションプロシャージャ）を呼び出すために行っています。

それとも他に意図があってのアドバイスでしょうか。
（通りすがり） 2018/06/19(火) 10:46

On Error Goto 文は、Gotoの派生命令ではなく、エラートラップ開始の命令なのです。 なんでこんな紛らわしい書き方なのかというと、元々のBASIC言語がこう書いていたためです。 おかげで、Goto文と同じ使い方をする人が多いという…。

どういうことかというと、エラーがあったらエラー処理に飛べ、という命令なのですが、エラートラップなので、エラー処理が終わったなら直ちにResume命令を使って、本流または先の処理に戻さなければならないのです。 通りすがりさんのコードだと飛ばしているだけで戻していませんよね？ 短いコードなのでトラブルにはなりませんが、スタックを積んだのに戻さないというのはメモリリークになり、ループしていると資源を食いつぶし、システムダウンを招きかねない書き方をしているのです。 VB.NET等をご存知なら、On Error Goto文は、Try〜Catch と同じものだと考えてもらえば判るかと思います。

On Error Resume Next ならば、エラーがあったらエラーコードをセットして終わりなので、エラー処理部分へのジャンプが無く、スタックが積まれないので安全なのです。 今回の場合だと、私のコードのCallByName直後に、変数に Err.Number を代入しておいて、これが0でない場合はユーザーファンクション処理を行う、という書き方にすべきだと思います。 そうしないと、エラー処理中にエラーになったらどうするの？、とか、後で困るケースが出てきそうです。
（???） 2018/06/19(火) 11:08

＞スタックを積んだのに戻さないというのはメモリリークになり、
全然知らずにGoto目的で使っていました。On Error Goto文はサブルーチン用だったのですね・・。

結局のところパラメタを解析して加工したものをEvaluateメソッドで処理することに決めました。
本当にありがとうございます。
（通りすがり） 2018/06/19(火) 13:04

コメント返信：

[ 一覧(最新更新順) ]

YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.