【入門編】初心者向け EXCEL VBA (5/10) – VBAユーザーフォームは男のロマン【これで完璧】

ワテのブログ記事の中でも、圧倒的に訪問者さんの数が多いのが、このVBA講座だ。

現在のところ、以下の７つのEXCEL VBA講座がある。

第一回目	【入門編】初心者向け EXCEL VBA (1/10) – 今年こそ使えるようになりたい人
第二回目	【入門編】初心者向け EXCEL VBA (2/10) –実際にVBAを開発する手順はこれだ
第三回目	【入門編】初心者向け EXCEL VBA (3/10) – EXCEL VBAのFor Nextループをマスターする
第四回目	【入門編】初心者向け EXCEL VBA (4/10) – EXCEL VBAのFor Nextループで文字列を置換する
第五回目	【入門編】初心者向け EXCEL VBA (5/10) – VBAユーザーフォームは男のロマン【これで完璧】
第六回目	【入門編】初心者向け EXCEL VBA (6/10) – 現在のシートを加工して新シートを作成する【前半】
第七回目	【入門編】初心者向け EXCEL VBA (7/10) – 現在のシートを加工して新シートを作成する【後半】

それと、読者の皆さんの疑問に答えるシリーズも開始した。

	タイトル	質問者さん(敬称略)
第一回目	【ワレコEXCEL講座】エクセルファイル①をエクセルファイル②に読み込む	しがない事務員

当記事はそのEXCEL VBA講座の第五回目。

これら過去四回の講座をお試し頂いた皆さんは、VBAプログラミングの基本であるループ処理をマスター出来た人も多いだろうと思う。

ループ処理はプログラミングの基本中の基本なので、ループを使えるようになったあなたはプログラミング手法の50%くらいはマスター出来たと言っても良い。

さて、いよいよ第五回目のワテ流VBA講座である。

今回の講座で使用したサンプルxlsmファイル

【入門編】初心者向け EXCEL VBA (5/10) – VBAユーザーフォームは男のロマン【これで完璧】用のxlsmファイル

試してみたい人はお気軽にダウンロードして下さい。

VBAプログラミングの醍醐味であるユーザーフォーム
今回の例題
まとめ

VBAプログラミングの醍醐味であるユーザーフォーム

第五回目では、いよいよVBAプログラミングの醍醐味であるユーザーフォームを作成してみよう。

この技術を習得できれば、あなたのEXCEL＆VBAライフは一変するに違いない。

職場でも学校でも、ユーザーフォームを使ったVBAプログラムをさり気無く使いこなしていると、

『○○さん、凄い。そんなプログラムが作れるんだ！』

と異性からモテモテ。

『○○さん、そんなプログラムが作れるならこんなヤツも作ってくれないか？』

と上司から新しい仕事の依頼が。これで出世コース間違いない。

今回の例題

毎度おなじみの新潟県のおかき製造会社さんのリストであるが、今回もこのデータを使ってVBAを覚えよう。

新潟県米菓工業協同組合		〒940-0048	新潟県長岡市台町1丁目8番4号
さくら製菓（株）	http://www.sakura-do.jp	〒957-0058	新発田市西園町1丁目11番22号
（株）栗山米菓	http://www.kuriyama-beika.co.jp	〒950-3134	新潟市北区新崎2661番地
（株）三幸	http://www.sanko-seika.co.jp	〒950-3134	新潟市北区新崎1丁目13番34号
亀田製菓（株）	http://www.kamedaseika.co.jp	〒950-0198	新潟市江南区亀田工業団地3丁目1の1
吉沢製菓（有）	http://www.gosennet.com/~yoshizawaseika/	〒956-0852	新潟市秋葉区柄目木101番地
（株）末広製菓	http://suehiroseika.co.jp	〒953-0131	新潟市西蒲区西長島779番地
浪花屋製菓（株）	http://www.naniwayaseika.co.jp	〒940-1104	長岡市摂田屋町2680番地
越後製菓（株）	http://www.echigoseika.co.jp	〒940-0056	長岡市呉服町1丁目4番地5
加藤製菓（株）	http://www9.ocn.ne.jp/~kato.k.k/	〒940-1106	長岡市宮内8丁目8番18号
岩塚製菓（株）	http://www.iwatsukaseika.co.jp	〒949-5492	長岡市浦9750番地
阿部幸製菓（株）	http://www.abeko.co.jp	〒947-0026	小千谷市上ノ山4丁目8番16号
竹内製菓（株）	http://www.takeuchiseika.com	〒947-0003	小千谷市大字ひ生乙1672番地
（有）山文	http://www.echigo-yamabun.com	〒949-7403	魚沼市根小屋1441-1
（株）ブルボン	http://www.bourbon.co.jp	〒945-0011	柏崎市松波4丁目2番14号
（株）新野屋	http://www1.ganba716.net/modules/aranoya	〒945-0055	柏崎市駅前1丁目5番14号
（株）みながわ製菓	http://www.minagawa-seika.co.jp/	〒943-0882	上越市中田原111番地

