🪩

Excel VBAやるならこれだけは入れておけ！

2024/05/12に公開

Excel

Excel VBA

私はC/C++を経てExcel VBAに触れたが、とても素晴らしい出会いだった。世の中の実務のほぼ全てでExcelが使われており、手作業等を自動化したいという欲求はいくらでもある。私は頼まれてもないのにデータ入力で入った職場であれやこれやを勝手にマクロ化して上司に提案して、めちゃくちゃびっくりされた事をよく覚えている（笑）。

幸いなことにその提案は受け入れられ、それからというもののツール開発者としての立場を確立し、そこで働く色々な人（上司や先輩、パートさん）から「こういう事できない？」とか色々頼まれるようになった。そしてそれをただやるのではなく、自分なりのアイデアも入れた最強のツールを常に提供するようにしていた。速度も常に限界まで最適化を図った。

そうすると現場の人達からものすごい喜ばれ、感謝されるのである。
それがとても嬉しい事だった。
その経験が生きて今ではSEとしてちゃんとした仕事に就く事ができたのだが、成果物に対してのリアルな反響というのはクライアントや上司からはあったりなかったりするものの、それを実際使う現場の人達からの声を聞く機会というのはあまりなかったりするのだ。

今思えば、私はVBAと出会う事でそんな素晴らしい経験を積む事ができたのだなと。
本当にこのツールには感謝しているのだが、本格的なプログラミング言語と比較するとどうしても癖があるというか、気になるというか、不足な部分が目に付いてくるのも事実だ。

しかしこれらの部分を理解した上でうまく付き合っていくと素晴らしい成果が得られるのも事実。今後もあらゆる会社で広く使い続けられるツールなのではないかと思う。

という事で、そういった経験を経て私が伝えたいテクニックなどは膨大にあるのだが、少しずつでも世の中に還元していければなという事でVBAシリーズの初回を始める事にしたい。初回で終わるかもしれないし、気が向けば色々な記事を追加していくかもしれない。まあ、この初回だけでも是非見ていってくれたら幸いである。

VBAやるならこれだけは入れておけ！【標準モジュール編】

①AlpNum

Option Explicit

Enum AlpNum
    A__ = 1
    B__
    C__
    D__
    E__
    F__
    G__
    H__
    I__
    J__
    K__
    L__
    M__
    N__
    O__
    P__
    Q__
    R__
    S__
    T__
    U__
    V__
    W__
    X__
    Y__
    Z__
    AA_
    AB_
    AC_
    AD_
    AE_
    AF_
    AG_
    AH_
    AI_
    AJ_
    AK_
    AL_
    AM_
    AN_
    AO_
    AP_
    AQ_
    AR_
    AS_
    AT_
    AU_
    AV_
    AW_
    AX_
    AY_
    AZ_
    BA_
    BB_
    BC_
    BD_
    BE_
    BF_
    BG_
    BH_
    BI_
    BJ_
    BK_
    BL_
    BM_
    BN_
    BO_
    BP_
    BQ_
    BR_
    BS_
    BT_
    BU_
    BV_
    BW_
    BX_
    BY_
    BZ_
    CA_
    CB_
    CC_
    CD_
    CE_
    CF_
    CG_
    CH_
    CI_
    CJ_
    CK_
    CL_
    CM_
    CN_
    CO_
    CP_
    CQ_
    CR_
    CS_
    CT_
    CU_
    CV_
    CW_
    CX_
    CY_
    CZ_
    DA_
    DB_
    DC_
    DD_
    DE_
    DF_
    DG_
    DH_
    DI_
    DJ_
    DK_
    DL_
    DM_
    DN_
    DO_
    DP_
    DQ_
    DR_
    DS_
    DT_
    DU_
    DV_
    DW_
    DX_
    DY_
    DZ_
    EA_
    EB_
    EC_
    ED_
    EE_
    EF_
    EG_
    EH_
    EI_
    EJ_
    EK_
    EL_
    EM_
    EN_
    EO_
    EP_
    EQ_
    ER_
    ES_
    ET_
    EU_
    EV_
    EW_
    EX_
    EY_
    EZ_
    FA_
    FB_
    FC_
    FD_
    FE_
    FF_
    FG_
    FH_
    FI_
    FJ_
    FK_
    FL_
    FM_
    FN_
    FO_
    FP_
    FQ_
    FR_
    FS_
    FT_
    FU_
    FV_
    FW_
    FX_
    FY_
    FZ_
    GA_
    GB_
    GC_
    GD_
    GE_
    GF_
    GG_
    GH_
    GI_
    GJ_
    GK_
    GL_
    GM_
    GN_
    GO_
    GP_
    GQ_
    GR_
    GS_
    GT_
    GU_
    GV_
    GW_
    GX_
    GY_
    GZ_
    HA_
    HB_
    HC_
    HD_
    HE_
    HF_
    HG_
    HH_
    HI_
    HJ_
    HK_
    HL_
    HM_
    HN_
    HO_
    HP_
    HQ_
    HR_
    HS_
    HT_
    HU_
    HV_
    HW_
    HX_
    HY_
    HZ_
    IA_
    IB_
    IC_
    ID_
    IE_
    IF_
    IG_
    IH_
    II_
    IJ_
    IK_
    IL_
    IM_
    IN_
    IO_
    IP_
    IQ_
    IR_
    IS_
    IT_
    IU_
    IV_ = 256
