EXCEL VBA におけるブック終了後の値の保存方法について

ＥＸＣＥＬ ＶＢＡについて質問があります。もしかしたら初歩的なことかもしれませんがよろしくお願いいたします。
まず、
Sub Test()
Dim i As String
i = InputBox("好きな果物を入力してください")
Sheets("Sheet1").Range("A1").Value = i
End Sub

を実行し、ブックを保存して終了する。
ブックを開く

Private Sub Workbook_Open()
Dim k As String
k = Sheets("Sheet1").Range("A1").Value
If k = "みかん" Then
MsgBox ("a")
Else: MsgBox ("b")
End If
End Sub

この処理を
Sheets("Sheet1").Range("A1").Value = i
というシート上のセルに値を保存するという方法を取らずに
iの値をブック終了後もプロシージャ内に持っておくというのは可能なのでしょうか。
Publicで宣言してもブックを一度閉じると
やっぱりiの値は保存されませんでした。
どうぞよろしくお願いいたします。

No.2 ベストアンサー 回答者： Wendy02 回答日時： 2009/07/18 21:07

こんばんは。

一般的には、CustomDocumentProperties を使います。
なお、変数の'i' はインクリメントのi ですから、String型などにはあまり使いません。

サンプルを書きます。

'-------------------------------------------
Sub Test1()
Dim strTxt As Variant

strTxt = Application.InputBox("好きな果物を入力してください", Type:=2)
If VarType(strTxt) = vbBoolean Or strTxt = "" Then Exit Sub

　On Error GoTo ErrHandler
　'MyFavorite に、登録する
　ThisWorkbook.CustomDocumentProperties("MyFavorite").Value = strTxt

Exit Sub
ErrHandler:
'プロパティがない時に、プロパティ項目を新たに作る
　 With ThisWorkbook.CustomDocumentProperties
　　　　.Add Name:="MyFavorite", _
　　　　　　LinkToContent:=False, _
　　　　　　Type:=msoPropertyTypeString, _
　　　　　　Value:=strTxt
　End With
　Resume Next
End Sub

'-------------------------------------------

Private Sub Workbook_Open()
　　Dim strTxt As Variant
　　strTxt = ThisWorkbook.CustomDocumentProperties("MyFavorite").Value
　　If strTxt = "みかん" Then
　　　　MsgBox "a", vbInformation
　　
　　Else
　　　　MsgBox "b!" & vbCrLf & "Your Registration: " & strTxt, vbExclamation
　　End If
End Sub

'=============================================
他に、iniファイルを使う方法もありますが、Office からですと、タイミングがあまりよくありません。

この回答へのお礼

ありがとうございました。やりたいことが出来ました。
カスタムプロパティについてざっとネットで見ましたが、知らないやり方だったので非常に勉強になりました。
変数も気をつけます。動けばいいというレベルのいい加減なコードをよく書いてしまうので、メンテをするときに自分でもわからなくなることがあります。
また何かありましたらよろしくお願いいたします。

お礼日時：2009/07/18 22:29

No.1 回答者： mitarashi 回答日時： 2009/07/18 20:30

他のブックのモジュールから実行し、そちらにモジュールレベルの

変数として宣言してあれば、そのブックを開いている間は保持されますが
（VBAのプロジェクトがリセットされる様な事態があると消えてしまいます）
>ブックを保存して終了する。
とエクセルを終了してしまうのであれば無理です。
そのブックに残したくない理由は分かりませんが、他には、
１．テキストファイルで適当な場所に保存しておく
２．PERSONAL.XLS（個人用マクロブック）がシートを持てるのでそちらに保存しておく
３．レジストリに保存する
http://officetanaka.net/excel/vba/tips/tips43.htm
くらいしか思いつきません。
４．禁じ手ですが、自分自身のVBAコードの定数値を書き換えるなんて事も可能らしいです。
数案あげてみましたが、そのブックに保存するのが一番だと思います。エクセルならではのメリットを享受すべきです。必要ならシートを隠す、VBAからしか、可視化出来ない隠しシートにするなんて事も可能です。（既にご存じでしたら、悪しからず）
目的をお示しいただけると、良案が回答されるかもしれません。

この回答へのお礼

ご回答ありがとうございました
３のレジストリに保存する方法は知らなかったので勉強になりました。
いつもはご教示のとおり非表示上のシートにデータを残しており、特に不都合は感じでいないのですが、
ふと他にもやり方があるのではないかと思って質問をさせていただきました。
また何かありましたらよろしくお願いいたします。

お礼日時：2009/07/18 22:34