EXCELのVBAで、あるワークシートでのみ使うユーザーフォームを作り

>シートモジュールは多用しない方がいい

Excelの場合、シートというローカルモジュールを多数増やしてしまうのですが、その中のモジュール等は、共有性がないものがありますので、その都度、同じようなものを作る手間やメモリ配分を考えると、標準モジュールの方に書き込むという手段が取れるようになっていったと考えています。

あくまでも、Excelの問題ですが、Excelは他のOffice などとは違う特徴を持っています。それだけに、MS側も古い機能をExcel VBAには残しているようです。最大の問題は、シートモジュールはシートを親オブジェクトとして、一緒にコピーされたり、移動されたりしますが、完全に実行されないこともあったり、前の部分のオブジェクトの痕跡を残したり、都合の悪いことも往々にしてあります。ただし、イベントなど、シートオブジェクトに付随したものはこの限りではありません。

Excelのシートに該当するようなオブジェクトは、他のアプリケーションには基本的にはありません。
Wordなどは、Document は似ていても、ThisDocument モジュールと、表面のDocument は直結しています。
Access は、フォームによってコントロールしますから、共有の関数型プロシージャ等以外は、標準モジュールは使わないです。また、管理がしにくくなります。

今度は、Excelの標準モジュールを使う場合、単に、標準モジュールに書き込むというよりも、そのモジュールを機能別に別けていくということを考えるようにします。例えば、ワークブックを開けた時に、設定する場合はに、ThisWorkbook も一部は代わりにはなるのですが、多くは標準モジュールに書くのが一般的です。理由は、オートメーション化した場合、標準モジュールのほうが切り離しが行われるからです。

絶対に、標準モジュールでなくてはならない、ということはないのですが、標準モジュールは、そのままグローバル変数やグローバル・プロシージャや関数になっていますから、うっかり、Public ステートメントを忘れても、それで動かないということはありません。ただし、Class モジュールでは、必ず要求されることがあります。

>プログラミングはバグとの闘いです。それは間違わないように注意しても間違うのが人間だからです。より間違いにくいような環境が提供されている方が、やはり優れた環境だと思います。

私は、Excelの場合、バグとの闘いというような感覚はありません。数百行ぐらいでは、そんなに間違いを起こさないのです。プロシージャ・レベル、モジュールレベルの修正で、最初の設計の段階で、間違えていなければ、後は、ほとんどプロシージャ・レベルの修正になりますが、ただし、修正方法には独特の「コツ」があります。まずい時は、バグ探しはせずに、プロシージャ自体の全面取り替えということになります。だから、よほどのことがない限りは、バグつぶしというようなことはありません。

これは、VBAは、旧VB6と同じく、構造化をするから可能なのです。既存のオブジェクトは、確かに階層化はされていますが、それは、ほとんどのカスタマイズは難しいです。

>階層化を自分自身で構築できる方が、より間違いにくいと感じています。ワークシート上のユー……

私は、他の言語の知識もあるつもりですが、そういうものは、VBAにも、VBにもありませんね。たぶん、クラスの概念のことを言っているかもしれませんが、VBAで、最初から、そういうものを作っても、上手くできないはずです。もともと、オブジェクト指向は導入されていません。単なる真似事しかできません。

Excelは、OLEオブジェクトの集合体で、アプリケーションに対して、並列に並んでいると考えたほうがよいと思います。本当は、プロ・レベルの作成した作り方などを学べると良いのですが、掲示板の単発のコードでは、設計の仕方は分かりません。私たちがあれこれいっても、実際には、いろいろなものを作っていただいて、そこで自分で体得していただくしかありません。設計の仕方などは、以前のデベロッパーの書籍などには、多少、触れていたと思います。

マクロの作成で、ある程度の規模のマクロを何年か書いてきて、もしかしたら、こうかもしれない、というものが、確信に変わっていくものだと思います。

シートモジュール用と標準モジュール用の簡単なサンプルコードを置いておきます。

シートモジュールで、Cells やRange の親オブジェクトは、シートオブジェクトです。だから、Me キーワードは省略しても構いませんが、通常、そのシートだけの利用になります。

標準モジュールは、CellsやRange そのものは、そのままですと、ActiveSheet になりますので、その点、親オブジェクトは気にしなくて良いということです。今回は、AutoFilterMode プロパティを取らないといけないので、以下では、ActiveSheet オブジェクトが必要になってしまいましたが、ActiveSheet はなくても済む例が多いです。

なお、Rows.Count は、どこでも同じ最大行数ですから、親オブジェクトがいりません。