今回の例題では以下作業を行う。

エクセルのシート上にボタンコントロールを一個配置する（下図のダイアログを開く）。
そのボタンコントロールをクリックすると自作のユーザーフォームが一個開く。
ユーザーフォームには変換するボタンがある。
そのボタンをクリックすると、前回の例題で行った文字列変換を実行する。
(株)　➜　株式会社　という変換

つまりこの作業内容は、シートにあるデータに対して対話型のユーザーインターフェースを用いて処理する場合の基本となる。

なので、この例題を応用すれば、あなた自身でも各種の便利機能をユーザーフォームを使って実現出来るのだ。

具体的な画面の説明（今回作成する画面）

シートにボタンコントロールを一個配置

ボタンコントロールをクリックするとユーザーフォームダイアログが開く（下図）

下図のように変換対象となる領域をマウス左クリックで選択する。

この状態でユーザーフォームの上にある変換するボタンをクリックすると、下図のように変換結果がA列に書き込まれる。

“(株)” が “株式会社” に置き換わった。

同じく

“(有)” も “有限会社” に正しくリプレースされている。

完璧や！

あなたもこういうGUIを自由自在に作れるようになるのだ。

今まで必死で手作業で行っていたEXCEL作業が、ちょっとしたVBAプログラムを書くだけで、一瞬で処理が完了する。

さっそくユーザーフォームを表示する手法を覚えよう

今回は、新規のEXCELブックを開くところから始めよう。

ブックを開いたら、開発タブのVisual Basicタブをクリックする。

上図のように、空のシートと空のVisual Basicのエディタ画面が開く。

もし開発タブが見つからない人は、第一回講座をお読みください。

開発タブを無事に開く事が出来たら、さっそく標準モジュールを追加する。

VBAのプログラムのコードは、SheetやThisWorkbookにも書く場合があるが、共通で使う関数やグローバル変数はこの標準モジュールと言う所に書いておく。

標準モジュールを追加する

下図のように、Microsoft Excel Objectを右クリックして、

挿入　➜　標準モジュール

を実行。

無事に追加出来ると下図のように空のウインドウが開く。

下図では黒い背景だが、皆さんの場合はデフォルトの白い背景だと思う。

私が黒背景にしている理由は、単なる好みの問題なので気にしなくても良いです。もし同じように設定したい人は、第二回講座をご参照下さい。

この空のウインドウには一行だけ

Option Explicit

と言う行が書かれている。

この行は、無くても良いのだが、通常は入れておくと良い。

変数を使う場合には、必ず前もって宣言してから使うと言う意味だ。

さて、この空のウインドウに以下のVBAのソースコードを貼り付ける。

Option Explicit

Public Const vbCrLf2 = vbCrLf + vbCrLf
Public Const vbCrLf3 = vbCrLf + vbCrLf + vbCrLf
Public Sub OpenUserForm1()
   UserForm1.Show vbModeless
End Sub

Public Sub LoopThroughAllSelection()
   Dim i As Long
   Dim rng_i As Range
   With Selection

   For i = 1 To .Areas.Count
      Set rng_i = Selection.Areas(i)
      Call LoopThroughOneSelection(rng_i)
   Next i

   End With

End Sub

Public Sub LoopThroughOneSelection(ByRef rng As Range)
   Dim adr As Variant
   Dim str As String
   Dim strNew As String
   Dim cell As Range
   Dim cellNew As Range

   For Each cell In rng
      adr = cell.Address
      str = cell.Value
      strNew = MyConvStrFunc(str)
      Set cellNew = cell.Offset(0, -1)
      cellNew.Value = strNew
   Next

End Sub

Private Function MyConvStrFunc(ByRef str As String) As String

