Excel VBA業務効率化

あなたのExcel定型業務をVBAで効率化、省力化をお手伝いします。

詳細はこちら

超々初心者向けExcelVBA、初めの一歩。コード書いてみませんか、超簡単シンプル電卓?

エクセルVBA
この記事は約12分で読めます。

VBAコード書いてみませんかシリーズ

このコーナーはVBAに触れたことがない人、超々初心者の人向けに書いています。
日頃会社でExcelで様々な表を作ったり。表計算機能などを活用しているけどVBAはやったことがないという人向けに、更に効率のいい利用法があることを知ってほしいと思っています。
果たしてこのような書き方でいいのかとてつもなく自信もありませんが、VBAの素晴らしさが少しでもわかっていただけたら嬉しいです。

はじめの一歩

セルの概念

Excelは表形式になっていて、アルファベットの横に並ぶ列と 数字の縦の行列で区切られた一つ一つのマス目に区切られています。

そしてそのマス目(セル)一つ一つに番地が割り当てられています。

下図の例では、赤い□枠のセル、D6を選択している状態を表しています。この場合赤い□枠の選択されたセルを【D6セル】や【セルのD6】といてます。

ただ単にB4と言ったら、コピー紙ではありません、B列の4行目のセルということです。

ここまでは、日ごろエクセルに触れている人は説明の必要もないと思いますが、VBAのコードを書くときこのセル番地の概念をよく理解する必要があるので、あえて確認のために説明しています。

超簡単電卓 (計算式埋め込み)

C5、E5、G5の各セルに注目します。

C5セルとE5セルに入力した数値の足し算結果をG5セルに表示するだけの、ごく簡単な計算式を設定します。

G5のセルを選択して計算式を書きます。
計算式を書く時のお約束のイコール【=】を入れます。
イコールに続けて、C5セルをマウスで選択すると、自動的に=C5と書かますから続いて演算子の+を入れます。
続けてE5をマウスで選択します。
続けてエンターキーを押すと、計算式が完成します。
計算式が完成すると即座に結果を計算して表示します。この状態ではC5セル,E5セルは空白なので計算結果は0になります。
ここで、C5とE5に具体的な数値を入れてみます。
足し算の結果が表示されたでしょうか。
ここの赤枠では足し算をする計算式しか書いていないので計算はいつも足し算の結果しか表示しません。

超簡単電卓 (四則計算プログラム)

今度は、下図のように青枠の各セルに注目して様々な計算結果を表示できるVBAを使ってプログラムを作ってみます。

コードエディター

早速コードエディターを開いてみましょう

エクセルの表(シート)が開いている状態で、【Alt】キーを押しながら【F11】キーを押します

下図のようなコードエディターが開きます

左側にあるプロジェクトウインドウにあるSheet1(Sheet1)をダブルクリックすると右側のフレームにコードウインドウが開きます(ここでは筆者の好みで黒い画面にしていっますが、普通は何も設定していなので白い画面になります)

下図で示している▼を押して「Worksheet」を選択します。

 

下図のような文字列が自動的に配置されます。

この2行の文字列のPrivate Sub・・・・・・

End Subの間に命令文を書きます。

この Private Sub と End Subに囲まれたブロックのことをプロシージャと読んでいます。

このプロシージャは特別な役割を持っていて、Excelのシートの任意のセルがマウスなどで選択されたときエクセルから自動的に呼び出されて、この二行間に書かれた内容を処理します。

この状態ではまだこの二行間に何も書かれていないのでこのプロシージャは呼び出されますが何も処理しません。

それでは、二行間に次のような命令文を書いて見ましょう

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

MsgBox   “選択したセルは ”   &   Target.Address

End Sub

書き終えたら、早速任意のセルを選択してみます。

下図ではD14セルを選択してみました。ほとんど同時にメッセージボックスが表示され、選択したセルのアドレスを教えてくれます。

どこかのセルをクリックするたびにエクセル本体がWorksheet_SelectionChangeというプロシージャが呼び出してMsgBoxという命令のコード文を処理した結果、メッセージボックスが表示されクリックしたセルの番地を確認できるというプログラムが実行されたのです。

プロシージャが呼び出されるきっかけをイベントと言っています。エクセルの中ではいたるところで多くのイベントが準備されています。

例えばWorkSheetにあるイベントにはWorksheet_SelectionChangeのほかにも様々なイベントがあるのでネットで調べてみましょう。
下図はリストからBeforeDoubleClickイベントを選択したとき、自動的にプロシージャのひな型が表示された状態です。この二行間に命令文を書けば、WorkSheetのどこかのセルをダブルクリックしたときその命令文を処理してくれるのです。

 

