現在のところ、以下の７つのEXCEL VBA講座がある。
それと、読者の皆さんの疑問に答えるシリーズも開始した。
| タイトル |
質問者さん(敬称略) |
|
第一回目 |
【ワレコEXCEL講座】エクセルファイル①をエクセルファイル②に読み込む | しがない事務員 |
当記事はそのEXCEL VBA講座の第四回目。
前回の記事、
【入門編】 初心者向け EXCEL VBA (3/10) – EXCEL VBAのFor Nextループをマスターする
ではVBAのFor Nextループの基本を覚えた。
もし分かり辛い点がありましたら、お気軽にコメント欄でお知らせください。
さて、単にループを回してセルのデータを読み取ってイミディエイトウインドウに出力するだけでは役に立たない。
と言う事で応用編を作成してみた。
今回の記事で使ったEXCELのデータやVBAプログラムをダウンロードしたい人はこちらからどうぞ。
サンプルEXCEL VBAファイル 【ワテ講座】EXCEL VBAのFor Nextループで文字列を置換する用
「（株）」を「株式会社」に置き換える
置換前の元データでは会社名に省略形のカッコ株（株）文字が使われている。
それを、正式名称に置き換えてみよう。
| 置換後 | 置換前 |
| さくら製菓株式会社 | さくら製菓（株） |
| 株式会社栗山米菓 | （株）栗山米菓 |
| 株式会社三幸 | （株）三幸 |
| 亀田製菓株式会社 | 亀田製菓（株） |
| 吉沢製菓（有） | 吉沢製菓（有） |
| 株式会社末広製菓 | （株）末広製菓 |
| 浪花屋製菓株式会社 | 浪花屋製菓（株） |
| 越後製菓株式会社 | 越後製菓（株） |
| 加藤製菓株式会社 | 加藤製菓（株） |
| 岩塚製菓株式会社 | 岩塚製菓（株） |
| 阿部幸製菓株式会社 | 阿部幸製菓（株） |
| 竹内製菓株式会社 | 竹内製菓（株） |
| （有）山文 | （有）山文 |
| 株式会社ブルボン | （株）ブルボン |
| 株式会社新野屋 | （株）新野屋 |
| 株式会社みながわ製菓 | （株）みながわ製菓 |
この左側の置換後データを作成するのが今回のVBA講座の目的だ。
そのVBAプログラムがこれだ。
Option Explicit
Sub Test_ForLoop_Conv1()
'【機能】
'
' （株）を 株式会社 に置換する
Dim r As Long
Dim val_r As Variant
For r = 4 To 19
val_r = ActiveSheet.Cells(r, 2).Value
val_r = Replace(val_r, "（株）", "株式会社")
Debug.Print r, val_r
Cells(r, 1) = val_r
Next r
End Sub
実行方法は今までと同じだが、念のために最初からその手順を説明すると、
- EXCEL 2003, 2007, 2010, 2013, 2016を起動する
- 開発タブをクリックする
- 開発タブが見つからない人は以下の記事を参考にして開発タブを表示する
【入門編】 初心者向け EXCEL VBA (1/10) – 今年こそ使えるようになりたい人
- 開発タブのVisual BasicアイコンをクリックしてVBAエディタを開く（下図）
開いたVBAエディタで標準モジュールを追加する（上図）。
そうすると、下図のように標準モジュール Module1 と言うのが追加される。
この標準モジュールはVBAプログラミングを行う場合には、自作の関数などはこの中に書いておくと良い。
上図左のフォルダ一覧のように表示されている部分であるが、何だかいろんな種類のアイコンが表示されているし、VBAProjectとかMicrosoft Excel Objectsとか見慣れぬ英単語が並んでいるのでとってもややこしそうに思う人もいるかもしれない。
でも心配ない。
VBAProject(Book1)
Microsoft Excel Objects
Sheet1(Sheet1)
ThisWorkbook
標準モジュール
Module1
VBAProject(Book1)と言うのは、今編集しているBook1にくっ付いているVBAのプログラム一式と言う感じだ。
VBAプログラムが無い通常のBookでも、必ずこのVBAプロジェクトと言うのはあり裏に隠れている。で、今のように開発タブからVBAエディタを開いてVBAの編集モードに入ると隠れていたVBAProjectが編集可能になっただけだ。
そのVBAProjectは、幾つかの項目に分かれていて、
Microsoft Excel Objects
と言うのは必ず一つある。
その中にSheet1とThisWorkbookと言う項目が入っている。
今の例ではシートが一枚だけであるが、試しにSheet2やSheet3を追加してみると良い。
あるいはシート名を変更してみるとどうなるか試してみると良い。
Sheet1をクリックすると、下図のように
各種の情報が下段にズラズラと沢山表示されている。
初めて見る人は、何のこっちゃサッパリ分からないと思うが、最初のうちはあまり気にしなくても良い。
例えば一番下にある Visible の値を
-1 xlSheetVisible 現状
0 xlSheetHidden これにしてみる
2 xlSheetVeryHidden
のように変更するとシートを非表示に出来る（Sheet1のみだと出来ないのでSheet2を追加して試してみると良い）。
なので、VBAのプログラムでシートのこのプロパティを変更すると表示／非表示を制御する事が可能になるのだ。
それ以外のプロパティはTrue/Falseの論理型が多いが、必要に応じてTrue/FalseをVBAで変更すると、Sheet1に関する各種のプロパティを有効化/無効化出来る。どんなプロパティなのかは今は知らなくても良い。あるいは、全然知らなくてもたいていのVBAプログラミングでは困らない。
また、ThisWorkbookを選択すると、この下段に表示されるプロパティの項目が変わり、ワークブックに対するプロパティの項目となる。ワークブックの場合には、シートよりも設定項目が多いので、沢山の項目が表示される。興味ある人はスクロールバーを動かしてみると良い。
なお、この辺りの値をどんな風に変更しても、EXCELが壊れる事は無い。
あくまで今編集中のエクセルのファイルに対してのみ影響するので、適当に弄りまくっていろいろ試すと良いだろう。
で、もし訳分からなくなってファイルがおかしくなったとしたらEXCELを終了してそのファイルを削除しておけば良いので。
まあ、適当に色々試してみるのが良いだろう（あまりにも極端にヘンテコな事をしてEXCELが動かんとか言われてもワテとしては責任は取れないが。まあそんな事は無いと思うのだが。）
さて、本題に戻ると、空のModuleが追加出来たので、以下のコードをModuleの空のウインドウに貼り付ける。
Option Explicit
Sub Test_ForLoop_Conv1()
'【機能】
'
' （株）を 株式会社 に置換する
Dim r As Long
Dim val_r As Variant
For r = 4 To 19
val_r = ActiveSheet.Cells(r, 2).Value
val_r = Replace(val_r, "（株）", "株式会社")
Debug.Print r, val_r
Cells(r, 1) = val_r
Next r
End Sub
そうするとこんな感じになる（下図）
画面の背景が黒くなったことは気にしない。
ワテ好みの配色だ。
一方、処理対象のデータは以下の通り。
VBAプログラムを実行して（株）を株式会社に置き換える
下図のように貼り付けた関数内の適当な場所をマウスクリックするとそこにカーソルが置かれる。その状態で
ツールバーにある実行アイコン（緑の▶）をクリック。またはF5を押す。
なお、ツールバーに実行アイコンが複数出る場合もあるが、どちらを実行しても良い。あまり気にしなくても良い。
実行すると、A列に結果が表示される。
処理の説明
難しい所は無い。
2列目（B列）のデータを上から順番に読み取って val_r という変数に代入。
この val_r に会社名が入っている。
Replace関数を使ってval_rの中にある “（株）” を “株式会社” に置換する。
val_r = ActiveSheet.Cells(r, 2).Value
val_r = Replace(val_r, "（株）", "株式会社")
Debug.Print r, val_r
Cells(r, 1) = val_r
置換結果を Cells(r, 1) に代入すれば、1列目に結果が表示出来る。
ちなみに変数名 val_r としたのは、単にval でも良かったのだが、r行のデータを保持している感じを表現したかったので。深い意味は無い。真似しなくても良いです。
と言う事で無事に（株）が株式会社に置き換わった。
さて、
幾つか問題がある。
カッコ株の場合、
（株） 全角（ 株 全角 ）
(株) 半角 ( 株 半角 )
㈱ 一文字で カッコ株のUnicode文字
などがある。
あるいは
（株) 全角（ 株 半角 )
(株） 半角 ( 株 全角 ）
などが混じっているかも知れない。そんな変なデータ作るな！と言っても、そうなっているかも知れない。
そういう場合も全部考慮して株式会社に置き換える必要がある。
その改良版がこれだ‼
「（株）」を「株式会社」に置き換える（改良版）ﾍﾝﾃｺﾃﾞｰﾀ対応
Sub Test_ForLoop_Conv2()
'【機能】
'
' （株）を 株式会社 に置換する
'
' 各種のカッコ株に対応版
'
'（株） 全角（ 株 全角 ）
'(株) 半角 ( 株 半角 )
'㈱ 一文字で カッコ株のUnicode文字
'（株) 全角（ 株 半角 )
'(株） 半角 ( 株 全角 ）
'
'などどれでも"株式会社"に置き換える。
Dim r As Long
Dim val_r As Variant
For r = 4 To 19
val_r = ActiveSheet.Cells(r, 2).Value
val_r = Replace(val_r, "（株）", "株式会社")
val_r = Replace(val_r, "(株)", "株式会社")
val_r = Replace(val_r, "㈱", "株式会社")
val_r = Replace(val_r, "（株)", "株式会社")
val_r = Replace(val_r, "(株）", "株式会社")
Debug.Print r, val_r
Cells(r, 1) = val_r
Next r
End Sub
Replace処理を追加しただけだ。
まあ、とりあえずこれで良いが、以下の点が気になる人もいると思う。
もし最初のReplaceで”（株）”を”株式会社”に置換したら、2番目以降のReplaceはスキップしても良いのでは？
その通りです。
と言う事で、その辺りはIF文と言うのが必要になるので、今日はこれくらいにしておこう。
また(株)ではなくて(有)のデータもあるので、その辺りは自分で応用して試して下さい。
まとめ
今回のVBA講座で以下のテクニックを身に付ける事が出来た。
- VBAのForNextループの中でセルの値を順番に取り出す方法を覚えた。
- 取り出したセルデータをReplace関数で置換する方法を覚えた。
- 置換後のデータをエクセルシートの別のセルに書き出す方法を覚えた。
この処理の流れはエクセルでシートのデータをループで順番に処理する基本となるので、これをマスターしたあなたは、もう、かなりの上級者レベルと言っても良い。
今回は文字列の処理を行ったが、次回は、もう少しステップアップしてUserFormと言われるユーザーインターフェースの作り方を覚えよう。
このユーザーフォームを自由自在に作れるようになると、エクセル作業がとても捗る。
続く
続きの記事を作成しました。
【入門編】 初心者向け EXCEL VBA (5/10) – VBAユーザーフォームは男のロマン【これで完璧】
本を読む
たった一日・・・ほんまかいな⁉
と思ったら、
たった一秒やて⁉
どないなってんねん??
コメント
こんにちは！
Excelの処理を自動化したくて
検索したら辿り着きました。
仕事で使わせていただきます。
ありがとうございました
さすらいのOL様
この度は小生のEXCEL VBA記事が皆様のお役に立てたようで嬉しいです。
今後もボチボチとVBA関連記事を追加して行きたいと思っていますので、時々お立ち寄り下さい。