関数は自分で作る

令和6年9月29日、2024年9月29日現在

マクロ記録してChatGTPに質問しましょう。

エラーの内容やコードの位置を質問しましょう。

わからないコードを貼り付けて質問しましょう。

時代は変わりました。

以下は、過去の話です。

仕事のすき間？。ちょっと繰り返しが多いし、単純なんで間違いそう。という処理はマクロ記録で繰り返したい。動画もマクロ記録を中心と考えていたが結構、マクロ記録できない部分ばかりを書いている。

いろいろ知っているので書きたいでしょうね＞自分へ

私のマクロ記録は、だんだん少なくなっている。それはいろいろ経験して覚えたんだろうな。仕事は記憶しない、メモ、テプラーで手順を貼る、私の考えだが、慣れて記憶する。仕事は、高校までの学習とは違います。苦労したらいい仕事はできない。

当初、マクロ記録があることで「ニヤ」っとしたなー。だって、プログラム言語ってライブラリを使ってやって仕事ができるんです。新しいライブラリを使う場合は本や、私の場合は使いまくる。調査時間がかかるんです。でもExcelやOffice製品は操作を記録できるのでね！！ワオ。うれしい。

私の場合は、VisualBasic（VB）を使っていた。だから、VBAの仕様はわかっており、コードも書ける状態から初めた。マクロ記録は、Excelのオブジェクトを調べるためだった。ヘルプで探すより早いもんね。２０００年ごろかな？。インターネット上にも情報は少ない。だからマクロ記録で調べる。現在は、ネットで検索する方が早いかもしれない。

とこで、「VBAの言語が先か？」という話しでは無い。ベースとしては言語仕様は変数、引数、プロシージャーの作成、Do Loop、For Eachの繰り返しができれば十分。というか、Excelオブジェクト無しにVBAのコードを書くことは無い。ので、同時進行。仕事の合間にするもんだからね。このサイトでは、「 Excelオブジェクトの説明が多い場合は<E>。VBAの言語の説明が多い場合は<VBA> 」と目次に書いてあるので、これを参考に必要に応じて見て下さい。まだまだ完成しませんが。

マクロ記録を見ながらだが、だいたい、前と最後に注目する。まずは、最後のプロパティとメソッド。要求したタイミングのプロパティやメソッドをチェック。そして、その親のオブジェクト。そのオブジェクトが出てくるながれかな。以下が箇条書きに。

1. そのオブジェクトのプロパティ、メソッド（要求内容）
2. どのオブジェクトで求めることをしているか
3. そのオブジェクトが現れるタイミング。親子関係。

生、ライブでマクロの記録を見るコツです。また、不要なコードはどんどん消しましょう。メンタル的にあちこちに目がいって集中できない。

上図のエラーメッセージを見たことがあるでしょうか？。「スタック領域が不足しています。」とは何なん？。再帰呼び出しのプロシージャを作らないと見ることも無いと思います。

以下のようなコードを標準モジュールかSheet1などのモジュールに書いaまたはbのプロシージャを実行すると上図が現れる。

Sub a()
    b
End Sub

Sub b()
    a
End Sub

この例では永遠に相手を呼び出すためスタックを使い果たす。で、「スタック領域」ですがC言語では説明が必ずあります。「ヒープとスタック」Googleですが、再帰呼び出しを理解する時に必要です。ヒープはある領域を確保、解放できる自由なメモリ領域。その領域をスタックとして使う。

別のコードを示す。mainを実行するとaプロシージャを呼び出し、aプロシージャのEnd Subを抜けると呼び出し元であるmainプロシージャに戻り、mainプロシージャのEnd Subで終了する。

Sub main()
  a 1, 2  
End Sub

Sub a(引数1 As Integer, 引数2 As Long)
  Dim ii As Long 'ローカル変数。aプロシージャ内で有効な変数。プロシージャレベルの変数
End Sub

プロシージャの呼び出しはスタック領域を使う決まりになっている。コンピュータの演算装置/CPUの仕組みとしてもスタックポインタという高速にメモリ番地を入れるレジスタ（箱のような物）がある。スタック領域は、メモリ上にある深い箱のようなものでメモリの番地を指す機能を持つ。スタック領域の構造は先入れ後出し法の深いツボの形Google。反対のキューの構造は、先入先出法のパイプの形。

プロシージャの呼び出しは、以下の２つのことをしてプログラムが動くことを気にして下さい。