'【機能】
'
'　（株）を　株式会社　に置換する
'
'　各種のカッコ株に対応版
'
'（株） = 　全角（　　株　　全角　）
'(株)　 = 　半角 ( 株　　半角 )
'㈱ = 一文字で　カッコ株のUnicode文字
'（株) = 全角（　　株　　半角 )
'(株） = 半角 (　　株　　全角 ）
'
'などどれでも"株式会社"に置き換える。
'有限会社に関しても同じ

   Dim strNew As String
   strNew = Replace(str, "（株）", "株式会社")
   strNew = Replace(strNew, "(株)", "株式会社")
   strNew = Replace(strNew, "㈱", "株式会社")
   strNew = Replace(strNew, "（株)", "株式会社")
   strNew = Replace(strNew, "(株）", "株式会社")
   strNew = Replace(strNew, "（有）", "有限会社")
   strNew = Replace(strNew, "(有)", "有限会社")
   strNew = Replace(strNew, "㈲", "有限会社")
   strNew = Replace(strNew, "（有)", "有限会社")
   strNew = Replace(strNew, "(有）", "有限会社")
   MyConvStrFunc = strNew

End Function

その結果、Module1が下図のようになっていれば、標準モジュールの追加は成功だ。

ユーザーフォームを追加する

ユーザーフォームとは、ウインドウが開いてその上にボタン、チェックボックス、ラジオボタンなどが乗っているやつだ。ダイアログとか、ポップアップウインドウ、メッセージボックスなどと言う場合もある。

下図のように、Sheet1（Sheet1）を右クリックして、

挿入　➜　ユーザーフォーム

を選択する。

そうすると、下図のように空のUserForm1というウインドウが開く。

もし、ツールボックスというウインドウが表示されていない場合には、

メニュー　➜　表示　➜　ツールボックス

で開ける。

ラベルコントロールを追加する

同じく、ボタンコントロールを二個追加する

無事に追加出来ると以下のようになる。

ボタンの位置や大きさはマウスドラッグで調整出来る（上図）。

左側のボタンをマウスクリックで選択して表示されるプロパティウインドウで以下のように変更しておこう。

オブジェクト名: CommandButton1Conv

Caption: 変換する

同じく右側のボタンは、以下のように変更しておく。

オブジェクト名: CommandButton2Close

Caption: 閉じる

なお、あくまでワテ流の変数のネーミングなのだが、多くのボタンを扱う場合に、

CommandButton1Conv

CommandButton2Close

CommandButton3XXXX

CommandButton4YYYYY

のように変数名の中に番号を入れておくと、自分としては分かり易い気分がするのでそうしている。

番号が無い場合には、以下のようになるが、

CommandButtonConv

CommandButtonClose

CommandButtonXXXX

CommandButtonYYYYY

ボタンの数が10個とか20個とか増えて来ると、分かり辛い。

あくまでワテ流の手法なので、皆さんは好き好きにして下さい。

CommandButton1Convをダブルクリックする

下図のユーザーフォームの編集画面で、左側のボタンの四角い部分でダブルクリックを行うと以下のウインドウが開く。

このウインドウは、CommandButton1Convがクリックされた時に実行される関数を記述するものだ。

何も無い空の関数テンプレートが表示されるので、以下の一行を追加しておく。

同様に、右側のCommandButton2Closeをダブルクリックして関数を追加するのだが、一個ずつ追加して行くのは手間がかかるので、以下のソースコードを全部コピーして、上の黒いウインドウに貼り付けると良いだろう。

Option Explicit

Private Sub CommandButton1Conv_Click()

   Module1.LoopThroughAllSelection

End Sub

Private Sub CommandButton2Close_Click()

   'UserForm1.Hide 'ユーザーフォームを一時的に非表示にする場合

   Unload Me 'ユーザーフォームを閉じる場合

   'Unload UserForm1 'これでも良い

End Sub

Private Sub UserForm_Initialize()

   Label2.Caption = "このプログラムでは選択した範囲に対して以下の変換を行います。" + vbCrLf2 _

   + " (株) → 株式会社" + vbCrLf _

   + " (有) → 有限会社" + vbCrLf2 _

   + "複数領域の選択も可能です。" + vbCrLf _

   + "複数領域を選択する場合には" + vbCrLf _

   + "[CTRL+左マウス] で可能"

End Sub

これでVBAソースコードの入力は完了した。

ファイル保存

念のためにここでマクロ有効ブックxlsm形式でファイルを保存しておく。