End Enum

②ShInfo

Option Explicit

Type ShInfo
    SR As Long 'StartRow
    ER As Long 'EndRow
    SC As Long 'StartCol
    EC As Long 'EndCol
    TC As Long 'TargetCol
    
    sh As Worksheet
    bk As Workbook
    
    region As Range '特定のセル範囲を格納
    matrix As Variant 'regionをVariant型で格納
End Type

AlpNum

という事でいきなりガガッと書いたが、まずは①AlpNumから見ていこう。

Enum AlpNum
    A__ = 1
    B__
    C__

これは何をしているかというと、A__という定数に1を代入、それ以降は勝手にインクリメントされて代入されているだけである。
つまり
B__ = 2
C__ = 3
と書いていっても同じことであるが、それを省略している。
なので最後の「IV_ = 256」も書かなくても良いが、これは見る人に分かりやすく256まで作りましたよと明示的に示しているだけである。

    IT_
    IU_
    IV_ = 256
End Enum

どうしてこのような列挙型を作ったかと言うとだが、皆さんは列を指定する時に番号で直接指定した事はないだろうか？例えば列をループする時にこのような感じでやるとか。

    For i = 6 To 24
        '何か処理
    Next

何てことない、普通のコードじゃないかと思った方もいるかもしれない。しかしこれは大いに改善の余地がある。こういったマジックナンバー（何の数字か分からない）を使った処理というのは極力避けるべきなのだ。それには色々な方法があるのだが、最も手軽にやるならこれでどうだろう。

    For i = F__ To X__
        '何か処理
    Next

先程のEnum定義を標準モジュールで作っておく事により、上記のような書き方が可能になる。最初の「6 to 24」だって、恐らくは元の表をみながら「F列からX列まで処理をしたいが・・・Fは６で、Xはいくつだ。。２４列目か！」みたいな感じで作ったのかもしれない。それがこれを使う事により、ただFやXを直接代入するだけで済むようになったのである。

あるいはこのようなコードを書く事もあるだろう。

    Cells(1, 8).Value = 1
    Cells(1, 23).Value = 37
    Cells(6, 104).Value = 11

ただの例なので数字は適当である。とりあえずCellsを使ってただ数字を入れているコードだ。これも堅牢にやるなら名前付きセルを使うとか改善には色々方法はある訳だが、手軽にやるなら先ほどと同じように書けるのである。

    Cells(1, H__).Value = 1
    Cells(1, W__).Value = 37
    Cells(6, CZ_).Value = 11

どの列に代入したいのか、ほんの一手間掛けるだけでグッと分かりやすくなった。それとコードの見た目で縦ラインが揃うのもポイント高い。定数にアンダーバーを桁数揃えで入れた理由がこれにある（＆変数名が他のと衝突しにくいように）。

このように、この定数はどこでも使えるので例えばConst定数の代入値としても使える。

Public Const 代入列1 As Long = G__
Public Const 代入列2 As Long = BA_

中々便利ではないだろうか。
しかしアンダーバーを入れたとはいえ、「私の既存マクロでは普通に変数でA__やB__など使っているんです！」そんな場合ももしかしたらあるかもしれない。そういうプロジェクトの時はEnum名を付加してあげればよい。
例えば次のコードはイミディエイトウィンドウに[1, 2, 1, 2]と普通は出る所だが、

    Debug.Print A__
    Debug.Print B__
    Debug.Print AlpNum.A__
    Debug.Print AlpNum.B__

つまり、Enum名のAlpNumを省略して書けているという事だ。
しかし次の場合はどうだろう。

    Dim A__ As String
    Dim B__ As String
    A__ = "A"
    B__ = "B"
    Debug.Print A__
    Debug.Print B__

