ユーザーフォームとPropertyプロシージャと私
ユーザーフォームにプロパティを追加する
フォームモジュールにPropertyプロシージャを書いてみる
なんかこう、クラスモジュールとフォームモジュールの違いが分からなくなってきている私です。
コチラに、id:imihito さんからいただいたコメントへの返信を書いているときにふっと思いついたのが、
フォームモジュールにPropertyプロシージャ書いたらどうなるんだろ？
フォームモジュールにもフィールド持たせたりできんの？
ということだった。
で、実験。
前回記事のリスト１に、次のように追加。
リスト１ フォームモジュール
Option Explicit
Private secretName_ As String '……(1)'
Public Property Let secretName(n As String) '……(2)'
secretName_ = n
End Property
Public Property Get secretName() As String '……(3)'
secretName = secretName_
End Property
Public Sub init(ByVal formCaption As String, _
ByVal btnLeftName As String, _
ByVal btnCenterName As String, _
ByVal btnRightName As String)
With Me
.Caption = formCaption
.BtnLeft.Caption = btnLeftName
.BtnCenter.Caption = btnCenterName
.BtnRight.Caption = btnRightName
End With
(1)の
Private secretName_ As String
はインスタンス変数。コイツに値（文字列）を持たせることで、インスタンスごとに固有の名前を持たせることができる。（もちろん、あまり実用上意味はないけど、あくまでも説明用のパラメータです。）
(2)からの３行
Public Property Let secretName(n As String) secretName_ = n End Property
はsetterメソッド。「[インスタンス].secretName = "○○"」の形で値を設定することができる。
(3)からの３行
Public Property Get secretName() As String secretName = secretName_ End Property
はgetterメソッド。「[インスタンス].secretName = "○○"」の形で値を返す。
生まれ変わったUserFormTemplateオブジェクトを使ってみる。
前回記事のリスト２に次のようにコードを追加する。
リスト２ 標準モジュール
Public Sub userFormTest()
Dim uFrm As UserFormTemplate
Set uFrm = New UserFormTemplate
With uFrm
.init "アホ", "ボケ", "クズ", "デコスケ"
.secretName = "ち～んｗ" '……(1)'
Debug.Print .secretName '……(2)'
.Show
End With
End Sub
追加したのは２箇所。
(1)の
.secretName = "ち～んｗ"
でUserFormTemplateのインスタンスのsecretNameプロパティに値「ち～んｗ」を設定。
(2)の
Debug.Print .secretName
でsecretNameプロパティの値をイミディエイト・ウインドウに出力する。
実行結果
ユーザーフォームが表示されて、
イミディエイトにはsecretNameプロパティの値が表示された。
フィールドやプロパティまで持たせられるなんて、ますますクラスモジュールとの区別が分からなくなってきたなあ。いや、分かるけどｗ
ユーザーフォームをクラスっぽく使おう！
ユーザーフォームをクラスっぽく使う
ユーザーフォームのテンプレート（？）
前回の
で、ユーザーフォームもNewできると分かった。
で、それが何の役に立つのか、イマイチよく分からないんだが、
よく使うパターンのユーザーフォームのひな形を作っておいて、部分的に柔軟性を持たせたらいいんでね？
と思ったので、実験してみた。
ユーザーフォームのひな形を作る
手の込んだものを作るのはメンドクサイので、次のようなものにした。
オブジェクト名は「UserFormTemplate」とした。
見ての通り、コマンドボタンを３つ設置。
オブジェクト名は、左から順に
「BtnLeft」。
「BtnCenter」。
「BtnRight」。
今まで、ユーザーフォーム上のコントロールのオブジェクト名は「lblPersonName」みたいにキャメル記法を使っていたんだけど、"オブジェクト名"ってことはクラス名みたいなもんだから、パスカル記法の方が良いと思い直した。
んで、UserFormTemplateのモジュールに、次のコードを書いた。
リスト１ フォームモジュール
Public Sub init(ByVal formCaption As String, _
ByVal btnLeftName As String, _
ByVal btnCenterName As String, _
ByVal btnRightName As String)
With Me
.Caption = formCaption '……(*)'
.BtnLeft.Caption = btnLeftName
.BtnCenter.Caption = btnCenterName
.BtnRight.Caption = btnRightName
End With
End Sub
おなじみ、擬似コンストラクタのinitメソッド。
ユーザーフォームの_Initializeメソッドも引数を持たせられないから、仕方なくこうした。
(*)からの４行
.Caption = formCaption .BtnLeft.Caption = btnLeftName .BtnCenter.Caption = btnCenterName .BtnRight.Caption = btnRightName
は、いづれもMe、すなわちUserFormTemplateのインスタンスに対する処理。
それぞれ、ユーザーフォーム、左ボタン、中央ボタン、右ボタンのキャプションに引数で渡された文字列をセットする。
Newしてインスタンス化した後は、必ずこのinitメソッドを呼ぶ、という約束にする。
んで、このユーザーフォームを使うコードを標準モジュールに書く。
リスト２ 標準モジュール
Public Sub userFormTest()
Dim uFrm As UserFormTemplate '……(1)'
Set uFrm = New UserFormTemplate '……(2)'
With uFrm
.init "アホ", "ボケ", "クズ", "デコスケ" '……(3)'
.Show '……(4)'
End With
End Sub
(1)の
Dim uFrm As UserFormTemplate
で、UserFormTemplate型の変数uFrmを宣言。
(2)の
Set uFrm = New UserFormTemplate
でUserFormTemplateをインスタンス化。クラスモジュールの使い方と同じ。
(3)の
.init "アホ", "ボケ", "クズ", "デコスケ"
は、uFrmの擬似コンストラクタinitメソッドを、４つの引数を渡して実行。４つの引数は、前から順に、それぞれフォーム、左ボタン、中央ボタン、右ボタンのキャプションとなる。
initメソッドで初期化をしたら、あとは(4)の
.Show
でフォームを表示しておしまい。
実行。
ちゃんとそれぞれキャプションが設定された状態で表示された。
最後に
ユーザーフォームを用いたアプリで、似たようなフォームを多用するようなやつを作成するときになら、少しは役に立つかも知れんなあ。
前に予定管理アプリみたいなのを自作した（させられた）ときにこの知識があったら、もう少しラクできていたかも。
ユーザーフォームはNewできるか
ユーザーフォームをNewしてみる
ユーザーフォームって、クラスモジュールに似てね？
クラスモジュールを使っていなかった頃はなんとも思ってなかったが、クラスモジュールをよく使うようになって、改めてユーザーフォームを使ってみると、結構共通点があったんだなあ、と。
「オブジェクト名」というパラメータがあったり、「～～_Initialize」というのがあったり。
……ということは、
Newして変数やら配列やらにぶち込むこともできるんじゃね？
と思ったわけです。
で、やってみた。
まずは、
このようなユーザーフォームを挿入。
ほぼデフォルトの状態。タテ幅を小さくしただけ。
コントロールはラベルだけにしとく。
こちらもほぼデフォルトの状態。フォントサイズを大きくしてテキストを中央寄せにしただけ。
フォームモジュールには、とりあえずコンストラクタだけを設定しておく。
リスト１ フォームモジュール
Option Explicit Private Sub UserForm_Initialize() Me.Show vbModeless '……(*)' End Sub
計算通りなら、インスタンス化されるとすぐにユーザーフォームが表示されるはず。
デフォルトだと、ユーザーフォームを閉じないと次の処理に進まないので、Modelessで表示することにしとく。
標準モジュールには次のコードを書く。
リスト２ 標準モジュール
Public Sub test()
Dim objForm(0 To 4) As UserForm '……(1)'
Dim i As Integer
For i = 0 To 4
Set objForm(i) = New UserForm1 '……(2)'
objForm(i).Label1.Caption = "アホ" & _
StrConv(i + 1, vbWide) & "号" '……(3)'
Next
End Sub
(1)の
Dim objForm(0 To 4) As UserForm
では、UserForm型の要素数５の配列を準備。
コード入力中は、
ちゃんとインテリセンスがきく。安心。
んで、(2)の
Set objForm(i) = New UserForm1
で、UserForm1をインスタンス化し、配列にぶち込む。
これが実行された段階で、リスト１の(*)が実行されるので、ユーザーフォームがModeless表示されるはず。
んで、その後(3)の
objForm(i).Label1.Caption = "アホ" & _ StrConv(i + 1, vbWide) & "号"
で、ラベルのCaptionプロパティに文字列「アホ○号」を設定しておしまい。
コード入力中は、
インテリセンスが働かない。不安。
【追記】
変数「objForm()」を「UserForm1型」で宣言していれば、ちゃんとインテリセンスが働きます。単なる私のコーディングミスです。すんまへん。
====追記ここまで====
いざ、実行
こんな感じ。ちょっと周辺の影が濃い……？
めくっていくと、
ちゃんと５つのフォームが表示されている。
おわりに
ユーザーフォームもNewできるし、変数や配列にぶち込むこともできます。
しかしながら、これが何の役に立つのか、サッパリ分かりまへん。
素人がアドインの世界に首を突っ込んでみる
素人、アドインに手を出す
アドインとは……？
「アドイン」って、よく聞く言葉だったけど、意味が分からんかったのでずっと放置していた。
何かの拍子に「マクロをアドインとしてうんたらかんたら」というのを読んで、ちょっと調べてみた。
そしたらアンタ、こういうのが見つかったのですよ。
作成したマクロを他のどのブックでも使用できるようにする方法です。アドインとして保存すると便利です。
何ーーーーッ！ マジか!!!!!!!!
し、知らんかった……。
ということは、ですよ、
しちめんどくさい操作をアドインとして登録しておいて、クイックアクセスツールバーなんかにボタンを設置しておいたらメッチャ楽
つうことですよね？
何ということだ……。そんなこと知らんかったから、今までめんどくさい文字列処理なんかは、それ専用のマクロ入りブックを立ち上げていたんだけど……。
なんちゅうムダなことをやってたんだｗ
マクロのアドイン化に挑戦
というわけで、やってみた。
サンプルコードは次のリスト１を使う。
リスト１ 標準モジュール
Option Explicit
Public Sub convertToNarrowWhenAlphaNumeric()
Dim objCell As Range
For Each objCell In Selection
objCell.Value = checkAndConvert(objCell.Value)
Next
End Sub
Private Function checkAndConvert( _
ByVal tgtString As String) As String
Dim i As Integer
Dim str As String
Dim chr As String
Dim tmp As String
str = StrConv(tgtString, vbWide)
tmp = ""
For i = 1 To Len(str)
chr = Mid(str, i, 1)
If chr Like "[０-９]" Or _
chr Like "[Ａ-ｚ]" Or _
chr = "－" Or _
chr = "＆" Then chr = StrConv(chr, vbNarrow)
tmp = tmp & chr
Next
checkAndConvert = tmp
End Function
今回はコードの説明は省略。リクエストがあったらやりますのでヨロシク。
選択範囲のセルの文字列について、英数字及び「－」（ハイフン）、「＆」（アンパサンド）については半角に統一する、というマクロですな。
コイツをアドインとして登録し、いつでも使えるようにしようという算段。
手順
まずは、上記のコードを標準モジュールに書く。
んで、「名前を付けて保存」ダイアログ・ボックスを呼び出し、
「ファイルの種類(T)」を「Excelアドイン(*.xlam)」にして保存
する。
保存場所は、自動的に
C:\Users＞ユーザー名＞AppData＞Roaming＞Microsoft＞AddInsフォルダ
が選ばれる。
ファイル名は日本語にしておくのが分かりやすいかな。画像では「英数記号半角統一」にしてあります。
これでExcel本体への登録はおｋ。不安になるぐらい簡単です。
お次は、登録したアドインを使えるようにする。
「ファイル」タブから、
「オプション」→
「アドイン」へと進む。
下の方に「管理(A)」というところがあるので、ドロップダウンリストから「Excelアドイン」を選んで「設定」をクリック。
アドインというウインドウが出てくるので、先ほど保存した「英数記号半角統一」にチェックを入れて
[ＯＫ]をクリック。これで使用準備は完了。
あとは、呼び出し方を設定するだけ。
今回は、クイックアクセスツールバーから呼び出すことにする。
「ファイル」タブをクリック→「オプション」へと進む。
「クイックアクセスツールバー」をクリック。
右側の画面にこんなのが出てくるので、「コマンドの選択」ドロップダウンリストから「マクロ」を選ぶ。
下段のボックスの中に、マクロ名が表示されるので、（今回の例の場合なら）「英数記号半角.xlam」に書かれている方のプロシージャ名を選択し（マウスポインタを当てると、ファイルフルパスがヒントで表示されるので見分けられる）、
[追加]ボタンをクリック。
右側のボックスに追加されている。
「アイコン、だっせぇよな！」と思ったら、
下の方にある[変更]ボタンをクリックすると、
「ボタンの変更」ウインドウが表示されるので、カッコいいアイコンに変更すればおｋ。
アイコンがカッコよくなった。
元の画面に帰ると、
クイックアクセスツールバーにカッコいいアイコンがｗ
これで準備おｋです。
いざ実行。
対象のセル範囲（よく見ないと分からないかも知れませんが、英数記号部分は全角・半角がチャンポンになっていて、かなりイラッと来るものとなっています）を選択して、
カッコいいアイコンｗをクリック！
英数記号が半角で統一されました。
おわりに
今回例として使用した「英数記号全角半角統一」マクロは、コチラの『実例で学ぶExcelVBA（講談社ブルーバックス B1802 立山秀利著）』がもとになっています。私がVBAに目覚めるきっかけとなった、大変ありがたい本です。密林での評価は前作の『入門者のExcelVBA（講談社ブルーバックス B1769 著者同じ）』の方が高いようですけど、どう考えても『実例で～』の方が上だと思っています。
手数の多い文字列処理なんかは、マクロで作っておいてアドインにすると便利かも。特に、いろんなところから集まってくるExcelブックを集約するような仕事のときには、こういうデータクレンジング系のマクロをアドインとして放り込んでおくと楽になるでしょうね。
WorkbooksコレクションのAddメソッドに自分自身のフルパスを渡すとExcelが落ちる
Excelが落ちる
WorkbooksコレクションのAddメソッド
に、ExcelVBAer(id:x1xy2xyz3) さんから、次のようなコメントをいただいた。
参考までに、WorkBooks.Add([originalFileFullName]) という方法も知っておくといいかもね～
Addメソッドの引数に、元のファイルのフルパスを指定して実行するという意味だと思うのだけれど、どんな結果になるのか想像もつかなかった。
で、やってみた
とりあえず、次のようなコードを書いてみた。
リスト１
Public Sub testAddMyself() Dim originalWorkbook As Workbook Set originalWorkbook = ThisWorkbook Dim newWorkbook As Workbook Set newWorkbook = Workbooks.Add(originalWorkbook.FullName) End Sub
んで、実行してみた。
……。
元のブックは「親ブック.xlsm」というファイル名なのだけれど、新しくできたブックは「親ブック1」という名前になっているみたい。
全く同じファイルパスなんだから、新しい方に「1」を付けたんだろうけど、次の瞬間にExcelが落ちる。
これは何回やっても同じだった。
まあ、そもそも ExcelVBAer(id:x1xy2xyz3) さんのコメントを理解できていないということなのでしょう。
おわりに
こんなしょうもない記事が通算100記事目だなんて……。
列番号を列符号に変換する関数
列符号を割り出す関数を作る
Split関数の挙動
セルのAddressプロパティを取得すると、例えばＡ１セルなら、「$A$1」という文字列が返る。
ということは、「$」をデリミタとしてSplit関数を使えば、列符号を表す「A」がSplit関数の返り値である配列のどこかに格納されるはずだ。
で、やってみた。
リスト１
Public Sub testSplitFunction()
Dim arraySample As Variant
arraySample = Split("$A$1", "$") '……(1)'
Dim i As Integer
For i = 0 To UBound(arraySample)
Debug.Print i & " : "; arraySample(i)
Next
End Sub
(1)の
arraySample = Split("$A$1", "$")
では、Split関数を使って文字列「$A$1」を区切り文字（デリミタ）「$」で区切った結果を配列にして変数arraySampleにぶち込んでいる。
元の文字列がデリミタである「$」で始まっているこんなとき、返り値である配列はどうなるか。
こうなる。
配列の１つ目の要素（インデックス番号「0」）は「""」になるようだ。
したがって、Split関数の引数にセルのAddressプロパティを渡して、出来た配列の２番目の要素（インデックス番号「1」）が列符号を表す文字列になると分かる。
列番号を渡したら列符号を返す関数
作ってみた。
リスト２
Public Function getColumnLetter(ByVal columnNumber As Long) As String On Error GoTo errorHandler Dim Sh As Worksheet '……(1)' Set Sh = ActiveSheet Dim tmpStr As String tmpStr = Sh.Cells(1, columnNumber).Address '……(2)' Dim tmpArray As Variant tmpArray = Split(tmpStr, "$") '……(3)' getColumnLetter = tmpArray(1) '……(4)' Exit Function errorHandler: getColumnLetter = "" End Function
(1)からの２行、
Dim Sh As Worksheet Set Sh = ActiveSheet
は別になくてもいいんですが、裸で「Range(……)」とか、「Cells(……)」とか書くのがイヤだからこうしているだけです。
まあ、「そんなこと言うならApplicationオブジェクトから書けや！」とか言われるかも知れませんが。
(2)の
tmpStr = Sh.Cells(1, columnNumber).Address
では、引数で渡された列番号に相当する列の１行目のAddressプロパティの文字列を変数tmpStrにぶち込んでいる。
まあ、直接Split関数に渡してもいいんだが、可読性のためにこうしている。
(3)の
tmpArray = Split(tmpStr, "$")
で変数tmpArrayの２番目の要素、すなわち「tmpArray(1)」には列符号を表す文字列が入っているはずなので、
(4)の
getColumnLetter = tmpArray(1)
でtmpArray(1)を返り値にしてやる。
動作テスト
次のコードで実験。
Public Sub test() Debug.Print getColumnLetter(10000) End Sub
10000列目の符号をイミディエイト・ウインドウに表示するというだけ。
ほれ、この通り、「NTP」と表示されている（核不拡散条約かよｗｗｗ←「NPT」な。）。
下の２行を見てもらったら、NTP列が10000列目であることも確認していただけよう。
おわりに
まあ、何に使うのかはよく分かりません。
親ブックから子ブックを量産する
データを変えて親ブックから子ブックを量産するマクロ
子ブック生成部分を切り出す
の続き。
FileSystemObjectオブジェクトのCopyFileメソッドを使うと、子ブックの生成が簡単にできることが分かったので、いよいよ量産体制に入る。
そのために、子ブック生成部分だけを切り出しておこう。
リスト１
Private Function saveNewWorkbook(ByVal originalFileFullName As String, _
ByVal newFileFullName As String) As Workbook
Dim fsObject As FileSystemObject '……(1)'
Set fsObject = New FileSystemObject
fsObject.CopyFile Source:=originalFileFullName, _
Destination:=newFileFullName
Set saveNewWorkbook = Workbooks.Open(newFileFullName) '……(2)'
Set fsObject = Nothing
End Function
見ての通り、引数を２つ受け取って、新たに生成して保存した子ブックを開いて返すメソッドにした。呼び出され専用なのでPrivateにしている。
まず、(1)からの３行
Dim fsObject As FileSystemObject
Set fsObject = New FileSystemObject
fsObject.CopyFile Source:=originalFileFullName, _
Destination:=newFileFullName
は、FileSystemObjectオブジェクトのインスタンスを生成して、CopyFileメソッドを用いる。
これで新しい子ブックが保存される。
次に(2)の
Set saveNewWorkbook = Workbooks.Open(newFileFullName)
では、早速保存した新しい子ブックを開いて返り値にしている。
新しく生成された子ブックに加工したいということが多いと思うので、保存しっぱなしではなく、一旦開いて返り値とするというやり方にした。
第２引数のnewFileFullNameをOpenメソッドの引数にそのまま使えるので楽。
子ブックを量産する
あとは、
- 親ブックの「個別」シートに「元データ」シートからデータ（笑）を転記する。
- 一旦親ブックを保存する。
- 別フォルダにコピーを作成し、子ブックとする。
- 子ブックを加工する。
- 子ブックを保存して閉じる。
という処理をForループで回したらよい。
コーディング
リスト２
Public Sub main() Dim originalWorkbook As Workbook Set originalWorkbook = ThisWorkbook Dim folderPath As String folderPath = originalWorkbook.Path & "\収容所\" '" If Dir(folderPath, vbDirectory) = "" Then MkDir (folderPath) Dim orgDataSh As Worksheet Set orgDataSh = originalWorkbook.Worksheets("元データ") Dim tgtSh As Worksheet Set tgtSh = originalWorkbook.Worksheets("個別") Dim maxRow As Long maxRow = orgDataSh.Cells(Rows.Count, 2).End(xlUp).Row Dim newWorkbook As Workbook Dim i As Long For i = 2 To maxRow tgtSh.Range("A1").Value = orgDataSh.Range("B" & i).Value '……(1)' Application.DisplayAlerts = False '……(2)' originalWorkbook.Save '……(3)' Set newWorkbook = saveNewWorkbook(originalWorkbook.FullName, _ folderPath & "子ブック" & Format(i - 1, "0#") & ".xlsm") '……(4)' newWorkbook.Worksheets("元データ").Delete '……(5)' newWorkbook.Close True '……(6)' Application.DisplayAlerts = True '……(7)' Next Set originalWorkbook = Nothing Set orgDataSh = Nothing Set tgtSh = Nothing Set newWorkbook = Nothing End Sub
Forループに入るまでの処理については、説明を省略。オーソドックスな処理ばかりだと思う。
で、Forループの中身だが、
まず(1)の
tgtSh.Range("A1").Value = orgDataSh.Range("B" & i).Value
で、親ブックの「元データ」シートから親ブックの「個別」シートにデータ（笑）を転記。
(2)の
Application.DisplayAlerts = False
でアラート表示を止める。これをやっておかないと、次の処理のときにアラート表示が出てしまう。
(3)の
originalWorkbook.Save
で親ブックを保存。こうしておかないと、データ（笑）の転記が子ブックに反映されない。
ここまで下ごしらえをしておいて、いよいよ(4)の
Set newWorkbook = saveNewWorkbook(originalWorkbook.FullName, _
folderPath & "子ブック" & Format(i - 1, "0#") & ".xlsm")
で子ブックを保存した上で開き、変数newWorkbookに格納。
(5)の
newWorkbook.Worksheets("元データ").Delete
で子ブックの「元データ」シートを削除し、
(6)の
newWorkbook.Close True
で、子ブックを保存して閉じる。
最後に(7)の
Application.DisplayAlerts = True
でアラート表示を元に戻したら、オブジェクト変数を解放して終了。
実行
mainプロシージャを実行すると、
「収容所」フォルダにちゃんと５つのファイルができている。
それぞれ「個別」シートのＡ１セルにデータ（笑）も転記されている。
おわりに
さっそく、
このとき作ったクラスを修正しようかなあ。
ただ、子ブックもマクロ付きのままってのはちょっと具合が悪いんだよなあ。
