エクセルマクロ・VBA達人養成塾 小川です。
キューバ旅行記、その24です。
ハバナでの、2日目の早朝。
だいぶ歩いた。奥にあるのは、内務省あたりの建物。
壁際には、所在なさげに座る男。
元気なニワトリ。建物の2階にひさしにいます。
塾長のキューバ旅行記、最初から読みたい方はこちらから
今日は、添削事例をひとつご紹介。
先日ブログでも紹介した、こちらの感想をいただいたとき、「添削依頼」ということで、さっそく彼女が作ったファイルをお送りいただきました。
10月28日ブログ – マクロを書くのがこんなに簡単だとは思いませんでした。
で、今日は、その解説を。
以下、受け取ったエクセルファイルについていた、VBAコードです。（エクセルファイルそのものは、ブログ公開できません。すいません。）
インデントも、受け取ったそのままです。
‘【CBデータのコピペマクロ】
‘1-1 このブック以外のファイルを閉じる（for each 構文を適用するため）
‘1-2 更新データが入っている元ファイルを開ける (findfile)
‘2-1 それぞれのデータをコピペ (ただし､日付はファイル名の後ろから8文字を日付の型にして使う)
‘2-2 コピペが終わった元ファイルは閉じる
‘2-3 すべてのコピペが終わったら､日付で並び替え
Sub CBcopipe_1()
‘Dim dolly As Workbook
‘
‘For Each dolly In Workbooks
‘
‘Debug.Print dolly.Name
‘
‘Next
‘
‘Debug.Print Workbooks(1).Name
‘Debug.Print Workbooks(2).Name
‘Debug.Print ThisWorkbook.Name
Select Case Workbooks.Count
Case Is > 2
MsgBox “他のエクセルファイルを閉じて下さい。”
Exit Sub
Case Is = 2
If Not Workbooks(1).Name = “PERSONAL.XLS” Or Workbooks(2).Name = “PERSONAL.XLS” Then
MsgBox “他のエクセルファイルを閉じて下さい。”
Exit Sub
End If
End Select
Application.FindFile
CBcopipe_2
End Sub
Sub CBcopipe_2()
Dim wbMoto As Workbook
Dim wbKoushin As String
Dim gyo As Long
Dim hizuke As String
Dim narabi As Long
wbKoushin = ThisWorkbook.Name
gyo = Workbooks(wbKoushin).Worksheets(“CB”).Range(“e” & Rows.Count).End(xlUp).Row + 1 ‘入力行の取得
narabi = gyo
For Each wbMoto In Workbooks
hizuke = wbMoto.Name
If hizuke <> “PERSONAL.XLS” And hizuke <> wbKoushin Then
Workbooks(wbKoushin).Worksheets(“CB”).Range(“e” & gyo).Value = Mid(hizuke, 11, 4) & “/” & Mid(hizuke, 15, 2) & “/” & Mid(hizuke, 17, 2)
wbMoto.Worksheets(“Sheet1”).Range(“x4:z4”).Copy
Workbooks(wbKoushin).Worksheets(“CB”).Range(“K” & gyo).PasteSpecial
wbMoto.Close
gyo = gyo + 1
End If
Next
‘並び替え
Workbooks(wbKoushin).Worksheets(“CB”).Range(“E” & narabi, “M” & gyo).Sort _
Key1:=Range(“E” & narabi), _
Order1:=xlAscending, _
Header:=xlNo
MsgBox “更新が終わりました。”
End Sub
そして、以下、僕が添削して返送したもの。
‘あとからファイルをひとつ開くだけであれば…。以下の要領で、そもそも他にファイルを開いていても問題なく操作できます。 ogawa
Dim wbMoto As Workbook
Application.FindFile
Set wbMoto = ActiveWorkbook ‘開いた直後に、ニックネームをつけてしまう ogawa
Dim wbKoushin As Workbook
Set wbKoushin = ThisWorkbook
Dim gyo As Long, narabi As Long
gyo = wbKoushin.Worksheets(“CB”).Range(“e” & Rows.Count).End(xlUp).Row + 1 ‘入力行の取得 ogawa
narabi = 11 ‘範囲全体をsort対象にしてみた（必要ないかもしれないが） ogawa
Dim hizuke As String
hizuke = Mid(wbMoto.Name, InStr(wbMoto.Name, “_”) + 1) ‘ここまでで、アンダースコアより後を取ってこれる ogawa
hizuke = Left(hizuke, Len(hizuke) – 4) ‘.xlsを取る ogawa
wbKoushin.Worksheets(“CB”).Range(“e” & gyo).Value = Mid(hizuke, 1, 4) & “/” & Mid(hizuke, 5, 2) & “/” & Mid(hizuke, 7, 2)
‘↓値だけ取ってくるなら、.Valueを使いたい。（そのほうが、命令の意味的に考えてムダがなくスッキリしているから） ogawa
wbKoushin.Worksheets(“CB”).Range(“K” & gyo & “:M” & gyo).Value = wbMoto.Worksheets(“Sheet1”).Range(“x4:z4”).Value
wbMoto.Close
gyo = gyo + 1 ‘並び替え
wbKoushin.Worksheets(“CB”).Range(“E” & narabi, “M” & gyo).Sort _
Key1:=Range(“E” & narabi), _
Order1:=xlAscending, _
Header:=xlYes ‘xlYesにしてみた ogawa
MsgBox “更新が終わりました。”
End Sub
おおまかに解説していくと…。
[1]
まず、インデントの調整をしました。
Sub … End Sub の中身は、すべて、一段右に行きます。
こうしておかないと、どこからどこまでがひとつのサブプロシージャの中身なのか、視覚的に分かりにくくなります。
可読性が落ちる、と言います。
可読性が落ちると、その後のメンテナンスもしにくくなりますし、バージョンアップをするのも難しくなります。
[2]
Set wbMoto = ActiveWorkbook
Dim wbKoushin As Workbook
Set wbKoushin = ThisWorkbook
上記の部分、新たに開いた元データの入っているファイルに、開いた直後にすぐに「参照設定」をしている。
さらに、データ転記先のファイルにも、「参照設定」をする。
添削依頼をされたファイルでは、
「元データの入ったファイル、転記先ファイル以外のファイルを開いているときには閉じる」
という処理をしていたが、これによって、そもそもその必要がなくなった。
[3]
Dim hizuke As String
hizuke = Mid(wbMoto.Name, InStr(wbMoto.Name, “_”) + 1) ‘ここまでで、アンダースコアより後を取ってこれる ogawa
hizuke = Left(hizuke, Len(hizuke) – 4) ‘.xlsを取る ogawa
この部分。
実は、元データファイルのファイル名が「motodata_20111005.csv」のようなファイル名になっているのだが、
その「20111005」の部分を取り出す処理。
やり方はいろいろ。
例えば、「 _ 」の場所と、「 . 」の場所を調べてきて、その間を Mid 関数で取ってくる、としてもよい。
ただ、今回の例では、宣言する変数をなるべく少なくすべく、上記の方法を採ってみた（好みです）。
[4]
‘↓値だけ取ってくるなら、.Valueを使いたい。（そのほうが、命令の意味的に考えてムダがなくスッキリしているから） ogawa
wbKoushin.Worksheets(“CB”).Range(“K” & gyo & “:M” & gyo).Value = wbMoto.Worksheets(“Sheet1”).Range(“x4:z4”).Value
ここが、エクセル VBAに慣れないうち意識したいことなのですが…。
「値を転記する」というような仕事をするとき、
エクセルVBAを覚えたてのころは、
「コピーして、貼り付けて…。」というようなプロセスを
再現するマクロを書きたくなります。
ですが、値を転記するときには、
「.Copy メソッド」や「.PasteSpecial メソッド」をを使うよりも、
「.Value プロパティ」を利用したほうが
スッキリしますし、らしい仕上がりになります。
エクセル操作で普段やっていたことをベースに発想してしまいがちですが、
今手元にある道具をベースに
それを活用する最短のステップを考えるようにすると、
いろいろアイデアが湧いてくるかと思います。
ちなみに、この添削でやりとりをしたRさんからは、その後、こんな感想をいただきました。
小川先生、おはようございます。 お褒めの言葉ありがとうございます。 今、一番褒められて嬉しい人にお褒めいただき、とっても嬉しいです！ ブログのネタになったこともとても嬉しいです。 みんなで切磋琢磨できる機会になれば光栄です。 ちなみに今回の添削で一番感動したのは、 他のファイルを閉じないで複数ファイルの 処理ができるようになったことです。 置かれた状況を考えると（ファイル名に規則性がある）、 とても簡単な条件分岐でしたね★ 業務の効率化や見直しって、こういうことに気づくことも大切なんでしょう。 発展編受講からまだ1週間足らずですが、すごく成長した気がします。 これからもよろしくお願いします。
エクセルマクロ・VBA達人養成塾のエクセルマクロ・VBA講座、受講受付中です。
セミナー受講後も、あなたのスキルアップにトコトンつきあいます。
お申し込みは、お早めにどうぞ☆
エクセルマクロ・VBA達人養成塾、12月末～3月までの日程を追加しました。
年末特訓も用意しています。
