カテゴリー: ExcelVBA

このカテゴリには、プロシージャとして作成されたコードが記載されています

これらは、いわゆるサブプロシージャと呼ばれるもので、本来の処理をサポート、または可読性向上に繋げる役割があります

ここにあるプロシージャは、広域関数なので同じプロジェクト内であればどこにあっても呼び出し可能です
ですが、どこに何があるか分からなくなると困るので１つ専用のモジュールを作成して、その中にコピペしてもらうようにした方がいいです

モジュールの作成は、VBAのメニューから簡単に行うことが出来ます
挿入メニューから標準のモジュールを追加してください
モジュールの名前は何でも構いません、参照することもありません

これで、部品を入れるモジュールが完成します
以下の記事で画像付きで解説していますので、参考にしてください

プログラムの入れ物を作る

モジュールが作成出来たら、この中に記事トップにあるコードをそのまま貼り付けしてください

それで、あとは別のプロシージャで必要に応じて呼び出しを行えば利用可能です

また、記事にあるコードは一切解説コメントを付けていません
コードの意味を理解したい方は記事で解説を行っていますので、頑張ってスクロールして読んでください

おおよそはそんなんどうでもええわ、って場合が多いと思うのでそういった方々には邪魔な行になってしまうし、なによりそっちで解説しすぎるとこのサイトのこの記事はなんのために読んだらええのん？という素朴な疑問が払しょくできないからです

プロシージャについて

このカテゴリの記事には、大きく分けて2種類のプロシージャがあります

それが、SubプロシージャとFunctionプロシージャです

これらの違いは戻り値を持つか持たないかと、Callステートメントを使うか使わないかの点になります

戻り値に関しては、正直引数を参照渡しにする方式であれば、処理中での計算値を返すことは出来るのでどうとでもなってしまいます

ですが、呼び出し方法に関しては対応していない方法ではエラーが出ますので注意が必要です

Subプロシージャの呼び出しについて

これには、Callステートメントを使用する必要があります
このステートメントは、他のプロシージャを実行する際に使用するものです

例えば、以下のようなSubプロシージャがあった場合で解説します

Sub zzzMsg(Optional zzString As String = "TestMsg")

MsgBox zzString

End Sub

このプロシージャは、引数を持っているSubプロシージャです
この引数は文字列型の引数になるので、処理の呼び出し時の引数に文字列を指定することで、この引数名に代入されます

処理内容としては、その引数に代入された文字列をメッセージボックスに表示するだけです

なお、引数名の前についている「Optional」キーワードは引数を省略することが出来ることを意味します
そして、省略された場合の初期値を型の後に指定することが出来ます
ここでは「”TestMsg”」が指定されていることを確認してください

引数があり、かつ省略可能なSubプロシージャとなりますのでCallステートメントの書き方は2通りあります

Call zzzMsg

この様に引数を省略して呼び出します、引数を全てを省略する場合や引数の存在しない場合はSubプロシージャの名前だけを記載するだけでいいです

この場合に表示されるメッセージは画像の様に省略時の文字列になります

次に、引数を指定して実行する場合の解説です