これはやってみると分かるが、ローカル変数に代入した[A, B]が出てしまう。
こういう場合はどうするか、それはこうするのである。

    Dim A__ As String
    Dim B__ As String
    A__ = "A"
    B__ = "B"
    Debug.Print A__
    Debug.Print B__
    Debug.Print AlpNum.A__
    Debug.Print AlpNum.B__

これであればイミディエイトウィンドウには[A, B, 1, 2]ときちんと区別して出てくれる。
つまり変数名が衝突する可能性がある所では全て明示的にEnum名を付けるというのが対策の一つ、もう一つはEnum定数の方でアンダーバーを一つ多く増やすとか、定数名自体を少しいじってあげてもいいかもしれない。あるいは一番簡単なのはローカル変数の方の名前を見直す事だろうか。

まあ、いずれにしてもどのような場合でも便利に使えるはずである。

ShInfo

Type ShInfo
    SR As Long 'StartRow
    ER As Long 'EndRow
    SC As Long 'StartCol
    EC As Long 'EndCol
    TC As Long 'TargetCol
    
    sh As Worksheet
    bk As Workbook
    
    region As Range '特定のセル範囲を格納
    matrix As Variant 'regionをVariant型で格納
End Type

次は、VBA版構造体ともいうべきか、Type変数の便利な使い方の一つを紹介する。
実務では色々な要件があるとは思うが、複数のシート、あるいは複数のブックを操作する処理を行うというのはよくある事である。それで、それらのシート毎にシート名、開始行、終端行などを変数として定義していくと、変数がやたら多くなってどれが何やら・・後から見た時に「さっぱり分からん！」となる事はないだろうか？

例えばこんな感じである。

    Dim sheetA As Worksheet
    Set sheetA = ThisWorkbook.Sheets("SheetA")
    Dim A_SR As Long '処理開始行（StartRow）
    Dim A_ER As Long '処理終了行（EndRow）
    Dim A_SC As Long '処理開始列（StartCol）
    Dim A_EC As Long '処理終了行（EndCol）
    A_SR = 2
    A_ER = sheetA.Cells(Rows.Count, A__).End(xlUp).Row
    A_SC = A__
    A_EC = sheetA.Cells(1, Columns.Count).End(xlToLeft).Column
    
    Dim i As Long
    For i = A_SR To A_ER
        '何か処理
    Next
    
    Dim sheetB As Worksheet
    Set sheetB = ThisWorkbook.Sheets("SheetB")
    '以下、SheetAの時と同じように色々定義・・・
    Dim B_SR As Long
    Dim B_ER As Long
    Dim B_SC As Long
    Dim B_EC As Long
    B_SR = 2
    B_ER = sheetB.Cells(Rows.Count, A__).End(xlUp).Row

よくありがちなコードだと思うが、中々面倒な設定作業である。
こういったよく使う変数パターンというのはクラスであったり、あるいは手軽にやるなら今回紹介したType変数を使う事でもっとスマートに実装できる。先ほどのShInfoのコードを適当な標準モジュールに定義した上で、下記のようなコードを書くのだ。

    Dim sheetA As ShInfo
    Set sheetA.sh = ThisWorkbook.Sheets("SheetA")
    sheetA.SR = 2
    sheetA.ER = sheetA.Cells(Rows.Count, A__).End(xlUp).Row
    sheetA.SC = A__
    sheetA.EC = sheetA.Cells(1, Columns.Count).End(xlToLeft).Column
    
    Dim i As Long
    For i = sheetA.SR To sheetA.ER
        '何か処理
    Next
    
    Dim sheetB As ShInfo
    Set sheetB.sh = ThisWorkbook.Sheets("SheetB")
    '以下、SheetAの時と同じように色々定義・・・簡単！
    sheetB.SR = 2
    sheetB.ER = sheetB.Cells(Rows.Count, A__).End(xlUp).Row

かなりスマートになったと言えないだろうか？

わざわざA_SR, B_SRなどと変数を定義する必要がなくなるし、ドットで繋がれた階層構造になっているので（例：sheetA.SR）、どのグループに属している変数かというのが分かりやすくなる。それに入力もめちゃ楽なのである。ドットを打った瞬間に候補がずらっと出てくるのがとてもいい。
これで入力ミスもなくなるだろう。

それと極めて大きな利点がもう一つある。
それはこのShInfoを引数としてプロシージャに渡せるという事である。
例えば次のような感じ。

