戻る 【縁木求魚】
■ VBAで素人が何か作ってみると・・
●エクセル・マクロ言語
連続的な集計作業のフローを明示化でき、作業条件が変わらなければ、マクロはそのまま継続使用できる。
ただ、キー記録のみに頼っていると、微小な条件が変わっただけで対応できず誤った集計結果を出力したり、
集計方法の検証もせず、<なんとなく合っているらしい>ことで作業が進められる可能性がある。
なので、キー記録主体のマクロではなく、マクロ言語主体のマクロを作成していくべきかと思う。
マクロ言語とは？
http://excelvba.pc-users.net/index.html
http://www.eurus.dti.ne.jp/~yoneyama/Excel/Exl-_zen.htm#vba_jitu
VBA入門
http://excelvba.pc-users.net/
http://www.officepro.jp/excelvba/
http://home.att.ne.jp/zeta/gen/excel/
http://www5b.biglobe.ne.jp/~suugaku/vbakougi/
その他にもたくさんあるようだ。
VBA画面の説明
http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_vbe.html
VBA for Excelの特徴
・構文の次群が自動表示され、また実行エラー箇所を示してくれる。
・ネット上に、山と言うほどの説明があるし、回答者が多くいる。
マクロ言語の基本文
①セル・シート・ブックの表現
セルA1 ⇒ range("a1")
sheet1 ⇒ sheets("sheet1")
book ⇒ workbooks("abc.xlsx")
マクロが保存されているブック ⇒ Thisworkbook
アクティブになっているブック ⇒ Activeworkbook : キーボード・マウスが対象とするブック
②セルに値を代入する。（右から左に代入）
セルA1に10を代入する
range("a1")=10
sheet2のシートのB1に、「住所」を代入する。
sheets("sheet2").range("b1")="住所"
セルA1の値をセル10に代入する。
range("a10")=range("a1") or range("a10")=range("a1").value
セルA1にSUM関数を代入する
range("a1")="=sum(b1:b10)"
セルA1からセルA100を選択する
range("a1:a100").select
セルA1からA100までに、一律5を入力する
Range("a1:a100") = 5
もし、abc.xlsxのシート1がアクティブで、セルA1に100を代入するなら、
range("a1")=100 で良いが、
sheet2がアクティブである場合に、sheet1のA1に100を入れるなら、
sheets("sheet1").range("a1")=100
とする。
2つのブックをオープンしている場合なら、
workbooks("abc.xlsx").sheets("sheet1").range("a1")=100
としないと、どのブックに代入するのか不明確となり、意に反する動きとなる。
③変数を使う（処理上仮に値を入れるもの）
まず、使うだろう変数を定義する。
dim i as long ' 変数iは慣習、longは数値型。文字を入れる変数なら、stringとする。
i=range("a1") ' ← A1の値は数値であること。でないと、エラーとなる。
range("b1")=i
range("b2")=i
④コピー＆ペースト
range("a1:a100").copy range("h2")
dim 範囲 as range 'セル範囲として 範囲と言う語を変数定義する。
set 範囲＝range("a1:a1000") 'セル範囲を"範囲"に代入する。（range型のみsetを使う。））
範囲.copy range("h1") '"範囲"をコピーして、H1セルに貼り付ける。
基本処理
（１）繰り返し処理
①for～next
セルA1からA100までに、1,2,3と連番を入力する。
dim i as long
for i=1 to 100
range("A" & i)=i
next
セルA10から上方向に、１０をカウントダウンした数値を代入する。
dim i as long
for i=10 to 1 step -1
range("a" & i)=10-i
next
②do While ～ loop
セルのA1～A10に1～10を入力する
i = 1
Do While i < 11
Worksheets("Sheet1").range("a" & i).Value = i
i = i + 1 ' for～nextの場合は自動で変数iはカウントアップするが、do～loopの場合はしない。
Loop
（２）条件文
①if～end if 条件文
A1セルが10だったら、A1セル値を2倍にした数値をB1セルに代入する。
if range("a1")=10 then range("b1")=range("a1")*2
A1セルが5だったら、B1セルにA1セル値を代入する。
if range("a1")=5 then
range("b1")=range("a1").value
end if
A1セルが5以上だったら、A1セルに10を加算した値をB1セルに代入し、
そうでなかったら、A1セルに10をマイナスした値をB1セルに代入する。
if range("a1")>=5 then
range("b1")=range("a1")+10
else
range("b1")=range("a1")-10
end if
（３）for～nextの繰り返し文とif文を組み合わせる
①A1からA100で、値が○だったら、B列に●を代入する。
dim i as long
for i=1 to 100
if range("a" & i)="○" then range("B" & i)="●"
next
②A1からA100で、A列が○で、かつB列が●だったら、C列に×を代入する。
dim i as long
for i=1 to 100
if range("a" & i)="○" and range("B" & i)="●" then range("C" & i)="×"
next
（４）マクロ言語の中でワークシート関数を使うには
セルA6からa4400,000までの可視セル（フィルター実行した結果）の数をカウントし、A1セルに代入する。
range("a1") = Application.WorksheetFunction.Subtotal(3, Range("a6:a400000"))
もし、A1セルに関数式を代入するなら、
Range("a1") = "=Subtotal(3, a6:a400000)" とする。
たとえば、マクロからシートに関数式を代入し、その結果をまたマクロで利用することも可能。
B1セルに、A1セル値を検索値するvlookup関数があったとして、
range("a1")=10
msgbox range("b1")
この場合のiには、vlookup関数の結果の値が入る。
計算が複雑であれば、結果が出る時間とマクロが動作する時間差が生まれて、
変数iに何も入らないかもしれないが、30万件のvlookupでも一瞬に計算される。 （CoreDuo2）
おそらく、通常のオフィスでの計算では、時間が掛かり過ぎるという計算はないと
思われる。あるとすれば、計算ではなく、シート選択やシート上の値の分類、フィルター、ピボットや
ブック選択・大量のセル値の変更などの処理だと思われる。
（５）キー記録を参考にしてコードを作る。（キー記録のアレンジ）
例：
L6～L13を選択し、コピーし、N6をクリックし、行列を入れ替えて貼り付ける処理をキー記録したら、
Range("L6:L13").Select
Selection.Copy
Range("N6").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("L6").Select
Application.CutCopyMode = False
操作を忠実に記録しているが、無駄も多い。
マクロ言語では、いちいちセルをクリックする必要がないので、
Range("L6:L13").copy でコピーが完了する。
copyの次にセルを指定すると、貼付となるので、
Range("L6:L13").copy
Range("N6").PasteSpecial Paste:=xlPasteAll, Transpose:=True
Range("l6").Select
Application.CutCopyMode = False
とすることができる。
（６）データの最終行を知る
マクロでデータを集計する場合に、どの行に対して行うのかを知る必要が出てくる。
特に、データを追加する場合はよくある。
お約束のコードは、
http://officetanaka.net/excel/vba/tips/tips130.htm
Cells(Rows.Count, 1).End(xlUp).Select
意味：A列のシート最終行からCTRL＋↑キーで最初に値が見つかったセルを選択する。
cells（行、列）の表記となり、range(列行）とは反対となる。
range(列行）は、A1,B1と分かり易い。
cells（行、列）となっているが、列についても変数が使える利点がある。
cells(1,1)は、range("a1")と同じ。
range()を使うか、cells()を使うか、人によっていろいろ。
だが、VBAに玄人の人は、cells()を使うべきという人が多い。
range()の方が、普段エクセルを操作している人間にはなじみがあるし、分かりやすい。
素人は、cells()など普段使っている表記法と逆の表記など使うべきではない。
混乱の基だろう。
そうはいっても、マクロを作成する場合に、複数列に繰り返し処理をしなければならない場合も
あるかと思う。 その時は使わざるを得ない。だが、それ以外では使うべきではない。
素人に対するコードの解読性維持のために。
Cells(Rows.Count, 1).End(xlUp).Select+1 とすれば、最終行の次の行となり、追加すべき行となる。
（７）指定フォルダーのファイル選択画面を表示し、人が選択したエクセルブックを開く。
http://officetanaka.net/other/extra/tips15.htm
Sub 部門フォルダーから_open()
' ネットワークフォルダーを開くように準備する。
With CreateObject("WScript.Shell")
.CurrentDirectory = "\\●●●●\●●●●\"
End With
Openfilename = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")
If Openfilename <> "False" Then ' 選択内容が空でなかったら、
Workbooks.Open Openfilename ' 選択したファイルを開く
else
exit sub
End If
End Sub
（８）ボタンとマクロ（モジュール）との関連付け
ボタンは図形から選んで、シート上に作成する。
ボタンを右クリックして、マクロの登録を選択。
既に、モジュールを作成していれば、モジュール名＝マクロ名が表示されているので、
それを選択して、OKボタンクリックで、関連付け完了となる。
これで、ボタンをクリックすると、マクロが実行される。
この関連付けは、オブジェクトであればできるので、テキストボックスにも、
グラフにもマクロとの関連付けを行え、クリックでマクロを開始することが可能となる。
（９）処理途中にメッセージを表示させる
マクロが勝手に処理を行い、いつ終了したのかわからないので、
メッセージを表示させることができる。
msgbox "処理が終了しました。"
atai=msgbox("次の操作に移りますか？",vbokcancel,"title")
if atai=vbok then msgbox "yes selected "
（１０）業務に応用してみる。
例：各部署に指定のエクセルブック（書式統一）を配付・回収し、その個々のブックの集計を半自動化する。
①前提条件
配付のエクセルブックは、以下のとおりとする。
C11～c15までを回答欄とする。
シートは1枚のみのブックとする。
シート名はsheet1とする。
C10は氏名とし、氏名がない場合は確認後氏名をハンド入力する。
②自動で行う事は
ファイルを手作業で指定し、開いたら自動で集計ブックに転記し、そのブックを閉じる。
③作っていく
新規ブックを"集計"の名前で保管。
集計ブックのVBEを開いて、挿入～標準モジュール
モジュール画面にコーディングしていく。
sub マクロ名（自由の名前を）() ' ()はお約束。
'処理を書いていく。
' がある行は、注釈となり、処理では無視される。
end sub
今回は、「集計」というマクロ名にする。
まず、行いたいことを注釈で書く。
その注釈をコードに直していく。
④処理を注釈（言葉）で書いてみる。
ファイル選択画面を表示する。
ファイル選択画面は、特定のフォルダーを指定したい。
ファイル選択画面で、対象ファイルを見つけ、選択する。
何も選択しない場合もある。
選択したファイルを開く。
サーバーからのファイルを開くのに時間が掛かるかもしれない。
貼付け先の最終行の次行を知る
開いたブック・シートの対象セル範囲をコピーする。
このブック・シートの最終行の次行に、貼り付ける。
クリップボードをクリアする。
開いたブックを閉じる。
⑤実際のコード
Sub 集計()
'回答表を選択して開く
Dim Openfilename as string
With CreateObject("WScript.Shell")
.CurrentDirectory = "\\●●●●\●●●●\"
End With
''貼付け先の行を調べる
Dim i As Long
i = ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1
Openfilename = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")
If Openfilename <> "False" Then ' 選択内容が空でなかったら、
Workbooks.Open Openfilename ' 選択したファイルを開く
else
exit sub
End If
MsgBox "しばらく待ってください。"
'回答表のC11～C15までをコピーし、集計ブック（このブック）の5行目から貼り付けていく。
'' オープンしたら、そのブックがアクティブになっている！
ActiveWorkbook.Sheets("sheet1").Range("c11:c15").Copy
' コピーしたセル範囲を行列入れ替えて、このシートの（最終行＋１）行に貼り付ける。
ThisWorkbook.Sheets("sheet1").Range("a" & i).PasteSpecial Paste:=xlPasteAll,
Transpose:=True
Application.CutCopyMode = False
'貼付けが完了したら、回答表は閉じる。
ActiveWorkbook.Close
'適時、処理が終わる都度、メッセージを表示する。
MsgBox "処理が完了しました。"
End Sub
（１１）モジュール名「集計」：集計()を改良する。
（１０）では、選択できるファイル（ブック）は１つのみでしたが、これを複数選択できるようにし、
選択したそれぞれのブック毎に、転記する処理を行うように改良する。
①ファイル選択画面で、複数選択できるようにするには、
Openfilename = Application.GetOpenFilename("Microsoft Excelブック,*.xls?",
MultiSelect:=True)
とする。
openfilenameに複数の値が格納される。（自動で、配列変数として扱われる。）
配列変数の値が空かどうかは、isarray()で調べられる。
http://www.moug.net/tech/exvba/0060013.html ←を参照
If IsArray(Openfilename) Then
For Each F In Openfilename
Workbooks.Open F
next
～～～～
～～～～
～～～～ ２）の処理を入れる。
～～～～
else
選択なし＝変数に何も格納されなかった場合はなにもしないので、何も書かない。
end if
②たぶん、３）の完成形
'回答表を選択して開く
sub 集計２()
Dim Openfilename, F As Variant
Dim Open_name As String
Application.ScreenUpdating = False
With CreateObject("WScript.Shell")
.CurrentDirectory = "\\●●●●\●●●●\"
End With
On Error GoTo エラー '何らかのエラーが発生した場合は、エラーに処理が移る。
Openfilename = Application.GetOpenFilename("Microsoft Excelブック,*.xls?",
MultiSelect:=True)
If IsArray(Openfilename) Then
For Each F In Openfilename
Workbooks.Open F
''貼付け先の行を調べる。
'書き込むセルはA列の最下行から上方向に最初に見つかる値の次の行。
'5行目から貼り付けたいなら、4行目に項目名を前もって入力しておくこと。
Dim i As Long
i = ThisWorkbook.Sheets("sheet3").Cells(Rows.Count, 1).End(xlUp).Row + 1
'回答表のC11～C15までをコピーし、集計ブック（このブック）の5行目から貼り付けていく。
'' オープンしたら、そのブックがアクティブになっている！
ActiveWorkbook.Sheets("sheet1").Range("c11:c15").Copy
ThisWorkbook.Sheets("sheet3").Range("a" & i).PasteSpecial Paste:=xlPasteAll,
Transpose:=True
Application.CutCopyMode = False
'貼付けが完了したら、回答表は閉じる。
ActiveWorkbook.Close SaveChanges:=False
'適時、処理が終わる都度、メッセージを表示する。
MsgBox "処理が完了しました。"
Range("a" & i).Select ' 貼り付け先が選択状態のままになっているのを解除するためにセレクトする。
Next
Else
End If 'if文の終わり
Exit Sub ' sub：モジュールの終わり
エラー:
MsgBox Err.Number 'エラーの番号を表示する。 エラー番号の意味は下記を参照
' http://officetanaka.net/excel/vba/error/execution_error/
End Sub
（１２）更なる改良又は機能追加
以上で、同列に連続したセルに値がある複数のブックを、自動で集計することができるマクロができた。
１ブックごとに転記するのが集計()で、複数選択したブックを転記するのが集計２()。
もし、アンケートというのを仮定するなら、最初は多く来るから、集計２()で対応し、その後、ぽちぽちと
しか来ないので、集計()を使うと便利かと思われる。
しかし、集計２()で、複数件数処理したあと、集計()を使った場合で、同じブックを２回集計してしまう可能性が
ある。
［重複集計への備え（対策）］
①集計したら、そのブックを別の指定フォルダーに移し替える。
②すでに集計したブック名は、集計前にチェックして、集計しないようにする。
その場合に、集計しないか、すでにある値を上書きするか、選択できれば、さらに良い。
戻る 2014/1/30