Excel 2007 VBA でパブリック・モジュールレベルの定数と変数の変更ができません

Question

Microsoft Office Excel 2007 (12.0.6665.5003) SP3 (12.0.6662.5000)

で　97-2003 形式のファイルを作成しています。

VBA (Microsoft Visual Basic 6.5 Version 1054) で

パブリック・モジュールレベルの定数と変数を定義し、

その後、定数の値、変数名等を変更したり、削除したり、コメント行にした後、

保存すると毎回フリーズしてしまいます。

タスクマネージャーで応答のないExcel または VBA を「タスクの終了」で終了させると、

Excel が再起動し、[ユーザーが前回保存済み]のファイルが読み込まれ、

何度試みても変更できません。

これまでから、VBA の変更後、応答がなくなることはしばしばありましたが、

[version 1] で保存されたファイルを元の名前で保存して対処できましたが、

今回は全く変更できません。

もしかして Excel 2007 が壊れているかもしれないと考えて、

別のパソコンで試みましたが、結果は同じでした。

ちなみに OS は、Microsoft Windows 7 Home Premium 6.1.7601 sp1 build 7601

の 32bit と 64bit を使用しています。

Microsoft Windows 7 Home Premium sp1 + Microsoft Office Excel 2007 SP3

ではパブリック・モジュールレベルの定数と変数の変更をする方法はありませんか？

これまで、Visual Basic 2008/2010 Express Edition で同様の変更をして

フリーズした経験は一度もありません。

もしかして、Microsoft Office Excel 2010/2013 では、この問題は解決済みなのか、

VBA ではどのバージョンでも変更ができないのか、わかったら教えて下さい。

今回、変更しようとしている定数は、現在他に使用していない状態にしてあります。

前回、使用中の配列を、(ex Num(30) ---> Num(50)) に変更するときは

Workbook_Open() と Workbook_BeforeSave() で使用していましたので、

Workbook_Open() を外してから変更して、Workbook_Open() を元に戻してから

保存すると、次回の読み込みから有効になりました。

今回も、何かをしたら変更できるのでしょうか？

Answer 1

話は極簡単です。

プログラムを修正すると、ワークエリアに保持されている値はすべて消えると思った方が無難です。

（この場合はPublic変数のあるモジュールを変更したためクリアされています）

そのため、修正後に保存しようとした時点では、

＞ Do Until W_Len < W_Num(1)

の  W_Num(1) が0となっており、無限ループを起こしているだけです。

だいたい、ここでは単に

 Do Until W_Len < １

    If Mid(ActiveWorkbook.Name, W_Len, W_Num(1)) = W_Dot Then Exit Do

    W_Len = W_Len - １

Loop

とすればよいだけでしょう。

なんで、このようなPublic変数の使い方をされているのかわかりませんが間違っています。

パブリック変数はいろいろなところで読み切れない影響がでますので、必要最小限にとどめるべきであり、

> For W_Step = 最小 To 最大

このようなローカルに使い捨てるべきコントロール変数に使っているなど論外ですし、

問題を起こした W_Num(1))　なども同じです。

Public変数をコントロールに使っているなら、それがどこにどういう影響を及ぼすか読み切ってから使うべきです。

もし、このままの形で修正を続けるなら、メンテはすべてマクロをOffにしてやるか、パブリック変数がクリアされるおる恐れがある場合は、保存前に手動でWorkBook_Openを実行させる必要があります。

追記（H25.4.9)

問題のループですが、それ自体不要です。

１．WorkSheet毎に毎回ブック名を取得してますが、ループの外で1回やっておけば済みます。

２．拡張子なしのBaseNameを取得する方法としては、一文字づつチェックしなくてもFileSystemObjectのBaseNameでできますし、それだけのためにFileSystemObjectを持ち込むのが嫌なら↓見たいなことでループなしでできます。

Sub xxx()

Dim wStr As String, wBaseName As String, wDotPos As Long

wStr = ActiveWorkbook.Name

wDotPos = InStrRev(wStr, ".")

wBaseName = Left(wStr, wDotPos - 1)

MsgBox wBaseName

End Sub

参考まで。

Answer 2

さっそくアドバイス、ありがとうございました。

必要なコードだけ、新規ブックにコピーしてみました。

