Home Excel(エクセル)マクロ・VBA

企業研修講師派遣のBESTグループ
出張パソコン教室ITスクール
webコンサルティングスクール
パソコンの家庭教師BEST

Q.前回、ポップアップでメッセージを表示する方法を見ていて、メッセージを改行するときに vbcr を使えば良いことが分かりました。それで文字列という考え方を知り、シート上で入力した値を文字列として、いろいろ操作できることがわかりました。すぐ使えそうな、代表的な文字列の操作方法について、教えていただけませんか。

 

A.わかりました。文字列操作は、とにかくたくさんありますので、順を追ってひとつひとつ説明いたしましょう。

さっそくコードを見ながら見ていきましょう。まずは、文字列のサイズ(長さ、文字数)を知る関数からです。
A1セルの文字数を知るには、下記のように書きます。(今回はプロシージャ名などは省略しますね)

Dim l As Integer
l = Len(Cells(1,1).Value)

さて、どうして文字列の文字数など知りたいのだろう?と思われた方もいらっしゃるかも知れません。
これに対する答えは、文字を操作しようとして場合に、何文字目から何文字目、とか、後ろ何文字残して、とかを指定する際に、
全体の文字数を知らないと面倒なことが多いためです。後で分かってくると思います。

次は、文字の先頭何文字を取る、というようなときに使う、Left関数です(文字の先頭は左ですよね)。以下は以前学んだ MsgBox を使ってポップアップで表示させています。

MsgBox(“A1セルの前3文字は” & Left(Cells(1,1).Value,3))

さらに、文字の末尾何文字を取る、というようなときに使う、Right関数です(Leftの逆)。

MsgBox(“A1セルの前3文字は” & Right(Cells(1,1).Value,3))

先頭(左)と末尾(右)ときたら、中間(真ん中)ですよね。下記のようにします。

MsgBox(“A1セルの前2文字目から4文字目までは” & Mid(Cells(1,1).Value,2,4))

さて応用編。入力されているデータが全て会社の場合、最後に「株式会社」「有限会社」「合同会社」が入っているとしましょう。
そのデータに対して、「会社」をなくした文字を取得するためにはどうしたら用でしょうか?

ここで最初に挙げた文字数を知る「Len」が登場します。会社名の文字数はそれぞればらばらなので、先頭(左)から最後から2文字前まで、という指定をするためには、長さを知る必要がありますよね。下記のようにします。(A1セルに会社名「マクロ株式会社」などが入っているとします)

MsgBox(“「会社」を除くと” & Left(Len(Cells(1,1).Value)-2))

コードを見るだけでなく、実際に入力して実行結果を確認してみて下さいね。

Q.前回、ポップアップでメッセージを表示する方法をお尋ねした者です。MsgBox関数、便利に使っていますが、使っているうちにデータ入力もポップアップのウィンドウから入力できないかと思い始めました。もちろん、シート上に入力領域を作っても良いのですが、そうするとやはり入力途中データもシート上に残ってしまいますので。同じような関数はあるでしょうか。

 

A.はい。このような場合には、InputBoxという関数が用意されています。こちらの説明を致しましょう。

こちらもまずは実物を見て頂きましょう。シート上にボタンを貼り付け、このプロシージャに以下のコードを記述して下さい(ボタン名は「ボタン1」とします)※。これがどのような処理を行うか想像できますか。

※補足:「開発」タブで「デザインモード」を選択した後、「挿入」からボタンのアイコンを選ぶ。シート上に配置したら「マクロの登録」が自動的に表示されるので、そこから「ボタン1_Click()」を選択する。

Sub ボタン1_Click()
Cells(1, 1).Value = InputBox(“年齢を入力して下さい”)
End Sub

もうだいたいおわかりですね。入力した値(これは年齢を想定)を、Cells(1,1)、すなわちA1セルに入力します。InputBox関数によって、ポップアップの入力ウィンドウが開きます。どのような表示になるかはご確認下さい。

さて、ここまでで単純なデータの入力はできます。次はちょっと応用してみましょう。
今回、入力したいデータは年齢ですので、数値です。従って、文字が入力できるようであってはデータとして相応しくありません。
InputBoxには、数値に限定して入力するパラメータを指定することが可能です。

Sub ボタン1_Click()
Cells(1, 1).Value = Application.InputBox(“年齢を入力して下さい”,Type:=1)
End Sub

