LAMBDA以降の新関数はVBAで使えるか

最終更新日：2022-11-15

LAMBDA以降の新関数はVBAで使えるか

タイトルを見て期待してこられた方には申し訳ありませんが、
残念ながら、LAMBDA以降に実装された新関数はWorksheetFunctionには入っていません。
※2022/11/11現在の365：今後実装される可能性も少ないとは思いますが全くないとも言えません。

しかしシートで使える関数なら、Evaluateメソッドを使えば呼び出せます。

・Evaluateメソッドの構文・Evaluateメソッドの解説と使用例・Evaluateメソッドの最後に

そこでEvaluateメソッドを使って、LAMBDA以降の新関数を動かしてみます。
ただしLAMBDAとヘルパー関数は、そもそもVBAで使う意味がないので、その他の新関数からいくつかをピックアップして解説します。

※これらのVBAを使う場合は、Excelバージョンについてはご注意ください。

TEXTSPLIT関数

TEXTSPLIT関数は列の区切り記号と行の区切り記号を使用して文字列を分割します。

TEXTSPLIT関数は、列の区切り記号と行の区切り記号を使用して文字列を分割します。列の区切り（横に分割）を使った場合は「データ」タブの「区切り位置」と同じような処理ができる関数です。さらに、列（横）への分割だけではなく行（縦）に分割することもできます。


  
    
      Sub sampl_TEXTSPLIT1()

      　　Dim strIn, strEval, v

      　　strIn = "Excel,エクセル;Word,ワード;Access,アクセス"

      　　strEval = "TEXTSPLIT(""" & strIn & ""","","","";"")"

      　　v = Evaluate(strEval)

      　　Stop

      End Sub

Evaluateで実行する文字列作成で"ダブルクォーテーションが面倒です。

シートで入力した数式を基に、"を""で置換する方法が良いと思います。

=TEXTSPLIT("Excel,エクセル;Word,ワード;Access,アクセス",",",";")

=TEXTSPLIT(""Excel,エクセル;Word,ワード;Access,アクセス"","","","";"")

もしくは、文字列内に入れる"を'（シングルクォート：別の文字でも良い）で記述しておいて後で置換する方法もあります。


  
    
      　　strEval = "=TEXTSPLIT('" & strIn & "',',',';')"

　　strEval = Replace(strEval, "'", """")

先頭の「=」はEvaluateに入れても入れなくてもどちらでも構いません。
実行すると、変数Vに2次元配列が作成されていることが分かります。

区切り文字を配列で指定して、複数の区切り文字で分割することもできます。


  
    
      Sub sampl_TEXTSPLIT2()

　　Dim strIn, strEval, v

　　strIn = "Excel,エクセル;Word,ワード;Access,アクセス"

