エクセルマクロ言語とはエクセルのマクロとは手番をあらかじめ記述しておくことにより通常手操作している処理を自動化すること自動化したものですマクロで手番を記録すれば繰り返し作業を自動化できますまた連続的

(1)

エクセルの

マクロ言語（

VBA

）に

慣れるためのメモ

2014/1/31

～

2015/12/31

作成：縁木精史

(2)

エクセルのマクロとは、手番をあらかじめ記述しておくことにより、通常手操作している処理

を自動化すること・自動化したものです。

マクロで手番を記録すれば、繰り返し作業を自動化できます。また、連続的な集計作業の

フローを明示化でき、作業条件が変わらなければ、マクロはそのまま継続使用できます。

マクロは、コードを直接入力する方法と操作が自動的にコード変換されたキー記録の２つの

方法があります。簡単なコードは覚えましょう。複雑なコードになるばあいはキー記録で作成

されるコードを利用しましょう。ただ、キー記録で作成されるコードの意味がほぼ分かる程度

までは、コード・言語レベルの知識を持つことが大切かと思います。

マクロ言語とは？

http://excelvba.pc-users.net/index.html

http://www.eurus.dti.ne.jp/~yoneyama/Excel/Exl-_zen.htm#vba_jitu

http://excelvba.pc-users.net/

VBA

画面の説明

http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_vbe.html

ExcelVBA

の使い方

http://homepage2.nifty.com/nandemoarchive/cyou_nyumon/excel_01.htm

(3)

● VBA for Excel

の特徴

（他のｽｸﾘﾌﾟﾄ・言語に比べ）

・コード（構文）の次群が自動表示され、また実行エラー箇所を示してくれます。

・ネット上に、山と言うほどの説明があるし、回答者が多くいます。

注１）英語語句の読み方は、 http://vbae.odyssey-com.co.jp/study/glossary.html を参照注２）マクロとは、プログラミング言語。

その一つに、「Visual Basic for Applications」（略してVBA）があり、EXCEL用のVBA、WORD 用のVBA、powerpoint用のVBAがあります。

注３）まずは、VBAが何であるかを理解する前に、実際に作成し実行し、その結果を見て経験する、「馴染む」「慣れる」ことが理解への近道。注４）「変数」とは？マクロ内で使用する”セル”みたいなものです。ただ、セルのように何でも入るわけではありません。入れるものが数値、文字、範囲、シート名などであれば、それぞれに変数の定義をする必要があります。変数は、値の入れ物ですが、その処理やブックを終了すると、消えます。

定義 dimias long ⇒ iを数値型（long）として定義します。

文字を入れるなら、dim i as string とします。ただ、慣習があり、I,j,kなどは数値を入れる変数とされています。ちょっと不便なセルが変数ということでしょうか？

成り立ちとしては、変数から、セルが考えられたのでしょうけど。

(4)

①セル・シート・ブックの表現 ①セル・シート・ブックの表現 ①セル・シート・ブックの表現 ①セル・シート・ブックの表現セルA1 ⇒ range(“a1”) range：読み方：レンジシート１ ⇒ sheets("sheet1") abcブック ⇒ workbooks("abc.xlsx") マクロが保存されているブック ⇒ Thisworkbook アクティブになっているブック ⇒ Activeworkbook : キーボード・マウスが対象とするブック ②セルに値を代入する。（右から左に代入） ②セルに値を代入する。（右から左に代入） ②セルに値を代入する。（右から左に代入） ②セルに値を代入する。（右から左に代入）セルA1に10を代入する range("a1")=10 sheet2のシートのB1に、「住所」を代入する。 sheets(“sheet2”).range(“b1”)=“住所” ‘ マクロでは、シートを切り替える必要はない。セルA1の値をセルA10に代入する。 range(“a10”)=range(“a1”) 又は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 としないと、どのブックに代入するのか不明確となり、意に反する動きとなります。 A1と言っても、同じ画面を見ている人同士はそれで通じるが、コードを記述する場合は、そのような前提は通じないので、どのブックの、どのシートの、A1かを指定しないといけない。シートやブックを明示的に指定しない場合、予期せぬ処理結果がシートに出力される可能性があるので、注意すること！

(5)

●マクロ言語の基本文

③変数を使う（処理上仮に値を入れるもの） ③変数を使う（処理上仮に値を入れるもの） ③変数を使う（処理上仮に値を入れるもの） ③変数を使う（処理上仮に値を入れるもの）まず、使うだろう変数を定義します。

