今回は英語系の記事ではなく、ITに関する記事を紹介していきたいと思います。
【発展的・実用的なVBAに関する記事は以下をご覧ください】↓
今回は最近業務でも多用するExcelのVBAについて書いていきます。
皆さんも日々の業務でExcelを使われる方は多いかと思いますが、マクロやVBAに関してはちらほらといった所でしょうか。
マクロやVBAを使いこなすことができればめんどくさい業務をボタン一つで一瞬で終わらせることができるので、ぜひとも身に着けるべきスキルであるといえます。
簡単な商品管理表を例にとって説明していきたいと思います。
まずはマクロとVBAの概要を簡単に説明していきます。
Excel マクロとは
マクロはExcelにおいて、複数の手順を記憶して、それをボタン一つで自動的に実行させる機能です。
VBAとは
ExcelなどのOffice製品でマクロを開発するために使うプログラミング言語のことです。
またマクロはこのVBAを使用して作成されたプログラムの事を指します。
つまりマクロ≒VBAという認識で間違いないかと思います。
VBAのプログラムによってマクロが動作するということを頭の中に入れておいて頂ければ大丈夫です。
今回作成する表はFor文とIf文を使用しますので、その二つの基礎知識がある前提で説明をしていきます。
管理表の作成
今回作成するものとしては、上のような表に各項目があって、
「値段」と「個数」を入力したら「合計計算」ボタンを押すことによって「合計金額」が表示できるような簡単なものです。
「合計計算」ボタンを押すと…
「値段」と「個数」をかけた金額が「合計金額」に表示されました。
また、「削除」ボタンを押すと、「合計金額」と「備考」欄が空欄になります。
ひとまずやりたいことをまとめると、
- 「値段」と「個数」を掛け合わせて、「合計金額」を表示させる
- 「合計金額」が10,000円以上であれば「合計金額」を赤字表示させて「備考」に※をつける
- 「削除」ボタンで「合計金額」と「備考」を空欄にする
事前準備として、簡単で良いのでExcelで上記の表を作っておいて頂きたいと思います。
必要な項目は、管理番号、名前、値段、個数、合計金額、備考の6つで、冒頭に書いたら、枠で囲むだけで大丈夫です。
合計金額を求める
まずは、「合計金額」を計算するプログラムを書いていきたいと思います。
以下のように「合計計算」ボタンを押したら、金額を計算するようなプロシージャを用意します。
————————————————————–
Sub Total()
End
————————————————————–
ご覧の通り、C列が「値段」、D列が「個数」、E列が「合計金額」の欄となっています。
これらの列は固定されているので、行を変化させて計算をしていけば、答えを導くことができます。
また最終行を求めるコードをここで用意しておきます。
Lastrow=cells(Rows.count,1).End(Xlup).Row
現時点でコードは以下のようになっています。
————————————————————–
Sub Total()
Lastrow=cells(Rows.count,1).End(Xlup).Row
End
————————————————————–
左辺の変数(=Lastrow)は自分の好きな変数を作ってもらって構いませんが、
右辺の式は、Excel VBAにおいて最終行が行番号何番に該当するかをもとめるコードになりますので、このまま暗記しておいた方が良いです。
cells(Rows.count,1).End(Xlup).Row
ここで使用するのがFor文です。
count変数iを用意して、
————————————————————–
Sub Total()
Lastrow=cells(Rows.count,1).End(Xlup).Row
for i= 1 to Lastrow
cells(i,5)=cells(i,3)*cells(i,4)
Next
End
————————————————————–
iは行を表していて、数値がある2行目から最終行(ここでは4行目=Lastrow)までの間を、ループしてfor文の式を実行します。
(行,列)のように表すので覚えておきましょう。
例えば、Cells(2,1)であればA2セル、Cells(4,4)であればD4セルになります。
よってこちらの式では(2〰4,3)のセルと(2〰4,4)のセル同士を順番に計算して、(2〰4,5)に掛け合わせた答えが入るようになっています。
これで2行目から最後の行までの「値段」と「個数」を掛け合わせて、「合計金額」を求めることができます。
このプロシージャを実行すると、(合計計算ボタンを押します。)
「合計金額」に「値段」と「個数」を掛け合わせた数値が表示されました。
ここまでのコードは以下のようになっています。
————————————————————–
Sub Total()
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To Lastrow
Cells(i, 5) = Cells(i, 3) * Cells(i, 4)
Next
End Sub
————————————————————–
「合計金額」の大きさで字の色を変える、※印をつける
先ほど作成した、「Total」プロシージャを利用していきます。
「合計金額」が10,000円以上、未満で字の色が変わるようなVBAを組んでいきたいと思います。
これはFor文を回している中でプログラムを組んであげる必要があり、1行目からLastRow行目までの各一行ずつ判定を行っていきます。
これはIf文を用いて値の判定を行います。
「合計金額」が10,000円以上であれば、赤字表示なおかつ「備考」欄に※印をつける。
————————————————————–
If Cells(i, 5) >= 10000 Then
Cells(i, 5).Font.ColorIndex = 3
Cells(i, 6) = “※”
————————————————————–
Font.ColorIndexメソッドはセルの中の文字の色を変えることができるメソッドです。
色によって指定する数字は異なりますが、赤色の場合は「3」なのでそれを指定します。
「合計金額」が10,000円未満であれば、黒字表示のまま
————————————————————–
ElseIf Cells(i, 5) < 10000 Then
Cells(i, 5).Font.ColorIndex = 1
Cells(i, 6).ClearContents
End If
————————————————————–
先ほどと同じようにセルにFont.ColorIndexメソッドを指定します。(黒の場合は「1」)
そして10,000円未満の場合は「備考」欄に何も入れないので、セルにClearContentsメソッドを指定します。
今作成したIf文とElseIf文も先ほどから使用しているコードに追加してみましょう。
ここまでで以下のコードになっているかと思います。
————————————————————–
Sub Total()
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To Lastrow
Cells(i, 5) = Cells(i, 3) * Cells(i, 4)
If Cells(i, 5) >= 10000 Then
Cells(i, 5).Font.ColorIndex = 3
Cells(i, 6) = “※”
ElseIf Cells(i, 5) < 10000 Then
Cells(i, 5).Font.ColorIndex = 1
Cells(i, 6).ClearContents
End If
Next
End Sub
————————————————————–
赤色部分が今追加したIf文とElseIf文です。
この場合、For文の中に書かないと想定通りに動かないので注意してください。
再度このプロシージャを実行させると、
この通り、想定通りに動きました。
(削除機能はこれから作るのですが、フライングで先に作ってあるときの画像になっています。)
「削除」ボタンを加える
次は削除機能です。「合計金額」と「備考」を削除させていきたいと思います。
さきほどはTotalプロシージャを使用していましたが、今回は新たに削除用のプロシージャを作ります。
————————————————————–
Sub Del()
End Sub
————————————————————–
手順としてはTotalプロシージャと似たところがあり、
- 最終行の設定
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row - For文の使用、そしてその中でのIf文使用
です。
先ほどのように2番目の行からLastRow(最終)行までFor文を動かすプログラムを書きます。
————————————————————–
Sub Del()
For i = 2 To Lastrow
Next
End Sub
————————————————————–
このFor文の中にIf文を書いていきます。
条件としては、「合計金額」もしくは「備考」が空白じゃなければ、値を消去させていくプログラムになります。
————————————————————–
Sub Del()
For i = 2 To Lastrow
If Cells(i, 5) <> “” Or Cells(i, 6) <> “” Then
Cells(i, 5).ClearContents
Cells(i, 6).ClearContents
End If
Next
End Sub
————————————————————–
<>はVBAにおいて!=や≠すなわち右辺と左辺が等しくないことを意味します。
また””は空白を意味します。
「合計金額」もしくは「備考」のどちらかが空白でなければ、(値が入っていれば)入っている値を消すプログラムになります。
値は先ほど使用したClearContentsメソッドで消去します。
Delプロシージャを「削除」ボタンに挿入して実行すると、
「合計金額」と「備考」に入っていた値が消えました。
Delプロシージャは以下のようになっています。
————————————————————–
Sub Del()
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To Lastrow
If Cells(i, 5) <> “” Or Cells(i, 6) <> “” Then
Cells(i, 5).ClearContents
Cells(i, 6).ClearContents
End If
Next
End Sub
————————————————————–
先ほどの合計ボタンのプログラムと合わせると、完成形は以下のようになります。
————————————————————–
//合計金額を求める
Sub Total()
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To Lastrow
Cells(i, 5) = Cells(i, 3) * Cells(i, 4)
If Cells(i, 5) >= 10000 Then
Cells(i, 5).Font.ColorIndex = 3
Cells(i, 6) = “※”
ElseIf Cells(i, 5) < 10000 Then
Cells(i, 5).Font.ColorIndex = 1
Cells(i, 6).ClearContents
End If
Next
End Sub
//合計金額と備考を削除する
Sub Del()
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To Lastrow
If Cells(i, 5) <> “” Or Cells(i, 6) <> “” Then
Cells(i, 5).ClearContents
Cells(i, 6).ClearContents
End If
Next
End Sub
————————————————————–
これで商品の金額と個数をかけて合計金額を求めるプログラムと、
合計金額と備考の値を消すプログラムが完成しました。
基本構文を抑える
今回は
- 「合計金額」を計算する
- 値を削除する
- 合計金額に応じて表示を変える
プログラムを書いてきました。
いずれも基本的なFor文やIf文で作成が可能ですので、まずは文法をしっかり覚え、
どういったロジックを組めば想定通りの動きをするかを考えていくのがいいかと思います。
この表を使って他の機能も実装していく予定なので、楽しくVBAを覚えていきましょう。
コメント