以前のコードとの違いがわかりましたか?InputBoxの前にApplication.があるのと、パラメータに Type:=1 が指定されています。
これにより、数値入力のみ可能となります。
(文字を入力すると、エラーとなります。どのような表示となるかは、コードを実行して確認して下さいね)

さらに、前回MsgBoxでご説明した、InputBoxの表示メッセージに改行を入れることももちろん可能です。

Sub ボタン1_Click()
Cells(1, 1).Value = Application.InputBox(“年齢を入力して下さい” & vbcr & “(数字のみ入力可)”,Type:=1)
End Sub

このように、文字に関するやり方は、たとえ関数が異なっても同じように適用できることが多いです。(VBAでは「文字列型」と認識する部分のため)

 

Q.このブログを見ながらいろいろなファイルに便利なマクロ機能を付けたりしています。それで人に使ってもうらうようにマクロ入りブックを作って渡しているのですが、操作したり入力したりしたときに、エラーや間違いの入力に対してメッセージを表示したいと思っています。Excelのシート上のどこかのセルに表示することはできますが、保存したときにその文字も一緒に保存されてしまうので、できればポップアップメッセージにしたいのですが。

A.このような場合には、MsgBox(MessageBoxの略)という関数が用意されています。こちらの説明を致しましょう。

まずは実物を見て頂いた方が良いかも知れません。シート上にボタンを貼り付け、このプロシージャに以下のコードを記述して下さい(ボタン名は「ボタン1」とします)※。これがどのような処理を行うか想像できますか。

※補足:「開発」タブで「デザインモード」を選択した後、「挿入」からボタンのアイコンを選ぶ。シート上に配置したら「マクロの登録」が自動的に表示されるので、そこから「ボタン1_Click()」を選択する。

Sub ボタン1_Click()
If Cells(1, 1).Value > 130 Then
MsgBox (“年齢は130歳までで入力して下さい”)
End If
End Sub

マクロを作ったことのある方ならだいたいおわかりでしょう。A1セルで入力した数字(これは年齢を想定)をチェックする関数であり、If文で130を超える入力があった場合は、メッセージボックスを表示します。どのように表示されるかは、試してみて下さい。

さて、ここまでで単純な文章の表示はできます。次はちょっと応用してみましょう。
まずは、改行を入れる場合です。メッセージボックスで2行表示するためには、 vbcr という定数を使います。早速、見てみましょう。

Sub ボタン1_Click()
Dim limit As Integer
limit = 130
If Cells(1, 1).Value > limit Then
MsgBox (“年齢が上限値を超えています。” & vbCr & “年齢は” & limit & “歳までで入力して下さい”)
End If
End Sub

“”でくくられた文字列を vbcr で連結(&)すれば良いですね。もう一つ工夫したのが分かって頂けましたか?
今回のご紹介とは直接関係はありませんが、上限値チェックと表示の値(年齢)を、変数 limit で同じ値で参照するようにしました。
こうすれば、年齢の上限値を変更する際に、変更箇所が1箇所で済むので、「保守性」が高まりますね。
このような観点にも注意して、マクロ開発をして頂ければと思います。

Q.前回ご質問させて頂きました、ある日付が休日かどうかを判定するのにIsNonWorkDay関数を便利に使っているのですが、いろんなファイルにこの関数を登録するのが面倒だし、最近、休日判定の部分のプログラム(コード)を足したいと思っているのですが、今まで登録した全てのファイルを直さないといけないと思うと憂鬱です。どこか1箇所で管理できないのでしょうか?

A.使用上の不便に対して、良い改善点に気づきましたね。このような場合は、Excelでは「アドイン」という仕組みを使います。

前回のご質問でお答えしたとおり、Excelでは、VBAのFunctionを、Excelのワークシートで使うことができます。これをワークシート関数に対して、「ユーザー定義関数」と呼んだりします。

※以前の記事は下記のリンクからご確認頂けます。

http://www.office-kaiketsu.com/excel%ef%bc%88%e3%82%a8%e3%82%af%e3%82%bb%e3%83%ab%ef%bc%89/%e3%83%a6%e3%83%bc%e3%82%b6%e3%83%bc%e5%ae%9a%e7%be%a9%e9%96%a2%e6%95%b0%e3%82%92%e4%bd%bf%e3%81%86/

