1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

VBAでmap関数を作りたい

Last updated at Posted at 2023-08-28

Application.Runメソッドの引数に、プロシージャ(Sub, Functionなど)の名前を渡すと、間接的にプロシージャを呼び出せる。
これを使えば、VBA版のmap関数が作れるのではと思い、作ってみた。

Option Explicit

'メイン関数
Public Sub main()
    Dim i As Long
    Dim arr(9) As Variant '要素数が10個(0~9)の配列
    
    '配列を初期化する
    For i = LBound(arr) To UBound(arr)
        arr(i) = i '0, 1, 2, 3, 4, 5, 6, 7, 8, 9
    Next
    
    'map関数を呼び出す(※プロシージャの名前"square"と、配列arrを渡す)
    Call map("square", arr)
    
    '結果を表示する
    For i = LBound(arr) To UBound(arr)
        Debug.Print arr(i) '0, 1, 4, 9, 16, 25, 36, 49, 64, 81
    Next
End Sub

'map関数
Public Function map(ProcName As Variant, ByRef arr() As Variant)
    Dim i As Long
    
    For i = LBound(arr) To UBound(arr)
        arr(i) = Application.Run(ProcName, i)
    Next
End Function

'二乗を返す関数
Public Function square(Num As Variant) As Variant
    square = Num ^ 2
End Function

コードの良し悪しは別として、想定通りに動くものが出来た。

注意

  • マクロ名は、「アクティブ シートの状態に応じて評価され」るため、注意が必要。
    • 「アクティブ シートの状態に応じて評価され」るとは?
      image.png
  • 引数の個数は、1~30個

参考

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?