シートにボタンを追加する

作業は、あと少しだ。

下図のようにsheet1を表示しておいて、

開発　➜　Visual Basic　➜　挿入

をクリックする。

フォームコントロールというウインドウが開くので、ボタン（フォームコントロール）というのをクリックする。

ボタンをクリックしたら、引き続き、シートの上で左クリックを行う。

その場所にボタンが配置されるのだが、その前に下図ウインドウが開く。

上図のウインドウが開いたら、下図のように、OpenUserForm1を選択してOKを押す。

これは、今から挿入予定のボタンをクリックするとOpenUserForm1という関数を実行すると言う意味だ。

OpenUserForm1は標準Module1の中で定義している。

上記のリストに表示される関数（=プロシージャ）は、標準モジュールの中でPublicで宣言されている関数である。

さて、無事にボタンが追加されると下図のようになる。

必要に応じてサイズや位置を調整しても良い。

また、文字の部分を左クリックして編集する事も可能（下図）。

以上で準備完了だ。

念のために、ここで上書き保存をしておく。

人生初作品のユーザーフォームを実行する

先ほどシート上に追加したフォームコントロールボタンをクリックする。

その結果、下図のようにユーザーフォームが表示されれば成功だ。

下図のように、目的の範囲を選択しておいて、変換するボタンをクリック！

その結果、選択範囲の中で(株)、(有)の文字がそれぞれ、”株式会社”や”有限会社”に置き換わるだろう。

どう！！！

これであなたもユーザーフォームの作成方法をマスター出来た。

VBAやマクロでの操作はUNDOは出来ない

なお、VBAのコードを実行した後は、UNDOは出来ない。理由は、VBAやエクセルではUNDO出来るのは人が手で行った操作のみで、VBA操作での変更はUNDO出来ない仕様なのだ。昔からそうなっている。

なお、あくまでワテの考えだが、VBA操作による変更であっても、技術的にはUNDO機能を入れる事は可能だと思う。何故マイクロソフトがそういう改良を行わないのかは不明だ。

複数選択範囲に対しても処理が可能

UNDOが出来ないので、取り敢えず、上の実行でA列にデータが書き込まれていると思うので、A列を選択してデ－タをクリアしておく。

次に、変換したい領域を下図のようにＣＴＲＬキーを押しながらマウス左クリックを行うと、複数の領域を選択出来る。下図では二ヶ所の領域を選択した。

この状態で変換するボタンをクリックすると、その選択したデータのみ変換される（上図）。

と言う事で、ネット上にある各種サンプルでは、あまり実用性を考慮せずに作られているものも多いと思うが、ワテ流のサンプルプログラムの場合には、実際のEXCEL作業で直面するであろうと思われる状況を想定して作っている。

なので、応用の効くサンプルだと思っているのだが。

モードレスとモーダル

UserFormを表示する場合に、以下のようにvbModelessを付けると、ユーザーフォームが開いている状態でも他の操作（セルの選択など）は可能である。

   UserForm1.Show vbModeless

一方

   UserForm1.Show vbModal

あるいは、

   UserForm1.Show

の場合にはモーダル状態になり、ユーザーフォームの操作以外は一切出来なくなる。

VBAに限らず、各種のプログラミング言語でも同じ機能はある。

どちらを使うかは好き好きだが、最近の流行としては、モードレス（他の操作が出来る）を使う事が多いようだ。と言うのは、モーダルにすると、ユーザーフォーム操作以外の他の操作が一切出来なくなるが、万一そのユーザーフォーム自身が他のウインドウの下に隠れてしまうなどの予期せぬ事態が起こると、パソコンの操作が不能になるなどの危険性がある。

また、ユーザーの感情としては、自分のパソコンなのにプログラムが勝手に利用者の操作を制限すること自体を快く思わない人も多い。

なので、ユーザーフォームとかダイアログとかポップアップなどの対話型のユーザーインターフェースはモードレスで作るのが良いだろうと思う。

ところが、ワテの場合には、もう一つ問題がある。それは、

モードレスとモーダルがどっちがどっちなのか混乱する

そういう人は多いのか少ないのか調査していないので分からないのだが、混乱する人も多いと思う。

何か良い覚え方は無いものか？

即席で考えてみた。

	ワテ流覚え方
モードレス	もーどーでもいいですから、何でもするよ。
モーダル	もーダルイから他の仕事は一切しない。