引数は「()」で囲んで入力します
「(」を入力した時点で、画像の様にクイックヒントが表示されます
ここでの表示は関数のものと同じです、「[]」で囲まれた引数は省略可能を意味しています
その際に、省略時の代入値も表示されているのが確認できます

Call zzzMsg("ProductionMsg")

そのまま引数に文字列を指定したのが、このコードになります
これを実行すると、引数に指定した文字列がメッセージボックスに表示されます

画像の様に、引数に指定した文字列が表示されるようになります

Subプロシージャは、Callステートメントを使用して引数を利用する場合はその引数も含めてコードを記載することで処理を実行する事ができます

Functionプロシージャの呼び出しについて

こちらは関数と同じ使用方法になるので呼び出しはあまり難しくはありません
ですが、Functionプロシージャでの作り方がSubプロシージャと違う点があるので注意してください

Function zzzLoopString(zzLoopSt As String, Optional zzLoop As Long = 1) As String

zzzLoopString = String(zzLoop, zzLoopSt)

End Function

大きく違う点が2つあります
・引数の()の後に、プロシージャ自体の型宣言を行うこと
・プロシージャ名に代入することで戻り値となる

この2点に注目しながらFunctionプロシージャは読み取るようにしてください

このプロシージャでは、まず1行目後半部分で型宣言しています
String型になっていますので、この関数の戻り値は文字列であることが分かります

また、処理中でプロシージャ名に文字列を代入しています
つまりこの時点で戻り値が取得可能な状態である、ということです
このコード以降にも処理を作成することは可能です
その場合に、Exitステートメントなどでプロシージャを強制終了したとしても戻り値の取得は可能です

なおこのFunctionプロシージャは引数「zzLoopSt」に指定された文字列を引数「zzLoop」の回数繰り返して返す関数です
というか、そのためのString関数を実行しているだけです

MsgBox zzzLoopString("A", 4)

呼び出しを行う場合は、通常の関数同様、Functionプロシージャの名前を直接記載して実行します
引数に関しても関数同様で、「()」で囲んで指定を行います

ここでは、「A」を4回繰り返す文字列を取得してメッセージボックスに表示する処理になります

コードを実行すると、メッセージボックスが表示されます
その時に表示される内容は、Functionプロシージャで返された文字列です

ここで、少しわかりにくいかもしれませんが
この1行のステートメントで、Functionプロシージャの関数と、MsgBox関数の2つが実行されています
しかし、順番は先に引数のFunctionプロシージャが実行されてから、MsgBox関数が実行されていますので問題はありません

また、今回作成したFunctionプロシージャでは引数「zzLoopSt」は省略不可の設定になっています、Optionalキーワードを付けなければ省略不可となります

この設定で、引数を省略して記載した場合は実行時エラーが発生しますので注意してください

引数は都度合わせて行いますが、要はSubプロシージャはCallステートメントで呼び出す・Functionプロシージャはプロシージャ名だけで呼び出す、という形でOKです

コードコピー

Function zzz可能ファイル名(ByVal zz保存名 As String) As String

Dim zz保存名_前 As String, zz保存名_後 As String
Dim zz連番 As Long
Dim zz検証用文字列 As String
zz保存名_前 = Mid(zz保存名, 1, InStrRev(zz保存名, ".") - 1)
zz保存名_後 = Mid(zz保存名, InStrRev(zz保存名, "."))
zz連番 = 2

zz検証用文字列 = zz保存名
Do Until Dir(zz検証用文字列) = ""
    zz検証用文字列 = zz保存名_前 & "_(" & zz連番 & ")" & zz保存名_後
    zz連番 = zz連番 + 1
Loop
zzz可能ファイル名 = zz検証用文字列

End Function

ExcelVBAのコードで、ファイル名を付けて保存する処理があります
その際に、付けたいファイル名が使用済みであった場合上書き保存の確認が出てきたり、自動的に上書きされてしまったりします

エクスプローラー上での連番が振られて、ファイルが別ファイルとして保存されていくような処理はVBAで自動的に行うことは出来ません

実際に保存処理を行う前に、重複しない名前を取得しておく必要があります
その時に使用する関数です

この関数は、引数に指定したパス文字列のファイル名を重複する場合は連番を付けて返します
この関数で取得した文字列をファイル名の絶対パスとして指定すれば、重複することが無くなります

コードの使用方法

コードコピー

ThisWorkbook.SaveAs zzz可能ファイル名(ThisWorkbook.FullName)

このコードの様に、関数として引数を指定して使用します
ここでは、このコードのあるExcelブックを同じ場所に名前を付けて保存します

コードがある元々のExcelブックは「TestFile.xlsm」になります

そのファイルに上記の関数とコードを記載して、元ファイルを開きなおして数回実行したフォルダ内の状態です

元々のExcelブック名に連番を付けて保存します

コード解説

Function zzz可能ファイル名(ByVal zz保存名 As String) As String

・・・

End Function

使用可能な文字列を返すので、戻り値を利用するのでFunctionプロシージャで作成を行います
また、戻り値はパス文字列なのでString型で宣言しています

引数は1つ設定しています
zz保存名は、使用したいファイル名の絶対パス文字列を指定してもらいます
なお、ここでの処理ではその文字列があれば良いのでByValで引数を指定しています
これは引数を値渡しとするものです、処理中に変更したりはしませんので
参照渡しでもええっちゃええです

Dim zz保存名_前 As String, zz保存名_後 As String
zz保存名_前 = Mid(zz保存名, 1, InStrRev(zz保存名, ".") - 1)
zz保存名_後 = Mid(zz保存名, InStrRev(zz保存名, "."))

作成する保存名を作成するうえで必要なパス文字列として、引数として指定された文字列を一旦拡張子までで分割します
引数に指定する文字列は拡張子まで含めた絶対パス文字列になるので、文字列の最後にはファイルの拡張子が存在します

なので、この引数の文字列の後に単純に連番を追加しても有効なパス文字列にはなりません
その為に、分割した文字列を別々の変数にそれぞれ取得させます

その変数の宣言と、分割取得の箇所になります

Mid関数は、指定文字列の中から指定文字数を抜き出す関数です
これを文字数を指定することでパス文字列を分割取得することが出来ます

引数の中にある、InStrRev関数は指定文字列の中から特定の文字を検索して何文字目にあるかを返す関数です
InStrRev関数は、特定の文字を検索する場合に文字列の右側から検索を行います

今回分割したい文字列は拡張子の部分になります
なので「.」を検索する必要がありますが、この文字自体はファイル名やフォルダ名に使用することが可能です

その為、文字列の左側から検索した場合拡張子が取得できない可能性があります
また拡張子の文字数も3文字や4文字など固定されたものではありません
ですが、さすがにファイルの拡張子に「.」が使用されることはありませんので、文字列の右側から検索を行えば、確実に拡張子との分割箇所が特定できます

ちなみに、この関数は絶対パスから「\」を検索することでフォルダまでのパスに分割するような事にもよく使われます

Dim zz連番 As Long
zz連番 = 2

ファイルに結合する連番の整数値用の変数です
重複があれば数字を連結するので、この数値を使用するタイミングは前提として重複するものがあるという事なので、初期値は2から始めます

Dim zz検証用文字列 As String
zz検証用文字列 = zz保存名

検証用の文字列を一時的に代入するための文字列変数です

初期値として、引数の文字列をそのまま代入します
まず最初にこの代入した文字列で検証を行う形になります

Do Until Dir(zz検証用文字列) = ""
    zz検証用文字列 = zz保存名_前 & "_(" & zz連番 & ")" & zz保存名_後
    zz連番 = zz連番 + 1
Loop

ループ処理で、使用可能な連番の数値になるまで繰り返します

Dir関数でパス文字列が使用可能かどうかを検証しています
この関数は、引数のパスが有効であればそのファイル名のみを取得する関数です

パスのファイルが存在しない場合は、空白を返します
つまり、この関数で空白が取得されれば重複するファイル名が存在しないことが判定できます

ループ処理の中では、連番数字を連結した文字列を検証用文字列に代入しています
代入が終わってから、数値変数を加算しています

代入が終わったら、また上記の検証を行い
使用可能なパス文字列が生成されるまでループ処理を行います

zzz可能ファイル名 = zz検証用文字列

最後に、使用可能なパス文字列が代入された検証用文字列の文字列を関数名に代入します
これが、この関数の戻り値になります

関数の型は、String型なので文字列として、このパス文字列を返すことが出来ます