オートフィルターとClearContentsメソッド
フィルターをかけているときのRange.ClearContentsメソッドの挙動
ちょっとビックリしたので、記しておく。
フィルターで非表示の列が含まれているRangeオブジェクトにClearContentsメソッドを実行する
こんな表があったとする。E列には全て値が入っている。
んで、オートフィルターで「武松」と「楊志」の行を非表示にする。
こうなる。
んで、この状態（E列の上から下までが選択されている）で
Public Sub testClearValue() Dim targetRange As Range Set targetRange = Selection targetRange.ClearContents End Sub
このコードを実行する。見ての通り選択範囲（Selection）を変数targetRangeにぶち込んで、targetRangeに対してClearContentsメソッドを実行するだけの簡単なプロシージャだ。
すると、
こうなる。一見、意図通りの結果に見えるが、フィルターを解除すると、
ファッ!!!!????
フィルターをかけた状態でイミディエイトで確認すると、
この通り。
よって、「Selection」すなわち「targetRange」は、ちゃんとセル14個分のはず。
なんでやねん!!!!????
範囲内のそれぞれのセルにClearContentsメソッドを実行する
わけが分からないけれど、現実なのだから仕方がない。
同じ状態から、今度は
Public Sub testClearValue()
Dim targetCell As Range
For Each targetCell In Selection
targetCell.ClearContents
Next
End Sub
このコードでやってみる。
今度は意図通りの結果となった。
おわりに
こんなことになるとは全く知らなかった。
フィルターと併用するときには気をつけないといけないなあ。
レジストリを使う(2)
レジストリを削除する
今回は、前回の
のマクロに、レジストリ削除機能を付加する。
レジストリの削除
『VBAエキスパート 公式テキスト ExcelVBAスタンダード』の206ページによると、
レジストリのデータを削除するには、DeleteSettingステートメントを使います。DeleteSettingステートメントの書式は次の通りです。
DeleteSetting アプリケーション名, セクション名, キー名, データ引数「セクション名」と「キー名」は省略可能です。
引数「キー名」を指定した場合は、指定したキーを削除します。引数「キー名」を省略した場合は、引数「セクション名」で指定したセクション全体を削除します。
引数「セクション名」と「キー名」の両方を省略すると、引数「アプリケーション名」で指定したフォルダを削除します。
ということなので、新たにこのマクロで作成するレジストリを根こそぎ削除するようにする。
全体的にコードを見渡してみると、オブジェクト名とか、命名に少し乱れがあったので、変えたところがある。
過去記事にさかのぼって改めるのはメンドウなので、ここで一括して修正しておく。
そのため、今回はフォームの姿形から、全て載っけておく。
オブジェクト・コード全体
フォーム
まずは、フォーム。
オブジェクト名は「TestForm」にしている。
コントロールは、
- ラベル（Label1）
- チェックボックス（CheckBoxIsUnRedisplayable）
- コマンドボタン（ButtonOK）
の３つ。カッコ内はオブジェクト名。チェックボックスの名前が、役割と合っていなかったので、ちょっと変えた。英語が正しいかどうかは知らんけどｗ この辺、少々ややこしくても、意味的（論理的？）に正しい名前にしておかないと、後々混乱の種になると思う。
リスト１ 標準モジュール
Propertyプロシージャ１セット。
Option Explicit
Public Property Let isDisplayable(ByVal isDisplayable_ As Boolean)
If isDisplayable_ Then
Call SaveSetting("FormTest", "TestForm", "isDisplayable", "True")
Else
Call SaveSetting("FormTest", "TestForm", "isDisplayable", "False")
End If
End Property
Public Property Get isDisplayable() As Boolean
isDisplayable = CBool(GetSetting("FormTest", "TestForm", "isDisplayable", "True"))
End Property
レジストリ内のフォルダ名を変えた。
- アプリケーション名：マクロ全体
- セクション名：設定対象オブジェクト
- キー名：設定値の種類
だいたいこんな感じが汎用性あるかな？
リスト２ 標準モジュール
フォーム呼び出し用プロシージャ。
Public Sub testTestForm() If isDisplayable Then TestForm.Show End Sub
初めてフォームを呼び出すときや、レジストリを削除した直後は、「isDisplayable」というレジストリ・キーが存在しないわけだが、GetSetting関数の既定値（第４引数）を"True"にしているので、レジストリ・キーが未作成の場合には「isDisplayable」プロパティがTrueになる、という仕掛け。
GetSetting関数の第４引数の意味がここで分かった。
リスト３ フォームモジュール
フォームのボタンクリック時のイベントプロシージャ。
Private Sub ButtonOK_Click() If CheckBoxIsUnRedisplayable Then isDisplayable = False Unload Me End Sub
チェックボックスのオブジェクト名を変えたので、それに伴って少しだけ記述が変わっている。
直訳すれば、「再表示不能チェックボックス」の値がTrueならば「表示可能」プロパティをFalseにせよという非常にややこしいコードだが、少々ややこしくてもかまわないと思う。
リスト４ 標準モジュール
チェックボックスリセット用プロシージャ。
Public Sub resetTestForm() isDisplayable = True End Sub
まあ、とにかくisDisplayableがTrueになればいいんだから、条件分岐をやめた。
リスト５ 標準モジュール
最後は、今回初お目見えのレジストリ削除用プロシージャ
Public Sub deleteRegistryTestForm()
Call DeleteSetting("FormTest")
End Sub
このマクロに関するレジストリを根こそぎ削除するために、第１引数のみを指定。
結果的に、ほとんどのプロシージャが１行。超絶シンプルになった。
実行
「次回から表示しない」をチェックして[OK]をクリックすると、
レジストリはこんな状態。値が"False"になっている。
[リセット]ボタンをクリックすると、
レジストリはこの通り。"True"になっている。
で、
[レジストリ削除]ボタンをクリックすると、
きれいサッパリ！
おわりに
登録、取得、削除、この３つをわきまえていれば、割とカンタンに利用できるなあ。
ただ、今回やったような用途だったら、やはりブックごとに管理すべきステータスであって、レジストリに登録するのはあまりオススメできませんなあ。どうなんでしょ。
ちなみに、一度レジストリを削除して、再度登録したとき、レジストリエディタに現れてくれないことがあるみたいですが、レジストリエディタの「編集」→「検索」でアプリケーション名なり、セクション名なり、キー名で検索すれば、無事出現します。私は今回これでちょっとハマりました。
レジストリを使う
レジストリに値を保存する
今回は、前回の
のisDisplayableプロパティを、レジストリで管理するように変更する。
レジストリへの値の書き込み
『VBAエキスパート 公式テキスト ExcelVBAスタンダード』の203ページによると、
レジストリにデータを登録するには、SaveSettingステートメントを使います。SaveSettingステートメントの書式は次の通りです。
SaveSetting アプリケーション名, セクション名, キー名, データ引数「アプリケーション名」には、一般的にプログラム名や企業名などを指定します。
引数「セクション名」はデータを保存するフォルダ名に該当します。
引数「キー名」がファイル名に相当し、その中に記録される内容が引数「データ」となります。
ということなので、isDisplayableの値を変更する処理、つまりProperty Letの中身をSaveSettingに置き換えたら良いことになる。
ただ、画像でも分かるように、最後の引数「データ」はString型みたい。
そこを踏まえてProperty Letプロシージャを書き換える。
リスト１ 標準モジュール
Public Property Let isDisplayable(ByVal isDisplayable_ As Boolean)
If isDisplayable_ Then
Call SaveSetting("FormSettings", "Displaying", "isDisplayable", "True")
Else
Call SaveSetting("FormSettings", "Displaying", "isDisplayable", "False")
End If
End Property
Propertyプロシージャに渡された引数、すなわち、isDisplayableプロパティを使うコード内での
isDisplayable =
の「=」の右側にTrueが来るかFalseが来るかによってSaveSettingステートメントの第４引数を切り替える。
第４引数はString型のようなので、ブール値ながら文字列で渡す。
レジストリからの値の取得
再び『VBAエキスパート 公式テキスト ExcelVBAスタンダード』の204ページによると、
レジストリに登録されているデータを取得するには、GetSetting関数を使います。GetSetting関数の書式は次の通りです。
GetSetting(アプリケーション名, セクション名, キー名, 既定値)引数「アプリケーション名」、引数「セクション名」引数「キー名」は、SaveSettingステートメントで指定するデータと同じです。実行すると、引数「キー名」で指定したキーに登録されているデータを返します。引数「既定値」は省略可能です。引数「キー名」で指定したキーが存在しないときに返す値を指定します。
ということなので、Property GetをGetSettingで書き換える。
リスト２ 標準モジュール
Public Property Get isDisplayable() As Boolean
isDisplayable = CBool(GetSetting("FormSettings", "Displaying", "isDisplayable", "True"))
End Property
レジストリから取得する値はString型のようなので、一応Boolean型にキャストすることにしている。
書き換えるのはこれだけ。
isDisplayableプロパティを読み書きする立場からは見えない世界でのできごとなので、メインのコード３種（フォーム呼び出し用プロシージャ・フォームのボタンクリック時のイベントプロシージャ・リセット用プロシージャ）を書き換える必要はない。
「カプセル化」の威力ってやつですかね？
実行
何ら問題なく実行できた。
ついでに、みたび『VBAエキスパート 公式テキスト ExcelVBAスタンダード』の202ページによると、
Excel VBAの命令で操作できるのは、レジストリ内で
HKEY_CURRENT_USER\Software\VB and VBA Program Settings
の配下に限定されます。
とのこと。
最初、レジストリエディタを開いたときに「Software」フォルダの中に「VB and VBA Program Settings」フォルダがなくて、かなり焦りましたが、SaveSettingステートメント実行後、再び見に行ったら、
このようにちゃんとありました。
おわりに
「レジストリ」というと、初心者にはどうしても敷居の高いものに感じられるが、こういう使い方なら割と気軽に使えるような気がした。
追記
登録したレジストリの削除についてはコチラ。
次回からは表示しない（ユーザーフォーム）[Excel](5)
次回からは表示しない
まさかの第５弾!!!!!!!!
四天王からのアドヴァイス
（私が勝手に認定している）VBA四天王の一人、 id:imihito さんから、前回の
にコメントをいただいた。
曰く、
もとい、
`myDocProp` という名前の`Function`や`Property Get` を定義して、
その中に `Set myDocProp = ThisWorkbook.CustomDocumentProperties.Item("isDisplayable")` を入れるのもありかな、と思います。
SUGEEEEEEEE!!!!!!!!
なるほど！ その手があったかーーーーー！
さっそくやってみた
リスト１ 標準モジュール
Public Property Let isDisplayable(ByVal isDisplayable_ As Boolean)
ThisWorkbook.CustomDocumentProperties.Item("isDisplayable").Value = isDisplayable_
End Property
Public Property Get isDisplayable() As Boolean
isDisplayable = ThisWorkbook.CustomDocumentProperties.Item("isDisplayable").Value
End Property
C#に比べるとずいぶん野暮ったい記法だな、おい。……てなことはさておき。
Public変数を使う代わりに、プロパティにした、ということ。
Propertyプロシージャはクラスモジュールでよく使うイメージだけれど、フォームモジュールでも使えるし、標準モジュールでも使えるんだった。
ThisWorkbook.CustomDocumentProperties.Item("isDisplayable").Value
といううっとうしい記述をPropertyプロシージャの中に封じ込めたので、メインのコード３種がそれぞれ異様にスッキリする。それを今からお目に掛けよう。
まずはフォーム呼び出し用プロシージャ。
リスト２ 標準モジュール
Public Sub testTestForm() If isDisplayable Then TestForm.Show End Sub
ついにここまで来たよ！
次。ボタンクリック時のイベントプロシージャ。
リスト３ フォームモジュール
Private Sub ButtonOK_Click() If CheckBoxIsRedisplayable Then isDisplayable = False Unload Me End Sub
これまたかくのごとし！
次！ リセット用プロシージャ。
リスト４ 標準モジュール
Public Sub resetTestForm() If Not isDisplayable Then isDisplayable = True End Sub
たったこんだけですよ！
もちろん、今まで通りに動く。
おわりに
ちょっとなんかこう、すげえ興奮している！
すげえ、すげえよ！
id:imihito さん……ていうか、id:imihito アニキ！
ホントーに、あざす!!!!!!!!
次回からは表示しない（ユーザーフォーム）[Excel](4)
次回からは表示しない
さらにシンプルなコードにする
「もうええっちゅうねん!!!!!!!!」という声が聞こえてきそうだが、もう一本だけ。
前回
かなりシンプルになったコードだが、
ThisWorkbook.CustomDocumentProperties.Item("isDisplayable")
というクソ長ったらしい記述が頻発するのがうっとうしい。
で、書き換えてみた。
DocumentProperty型というものがある
オブジェクトブラウザーでテキトーに調べていたら、
「DocumentProperties」というのがあった。「Item」なんていうメンバがあるし、このコレクションの要素っぽい
「DocumentProperty」クラスというのがあって、メンバを見ても
このときのリスト１とほぼ一致しているし、間違いないだろう、と。
要するに、
ThisWorkbook.CustomDocumentProperties.Item("isDisplayable")
を変数にぶち込んでしまえばいいということだ。
ただ、標準モジュールとフォームモジュールをまたがることになるので、とりあえずPublic変数を使うことにした。
書き換えたコード
まずは宣言セクション。
リスト１
Option Explicit Public myDocProp As DocumentProperty
DocumentProperty型の変数myDocPropを宣言する。
お次は、変数ぶち込み用プロシージャ。
リスト２ 標準モジュール
Private Sub setDocumentProperty()
Set myDocProp = ThisWorkbook.CustomDocumentProperties.Item("isDisplayable")
End Sub
フォーム呼び出し用プロシージャとリセット用プロシージャから呼び出すためのプロシージャ。Public変数にユーザー設定のドキュメントプロパティisDisplayableをぶち込む。
Set myDocProp = ThisWorkbook.CustomDocumentProperties.Item("isDisplayable")
を２回も書くのはブサイクなので、１箇所にまとめただけ。
お次はフォーム呼び出し用プロシージャ。
リスト３ 標準モジュール
Public Sub testTestForm() Call setDocumentProperty If myDocProp.Value Then TestForm.Show End Sub
さらにカンタンになった。
お次は、フォームのボタンクリック時のイベントプロシージャ。
リスト４ フォームモジュール
Private Sub ButtonOK_Click()
If CheckBoxIsRedisplayable Then
myDocProp.Value = False
End If
Unload Me
End Sub
これまたおっそろしくカンタン。
あとは、リセット用プロシージャ。
リスト５ 標準モジュール
Public Sub resetTestForm()
Call setDocumentProperty
With myDocProp
If Not .Value Then .Value = True
End With
End Sub
これまたｒｙ
これもちゃんと動く。
おわりに
最初に比べてめちゃくちゃシンプルになって、ちょっとビックリしている。
まあ、Public変数を使っているあたりがイマイチなんだろうけれど。
追記
実は、第５弾がありますｗ
次回からは表示しない（ユーザーフォーム）[Excel](3)
結局……
WorkbookオブジェクトのCustomDocumentPropertiesコレクションを使うんだから、もはやPublic変数は必要なかった、という話。
今回使用するユーザーフォームについては、コチラの記事をどうぞ。
コードの修正
前回
のコードを修正する。
メンドクサイので、いきなり修正後のコードを列挙する。
まずは、フォーム呼び出し用プロシージャ。
リスト１ 標準モジュール
Public Sub testTestForm()
If ThisWorkbook.CustomDocumentProperties.Item("isDisplayable").Value Then _
TestForm.Show
End Sub
なんと、たったのこれだけ。実質１行ｗ
ユーザー設定のドキュメントプロパティisDisplayableがTrueのときだけフォームを呼び出す。
C#みたいにusingが使えたら、もっとカンタンに書けるのに。
んで、お次はフォームのボタンクリック時のイベントプロシージャ。
リスト２ フォームモジュール
Private Sub ButtonOK_Click()
If CheckBoxIsRedisplayable Then
ThisWorkbook.CustomDocumentProperties.Item("isDisplayable").Value = False
End If
Unload Me
End Sub
これまたたったのこれだけ。ボタンクリック時にチェックボックスの値を調べ、チェックボックスの値がTrueだったら、ユーザー設定のドキュメントプロパティisDisplayableをFalseにする。
あとは、リセット用のプロシージャ。
リスト３ 標準モジュール
Public Sub resetTestForm()
With ThisWorkbook.CustomDocumentProperties.Item("isDisplayable")
If Not .Value Then .Value = True
End With
End Sub
これまたたったのこれだけ。
ユーザー設定のドキュメントプロパティisDisplayableがFalseの場合にTrueに戻す。
これで、呼び出せなくなっていたフォームが再び呼び出し可能になる。
おわりに
Excelの場合、保持しておきたいデータはワークシートに書き込んでおけば良いのだから、基本的にこんなアクロバチックなことはせんでも良いと思うが、こんな方法もあるのだなあ、ということで。
次はレジストリもやってみようかなあ。割と簡単そうだし。
次回からは表示しない（ユーザーフォーム）[Excel](2)
変数の値を保存する
WorkbookオブジェクトのCustomDocumentPropertiesコレクションを使う
前回
の続き。
変数の値を保存するには、
- シートに書き込む
- テキストファイルに書き込む
- レジストリに書き込む
ぐらいしか方法がないと思っていたが、チョー有名なOffice TANAKAさんのサイトで
CustomDocumentPropertiesコレクション
というものの存在を知った。
これならできそうだ！
邪道かもしれんけどｗ
コーディング
まずは、イミディエイトで以下のコードを実行する。
リスト１ イミディエイトウインドウに入力
Thisworkbook.CustomDocumentProperties.Add Name:="isDisplayable", _
LinkToContent:=False, _
Type:=msoPropertyTypeBoolean, _
Value:=True
コイツを入力して、[Enter]を押すと、このWorkbookに、「isDisplayable」という
ユーザー設定のドキュメントプロパティ
とやらが設定される模様。
Addメソッドの引数ValueにTrueを指定しているので、既に値はTrueになっている。
ほらね。
これで準備はおｋ
お次はフォーム呼び出し用のプロシージャ。
リスト２ 標準モジュール
Option Explicit
Public isDisplayable As Boolean
Public calledCount As Integer
Public Sub testTestForm()
calledCount = calledCount + 1
If calledCount = 1 Then isDisplayable = True
With ThisWorkbook.CustomDocumentProperties.Item("isDisplayable") '……(1)'
If Not .Value Then isDisplayable = False
End With
If isDisplayable Then TestForm.Show '……(2)'
End Sub
さきほど設定したユーザー設定のドキュメントプロパティには、
ThisWorkbook.CustomDocumentProperties.Item("isDisplayable")
でアクセス可。
(1)からの３行
With ThisWorkbook.CustomDocumentProperties.Item("isDisplayable")
If Not .Value Then isDisplayable = False
End With
では、ユーザー設定のドキュメントプロパティ「isDisplayable」がFalseだったら、Public変数の「isDisplayable」をFalseにする。
(2)の
If isDisplayable Then TestForm.Show
では、Public変数の「isDisplayable」の値を調べ、Trueだったらフォームを呼び出すようにしている。
同じ名前にしてしまったのでややこしいが、ユーザー設定のドキュメントプロパティ「isDisplayable」がFalseだったら、ユーザーフォームは呼び出されない、ということになる。
お次は、フォームのボタンクリック時の処理。
リスト３ フォームモジュール
Private Sub ButtonOK_Click()
If CheckBoxIsRedisplayable Then
isDisplayable = False
ThisWorkbook.CustomDocumentProperties.Item("isDisplayable").Value = False
End If
Unload Me
End Sub
チェックボックスにチェックが入っていたら、Public変数の「isDiplayable」をFalseにするとともに、ユーザー設定のドキュメントプロパティ「isDisplayable」もFalseにして、フォームを閉じる。
従って、チェックボックスにチェックを入れて[OK]をクリックすると、ユーザー設定のドキュメントプロパティ「isDisplayable」がFalseになるので、リスト２のフォーム呼び出しプロシージャを実行してもフォームは表示されないことになる。
あとは、リセットボタン用プロシージャ。
リスト４ 標準モジュール
Public Sub resetTestForm()
calledCount = 0
With ThisWorkbook.CustomDocumentProperties.Item("isDisplayable") '……(3)'
If Not .Value Then .Value = True
End With
End Sub
もはや解説不要だとは思うけれど、(3)からの３行
With ThisWorkbook.CustomDocumentProperties.Item("isDisplayable")
If Not .Value Then .Value = True
End With
で、ユーザー設定のドキュメントプロパティ「isDisplayable」がFalseだったら、値をTrueにするようにしている。
これで、次にフォーム呼び出しプロシージャを実行したときには、再びフォームが表示されることになる。
実行
画像を貼るのがめんどくさいので省略。
一度チェックボックスをオンにして[OK]をクリックしたら、ブックを閉じて開き直しても、もはやフォームが表示されることはなくなった。
もちろん、[リセット]ボタンをクリックしたら復活しますが。
おわりに
でもたぶん、邪道・大仁田厚
なやり方なんだろうね。
追記
いろいろマヌケなことをしていることに気づいたので、慌てて修正しましたｗ
んで、最新版がコチラ。
レジストリ使用版もあります。