シートモジュール
Me.Range("A1", Me.Cells(Rows.Count, 1).End(xlUp)).Resize(, 3)

標準モジュール
Range("A1", Cells(Rows.Count, 1).End(xlUp)).Resize(, 3)

'//A列～C列をオートフィルタで範囲を選択して、それを、シート2にコピーするマクロ
'シートモジュール用
Sub Test_For_SheetModule()
　　Dim RwCount As Long
　　With Me
　　　　If .AutoFilterMode Then
　　　　　　.AutoFilterMode = False
　　　　End If
　　　　'3列　(A列2～9までを検索)
　　　　.Range("A1", .Cells(Rows.Count, 1).End(xlUp)).Resize(, 3).AutoFilter , _
　　　　Field:=1, _
　　　 　　 Criteria1:=">=2", Operator:=xlAnd, _
　　　　　　Criteria2:="<=9"
　　　　With .AutoFilter.Range
　　　　　　RwCount = .Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count
　　　　　　If RwCount > 1 Then
　　　　　　　　'タイトル行を抜いてコピーする
　　　　　　　　.Offset(1).Resize(RwCount - 1).Copy Worksheets("Sheet2").Range("A2")
　　　　　　Else
　　　　　　　 MsgBox "該当範囲のデータが見つかりません。", vbExclamation
　　　　　　End If
　　　　End With
　　　　.AutoFilterMode = False
　　End With
End Sub

'//標準モジュール用（プロシージャはPublic ステートメントと同義です。)
Sub Test_For_GeneralModule()
　　Dim RwCount As Long
　　　　If ActiveSheet.AutoFilterMode Then
　　　　　 ActiveSheet.AutoFilterMode = False
　　　　End If
　　　　'3列　(A列2～9までを検索)
　　　　'ここの部分が大きく違います。
　　　　Range("A1", Cells(Rows.Count, 1).End(xlUp)).Resize(, 3).AutoFilter , _
　　　　　Field:=1, _
　　　　　Criteria1:=">=2", Operator:=xlAnd, _
　　　　　Criteria2:="<=9"
　　　　With ActiveSheet.AutoFilter.Range
　　　　　　RwCount = .Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count
　　　　　　If RwCount > 1 Then
　　　　　　　　'
　　　　　　　　.Offset(1).Resize(RwCount - 1).Copy Worksheets("Sheet2").Range("A2")
　　　　　　Else
　　　　　　　 MsgBox "該当範囲のデータが見つかりません。", vbExclamation
　　　　　　End If
　　　　 End With
　　　　 ActiveSheet.AutoFilterMode = False
End Sub

ゴメンナサイ、No.3 は間違ったコメントでした。

> イキナリ UserForm の中から Module の中の変数なり、
> 定数なりを参照することはできないと思います。

と書きましたが、どうも気になるので、一晩寝て考えましたら、
ヤッパリ裏技がありました。

例えば下の例で、Module1 の２～４行目を文字列として取り出すことが、
UserForm の中からできます。

　　Dim 文字列 As String
　　文字列 = Application.VBE.VBProjects(1).VBComponents("Module1").CodeModule.Lines(2, 3)

でも、よい子はあまりこんなことをしないほうが　・・・　　　（＾－＾）

#2の回答者です。

>あくまでもそのワークシート上でのみの定数やプロシージャというイメージだったのです。

Excel VBAに限ったことですが、まず、シートモジュールは多用しないほうがよいということは頭に入れておいてください。

>ワークシートという上位のデレクトリに対して、その中にある下位のユーザーフォームというデレクトリがあるようなものを考えていました。

#2で書いたように、UserForm は、UserForm というインスタンスがある限りですから、変数については、あまり深く考えなくてもよいのですが、ワークシートとUserFormの関係は、ブックとしてのつながりも低い独立した関係に近いです。

>階層化構造というか、入れ子構造というか）
それなら、このようにするのではないかと思います。

'ユーザーフォーム内で、With ステートメントを使って、

With Worksheets("Sheet1")　　'ブックをまたぐ場合は、ThisWorkbook の親オブジェクトを加えます。
　myVal1 = .Range("A1").Value
　myVal2 = .Range("B1").Value
End With

また、テキストボックスなら、直接、ControlSource に、アドレスを書く方法があります。

ただし、正確にワークシートの値を取るということは、書式に影響を受けているものもあるので、必ずしも、.Valueプロパティが正しいとは限りません。関数の戻り値を取ることはしても、関数自体を取得することは一般的にはありえません。

