【他のレンジ/シート/ブックを更新する】
3 逆Ｚ式並びで更新
4 セルで指定したシートを更新
5 複数シートを連続更新
6 Subプロシージャの定義と呼び出し
7 あるブックから別ブックを更新
8 他のブックをオープン・クローズ
9 テンプレートから出力ブックを作成し更新
Subプロシージャの定義と呼び出し
今回は、Python-xlwingsによって、あるシートからデータを読みとり
別シートへデータを書きこむSubプロシージャを定義し、
そのSubプロシージャを２回呼び出して二つのシートを更新してみます。
前回のマクロ『シートから複数シート2』はすごく長いです。
『漢字問題集』シートと『漢字解答集』シートを作成する処理（青の枠線内のコード）は、
『出題漢字の空白化』の箇所を除けば全く同じです。
『漢字問題集』シートと『漢字解答集』シートを作成する処理で共通する部分は、
別のプロシージャに切り離すことができます。
そして、その切り離したプロシージャを元のプロシージャから何度でも呼び出すことができます。
そうすることで、長いマクロを非常に短くすることができます。
今回は、『漢字入力帳』シートからデータを読みとり、
『漢字問題集』シート、及び、『漢字解答集』シートへデータを書きこむために、
元のプロシージャから共通部分を切り離して、
元のプロシージャからそのプロシージャを２回呼び出すマクロをつくります。
それを図に表すと次のようになります。
そこで、その準備として、Excelマクロ有効ブック内に、『漢字入力帳』シートと
『漢字問題集』・『漢字解答集』シートを作成しておきましょう。
前回使用したExcelマクロ有効ブックをコピーして『9_他の複数シートを更新3.xlsm』を作成します。
『漢字入力帳』シート
今回も、『漢字入力帳』シートからデータを読みとり、
一つ目に『漢字問題集』シート、二つ目に『漢字解答集』シートを作成しますが、
元のプロシージャから共通部分を切り離して、
元のプロシージャからそのプロシージャを２回呼び出すようにマクロのみ変更します。
マクロ『シートから複数シート2』を、マクロ『シートから複数シート3』に名前を変えます。
では、宣言部を改修しましょう。
先ず、マクロ『シートから複数シート3』から、宣言部を切りとって、モジュールの最上部に貼り付けます。
Const Est = "漢字入力帳"
Const WSheetMD = "漢字問題集"
Const WSheetKT = "漢字解答集"
Const 読取問題番号列 = "A"
Const 読取列 = "B"
Const 最大枠数 = 4
Const 漢字最小列 = 3
Const 漢字最大列 = 6
Const 読取最小行 = 2
Const 最大列 = 10
Const 書込列数 = 5
Const 書込ステップ列 = -2
Const 書込問題番号最小行 = 1
Const 書込ステップ行 = 15
Dim Wst As String
Dim 読取最大行 As Long
Dim 読取行 As Long
Dim 行番号 As Long
Dim txt As String
Dim 最大文字数 As Integer
Dim 文字位置 As Integer
Dim 漢字列 As Integer
Dim 漢字名 As String
Dim 列英字名 As String
Dim 枠領域 As String
Dim ふりがな読取列 As Integer
Dim ふりがな列 As Integer
Dim ふりがな名 As String
Dim 列番号 As Integer
Dim 問題番号 As Long
Dim 書込問題番号行 As Long
Dim 最小行 As Long
これで、宣言部が、サブプロシージャ『シートから複数シート3』から独立したことになりますが、
このままでは二つ以上のプロシージャの間で変数及び定数を共有することができません。
二つ以上のプロシージャの間で変数を共有できるようにするためには、
変数宣言文字『Dim』を別の変数宣言文字に置き換える必要があります。
変数宣言文字には次の図のような順序があります。
このように、変数宣言文字は、Dim < Private < Publicの順に変数として使用できる範囲が
広くなっていきます。
変数宣言文字『Dim』はそのプロシージャ内だけでしか変数を使用できませんが、
変数宣言文字『Private』または『Public』を使えば二つ以上のプロシージャの間で
変数を共有できるようになります。
今回のマクロでは、変数宣言文字『Private』でも構わないのですが、
変数宣言文字『Public』を使えばより確実に広範囲に変数を共有できるため、これからは、
二つ以上のプロシージャの間で変数を共有できるようにするために『Public』を使うようにします。
変数宣言文字『Dim』を、変数宣言文字『Public』に置き換えてみましょう。
Public Wst As String
Public 読取最大行 As Long
Public 読取行 As Long
Public 行番号 As Long
Public txt As String
Public 最大文字数 As Integer
Public 文字位置 As Integer
Public 漢字列 As Integer
Public 漢字名 As String
Public 列英字名 As String
Public 枠領域 As String
Public ふりがな読取列 As Integer
Public ふりがな列 As Integer
Public ふりがな名 As String
Public 列番号 As Integer
Public 問題番号 As Long
Public 書込問題番号行 As Long
Public 最小行 As Long
変数宣言文字『Public』は、定数宣言文字『Const』の前に付けることで、
二つ以上のプロシージャの間で定数を共有できるようになります。
変数宣言文字『Public』を、定数宣言文字『Const』の前に付けてみましょう。
Public Const Est = "漢字入力帳"
Public Const WSheetMD = "漢字問題集"
Public Const WSheetKT = "漢字解答集"
Public Const 読取問題番号列 = "A"
Public Const 読取列 = "B"
Public Const 最大枠数 = 4
Public Const 漢字最小列 = 3
Public Const 漢字最大列 = 6
Public Const 読取最小行 = 2
Public Const 最大列 = 10
Public Const 書込列数 = 5
Public Const 書込ステップ列 = -2
Public Const 書込問題番号最小行 = 1
Public Const 書込ステップ行 = 15
宣言部の改修は完了したのですが、ここで一つアドバイスをしておきます。
モジュールの最上部に宣言部を記述するときは、
先頭行に、『Option Explicit』ステートメントを入れるようにしましょう。
この『Option Explicit』ステートメントは、モジュール内のすべての変数の明確な宣言を強制します。
『Option Explicit』ステートメントを使用すると、
既存の変数の名前を入力する場合の誤入力を避けることができます。
これで、モジュール最上部の宣言部は次のようになりました。
【宣言部】
Option Explicit
Public Const Est = "漢字入力帳"
Public Const WSheetMD = "漢字問題集"
Public Const WSheetKT = "漢字解答集"
Public Const 読取問題番号列 = "A"
Public Const 読取列 = "B"
Public Const 最大枠数 = 4
Public Const 漢字最小列 = 3
Public Const 漢字最大列 = 6
Public Const 読取最小行 = 2
Public Const 最大列 = 10
Public Const 書込列数 = 5
Public Const 書込ステップ列 = -2
Public Const 書込問題番号最小行 = 1
Public Const 書込ステップ行 = 15
Public Wst As String
Public 読取最大行 As Long
Public 読取行 As Long
Public 行番号 As Long
Public txt As String
Public 最大文字数 As Integer
Public 文字位置 As Integer
Public 漢字列 As Integer
Public 漢字名 As String
Public 列英字名 As String
Public 枠領域 As String
Public ふりがな読取列 As Integer
Public ふりがな列 As Integer
Public ふりがな名 As String
Public 列番号 As Integer
Public 問題番号 As Long
Public 書込問題番号行 As Long
Public 最小行 As Long
では、処理部を改修しましょう。
サブプロシージャ『ワークシート更新』を新しくつくります。
(1) サブプロシージャ『シートから複数シート3』のすぐ下に『ワークシート更新』という名前の
サブプロシージャをつくります。
(2) サブプロシージャ『シートから複数シート3』から、『漢字問題集』シートを作成する部分
（青の枠線内のコード）を切りとって、サブプロシージャ『ワークシート更新』に貼り付けます。
(3) サブプロシージャ『ワークシート更新』の中で、『出題漢字の空白化』の箇所（赤の枠線内のコード）を、
書込ワークシートが『漢字問題集』のときのみ、『出題漢字の空白化』を実行するようにコードを書き換えます。
If Wst = WSheetMD Then
Worksheets(Wst).Range(枠領域).Value = ""
End If
とします。
【サブプロシージャ『ワークシート更新』】
Sub ワークシート更新()
読取最大行 = Worksheets(Est).Cells(Rows.Count, "B").End(xlUp).Row
' ===== 読取行のｶｳﾝﾄｱｯﾌﾟS =====
For 読取行 = 読取最小行 To 読取最大行
問題番号 = Worksheets(Est).Cells(読取行, 読取問題番号列).Value
書込問題番号行 = Int((問題番号 - 1) / 書込列数) * 書込ステップ行 _
+ 書込問題番号最小行
最小行 = 書込問題番号行 + 1
列番号 = 最大列 + ((問題番号 - 1) Mod 書込列数) * 書込ステップ列
' Wst = Worksheets(Est).Cells(読取行, 書込ワークシート列).Value
列英字名 = Replace(Worksheets(Wst).Cells(1, 列番号) _
.Address(False, False), "1", "")
枠領域 = 列英字名 & 書込問題番号行
Worksheets(Wst).Range(枠領域).Value = 問題番号
Worksheets(Wst).Range(枠領域).Font.Size = 10
txt = Worksheets(Est).Cells(読取行, 読取列).Value
最大文字数 = Len(txt)
' ===== 対象文字列文字位置のｶｳﾝﾄｱｯﾌﾟS =====
For 文字位置 = 1 To 最大文字数
行番号 ＝ 最小行 + 文字位置 - 1
Worksheets(Wst).Cells(行番号, 列番号).Value = Mid(txt, 文字位置, 1)
Next 文字位置
' ===== 対象文字列文字位置のｶｳﾝﾄｱｯﾌﾟE =====
' ===== 漢字列のｶｳﾝﾄｱｯﾌﾟS =====
For 漢字列 = 漢字最小列 To 漢字最大列
漢字名 = Worksheets(Est).Cells(読取行, 漢字列).Value
If 漢字名 <> "" Then
文字位置 = InStr(Worksheets(Est).Cells(読取行, 読取列).Value, _
漢字名)
If 文字位置 > 0 Then
行番号 ＝ 最小行 + 文字位置 – 1
列英字名 = Replace(Worksheets(Wst).Cells(1, 列番号) _
.Address(False, False), "1", "")
枠領域 = 列英字名 & 行番号
Worksheets(Wst).Range(枠領域).Borders.LineStyle _
= xlContinuous
If Wst = WSheetMD Then
Worksheets(Wst).Range(枠領域).Value = ""
End If
ふりがな読取列 = 漢字列 + 最大枠数
ふりがな名 = Worksheets(Est).Cells(読取行, ふりがな読取列).Value
If ふりがな名 <> "" Then
ふりがな列 = 列番号 + 1
列英字名 = Replace(Worksheets(Wst).Cells(1, ふりがな列) _
.Address(False, False), "1", "")
枠領域 = 列英字名 & 行番号
Worksheets(Wst).Range(枠領域).Value = ふりがな名
Worksheets(Wst).Range(枠領域).Font.Size = 8
Worksheets(Wst).Range(枠領域).HorizontalAlignment _
= -4131
Worksheets(Wst).Range(枠領域).Orientation = -4166
Worksheets(Wst).Range(枠領域).ShrinkToFit = True
End If
End If
End If
Next 漢字列
' ===== 漢字列のｶｳﾝﾄｱｯﾌﾟE =====
列番号 = 列番号 + 書込ステップ列
Next 読取行
' ===== 読取行のｶｳﾝﾄｱｯﾌﾟE =====
End Sub
サブプロシージャ『シートから複数シート3』を改修します。
(1) サブプロシージャ『シートから複数シート3』から、『漢字解答集』シートを作成する部分を削除します。
(2) 書込ワークシートに『漢字問題集』を格納した後、サブプロシージャ『ワークシート更新』を呼び出し、
『漢字問題集』シートを作成するように記述します。
(3) 書込ワークシートに『漢字解答集』を格納した後、サブプロシージャ『ワークシート更新』を呼び出し、
『漢字解答集』シートを作成するように記述します。
あるサブプロシージャから別のサブプロシージャを呼び出すには、『Call』ステートメントを使用します。
サブプロシージャ『ワークシート更新』を呼び出すときは、
Call ワークシート更新
とします。
【サブプロシージャ『シートから複数シート3』】
Sub シートから複数シート3()
'◆◆◆◆◆◆◆◆◆◆◆◆◆◆ 漢字問題集 ◆◆◆◆◆◆◆◆◆◆◆◆◆◆
Wst = WSheetMD
Call ワークシート更新
'◆◆◆◆◆◆◆◆◆◆◆◆◆◆ 漢字解答集 ◆◆◆◆◆◆◆◆◆◆◆◆◆◆
Wst = WSheetKT
Call ワークシート更新
End Sub
それでは、このマクロを実行してみましょう。
_ 『漢字問題集』シート
_ 『漢字解答集』シート
『漢字入力帳』シートに用意した10問の漢字文字列が、出題漢字を空欄にした『漢字問題集』シートと、
出題漢字をそのまま残した『漢字解答集』シートへ、書きこまれました。
長いプロシージャは、共通する部分を分離して、別のプロシージャに切り離すことができます。
そして、その切り離したプロシージャを元のプロシージャから何度でも呼び出すことができます。
そうすることで、長いマクロを非常に短くすることができます。