ただこれだけでは面白くないので青枠で示したセルに数字を入れたら計算をするプログラムを書いてみましょう。

先ほどの MsgBox   “選択したセルは ”   &   Target.Address は消しておきます。

そして今度はいきなり沢山のコードを書きます。ちょっと大変ですがここからコピーして貼り付けしても大丈夫ですから、頑張ってやってみましょう。

図、SelectionChange(セレクションチェンジ)プロシージャ

 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range(“D10”) = “かける” Then

‘掛け算する
Range(“G10”).Value = Range(“C10”).Value * Range(“E10”).Value

ElseIf Range(“D10”) = “わる” Then

‘割り算する
Range(“G10”).Value = Range(“C10”).Value / Range(“E10”).Value

ElseIf Range(“D10”) = “たす” Then

‘足し算する
Range(“G10”).Value = Range(“C10”).Value + Range(“E10”).Value

ElseIf Range(“D10”) = “ひく” Then

‘引き算する
Range(“G10”).Value = Range(“C10”).Value Range(“E10”).Value

End If

End Sub

書き終えたら、早速青枠にあるセルに具体的に数値を書き入れてみます。

D10セルにたす、ひく、かける、わるの文字列を入れてみます。

どうでしょう、正しく計算されたでしょうか。

プログラムを書く上で決められた約束事や作法など沢山のルールがあるので、少しずつ解説していきます。

 

VBAのセル番地の表し方

番地 E列の3行目のセルを表す・・・・・Range(”E3”)

     〃         ・・・・・Cells(3,5)

プロシージャの中でキーボードから r を入力して【Ctrl】+【スペース】キーを押すとrで始まる命令文の入力候補がリスト表示されるのでそれらの中から適当な命令を選択すると正確な分を作成することができます(インテリセンスと言っています)更に、その選択したオブジェクト(例えばRange)に続けてドット(.)を打つとそのオブジェクトがが持っているメンバーを表示することができます。
オブジェクトとかメンバーとか出てきましたが、この辺はまだすこし難しい概念なので今は飛ばして特に理解してなくても大丈夫です。下図はワークシートのどこかのセルをダブルクリックしたときセルのA3に「あいうえお 」という文字列を書き込む命令文のコードを書く時のインテリセンスの様子です。

Range(  に続く  ”A3”)は手入力します。A3は作成者が任意に指定する場所なので明示的に入力しなければなりません。
ドットに続くValueのところは Texstを選択しても大丈夫です。
これで、ワークシートをダブルクリックしたらこのプロシージャが呼び出されてワークシートのA3セルに「 あいうえお 」の文字列を書き込まれます。お約束 : プログラムの中で文字列を扱うときはその文字列を ”(ダブルクォーテーション)で囲みます。更に、命令文(コード)はすべて半角英数字を使います。

If制御文の使い方

条件式で処理を分岐

条件式の例 条件式が真(True)か、偽(False)か、ということ

図、SelectionChange(セレクションチェンジ)プロシージャに入って一番最初の文にある

Range(“D10”) = “かけるが条件式です。

実際のワークシートのD10セルに「 かける 」と書いてあるとします。この場合

Range(“D10”) = “かけるという条件式はになるといえます。そして、実際のワークシートのD10セルに「 ひく 」と書いてあったとすると、Range(“D10”) = “かけるという条件式はになるといえます。

つまり、条件式Range(“D10”) = “かけるはD10セルにかけると書いてあるんか?と聞いているんです。言い換えるとD10セルに書いてある値かけるイコールなのか?という意味です。

算術演算子。

演算子 説明 得られる結果
+ 足し算 8 + 5 13
引き算 10 – 4 6
* 掛け算 3 * 5 15
/ 割り算 8 / 5 1.6
¥ 割り算の商 8 ¥ 5 1
Mod 割り算の余り 8 Mod 5 3
^ べき乗 6 ^ 2 36

比較演算子

演算子 説明 得られる結果
< 小さい 8 < 5 False
<= 以下 3 <= 8 True
> 大きい 8 > 5 True
>= 以上 3 >= 8 False
= 等しい 3 = 8 False
<> 等しくない 3 <> 8 True

応用
D10セルにたすと書いてあるとすると、条件式Range(“D10”)<>かけるは、真となります。
Range(“D10”)の値かける以外か(等しくない)?と聞いているので、まさに等しくないのでその通り()であるといえるのです。

 

 