　　strEval = "TEXTSPLIT(""" & strIn & """,{"","","";""})"

　　v = Evaluate(strEval)

　　Stop

End Sub

VBA関数のSplit関数は、分割する文字を1つしか指定できませんし、1次元配列しか作成できません。

・Split関数・区切り文字が文字列式内に存在しない場合・空文字（""）をSplitした場合・Split関数の使用例

これに対して、TEXTSPLIT関数なら複数区切り文字で分割も出来れば、列と行の区切りを指定して一気に2次元配列にすることができます。
対象は文字列なので、単純にEvaluateの引数に&で入れることができます。

もちろん1つの文字列で分割する等のSplit関数で事足りるならSplit関数を使った方が良いです。
もし上記のような分割が必要になった場合は、利用する価値は十分にあると思います。

TEXTBEFORE関数,TEXTAFTER関数

TEXTBEFORE関数はテキストから指定した文字または文字列より前に出現するテキストを返します。

TEXTBEFORE関数は、テキスト文字列から指定した区切り文字列の前に出現するテキストを返します。区切り文字列の前ではなく後ろを取得する場合はTEXTAFTER関数を使用します。TEXTBEFORE関数の構文 =TEXTBEFORE(text,delimiter,[instance_num],[match_mod…

TEXTAFTER関数はテキストから指定した文字または文字列より後ろに出現するテキストを返します。

TEXTAFTER関数は、テキスト文字列から指定した区切り文字列の後ろに出現するテキストを返します。区切り文字列の後ろではなく前を取得する場合はTEXTBEFORE関数を使用します。TEXTAFTER関数の構文 =TEXTAFTER(text,delimiter,[instance_num],[match_mode…


  
    
      Sub sampl_TEXTAFTER()

　　Dim strIn, strEval, v

　　strIn = "Office製品には：エクセル、ワード、パワポがあります。"

　　strEval = "TEXTAFTER(""" & strIn & """,""："")"

　　v = Evaluate(strEval)

　　Stop

End Sub

普通に使えます。
とはいえ、VBAでも簡単にできてしまいます。
v = Mid(strIn, InStr(strIn, "：") + 1)
むしろこちらの方が簡単なので、TEXTAFTER関数をVBAで使用するメリットはあまり無いと思われます。
TEXTBEFORE関数も同様です。

TOROW関数,TOCOL関数

TOROW関数は配列を横1行に変換して新たな配列を返します。

配列を横1行に変換して新たな配列を返します。TOROW関数は、セル範囲や1次元または2次元の配列を受け取り、オプションで指定された順番で横1行の配列を作成します。横1行ではなく縦1列の配列を作成する場合はTOCOL関数を使用します。

TOCOL関数は配列を縦1列に変換して新たな配列を返します。

配列を縦1列に変換して新たな配列を返します。TOCOL関数は、セル範囲や1次元または2次元の配列を受け取り、オプションで指定された順番で縦1列の配列を作成します。縦1列ではなく横1行の配列を作成する場合はTOROW関数を使用します。


  
    
      Sub sampl_TOROW_TOCOL()

　　Dim strIn, strEval, v1, v2

　　strIn = "Office製品には（エクセル）（ワード）そして（パワポ）があります。"

　　strEval = "TOROW(TEXTBEFORE(TEXTSPLIT(""" & strIn & """,{""("",""（""}),{"")"",""）""}),3)"

　　v1 = Evaluate(strEval)

　　strEval = "TOCOL(TEXTBEFORE(TEXTSPLIT(""" & strIn & """,{""("",""（""}),{"")"",""）""}),3)"

　　v2 = Evaluate(strEval)

　　Stop

End Sub

かなり無理やりです。
TOROW関数,TOCOL関数自体は普通に使えるのですが、
そもそもEvaluteに配列を入れる手段がありません。
したがって、Evaluateの数式で配列を作成したものに対してしか使い道がありません。
さすがに実用的な使い方は思いつきませんでした。

WRAPROWS関数,WRAPCOLS関数

WRAPROWS関数は1次元配列（1列または1行）を指定された数で各行を折り返して新しい配列を作成します。

WRAPROWS関数は、1次元配列（1列または1行）を指定された数で各行を折り返して新しい配列を作成します。行で折り返した結果、配列の最終行の要素が不足している場合は指定の文字で埋められます。行での折り返しではなく、列で折り返す場合はWRAPCOLS関数を使用します。

WRAPCOLS関数は1次元配列（1列または1行）を指定された数で各列を折り返して新しい配列を作成します。

WRAPCOLS関数は、1次元配列（1列または1行）を指定された数で各列を折り返して新しい配列を作成します。列で折り返した結果、配列の最終列の要素が不足している場合は指定の文字で埋められます。列での折り返しではなく、行で折り返す場合はWRAPROWS関数を使用します。


  
    
      Sub sample_WRAPROWS()

　　Dim strIn, strEval, v

　　strIn = Evaluate("SEQUENCE(10)")

　　strIn = WorksheetFunction.Transpose(strIn)

      　　strIn = Join(strIn, Chr(1))

      　　strEval = "WRAPROWS(TEXTSPLIT(""" & strIn &
      """,""" & Chr(1) & """),3,"""")"