1. 実行位置をたどり、書いてあるコードを処理する。処理自体が実行位置を進むこと。
2. 個々のプロシージャの処理コードは、あるメモリ領域にあり、プロシージャー間で呼び出す場合は、呼び出した所に戻るための位置や複数の引数、そのサイズをプロシージャーの定義（Sub/FunctionからEnd Sub/Functionのこと）、引数の通りにスタックに積み、呼び出されらたプロシージャーは、スタックから引数の順に取り出す。

Sub main()がaプロシージャを呼び出す時に、引数に1,2に渡すが渡すとはスタックに積むこと。引数１が引数２の半分の大きさにしているのは引数１はInteger型、引数２はLong型のため。さらにaプロシージャ内で宣言したii変数もスタック上に作られ、aプロシージャの処理が終わるとスタックポインタは「リターンアドレス」呼び出し元であるmainのコード上のaプロシージャを呼び出した位置（アドレス）に処理先を戻す。

前のコードの場合は、aプロシージャーを呼び出したらmainプロシージャーに戻るのでスタックオーバー（スタック領域が不足しています。）にはならない。

次のコードは、aプロシージャーは内部でaプロシージャーを呼び出す、「再帰呼び出し」をしている。aプロシージャーのコードは、aプロシージャーが終わる、抜ける分岐処理をしていないためスタック領域を使い果たす。

Sub main()
  a 1, 2
End Sub

Sub a(引数1 As Integer, 引数2 As Long)
  Dim ii As Long 'ローカル変数。aプロシージャ内で有効な変数。プロシージャレベルの変数
  a 11, 22 '自分自身を呼び出す。再帰呼び出しでスタックにデータは積むが取り出すことは無い
End Sub

再帰呼び出しは、同じ名前のプロシージャーだが引数やプロシージャー内でDim宣言した変数は、呼び出す度に違う引数、変数です。スタック上で使いますわす事は無い。これがあるから再帰呼び出しで同じ処理だが、引数や変数が違うのでそれぞれで適切な処理ができる。

ある仕事をする処理する単位は、プロシージャと言えます。クラスという巨大化した変数の前にプロシージャー間のインターフェースは知るべきです。ただ、RubyやPythonなどはクラスを作るところから始まることが多いが、RubyやPythonでもVBAで言うプロシージャが使えるだけで十分に仕事はできます。

クラスのプロパティやメソッドが使う側のインターフェースであるように、スタックは呼び出す側と呼び出される型のプロシージャとのインターフェースです。クラスうんぬんの前に知るべき機能です。

なお、プロパティやメソッドの呼び出しにもスタック領域は使います。

次図は、再帰呼び出しの説明用です。再帰呼び出しとは、プロシージャの中で自分自身のプロシージャを呼び出すことです。作った関数はVBAの機能だけで使える。

次図のディスクトップのabcフォルダ以下の検索を例に説明する。

以下の内容のファイルをディスクトップに作って下さい。ファイル名はmakeabc.batです。このファイルをダブルクリックするとディスクトップ上にmakeabc.batがあればディスクトップ下に同じフォルダができます。

mkdir abc
copy makeabc.bat abc\a.txt
copy makeabc.bat abc\b.txt
mkdir abc\456
copy makeabc.bat abc\456\a.txt
copy makeabc.bat abc\456\b.txt
mkdir abc\123
copy makeabc.bat abc\123\a.txt
mkdir abc\123\aaa
copy makeabc.bat abc\123\aaa\aa.txt
copy makeabc.bat abc\123\aaa\bb.txt

以降は、作成した関数（Functionプロシージャ）の説明。

ディレクトリ構造を再帰呼び出しで処理するには、再帰呼び出し前の処理とその後の処理に分けて考える。

1. 前半：再帰呼び出しの対象はフォルダ。プロシージャの引数のフォルダ下 （直下のフォルダだけ） の全フォルダを調べ。直下のフォルダだけ に対して再帰呼び出しする。①と②
2. 後半：プロシージャの値を改行コードをはさんで作る。直下のファイルだけを処理する。目的の処理。③

再帰呼び出しは、呼び出し時に一時的に積まれる変数、プロシージャの引数やプロシージャ内の変数はすべて呼び出しの度に個別にスタックに積まれる。呼び出し時に積まれるスタックを意識できるのは次図のエラーだろう。