dim i as long ‘ 変数iは慣習、longは数値型。文字を入れる変数なら、stringとします。 i=range(“a1”) ‘ ← A1の値は数値であること。でないと、エラーとなります。 range("b1")=i range("b2")=I ④ ④ ④ ④コピー＆ペーストコピー＆ペーストコピー＆ペーストコピー＆ペースト

range(“a1:a100”).copy range(“h2”) ‘ a1からa100のセル範囲をコピーし、h2に張り付けます。 dim 範囲as range ‘セル範囲として範囲と言う語を変数定義。

set 範囲＝range(“a1:a1000”) ‘セル範囲を“範囲”に代入する。（range型のみsetを使う。））範囲.copy range(“h1”) ’範囲“をコピーして、H1セルに貼り付けます。

(6)

（１）繰り返し処理

① ① ① ①for～～～～next セルA1からA100までに、1,2,3と連番を入力する場合。 dim i as long for i=1 to 100

range("A" & i)=i

next ‘ nextは、next i と変数を付けてもOKです。

‘ for～nextのなかに、for～nextがあるような入れ子構造をネストすると言うそうですが、 ‘ そのような場合、nextがどの変数のものかなどを明示するために、nextの後ろに変数を ‘ 付けたほうが良いと思われますが、そもそも的にはネストした構文は、使わないことを ‘ お勧めします。

セルA10から上方向に、１０をカウントダウンした数値を代入します。 dim i as long

for i=10 to 1 step -1 range("a" & i)=10-i next

② ② ②

②do While ～～～～ loop while：読み方：ホワイル：読み方：ホワイル：読み方：ホワイル：読み方：ホワイル

セルの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

(7)

●基本処理

（２）条件文

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

(8)

（３）

for

～

の繰り返し文と

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でも一瞬に計算される。おそらく、通常のオフィスでの計算では、時間が掛かり過ぎるという計算はないと思われる。あるとすれば、計算ではなく、シート選択やシート上の値の分類、フィルター、ピボットやブック選択などの処理だと思われます。

(9)

●基本処理

（５）キー記録を参考にしてコードを作る。（キー記録のアレンジ）

例：

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

(10)

（６）データの最終行を知る

マクロでデータを集計する場合に、どの行に対して行うのかを知る必要が出てきます。お約束のコードは、［参考］ http://officetanaka.net/excel/vba/tips/tips130.htm Cells(Rows.Count, 1).End(xlUp).Select cells：読み方：セルズ

意味：A列のシート最終行からCTRL＋↑キーで最初に値が見つかったセルを選択する。 A列の最終行番号なら、

Cells(Rows.Count, 1).End(xlUp).row

