Book、Sheet、Cell の取り扱い
文例を見てもなかなかしっくりこないものです。
マクロを使う上でのファイル(Book)の関係や命令（メソッド）の詳細を解説します。
1.マクロとBookの関係（及びメニュー）
全てデータを一人で入力して加工する場合は全く必要のないことですが、他の人にデータを入力してもらったり、サーバからダウンロードしたデータをExelに呼び込んで処理する場合などは必要に応じてBookとモジュールの関係性を整理しておいた方が良いでしょう。
その前に呼び出す（実行する）プロシージャを選択できるメニューの作り方を簡単に説明します。
メニューの作り方
開発－マクロの画面からモジュールを呼び出すのが基本ですが、メニュー画面を作成し、そこにコマンドボタンを配置することで呼び出しやすくなります。
手順は以下の通りです ①開発リボンから 【挿入】【フォームコントロール】【ボタン】を選択する
②③登録したいモジュール（プロシージャ）を選択して【ok】を押す
④ ボタンの表示を変える（右クリックで）【テキストの編集】を選ぶとボタンの表示が変更できます。
(1)マクロを登録するBook内での処理
マクロを登録するBookを決めてそこにモジュールを登録し、必要があれば他のBookからデータを貼り付けて使用するのが基本となります。
イメージとしては右の図のようになります。黄色はマクロのモジュールが登録されていることを意味しています。すべてのSheet名はこのBookにあるため、Sheet名が重複することはありません。
(2)マクロを持たないファイルでの処理
(2) - ① マクロを持っていないBookからマクロを持っているBookの中のマクロを呼び出す
マクロを持たないBookとマクロを持っているBookを両方開きます。次にマクロを持たないBookから開発⇒マクロを選ぶと別ファイルのマクロを呼び出せます。マクロ作成者がそのままデータ管理をする場合の安直な方法です。
ただし呼ばれる側のモジュールはSheetXX.selectを外すなどの配慮が必要です。（開いているSheet以外に書き込みをしないため）
Bookの実線は書き込み処理有り、破線は読み込みだけを意味します。
(2) - ② マクロを持っているBookからマクロを持っていないBookを操作する。
これはBookの開き方は以下の(3)と同じです。ただUpdateするのがマクロを持たないBookなのか、マクロを持っているBookなのかの違いです。配信が必要なBookなどはxlsmは不適なのでこの方法を用います。
(3)マクロを登録してあるBookに他のBookからデータを読み込む
(1)の処理と類似していますが、他のBookから貼り付けるデータがある時次のような効果があります。
- （手作業に比べて）ファイルをオープンする速度、貼り付ける速度が速い。
- コピー、貼付ミスを防げる
データを呼び込む時はシートに対して新規上書き、追加などできますが、前者の時はエリアの事前クリアーが必要になります。後者の場合は追加ポイントをどこかに記憶させておくことをおすすめします。
(4)マクロを登録してあるBookとは別のBook間での処理
マクロの登録されているBookからマクロを起動させたら後は他のBookを複数開いて、そのBook間でデータ移動・集計などの処理をします。次のような場合にこの方法をとります。
- 更新するBookの管理権を持っていない場合（所在・フォームを勝手に変えられない）
- 更新Bookを配信するため、マクロ付Bookでははばかれる場合
（5）具体的に複数のBookを開いて扱うモジュールの一例
下記のモジュールはそのマクロが入っているシートから別のworkbookの各Sheet1 Range("C3") を表示してBookをクローズするものです。
複数のBook、複数のSheetを扱う時にはWorkbookオブジェクトとWorksheetオブジェクトを使うことをお勧めします。下記の①と②、③と④は全く同じ処理をした結果となります。記述がすごく簡単になりますね。
特にファイルを開いて閉じる場合には
A.開く場合 ⇒ ディレクトリを含めたフルネーム
B.閉じる場合 ⇒ Excelのファイル名のみ 2つの名前を使い分けないといけませんが workbookオブジェクトを使うと
それが１つで済みます。
またWorksheetオブジェクトを使用すれば、Book名を記述せずに直接Sheetを参照できます。
Sub A020_データ表示()
Dim xlBook1, xlBook2, xlBook3 As Workbook
Dim Fname1, Fname2, Fnmae3 As String
Dim sh_1, sh_2 As Worksheet
Set xlBook1 = ActiveWorkbook
Worksheets("環境設定").Select
Fname2 = Range("C2")
' RANGE("C2") の中身 C:\Users\zuikak\OneDrive\ドキュメント\2022\VBATEST\VBATEST2.xlsx
Fname3 = Range("C3")
' RANGE("C2") の中身 C:\Users\zuikak\OneDrive\ドキュメント\2022\VBATEST\VBATEST3.xlsx
'ファイルが存在するかチェックして、存在しなければメッセージを出して終了する
If Dir(Fname2) <> "" Then
'Bookを開くと同時に Workbook オブジェクトにセット
Set xlBook2 = Workbooks.Open(Fname2)
Else
MsgBox Fname2 & " ファイルが存在しません。プログラムを終了します"
Exit Sub
End If
'worksheet オブジェクトにセット
Set sh_1 = xlBook2.Sheets("sheet1")
If Dir(Fname3) <> "" Then
Set xlBook3 = Workbooks.Open(Fname3)
Else
MsgBox Fname3 & " ファイルが存在しません。プログラムを終了します"
Exit Sub
End If
Set sh_2 = xlBook3.Sheets("sheet1")
MsgBox Workbooks("VBATEST2.xlsx").Sheets("Sheet1").Range("C3") '①
MsgBox sh_1.Range("C3") '②
MsgBox sh_2.Range("C3")
Workbooks("VBATEST2.xlsx").Close '③
'xlBook2.Close '④
xlBook3.Close
End Sub
2.セルとセルの範囲の表現方法
コピーし・貼り付けする時、For文でループさせるにセルの位置関係を変数表記で正確に表現することが必要になります。VBAではセルの表現方法は一般的に3種類あります。
- 列をアルファベット、行を数字で表す方法
- 行を数字、列も数字で表す方法
- 行を数字、列はアルファベットで表す方法
しかも単一セルと複数セルの表現が異なるのでなれるまで少したいへんです。
以下３つの例を示しますが前提となるセルは
単一・・・B3 範囲・・・B3:D8です
変数は i = 3 j = 8 の前提です
(1)列をアルファベット、行を数字で表す方法
単一 Range(B & "i")
範囲 Range(B & "i" & ":D" & j)
(2)行を数字、列も数字で表す方法
単一 Cells(i,2)
範囲 Range(Cells(i,2),Cells(j,4)
(3)行を数字、列はアルファベットで表す方法
単一 Cells(i,”B”)
範囲 Range(Cells(i,"B"),Cells(j,"D")
(2)の場合は列にも変数を用いることができます。普段私は単一セルでは(3)、範囲を指定する時には(1)を使う時が多いですが、特に理由はありません。習慣の問題です。
3.IFを使いこなす
プログラミングではIF文が重要な役割を持ちます。
様々な属性を持ったデータをそれぞれに適した扱いをするにはIF文が必要になります。
ここでは少し踏み込んだ使用方法を紹介します。
(1)基本的なIFの使用方法３種
単一同じIF文でも使い方で３つの形をとります
1.基本型 IF Cells(i,"C") > Cells(i,"B") Then 'もしi行C列の値がi行B列の値より大きければ
Cells(i,"D") = 1 'i行D列の値を 0 とする
else
Cells(i,"D") = 0 'そうでなければ1とする
End if
2.elseを持たない形
①IF Cells(i,"C") > Cells(i,"B") Then 'もしi行C列の値がi行B列の値より大きければ
Cells(i,"D") = 1 'i行D列の値を １ とする
End if
同じ機能で短縮形
②IF Cells(i,"C") > Cells(i,"B") Then Cells(i,"D") = 1
2.のケースではあらかじめ Cells(i,"D")に０を代入しておくのが一般的です
(2)アンド条件とオア条件
上記は一つの判断条件だけでしたが複数条件の判定が必要な場合があります。
(1)の判定の他にたとえば年齢条件を加えることにします。
アンド条件・・・20歳以上であることが必要となる。20歳以上でなければCells(i,"D")は０となる
オア条件 ・・・20歳以上であれば(1)の判定に関係なくCells(i,"D") は 1 となる
1-1.アンド条件の例 基本型
IF Cells(i,"C") > Cells(i,"B") Then 'もしi行C列の値がi行B列の値より大きければ
IF 年齢 >= 20 Then
Cells(i,"D") = 1 'i行D列の値を 1 とする
else
Cells(i,"D") = 0 'そうでなければ0とする
End if
else
Cells(i,"D") = 0 'そうでなければ0とする(対象がわかりずらい）
End if
1-2.アンド条件の例 elseを極力使わない方法
Dim F1 as Long
Dim F2 as Long
・・・・・・・・・・・・・・・・・・・・ここからループ文（Forなど）
F1 = 0
F2 = 0
IF Cells(i,"C") > Cells(i,"B") Then 'もしi行C列の値がi行B列の値より大きければF1を１とする
F1= 1
End if
IF 年齢 >= 20 Then 'もし年齢の値が20以上ならばF2を１とする
F2 = 1
End if
Cells(i,"D") = F1 * F2
個々の条件が成立しているかどうかをで記憶しておき、あとで掛け合わせれば一つでも不成立の時に結果は０となり全て成立している時だけ結果が１となります。
2-1.オア条件の例 基本型
IF Cells(i,"C") > Cells(i,"B") Then 'もしi行C列の値がi行B列の値より大きければ
Cells(i,"D") = 1 'i行D列の値を 1 とする
else
IF 年齢 >= 20 Then
Cells(i,"D") = 1 'i行D列の値を 1 とする
End if
else
Cells(i,"D") = 0 'そうでなければ0とする
End if
2-2.オア条件の例 elseを極力使わない方法
Dim F1 as Long
Dim F2 as Long
・・・・・・・・・・・・・・・・・・・・・ここからループ文（Forなど）
F1 = 0
F2 = 0
IF Cells(i,"C") > Cells(i,"B") Then 'もしi行C列の値がi行B列の値より大きければ
F1 = 1
End if
IF 年齢 >= 20 Then 'もし年齢の値が20以上ならばF2を１とする
F2 = 1
End if
F1 = F1 + F2
IF F1 > 1 Then
Cells(i,"D") = 1
else
Cells(i,"D") = 0 'そうでなければ0とする
End if
個々の条件が成立しているかどうかをで記憶しておき、あとで足し合わせればどれか一つでも成立したときに判定フラグは１以上になります。
1-1や2-1を理解することは（人のプログラムを流用したりする時に）必要ですが、あとは自分でプログラムを見直した時に理解しやすい方法が一番です。特に３レベル以上の条件になると else はそこから上位の判定を背負うことになりますので、見直す時など大変になります。
(3)Select Case 文及びIf文からの離脱
Ifの判定がyes,noではなく複数の選択肢からなる場合はSelect Case文を使うのが一般的かと思います。
例として年齢別の料金で考えてみます
年齢が60歳以上なら3000円、30歳以上60才未満なら5000円、23歳以上30歳未満なら4000円、23歳未満なら2000円という料金があったとします。1-1の方法をとるとIfとelseをかなり深いレベルまでさげていかなければな
りません。
1-2に準じた方法で
①60歳以上の場合
②30歳以上60歳未満の場合
③23歳以上30才未満の場合
④23才未満の場合
と独立した条件判定を設ければIf文のレベルは最大2レベルで抑えることができます。しかし②③に関しては２つのIf判定が必要になり、また4つのケースが全ての条件を満たしているのか、①～④が全て同じレベルの並列の判断なのかデバッグする時少し悩む可能性があります。そこで使われるのがSelect Case文です
1.基本型
Select Case 年齢.value (あるいは Cells(i,"F").value など）
Case Is >= 60
料金 = 3000 (あるいは Cells(i,"H") = 3000など
Case Is >=30
料金 = 5000
Case Is >=23
料金 = 4000
Case else
料金 = 2000
End Select
この文を使うと一つの条件が成立すれば他のCase以下は判定せずにEnd Select までジャンプしてくれるので
結果のデバッグもしやすいし、If文の節約にもなります。但し、年齢の他に条件がある場合（男女別の料金差など）片方ではCase文の判定、以下はIf文の判定となり解りにくくなる可能性もあります
2.SelectCase文を使わない方法
SelectCase文を使わない場合、一つの判定がされたらそれ以下は判定しないという仕組みが必要となりますがIf文は『exit』することができません。そこでダミーでFor文を入れることを考えました。For文はダミーで１回しか実行されません。
料金 = 0
For i = 1 to 1
If 年齢 >= 60 Then
料金 = 3000
Exit For
End If
If 年齢 >= 30 Then
料金 = 5000
Exit For
End If
If 年齢 >= 23 Then
料金 = 4000
Exit For
End If
料金 = 2000
Next
文は少し長くなりますが、何と何を比較しているのかわかりやすくなるかもしれません。
お好みの方法をお使い下さい。