スタックの存在は、次のコードを実行すると「スタックオーバー」のエラーになるのでスタックの存在がわかる。

Sub main()
  a 1, 2
End Sub

Sub a(引数1 As Integer, 引数2 As Long)
  Dim ii As Long 'ローカル変数。aプロシージャ内で有効な変数。プロシージャレベルの変数
  引数1  = ii + 1
  a 引数1, 引数2 + 1  '自分自身を呼び出す。再帰呼び出しでスタックにデータをその都度積む
End Sub

注意！作成中のExcelやWordが飛ぶことがあります。私の環境Office32ビット、Windows10は64ビットでは２度以上実行すると飛びました。Excel本体が動くメモリ領域の下のアドレスからアドレス番地をアップしていきます。Excel本体のメモリまで突入？。会社のPCでも32ビットのOfficeですが問題ありません。私のPCが例外です。

再帰呼び出し/リカーシブルコールは、以下の２つの図の考えが必要です。

引数は、呼び出し側から渡された変数。プロシージャー内でDim宣言した変数は内部で使える一時的な変数。これらは、再帰呼び出し毎に違う。再帰呼び出しのプロシージャーを書くときは各処理で引数に応じて処理が完結できるように書く。でも、プロシージャー名が同じで引数は違うって言ってもなかなかね。上図をもう一度見て下さい。フォルダごとに引数が違います！！。

今回使った関数のコードです。

'vba.dirで再帰処理
Function myAllFileFolder(sPath As String, sWildCard As String) As String
   On Error GoTo myErr '使用中や権限のなのﾌｧｲﾙを扱ったときは、無視させる
   Dim r As String, col As New VBA.Collection
   If VBA.Right(sPath, 1) <> "\" Then sPath = sPath & "\" 'sPath最後が\で無い場合\を付ける
'あるフォルダ内の全フォルダを検索して、colに登録
   r = VBA.Dir(sPath & "*.*", vbDirectory) 'vbDirectoryでもファイルを含むので以後でGetAttrで分別
   Do Until r = ""
      If r <> "." And r <> ".." Then '無視。"."は自分のﾃﾞｨﾚｸﾄﾘ。".."は親ﾃﾞｨﾚｸﾄﾘ
        If VBA.GetAttr(sPath & r) = VBA.vbDirectory Then   'フォルダ/ﾃﾞｨﾚｸﾄﾘの場合だけ
            col.Add r
        End If
      End If
      r = VBA.Dir
      DoEvents '長い場合に中断できるようにWindowsからのメッセージを受け付ける
   Loop
'ﾃﾞｨﾚｸﾄﾘの場合は、再帰呼び出し
   Dim x
   For Each x In col
            myAllFileFolder = myAllFileFolder & myAllFileFolder(sPath & x, sWildCard) '再帰呼び出し
   Next
'sWildCardで指定したﾌｧｲﾙだけを検索。検索対象はsPathﾌｫﾙﾀﾞ内だけ
   r = VBA.Dir(sPath & sWildCard, vbArchive + vbHidden + vbNormal + vbReadOnly + vbSystem) '全ファイルが検索対象
   Do Until r = ""   '""は、もう該当のﾌｧｲﾙがないことを意味する
      If r <> "." And r <> ".." Then '無視。"."は自分のﾃﾞｨﾚｸﾄﾘ。".."は親ﾃﾞｨﾚｸﾄﾘ
         myAllFileFolder = myAllFileFolder & sPath & r & VBA.vbCrLf '改行ｺｰﾄﾞを挟んで検索されたﾌｧｲﾙ名を保存
      End If
      r = VBA.Dir '繰り返し
      DoEvents '長い場合に中断できるようにWindowsからのメッセージを受け付ける
   Loop
   Exit Function
myErr: '多くのエラーは権限の問題
   Debug.Print Err.Number, Err.Description
   Debug.Print sPath, r
'   Stop 'エラーが多い場合の確認用
   Resume Next '状況を出力したら、やりなおしはResume。もし、そのエラーを無視して次へ行く場合はResume Nextに
End Function
'?myAllFileFolder(VBA.Environ("userprofile")  & "\desktop\abc", "*.*")
'?myAllFileFolder("c:\windows\system32\drivers","*.*")

がおすすめです。

とゆうのは、マクロは繰り返し処理で本領を発揮します。特定のセル番地がだと編集が手間です。よって、「相対参照で記録」ボタンは必ず押しましょう。

