【他のレンジ/シート/ブックを更新する】
1 あるレンジから別レンジを更新
2 あるシートから別シートを更新
3 逆Ｚ式並びで更新
4 セルで指定したシートを更新
5 複数シートを連続更新
6 Subプロシージャの定義と呼び出し
7 あるブックから別ブックを更新
あるシートから別シートを更新
今回は、Excel VBA/マクロによって、あるシートからデータを読みとり別シートへデータを書きこんでみます。
1. あるシートから別シートを更新
Excel VBA/マクロで、シート内で、あるセルから別のセルに値を代入するときは、次のような構文になります。
シート内で、あるセルから別のセルに値を代入するときは、ワークシート名が無くても、コンピューターは
どこから、どこに代入するかを判断できます。
だけど、あるシートから別のシートに値を代入するときは、ワークシート名が無ければ、コンピューターは
どのワークシートのセルから、どのワークシートのセルに代入するかを判断できなくなります。
そのため、Excel VBA/マクロで、あるシートのセルから、別シートのセルに値を代入するときは、
それぞれのワークシート名を指定します。
次のような構文になります。
2. 別シートに漢字学習帳を作成
前回は、同一シート内で、『漢字入力帳』レンジからデータを読みとり、
『漢字学習帳』レンジへデータを書きこみました。
今回は、『漢字入力帳』シートからデータを読みとり、
『漢字学習帳』シートへデータを書きこんでみます。
そこで、その準備として、Excelマクロ有効ブック内に、
『漢字入力帳』シートと『漢字学習帳』シートを作成しておきます。
先ずは、前回使用したExcelマクロ有効ブックをコピーして
『9_シートからシートへ代入する.xlsm』を作成します。
次に、『9_シートからシートへ代入する.xlsm』の『Sheet1』のシート名を『漢字入力帳』に変更し、
レンジ『L2:AE13』をクリアします。
そして、シート名『漢字入力帳』にカーソルを合わせて右クリックし、『移動またはコピー』を選択します。
『シートの移動またはコピー』画面が表示されたら、『末尾へ移動』を選択します。
『コピーを作成する』にチェックを入れて、『OK』をクリックします。
最後に、コピーして作成された『漢字入力帳 (2)』のシート名を『漢字学習帳』に変更し、
レンジ『A1:J11』をクリアします。
これで、『漢字入力帳』シートからデータを読みとり、『漢字学習帳』シートへデータを書きこむ
準備ができました。それでは、『漢字入力帳』シートからデータを読みとり、『漢字学習帳』シートへ
データを書きこむマクロを作成しましょう。『漢字入力帳』シートを選択します。
Excelの『漢字入力帳』シートのB列に記述されている文を１文字ずつ分解しながら、
『漢字学習帳』シートに2行目から縦書きで書きこみ、
『漢字入力帳』シートのC～F列に記述されている漢字が
『漢字学習帳』シートに書きこんだ文字の中にあれば、
当セルを空欄のマスにして、併せてそのすぐ右側に、
『漢字入力帳』シートのG～J列に記述されているふりがなを書きこむ。
この一連の処理を、『漢字入力帳』シートの最小行から最大行までのループ処理で一気に実行し、
『漢字学習帳』シートのL列からAD列まで瞬時に書きこみます。
定数『Est』に『漢字入力帳』を代入
定数『Wst』に『漢字学習帳』を代入
定数『読取列』に『B』列を代入
定数『最大枠数』に『4』を代入
定数『漢字最小列』にC列つまり『3』列を代入
定数『漢字最大列』にF列つまり『6』列を代入
定数『読取最小行』に『2』を代入
定数『最小列』にL列つまり『12』列を代入
定数『書込ステップ列』に『2』を代入
定数『最小行』に『2』行目を代入
読取最大行を長整数型の変数『読取最大行』として定義
読取行を長整数型の変数『読取行』として定義
行番号を長整数型の変数『行番号』として定義
対象文字列を文字列型の変数『txt』として定義
対象文字列の文字数を整数型の変数『最大文字数』として定義
文字数を取り出す開始位置を整数型の変数『文字位置』として定義
漢字列番号を整数型の変数『漢字列』として定義
漢字名を文字列型の変数『漢字名』として定義
列英字名を文字列型の変数『列英字名』として定義
枠領域を文字列型の変数『枠領域』として定義
ふりがな読取列を整数型の変数『ふりがな読取列』として定義
ふりがな書込列を整数型の変数『ふりがな列』として定義
ふりがな名を文字列型の変数『ふりがな名』として定義
列番号を整数型の変数『列番号』として定義
EstのB列の最大行を取得し、変数『読取最大行』に格納
変数『列番号』に定数『最小列』の値を代入
変数『読取行』を『読取最小行』から『読取最大行』までカウントアップしながら以下の処理を繰り返す
変数『txt』にEstのセル(『読取行』,『読取列』)の値を代入
変数『txt』から文字数を取得し、変数『最大文字数』に格納
変数『文字位置』を 1 から『最大文字数』までカウントアップしながら以下の処理を繰り返す
変数『行番号』に『最小行』＋『文字位置』－『1』を代入
Wstのセル(『行番号』,『列番号』)の値に変数『txt』から取得した『文字位置』の文字を代入
変数『漢字列』を『漢字最小列』から『漢字最大列』までカウントアップしながら以下の処理を繰り返す
変数『漢字名』にEstのセル(『読取行』,『漢字列』)の値を代入
変数『漢字名』が空白でないとき
Estのセル(『読取行』,『読取列』)の値から『漢字名』の位置を調べて変数『文字位置』に格納
変数『文字位置』が 0 より大きいとき
変数『行番号』に『最小行』+『文字位置』－『1』を代入
Wstの変数『列番号』を列英字名に変換し変数『列英字名』に代入
変数『枠領域』に『列英字名』&『行番号』を代入
Wstの枠領域のセルの周りを実線で囲う
Wstの枠領域を空白にする
変数『ふりがな読取列』に『漢字列』＋『最大枠数』を代入
変数『ふりがな名』にEstのセル(『読取行』,『ふりがな読取列』)の値を代入
変数『ふりがな名』が空白でないとき
変数『ふりがな列』に『列番号』＋ 1 を代入
Wstの変数『ふりがな列』を列英字名に変換し変数『列英字名』に代入
変数『枠領域』に『列英字名』&『行番号』を代入
Wstの枠領域のセルの値に『ふりがな名』を代入
枠領域のセルのフォントサイズを8に設定
Wstの枠領域のセルの横方向の配置を左詰めに設定
Wstの枠領域のセルの文字の配置を縦方向に設定
Wstの枠領域のセルの文字を縮小して全体を表示に設定
変数『列番号』に『列番号』＋『書込ステップ列』を代入
マクロ『レンジからレンジ4』をコピーして、マクロ『シートからシート1』をつくります。
先ず、宣言部を改修します。定数を追加します。
定数『Est』に『漢字入力帳』を代入
定数『Wst』に『漢字学習帳』を代入
Const Est = "漢字入力帳"
Const Wst = "漢字学習帳"
そして、処理部を改修します。
マクロ『レンジからレンジ4』では、同一シート内で、あるレンジからデータを読みとり、
別レンジへデータを書きこみました。
マクロ『シートからシート1』では、
あるシート：Estのレンジからデータを読みとり、別シート：Wstのレンジへデータを書きこみます。
そのため、変更箇所は、次のようになります。
Cells(読取行, 読取列)の前に、Worksheets(Est).を追加し、
Worksheets(Est).Cells(読取行, 読取列)とします。
Cells(行番号, 列番号)の前に、Worksheets(Wst).を追加し、
Worksheets(Wst).Cells(行番号, 列番号)とします。
【マクロ『シートからシート1』】
Sub シートからシート1()
Const Est = "漢字入力帳"
Const Wst = "漢字学習帳"
Const 読取列 = "B"
Const 最大枠数 = 4
Const 漢字最小列 = 3
Const 漢字最大列 = 6
Const 読取最小行 = 2
Const 最小列 = 12
Const 書込ステップ列 = 2
Const 最小行 = 2
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
読取最大行 = Worksheets(Est).Cells(Rows.Count, "B").End(xlUp).Row
列番号 = 最小列
' ===== 読取行のｶｳﾝﾄｱｯﾌﾟS =====
For 読取行 = 読取最小行 To 読取最大行
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
Worksheets(Wst).Range(枠領域).Value = ""
ふりがな読取列 = 漢字列 + 最大枠数
ふりがな名 = 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
それでは、マクロ『シートからシート1』を実行してみます。
『漢字学習帳』シートのL列からAD列にかけて、漢字の書きこみ問題が作成されました。
次は、『漢字学習帳』シートのB列からT列にかけて、漢字の書きこみ問題を作成してみましょう。
『漢字学習帳』シートのB列からK列を削除します。
レンジ『B2:U13』をクリアします。
マクロ『シートからシート1』では、『漢字学習帳』シートのL列からAD列にかけて、
漢字の書きこみ問題を作成しました。
今度は、マクロ『シートからシート1』をコピーして、マクロ『シートからシート2』とし、
『漢字学習帳』シートのB列からT列にかけて、漢字の書きこみ問題を作成します。
書込最小列がL列からB列に変わるだけだから、
書込最小列『最小列』を『12』列目から『2』列目に変更します。
宣言部を改修します。
定数『最小列』にB列つまり『2』列を代入
Const 最小列 = 2
今回は、処理部の変更はありません。それでは、このマクロを実行してみましょう。
『漢字学習帳』シートのB列からT列にかけて、漢字の書きこみ問題が作成されました。
3. 左方向への列のカウントアップ処理
『漢字学習帳』シートのT列からB列にかけて右から左へ、漢字問題を書きこむマクロを作成してみましょう。
予め、レンジ『B1:U13』をクリアしておきましょう。
マクロ『シートからシート2』では、『漢字学習帳』シートのB列からT列にかけて
左から右へ２列ずつカウントアップしながら、漢字問題を作成しました。
今度は、マクロ『シートからシート2』をコピーして、マクロ『シートからシート3』とし、
『漢字学習帳』シートのT列からB列にかけて右から左へ２列ずつカウントアップしながら、
漢字問題を作成します。
書込最小列がB列からT列に変わるから、書込最小列『最小列』を『2』列目から『20』列目に変更します。
Const 最小列 = 20とします。
宣言部を改修します。
定数『最小列』にT列つまり『20』列を代入
Const 最小列 = 20
今回は、処理部の変更はありません。それでは、このマクロを実行してみましょう。
『漢字学習帳』シートのT列からB列にかけて右から左へ２列ずつ、漢字問題を作成したかったのに、
『漢字学習帳』シートのT列からAL列にかけて左から右へ２列ずつ、漢字問題が作成されました。
これは、このマクロの書込列カウントアップの代入式
列番号 = 列番号 + 書込ステップ列が、関わっています。
【『シートからシート3』の一部】
End If
End If
End If
Next 漢字列
' ===== 漢字列のｶｳﾝﾄｱｯﾌﾟE =====
列番号 = 列番号 + 書込ステップ列
Next 読取行
' ===== 読取行のｶｳﾝﾄｱｯﾌﾟE =====
End Sub
Const 書込ステップ列 = 2と定数宣言しているから、
書込列カウントアップの代入式列番号 = 列番号 + 書込ステップ列の中の、書込ステップ列の値は2です。
入力行のループの中で、列番号 = 列番号 + 2ということは、
入力行が1行ずつ増える毎に、列番号(書込列)が、左から右に2列ずつ増えていくということになります。
列番号(書込列)が、右から左に2列ずつ減っていくようにするには、どうしたら良いでしょうか。
列番号 = 列番号 + 2ならば、列番号(書込列)が、左から右に2列ずつ増えていくのだから、
列番号 = 列番号 – 2ならば、列番号(書込列)が、右から左に2列ずつ減っていくと思います。
列番号 = 列番号 + 書込ステップ列という代入式で、列番号 = 列番号 – 2を成り立たせるために、
定数『書込ステップ列』を『-2』に変更する必要があります。
宣言部を改修します。
定数『書込ステップ列』に『-2』を代入
Const 書込ステップ列 = -2
それでは、マクロ『シートからシート3』を実行してみます。
『漢字学習帳』シートのT列からB列にかけて右から左へ２列ずつ、漢字問題が作成されました。
4. 先頭行に問題番号を書き込む
次は、『漢字学習帳』シートのT列からB列にかけて右から左へ２列ずつカウントアップしながら、
『漢字入力帳』シートのA列に記述されている問題番号を、先頭行に書きこみます。
今度は、マクロ『シートからシート3』をコピーして、マクロ『シートからシート4』とし、
『漢字学習帳』シートのT列からB列にかけて右から左へ２列ずつカウントアップしながら、
漢字問題を作成すると同時に、先頭行に問題番号を読み書きするプログラムコードを追加します。
定数『Est』に『漢字入力帳』を代入
定数『Wst』に『漢字学習帳』を代入
定数『読取問題番号列』に『A』列を代入
定数『読取列』に『B』列を代入
定数『読取行』に『2』行目を代入
定数『最大枠数』に『4』を代入
定数『漢字最小列』にC列つまり『3』列を代入
定数『漢字最大列』にF列つまり『6』列を代入
定数『読取最小行』に『2』を代入
定数『最小列』にT列つまり『20』列を代入
定数『書込ステップ列』に『-2』を代入
定数『書込問題番号行』に『1』行目を代入
定数『最小行』に『2』行目を代入
読取最大行を長整数型の変数『読取最大行』として定義
読取行を長整数型の変数『読取行』として定義
行番号を長整数型の変数『行番号』として定義
対象文字列を文字列型の変数『txt』として定義
対象文字列の文字数を整数型の変数『最大文字数』として定義
文字数を取り出す開始位置を整数型の変数『文字位置』として定義
漢字列番号を整数型の変数『漢字列』として定義
漢字名を文字列型の変数『漢字名』として定義
列英字名を文字列型の変数『列英字名』として定義
枠領域を文字列型の変数『枠領域』として定義
ふりがな読取列を整数型の変数『ふりがな読取列』として定義
ふりがな書込列を整数型の変数『ふりがな列』として定義
ふりがな名を文字列型の変数『ふりがな名』として定義
列番号を整数型の変数『列番号』として定義
問題番号を長整数型の変数『問題番号』として定義
EstのB列の最大行を取得し、変数『読取最大行』に格納
変数『列番号』に定数『最小列』の値を代入
変数『読取行』を『読取最小行』から『読取最大行』までカウントアップしながら以下の処理を繰り返す
変数『問題番号』にEst のセル(『読取行』,『読取問題番号列』)の値を代入
Wst の『列番号』を列英字名に変換し変数『列英字名』に代入
変数『枠領域』に『列英字名』&『行番号』を代入
Wst の枠領域のセルの値に『問題番号』を代入
Wst の枠領域のセルのフォントサイズを10に設定
変数『txt』にEstのセル(『読取行』,『読取列』)の値を代入
変数『txt』から文字数を取得し、変数『最大文字数』に格納
変数『文字位置』を 1 から『最大文字数』までカウントアップしながら以下の処理を繰り返す
変数『行番号』に『最小行』＋『文字位置』－『1』を代入
Wstのセル(『行番号』,『列番号』)の値に変数『txt』から取得した『文字位置』の文字を代入
変数『漢字列』を『漢字最小列』から『漢字最大列』までカウントアップしながら以下の処理を繰り返す
変数『漢字名』にEstのセル(『読取行』,『漢字列』)の値を代入
変数『漢字名』が空白でないとき
Estのセル(『読取行』,『読取列』)の値から『漢字名』の位置を調べて変数『文字位置』に格納
変数『文字位置』が 0 より大きいとき
変数『行番号』に『最小行』+『文字位置』－『1』を代入
Wstの変数『列番号』を列英字名に変換し変数『列英字名』に代入
変数『枠領域』に『列英字名』&『行番号』を代入
Wstの枠領域のセルの周りを実線で囲う
Wstの枠領域を空白にする
変数『ふりがな読取列』に『漢字列』＋『最大枠数』を代入
変数『ふりがな名』にEstのセル(『読取行』,『ふりがな読取列』)の値を代入
変数『ふりがな名』が空白でないとき
変数『ふりがな列』に『列番号』＋ 1 を代入
Wstの変数『ふりがな列』を列英字名に変換し変数『列英字名』に代入
変数『枠領域』に『列英字名』&『行番号』を代入
Wstの枠領域のセルの値に『ふりがな名』を代入
枠領域のセルのフォントサイズを8に設定
Wstの枠領域のセルの横方向の配置を左詰めに設定
Wstの枠領域のセルの文字の配置を縦方向に設定
Wstの枠領域のセルの文字を縮小して全体を表示に設定
変数『列番号』に『列番号』＋『書込ステップ列』を代入
マクロ『シートからシート3』をコピーして、マクロ『シートからシート4』をつくります。
先ず、宣言部を改修します。定数及び変数を追加します。
定数『読取問題番号列』に『A』列を代入
定数『書込問題番号行』に『1』行目を代入
問題番号を長整数型の変数『問題番号』として定義
Const 読取問題番号列 = "A"
Const 書込問題番号行 = 1
Dim 問題番号 As Long
そして、処理部を改修します。
『漢字入力帳』シートの最小行:『読取最小行』(2行目)から最大行:『読取最大行』(11行目)
にかけて、各行の漢字問題作成処理をループ処理で実行しますが、
この漢字問題作成処理の最初に、先頭行に問題番号を読み書きし、
問題番号のフォントサイズを10にするプログラムコードを追加します。
For 読取行 = 読取最小行 To 読取最大行
問題番号 = Worksheets(Est).Cells(読取行, 読取問題番号列).Value
列英字名 = Replace(Worksheets(Wst).Cells(1, 列番号) _
.Address(False, False), "1", "")
枠領域 = 列英字名 & 書込問題番号行
Worksheets(Wst).Range(枠領域).Value = 問題番号
Worksheets(Wst).Range(枠領域).Font.Size = 10
・・・・・・・・・・・・・・・・・・・・・・
Next 読取行
とします。
【マクロ『シートからシート4』】
Sub シートからシート4()
Const Est = "漢字入力帳"
Const Wst = "漢字学習帳"
Const 読取問題番号列 = "A"
Const 読取列 = "B"
Const 最大枠数 = 4
Const 漢字最小列 = 3
Const 漢字最大列 = 6
Const 読取最小行 = 2
Const 最小列 = 20
Const 書込ステップ列 = -2
Const 書込問題番号行 = 1
Const 最小行 = 2
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
読取最大行 = Worksheets(Est).Cells(Rows.Count, "B").End(xlUp).Row
列番号 = 最小列
' ===== 読取行のｶｳﾝﾄｱｯﾌﾟS =====
For 読取行 = 読取最小行 To 読取最大行
問題番号 = 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
Worksheets(Wst).Range(枠領域).Value = ""
ふりがな読取列 = 漢字列 + 最大枠数
ふりがな名 = 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
それでは、マクロ『シートからシート4』を実行してみます。
『漢字学習帳』シートのT列からB列にかけて、先頭行に問題番号が書きこまれました。