ここで作成したユーザー定義関数「IsNonWorkDay」を、いろんなワークブックで使いたいと言うことですね。いろんなワークブックというより、自分のパソコンで開く全てのワークブックで使えるようにする、これがアドインです。

さて、それでは実際の手順を追って説明しましょう。
まず、新しいワークブックを開き、前回用意した、InNonWorkDay関数を標準モジュールにコーディングして下さい。(ちゃんと動作確認はして下さいね)
その後は、下記の手順でアドインとして保存します。

1.「ファイル」タブの「名前を付けて保存」をクリック
2.「コンピュータ」の「参照」をクリック
3.「名前を付けて保存」のダイアログが開くので、「ファイルの種類」から「Excelアドイン」を選択
※この時点で、保存フォルダが自動的に「AddIn」フォルダに変わります。これがアドインの保存されるフォルダです。
4.「ファイル名」はお好きな(分かりやすいもの)ものとして、保存します。

この時点で、アドインファイルが作成されましたから、これを組み込みします。

1.「ファイル」タブの「オプション」をクリック
2.「Excelのオプション」ダイアログで左側の「アドイン」をクリック
3.「設定」をクリック
4.「アドイン」ダイアログで先ほど保存したアドインファイルの名前にチェックして「OK」で決定

これで、これから開くブックすべてで「IsNonWorkDay」関数が使えるようになります。
もちろん、他のユーザー定義関数でも同様のやり方が使えますから、お気に入りの自分だけの関数をアドイン登録すると便利に使えますね。

このようにアドインのユーザー定義関数を使うことで、いろんなファイルで、すぐに自分の用途に合ったユーザー定義関数が使えるというメリットがあります。ただし、逆のデメリットとしては、自分で作ったユーザー定義関数入りのアドインが、他の人のパソコンにない場合、これまで説明した手順でアドインを渡して追加してもらうか、ブックの標準モジュールで定義しなおさないと使えなくなります。
その点を踏まえて、状況に応じて便利に使っていきましょう。

Q.Excelでスケジュール管理などを行っていて、日程が休日かどうかを判定するのにWEEKDAY関数を使っていますが、これだと祝日や会社の創立記念日などの休日が判定できません。何か良い方法はありませんか?

A.ユーザー定義関数で、休日かどうかを判定する関数を作成しましょう。

Excelでは、VBAのFunctionを、Excelのワークシートで使うことができます。これをワークシート関数に対して、「ユーザー定義関数」と呼んだりします。まずは下記のコードを「標準モジュール」の関数(Function)として作成して下さい。

Function IsNonWorkDay(d As Date)

IsNonWorkDay = False

If WorksheetFunction.Weekday(d) = 1 _
Or WorksheetFunction.Weekday(d) = 7 Then ‘★土日の判定
IsNonWorkDay = True
Else
If Month(d) = 4 And Day(d) = 1 Then ‘★創立記念日
IsNonWorkDay = True
End If
End If

End Function
これでユーザー定義関数「IsNonWorkDay」ができました。先にこの関数のコードを説明しましょう。まず、この関数は何もなければFALSEつまり休日でないとの結果を返します。この後のIF分では、休日となる条件を判定し、その場合にこの関数の戻り値をTRUEすなわち休日として返すような作りになっています。ひとつめの★部分で、ワークシート関数のWeekdayを使用して、この値が1(日曜日)または7(土曜日)の場合にTrue(つまり休日と判定する)を返します。もしここで休日でなければ、次の★の部分の処理で、土日以外の休日判定をします。この例では、4月1日が会社の創立記念日で休日と判定されるようになっています。この後に、例えば国民の休日の日付などを追加することで、IsNonWorkDay関数で全ての休日を判定できるように処理することができます。

さてこのIsNonWorkDay関数は、ワークシートのセル上で、普通のワークシート関数と同じように使用できます。試しにA1セルに任意の日付を入力し、A2セルに =IsNonWorkDay(A1) を入力してみましょう。TRUEと表示されれば休日、FALSEと表示されれば休日ではありませんが、期待した結果になっていたことと思います。
このように、ユーザー定義関数を使うことで、セルの式がすっきりするというメリットがあります。ただし、逆のデメリットとしては、自分で作ったユーザー定義関数の入ったシートは、きちんと説明しないと、別の人が使う場合にかえってわかりにくかったりします。
その点を踏まえて、状況に応じて便利に使っていきましょう。