絶対的なセル「A1」のようなセルの位置を記録するより、相対的に「今のセルの１つ下のセル」とか「今のセルの右２つ先のセル」と記録した方がプログラムでは利用しやすい。「相対参照で記録」にすると、RangeオブジェクトのOffsetプロパティが使わる。

“Scripting.FileSystemObject”を使うのが簡単です。

作ったユーザー定義関数の引数は、fileSearch(パス名,ファイルのワイルドカード)。パス名の下の全ワイルドカードのファイルをVBA.vbCrLfを挟んで文字列にする。

Function fileSearch(sPath As String, sWildCard As String) As String
    On Error GoTo myErr 'エラートラップ
    Dim fso, x
    Set fso = VBA.CreateObject("Scripting.FileSystemObject")
    For Each x In fso.GetFolder(sPath).SubFolders 'サブフォルダまで検索できる
        fileSearch = fileSearch & fileSearch(x.Path, sWildCard) '再帰呼び出し
    Next
    For Each x In fso.GetFolder(sPath).Files 'あるフォルダの全ファイルを一つずつｘに入れる
        If x.Name Like sWildCard Then 'Likeはパターン一致の演算子。＝も演算子
            fileSearch = fileSearch & x.Path & VBA.vbCrLf
'            Debug.Print File.Path
        End If
    Next
   DoEvents '長い場合に中断できるようにWindowsからのメッセージを受け付ける
   Exit Function
myErr:
   Debug.Print Err.Number, Err.Description
'   Stop 'エラーが多い場合の確認用
   Resume Next '状況を出力したら、やりなおしはResume。もし、そのエラーを無視して次へ行く場合はResume Nextに
End Function
'?fileSearch(VBA.Environ("homedrive") &  VBA.Environ("homepath") & "\desktop", "*.*")

上のコードの最後の行、コメントアウトしている行をイミディエイトウィンドウに貼り付けると実行できます。

Excelに標準モジュールを追加して、以下を貼り付けtestを実行して下さい。

Option Explicit

'CreateObject("Scripting.FileSystemObject")で再帰処理
Function fileSearch(sPath As String, sWildCard As String) As String
    On Error GoTo myErr 'エラートラップ
    Dim fso, x
    Set fso = VBA.CreateObject("Scripting.FileSystemObject")
    For Each x In fso.GetFolder(sPath).SubFolders 'サブフォルダまで検索できる
        fileSearch = fileSearch & fileSearch(x.Path, sWildCard) '再帰呼出/リカーシブルコール.上と下の処理
    Next
    For Each x In fso.GetFolder(sPath).Files 'あるフォルダの全ファイルを一つずつｘに入れる
        If x.Name Like sWildCard Then 'Likeはパターン一致の演算子。＝も演算子
            fileSearch = fileSearch & x.Path & VBA.vbCrLf
'            Debug.Print File.Path
        End If
    Next
  VBA.DoEvents '長い場合に中断できるようにWindowsからのメッセージを受け付ける
   Exit Function
myErr: 'ラベル
   Debug.Print Err.Number, Err.Description
'   Stop 'エラーが多い場合の確認用
   Resume Next '状況を出力したら、やりなおしはResume。もし、そのエラーを無視して次へ行く場合はResume Nextに
End Function
'?fileSearch(VBA.Environ("homedrive") &  VBA.Environ("homepath") & "\desktop", "*.*")

Sub test()
    Dim ss As String
    Dim pa As String, fi As String
    pa = VBA.Environ("homedrive") & VBA.Environ("homepath") & "\desktop"
    fi = "*.*"
'    ss = myAllFileFolder(pa, fi)
    ss = fileSearch(pa, fi)
    Application.Range("a1").Select
    copyExcel ss
End Sub

Sub copyExcel(str As String)
    Dim tmp, i As Integer
    tmp = VBA.Split(str, VBA.vbCrLf)
    For i = 0 To UBound(tmp)
        ActiveCell.Offset(i, 0).Value = tmp(i)
    Next
End Sub

Function myGetFile(r As Range) 'ｒはフルパスのセルを指定
    Dim ar
    ar = VBA.Split(r, "\")
    myGetFile = ar(UBound(ar))
End Function

Function myGetPath(r As Range) 'ｒはフルパスのセルを指定
    Dim x
    x = myGetFile(r)
    myGetPath = VBA.Replace(r.Value, x, "")
End Function