If文の基本

If 条件式 Then

真の時の処理文をかくところ

Else

偽の時の処理文をかくところ

End If

VBAのプログラム処理

VBAはあるイベントが呼び出したプロシージャの内容(処理文)を処理するときそのコード文が何千行、何万行あろうとも、必ず一番上の行から一行一行、地道に処理実行していきます。最近のコンピュータの処理速度は非常に高速です(CPU、1GHzで一秒間に10億回?も計算できるらしい)からほんの一瞬で処理が終了します。

あまり詳しくは自分もよく理解していませんが、初心のうちはこのくらいの理解があれば十分だと思うので、とにかく数多くのコードを書くうちに少しづつ理解が進んでいくと思います。

分からないことが出てきたとき「VBA IF文」とかで、ネット検索すれば詳しい解説を見ることができます。数多く検索していろんな使い方を知って、実際に書いてみるを繰り返し続けると、いつの間にかコードも読めるようになるし、使えるようにもなりますよ。

がんばりましょう。

Cellsの使い方

VBAプログラムの中でワークシートのある特定のセルを表す方法としてRangeを紹介しましたが、ここではセルを扱うためのもう一つのCellsの使い方を勉強します。

Rangeではセルの位置情報を文字列形式で表しました。Range(“B3”)は、B列の3行目のセルと解釈しました。

Cellsではその位置情報を数値で表します。上のB列の3行目のセルの場合、Cells(3,2)と書きます。かっこ内のカンマを挟んで左側の数値は行を表し、右側の数値は列を表します。

このCellsは次で説明する「繰り返し処理」で力を発揮します。

繰り返しの処理

繰り返し処理とは、例えばワークシートにデータが縦横に連続して並んでいるある種のデータベースのようなものがあるとすると、そのセルのデータに連続してアクセスするとき非常に効率よく作業できます。

具体例:参照入力のやり方

ここでは、別のワークシートに作成してる食品名データベースを参照して特定の食材を取り出して表示するプログラムを作ってみます。少し初心者にはハードかもしれませんが、こんな風に使うんだとの一例ですから、軽く付き合ってください。

牛という文字列でデータベースを検索して取り出すイメージ例

食材名データベース

データを検索して表示するフォーム

 

 

食材名データベースには約2000件のデータがあります。そのデータの中から牛という文字列が含まれた食材名を拾い出します。

前提・・・ ワークシートのD列は食材品番で、E列が食品名です。

処理概要・・・ データベースの全ての行に牛という文字列が含まれているか調べる。
含まれていたらすべてを変数に取り出しておく。
変数に入っていその品名データをとりだして、フォームに配置したリストボックスに
一覧表示する。

繰り返し処理の実際

まず、データベースが何行あるか調べるます。(繰り返し処理の回数を指定するため)

8行目の r  =  Range(“D1”).CurrentRegion.Rows.Count でデータベースの行数が取得できます。

10行目の For gyou = 1 To r  から

18行目の Next gyou までを rの行数まで繰り返します。

何を繰り返すのか? 12行目から16行目までのコードを処理します

If Cells(gyou, 5) Like “*” & mojiretu & “*” Then

  listbox1.AddItem Cells(gyou, 5)

End If

10行目の gyou = 1 To r gyouには繰り返すたびに1から順にまで入ります

一回目の繰り返しのときgyouには1が入っています。つまりCells(gyou, 5)はCells(1, 5)と読み替えます。

二回目以降の繰り返しでは順次Cells(2, 5)、Cells(3, 5)と読み替えます。

その後の Like “*” & mojiretu & “*” Then はちょっと難しいですが、具体的にCells(gyou, 5)に入っている文字列値の中にmojiretuが入っているかを判断しています。mojitetuには 牛 が入っているので Cells(gyou, 5)の食材名の中にという文字が含まれていればとなると判断します。

ややこしい文ですが、今は詳しくわからなくても大丈夫です。繰り返し処理の中でCellsをどう使うかの意味が分かっていただければいいのです。

そして、14行目の文でリストボックスの一覧リストにCells(gyou, 5)の値を登録しています。

12行目はIf文なので条件が真(True)なら14行目を処理しますが、偽(False)なら何も処理せずにEnd Ifを抜けて、繰り返しの10行目に戻ります。

 

 

どうでしょうか?Cellsの使い方繰り返しの処理、If文のイメージがお分かりいただけたでしょうか。

 

 

コメント

タイトルとURLをコピーしました