#3さんの
「質問者さんが運用上、間違わなければよいだけの話でしょう。」
その一言につきますね。だいたいは、そんなに複雑な構造のものを作るわけではないし、今回は、ひとつのシートだけのものであるというわけですから、問題は発生する可能性は低いです。一応、UserForm のInitializeで、シートのSelect をしておけばよいかと思います。もし、Pascal のスタイルのこのようにしたいというなら、具体的に書いていただいたほうがよいでしょうね。言葉だけでは、双方の用語も違いますから。

ところで、今回の質問は、参りましたね。私自身、実際の場では、VBAプログラムを書いていて、グローバル変数を何十個も使うと、間違いをしやすい部分がないとも言えないのです。時に、こういう質問は、ある意味、プログラマとしての常識と資質を試されるようなものです。

> あるワークシートでのみ使うユーザーフォーム

にこだわっておられますが、ユーザーフォームはワークシートとは別ですよね ☆
なので技術的には、そのことにこだわる必要はありません。
質問者さんが運用上、間違わなければよいだけの話でしょう。

それで、ワークシート上の定数や関数
（セルのデータではなく、ワークシート上に書いたＶＢＡの定数ですよね）
をユーザーフォームのイニシャライズなどに使う方法ですが、
一案を紹介します。

ユーザーフォームのＶＢＡ中に

Public 文字列 As String, 整数 As Integer
などと書いておきます。
ユーザーフォームをイニシャライズするときは、この文字列、整数をつかう。

で、ワークシート上のＶＢＡには下例のようにフォームを表示するコードを書く。

Sub UserForm1を表示()
　　UserForm1.文字列 = "なんとかかんとか"
　　UserForm1.整数 = 12345
　　UserForm1.Show
End Sub


なお、上記によらずイキナリ UserForm の中から Module の中の
変数なり、定数なりを参照することはできないと思います。
（マイクロソフトの文書を確認したわけではありませんので、一人の素人考えですが）
また（ご存じのことと思いますが）ワークシートのセルにかかれたデータなら、
UserForm から簡単に参照できます。

ワークシート上なら、コンテナスタイルで、このようにすればよいけれども、

　ThisWorkbook.Worksheets("Sheet1").Range("A1").Value

後は、質問に具体性なコードがありませんので、私には正確に指摘できません。
一体、シートモジュールで、どんなプロシージャを使っているのか分かりません。ただ、おそらくは、失礼ですが、間違ったプロシージャの登録方法をしているような気がします。

>他への影響を考えると、標準モジュールには、置くのは不適切だと思います。

どのような問題があるのでしょうか？一般的には、問題が発生するようなことはありません。UserForm のみで使うものなら、UserForm に使うべきで、わざわざプロシージャそのものをシートモジュールに置く必要はありません。シートモジュールに置くプロシージャ等は、イベントなどの特殊な目的のためです。

グローバル変数は、一般的に、標準モジュールに置くというのが約束事です。そうしないと、グローバル変数として、見失うことが多いので、まとめてグローバル（＝プロジェクトレベル)変数を置くようにします。

Public ステートメントも、もともと、標準モジュールに、明示的に用いるものです。それ以外の場所では、用いることはしません。逆に、他から用いられるのを防ぐにには、以下のような方法はあります。または、プロシージャレベルで、Static 変数でも良いです。mCnt の変数の役割が終わるのは、UserForm が失われた時(Unload)です。

'ユーザーフォームモジュール
'Label1 と、CommandButton1 各ひとつずつ、UserForm1に設けた場合

Private mCnt As Variant

Private Sub CommandButton1_Click()
　Call Sheet1.Test1(mCnt)
　Label1.Caption = mCnt
End Sub

***
'シートモジュール（通常は、シートモジュールに置くことはありません。)
Sub Test1(ByRef Cnt)
　Cnt = Cnt + 1
End Sub

シートモジュールは、もともと、シートモジュール範囲のスコープに限って使うようにします。

方法１：

パッケージにしてアドイン化してしまうのも，また一つの方法かなと思います。


方法２：
モジュールシート上でPublicで宣言し，モジュール名で修飾して呼び出し利用します。

【練習】
Sheet1のシートモジュールに：

Option Explicit

Public S1 As String

Public Function fs1(ByVal a) As String
　fs1 = "ret from Sheet1 :" & a
End Function

Public Sub ps1()
　MsgBox "hello from sheet1"
End Sub



UserForm1からは：

private sub UserForm_Initialize()
　Sheet1.S1 = "hello"
　me.textbox1 = Sheet1.fs1(Sheet1.S1)
end sub

private sub Commandbutton1_Click()
　call Sheet1.ps1
End Sub