その間に２回フリーズしましたが、[version 1] のおかげで

とりあえず、変更できました。

昨日変更できなかった定数と、Workbook_BeforeSave()

の変数も合わせてパブリックで宣言しました。

この後の返信にコードの抜粋を貼り付けます。

---

Option Explicit    '　　全ての変数に対して、明示的な宣言を強制します（宣言なしで使用不可）

'

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

' Public_Declarations Macro （全てのModuleで参照可能な変数・定数）

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

'

    Public W_Protect As Boolean

    Public W_Num(49) As Byte, W_Step As Byte, W_Response As Byte

    Public W_Message As String, W_Title As String

    Public W_Range As Range, W_Visible(49) As Boolean

    Public W_Len As Integer

    Public Work_Sheet As Worksheet

    Public Const W_MIN_C As Byte = 0

    Public Const W_MAX_C As Byte = 49

    Public Const W_NULL As String = ""

    Public Const W_EXCLAMATION_MARK As String = "!"  '  昨日変更できなかった定数

    Public Const W_Dot As String = "."

---

Option Explicit    '　　全ての変数に対して、明示的な宣言を強制します（宣言なしで使用不可）

'   Dim W_Range As Range, W_Visible(49) As Boolean

'   Const W_EXCLAMATION_MARK As String = "!"

'

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

' W_Num_Set Macro

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

'

Sub W_Num_Set()

Step_1:    '  W_Num(0)～W_Num(49)

    Call 数値_Set(W_MIN_C, W_MAX_C)

End Sub

'

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

' 数値_Set(最小,最大) Macro

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

'

Sub 数値_Set(最小 As Byte, 最大 As Byte)

Step_1:

    For W_Step = 最小 To 最大

        W_Num(W_Step) = W_Step

    Next W_Step

End Sub

---

Option Explicit    '　　全ての変数に対して、明示的な宣言を強制します（宣言なしで使用不可）

'

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

' Workbook_Open Macro

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

'

Private Sub Workbook_Open()

Step_1:    '  W_Num(0)～W_Num(49)

    Call 数値_Set(W_MIN_C, W_MAX_C)

End Sub

'

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

' Workbook_BeforeSave Macro

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

'

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Step_1:

'   Dim W_Len As Integer

'   Dim Work_Sheet As Worksheet

    For Each Work_Sheet In ActiveWorkbook.Worksheets

        W_Len = Len(ActiveWorkbook.Name)

        Do Until W_Len < W_Num(1)

            If Mid(ActiveWorkbook.Name, W_Len, W_Num(1)) = W_Dot Then Exit Do

            W_Len = W_Len - W_Num(1)

        Loop

Step_2:

        Work_Sheet.Range("$A$1").Value = Left(ActiveWorkbook.Name, W_Len - W_Num(1))

        Work_Sheet.Range("$A$2").Value = Work_Sheet.Name

        Work_Sheet.Range("$A$3").Value = Work_Sheet.Index

    Next Work_Sheet

End Sub

---

Workbook_BeforeSave() のサブルーチンは

何かの本に載っていたので利用していますが、

パブリック宣言を覚えてから Workbook_Open()  を

追加しました。

パブリックまたはモジュール変数が変更できないとは

自分でも考えにくいのですが、このコードは至るところで

使用しているし、新規ブックでもフリーズしてしまうので

困っています。

自分では Workbook_Open()  でテーブルの初期化をするのがいけないのかと考えて、

その行をコメントにしてからパブリック宣言を追加してもフリーズします。

[version 1] ができる場合と、[ユーザーが前回保存済み]になる場合と

傾向がつかめません。

長々と返信を書きましたが、よろしくお願い致します。

Answer 3

普通はそんなことないはずです。

もし、新規ブックでOKなら、該当ブックが壊れている可能性もないではないです。

また、コードを拝見しないと再現実験もできませんが、

>Workbook_Open() と Workbook_BeforeSave() で使用していましたので、

この絡みがある可能性はあるかもしれません。

（発生するサンプルコードを示していただければ、つい検証します）

もし、使用中の変数を変更したために発生しているとしたら、マクロセキュリティを高、最高等にしてマクロ無効でブックを開きコード修正をされたらいかがでしょうか？

対症療法ですが、参考まで。