　　v = Evaluate(strEval)

　　Stop

End Sub

これもEvaluateに配列を直接入れられないので、配列を通常使用しない文字Chr(1)で結合してから使っています。
この文字列をEvaluateの中でTEXTSPLITで配列に戻してからWRAPROWSを使用しています。
さすがにこれならVBAで普通に配列操作したほうが良さそうに思われます。
試しに使って見たという程度になります。
WRAPCOLS関数も同様です。

VSTACK関数,HSTACK関数

VSTACK関数は配列を縦方向（垂直方向）に順番に追加し、1つの大きな配列を作成して返します。

・VSTACK関数の構文・VSTACKの基本動作・VSTACK関数の使用例と解説

HSTACK関数は配列を横方向（水平方向）に順番に追加し、1つの大きな配列を作成して返します。

・HSTACK関数の構文・HSTACKの基本動作・HSTACK関数の使用例と解説

同一ブック内にあるテーブル2つをVSTACKで縦につなげて、アクティブシートのA1以降に出力しています。


  
    
      Sub sample_VSTACK()

      　　Dim v

      　　v = Evaluate("VSTACK(テーブル1[#ALL],テーブル2)")

      　　Range("A1").Resize(UBound(v, 1), UBound(v, 2)).Value = v

      End Sub

途中の変数vには2次元配列で入ります。
上記のVBAでは、セル出力を意識してテーブル1は見出しも含めましたが、VBA内で配列を処理するならない方が良いでしょう。
ただし数式なので、事前にテーブル名が分かっている場合にしか使えません。
もちろんVBAでテーブル名を取得して埋め込んでも良いですが、それなら全てVBAの方が良いと思います。
かつ、VBA内でテーブル名を固定してしまうので、テーブル名の変更時はVBAも変更する必要があります。

VBAで複数テーブルを結合するコードもそれほど難しいものではありません。

シート内の全テーブルを1つに統合

ツイッターで出題した問題です。シート内にある複数のテーブルを、1つのテーブルに結合して別シートに出力します。出題ツイート【VBA問題】「Sheet1」のランダムな位置に複数のテーブルがあります。（添付1）テーブルは全て同一形式（列数・ヘッダーが同じ）です。

使用する場面は限られそうですが、使えないことはないと思います。
先々新関数が広まった時には、VBAでも使用する場面はあるかもしれません。

同じテーマ「マクロVBA技術解説」の記事

アクティブシート以外のWindowを設定できるWorksheetView
LSetとユーザー定義型のコピー（100桁の足し算）
省略可能なVariant引数の参照不可をラップ関数で利用
 ブックのいろいろな開き方（GetObject,参照設定,アドイン）
入力規則への貼り付けを禁止する
 Select Caseでの短絡評価（ショートサーキット）の使い方
 RangeオブジェクトのFor EachとAreasについて
 画像が行列削除についてこない場合の対処
 新関数SORTBYをVBAで利用するラップ関数を作成
LAMBDA以降の新関数はVBAで使えるか
数字（1～50）を丸付き数字に変換するVBA

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.RangeとCellsの使い方｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.繰り返し処理（For Next)｜VBA入門
5.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
6.マクロって何？VBAって何？｜VBA入門
7.並べ替え（Sort）｜VBA入門
8.Excelショートカットキー一覧｜Excelリファレンス
9.エクセルVBAでのシート指定方法｜VBA技術解説
10.Range以外の指定方法（Cells,Rows,Columns）｜VBA入門

ホーム
マクロVBA応用編
マクロVBA技術解説
LAMBDA以降の新関数はVBAで使えるか

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

VBA技術解説
LAMBDA以降の新関数はVBAで使えるか