cells（行、列）の表記となり、range(列行）とは反対となる。

range(列行）は、A1,B1と分かり易いが、cells（行、列）となっているが、列についても変数が使える利点があります。 cells(1,1)は、range("a1")と同じ。

(11)

●基本処理

（７）ファイル選択画面を表示し、選択したエクセルブックを開く。①

［参考］http://officetanaka.net/other/extra/tips15.htm Sub Book_open()

Dim Atai As Variant, aFile As Variant, ws As Variant, wb As Variant, flag As Variant Dim F_name As String

Dim i As Long, j As Long

Atai = MsgBox("ブックを開く" & vbCrLf & _

"オープンするブックを選択して下さい。", vbOKCancel, "ブックの選択") If Atai = vbCancel Then Exit Sub

'※ ファイルを取得する為に、指定した拡張子のみ表示したエクスプローラを起動する。

aFile = Application.GetOpenFilename("xlsﾌｧｲﾙ(*.xls?),*.xls?", , "ブックを選択して下さい", "OK", False) If aFile <> False Then

j = Len(aFile) For i = j - 1 To 1 Step -1 '左から最初に見つかる¥で、文字列を区切る。 If Mid(aFile, i, 1) = "¥" Then F_name = Mid(aFile, i + 1, j) 'ブック Exit For End If Next Else Exit Sub End If '同名のブックがあれば、flag=trueとする。既に、開いているなら、処理中止する。 For Each wb In Workbooks

If wb.Name = F_name Then flag = True

Exit For End If Next wb

If flag = True Then

Atai = MsgBox(F_name & "は、開いています" & vbCrLf & "処理を開始する前に、閉じてください。" & vbCrLf & _ "オープンしているブックをアクティブにするなら、OKをクリックしてください。", vbOKCancel)

If Atai = vbCancel Then Exit Sub Workbooks(F_name).Activate Exit Sub End If Workbooks.Open Filename:=aFile End Sub '選択したxlsのパスとファイル名を変数に代入。 ' MsgBox aFile ' パス付ファイル名 ' MsgBox Mid(aFile, 1, i) 'パス名 ' MsgBox F_name 'ファイル名

(12)

（７）ファイル選択画面を表示し、選択したエクセルブックを開く。②

［参考］http://officetanaka.net/other/extra/tips15.htm Sub ブック選択open() 'ブックを複数選択して、オープンする。 '既にopenしている場合は、openしない。

Dim OpenFileName As Variant, Target, Wb As Variant Dim I As Long, j As Long

Dim Filename

Application.ScreenUpdating = False

OpenFileName = Application.GetOpenFilename(FileFilter:="Microsoft Excelブック,*.xls?", MultiSelect:=True) i = 5

If IsArray(OpenFileName) Then For Each Target In OpenFileName

j = 0

For Each Wb In Workbooks If Wb.Name = Dir(Target) Then

j = 1

MsgBox "既に" & Wb.Name & " はopenしています。" End If

Next Target Else

' MsgBox "ブック選択は、キャンセルされました" End If

ThisWorkbook.Activate: Sheets("ブック一覧").Activate End Sub

(13)

●基本処理

（８）図形（ボタン）とマクロ（モジュール）との関連付け

ボタンは図形から選んで、シート上に作成します。ボタンを右クリックして、マクロの登録を選択。既に、モジュールを作成していれば、モジュール名＝マクロ名が表示されているので、それを選択して、OKボタンクリックで、関連付け完了となります。これで、ボタンをクリックすると、マクロが実行されます。この関連付けは、オブジェクトであればできるので、テキストボックスにも、グラフにもマクロとの関連付けを行えます。 ○コントロールのコマンドボタンを使っても、当然に関連付けることができます。フォームのコマンドボタンだと、サイズを確定した後に、マクロの登録フォームが開きます。

（９）処理途中にメッセージを表示させる

マクロが勝手に処理を行い、いつ終了したのかわからないので、メッセージを表示させることができます。・・・ msgbox "処理が終了しました。" Atai=msgbox("次の操作に移りますか？",vbokcancel,"title") if atai=vbok then msgbox “yes selected ”

(14)

（１）業務フローを想像してみる。

例：各部署に指定のエクセルブック（書式統一）を配付・回収し、その個々のブックの集計を半自動化する。 ①前提条件 ①前提条件 ①前提条件 ①前提条件配付のエクセルブックは、以下のとおりとする。 C11～c15までを回答欄とする。シートは1枚のみのブックとする。シート名はsheet1とする。 C10は氏名とし、氏名がない場合は確認後氏名をハンド入力する。 ②処理イメージ ②処理イメージ ②処理イメージ ②処理イメージ自動で行う事はファイルを手作業で指定し、開いたら自動で集計ブックに転記し、そのブックを閉じる。 ③作っていく ③作っていく ③作っていく ③作っていく新規ブックを"集計"の名前で保管。集計ブックのVBEを開いて、挿入～標準モジュールモジュール画面にコードを入力していく。 sub マクロ名（自由の名前を）() ' ()はお約束。 '処理を書いていく。「‘」が行の先頭にある行は、注釈となり、処理では無視される。 end sub 今回は、集計というマクロ名にする。まず、行いたいことを注釈で書く。その注釈をコードに直していく。

(15)

●業務作業に適用する

（２）業務に応用してみる。～注釈書き、その後コード化する。

④処理を注釈（言葉）で書いてみる。

ファイル選択画面を表示する。（ファイル選択画面は、特定のフォルダーを指定したい。）ファイル選択画面で、対象ファイルを見つけ、選択する。何も選択しない場合もある。選択したファイルを開く。サーバーからのファイルを開くのに時間が掛かるかもしれない。貼付け先の最終行の次行を知る。開いたブック・シートの対象セル範囲をコピーする。このブック・シートの最終行の次行に、貼り付ける。クリップボードをクリアする。開いたブックを閉じる。

(16)

⑤実際のコード

Sub 集計() '回答表を選択して開く Dim Openfilename as string

With CreateObject("WScript.Shell") .CurrentDirectory = “¥¥○○○¥総務部¥01_作業¥” End With ''貼付け先の行を調べる Dim i As Long i = ThisWorkbook.Sheets(“sheet1”).Cells(Rows.Count, 1).End(xlUp).Row + 1 ‘ 貼り付け先はこのマクロがあるブックのシート１のA列 Openfilename = Application.GetOpenFilename(“Microsoft Excelブック,*.xls?”) ‘ 開きたいファイル名をフルパスで取得。

‘ フルパス付ファイル名= Openfilename If Openfilename <> "False" Then ' 選択内容が空でなかったら、

Workbooks.Open Openfilename ' 選択したファイルを開く Else

exit sub End If

MsgBox dir(openfilename) & “を転記します。“

'回答表の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

(17)

●機能を追加する

「集計」というマクロ（プロシージャ）が完成したら、更に使い易くするために機能追加を考えてみます。だた、この段階では、機能の為の機能を考えてしまうことがよくあります。ハンドで簡単にできるのであれば、マクロを作成する必要はありません。マクロを本当に作成するべきかどうか、自問することが大切かと思います。［Ｑ．］ ①自らの作業効率と間違い防止の為なのか、 ②他の人に使ってもらう為に作成するのか、 ③マクロの勉強の為に作成するのか、充分に検討・考えるべき。［Ａ．］ ①作業効率よりも間違い・誤操作の防止に重点をおいて、考え・作成するべきでしょう。 ②他の人の力量レベルはそのままか、力量レベルを上げるのか、2つの方法がある。 ③あまり好ましいとは言えない。更に、欲しい機能は？転記したファイルは、転記済みとして記録・チェックし、２重の転記を防ぐ。

(18)

コードを書いて、正常に実行すれば、終わりではありません。その処理が何をしているのか、同時に決めた変数が何を表すのか、条件分岐はなぜしないといけないのか、などなど、出来るだけ詳細に注釈を入れてください。作成中や作成直後は、その処理がイメージされているが、時間と共にその記憶は薄れます。１年後に、不具合や機能追加・機能修正が発生した場合に、自ら作成したとはいえ、マクロの処理イメージを忘れていることが多々あります。一からコードを読んで、理解する手間を省くためにも、自分のために、注釈を書いておきましょう。［例］ Sub tesuto1() ‘テストのマクロです。セルに連続したデータを入力します。 'ただし、５の倍数の場合は、値を10倍します。 'セルの値を入れる変数を定義します。 Dim i As Long 'セル1行から100行まで、セル毎に判定し、結果を代入します。 For i = 1 To 100 'まずiには、1が代入されます。 'nextまで行ったら、iに1が加算されます。 Range("a" & i) = i ' a mod 5とは、aを5で割った時の余りを求めます。 ' 5の倍数とは、5で割ると余りがないので、余り=0となります。

If (i Mod 5) = 0 And i > 1 Then Range("a" & i) = (i) * 10 Else '5の倍数以外の時は何も行いません。 End If Next End Sub 注釈は、コードの実行とは関係なく、コードの意味、役割など、コード編集時に役立てます。注釈は、行の先頭に、「’」を入力します。この「’」以降の行の語句は、注釈と解釈されて実行時は無視されます。1行ごとにつける必要があります。注釈の始まり、注釈の終わりという記号は、ありません。

(19)

●簡単な練習（マクロを作ってみましょう。）

（１）

A1

から

A20

までに、連番を入力してみる。

開発タブ～

VisualBasic

を選択し、挿入～標準モジュールを選択。

白い入力スペースが表示されるので、下記のように入力します。

sub tesuto1()

Dim i as long

for i=1 to 20

range(“a” & i)=i

end sub

エクセルのシートをアクティブにして、

VBE

画面も見えるようにウインドウ調整して、

ツールバーにある ▶ ボタン（実行ボタン）をクリックし、シート上の

A1

から

A20

セルに、

1 からの連番が

入力されたことを確認します。

このコードをシートを切り替えて実行してみましょう。やはり、同じ結果になります。

つまり、このコードはアクティブシートに対して行われるコードです。アクティブシートが予期していな

いシートであった場合、既に入力されている値を書き換えます。

マクロでの変更は、

ctrl+z

の元に戻すが効きません。

シートとVisualBasicの編集画面（VBE）を併置すると分かり易い。