Sub CallA()
    Dim sheetA As ShInfo
    Set sheetA.sh = ThisWorkbook.Sheets("SheetA")
    sheetA.SR = 2
    sheetA.ER = sheetA.Cells(Rows.Count, A__).End(xlUp).Row
    sheetA.SC = A__
    sheetA.EC = sheetA.Cells(1, Columns.Count).End(xlToLeft).Column
    
    Dim sheetB As ShInfo
    Set sheetB.sh = ThisWorkbook.Sheets("SheetB")
    sheetB.SR = 2
    sheetB.ER = sheetB.Cells(Rows.Count, A__).End(xlUp).Row
    sheetB.SC = A__
    sheetB.EC = sheetB.Cells(1, Columns.Count).End(xlToLeft).Column
    
    Call FuncA(sheetA, sheetB)
End Sub

Sub FuncA(sheetA As ShInfo, sheetB As ShInfo)
    Dim i As Long
    For i = sheetA.SR To sheetA.ER
        '何か処理
    Next
End Sub

変数としてSheetAを渡すだけで、SheetA配下で定義した色々な変数がFuncAでも使えるようになるのである。これをShInfoを使わない場合でやったらどうなるか・・・もう経験している方も多いかと思うが、中々地獄絵図ではないだろうか（笑）。つまりは各プロシージャでまた同じように「A_SR = ...」と定義するとかしないといけない。あるいはそれが面倒であれば、全てをグローバル変数にしているプロジェクトも多いだろう。もうなんか、グローバル変数だらけになるのである。

勿論ShInfoを使った場合でもShInfoをグローバル変数にするやり方は全然アリだ。それであっても素で書くよりは随分と使いやすくなるはずなので、恩恵は十二分に得られるだろう。

おまけ
このShInfoには「region」と「matrix」という変数も定義しているのだが、これの使い方だけさらっと示して終わりにする。

    region As Range '特定のセル範囲を格納
    matrix As Variant 'regionをVariant型で格納
End Type

一般的な高速化テクニックとして、セルに値を入れるときに直接そのまま代入するよりも配列に入れてから最後に代入するというのがある。
それについて私がお世話になった記事はこれ↓

「region」と「matrix」はこういった作業をする時の設定の手間を少し減らしてくれる変数群である。それで、実際使うとしたらこんな感じだ。

    Set sheetA.region = sheetA.sh.Range("A1").CurrentRegion
    sheetA.matrix = sheetA.region
    
    'ここでsheetA.matrixに何か色々代入処理
    '例えば、
    'sheetA.matrix(1, 1) = "A"
    'みたいな感じで、セルに代入するのと同じような書き方で扱える。
    
    '最後にそれをregionに代入する
    sheetA.region = sheetA.matrix

ただ単にセルに直接値を代入するよりも、そのセルと同じ領域をmatrix変数に入れてあげて、それに代入してから最後にセルに戻しているだけである。この一手間を加える事で速度がかなり改善される。
まあ、これはこれでセルに値が実際入るのが最後だけなのでデバッグがしづらいとか開発段階ではデメリットもあるので、最後の方で考える最適化にしてもよい。
つまりはケースバイケースで使い分けるといいだろう。

標準モジュール名について

ここまで見てきた「AlpNum」と「ShInfo」だが、私は次のような標準モジュール名でプロジェクトで活用する事が多い。
見辛くて申し訳ないが、これでいうと「AlpNum」を「Z_Enum_AlpNum」、「ShInfo」を「Z_Utility_Type」という標準モジュール名で定義して、その中に紹介したコードを書いている。私の場合、このようによく使うセットを「Utility.xlsm」というマクロファイルにまとめておいて、新規で何か作る場合はそのブックに使いたい標準モジュールだけ持ってくるという感じにしている。
「Z_Utility_Type」はShInfo以外にも有用なType変数があればどんどんここに追加していく感じ。
「Z_Enum_AlpNum」とかEnum定数で長くずらっと書かれてるものはモジュールを分けた方が管理しやすそうなので、名前もAlpNumという固有名詞まで付加している。

何故「Z_」を標準モジュールの先頭に付けているか？それは標準モジュールの並び順が一番下に来てくれることが多いからである。勿論既存ブックとかで既にこういう名前のモジュールが存在するなら「ZZZ_」に変えるとか臨機応変に対応してもいいだろう。

見て分かるように、他にも色々と便利なユーティリティモジュールを私は定義しているので、機会があれば紹介するかもしれない。

終わりに

どうだっただろうか。
今回紹介した技は私がこれまで培ってきた膨大な知識の１万分の１程度の内容に過ぎない。
しかしこれだけ手軽に活用できる技でもガラッとコードの質を変える事ができるのである。
いいなと思った方は是非自由に使っていただいて、広めていただけたら幸いである。