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

質問

EXCELのVBAで、あるワークシートでのみ使うユーザーフォームを作りました。そのワークシート上での定数や、プロシージャや関数をワークシートのモジュールに置いています。そして、その定数や関数などをユーザーフォームのイニシャライズなどで使いたいと思いますが、ユーザーフォームのモジュールはワークシートのモジュールとは別なので、使えません。他への影響を考えると、標準モジュールには、置くのは不適切だと思います。
特定のワークシート上でのみ使うユーザーフォームと、そのワークシートとで、共通の定数や関数などを使う方法は、あるのでしょうか。どのようにすればよいのでしょうか。

回答No.6

Wendy02 · ベストアンサー

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

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

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

お礼

何度もありがとうございました。丁寧で、わかりやすくとても嬉しかったです。

補足

何度もありがとうございます。「Excelは、OLEオブジェクトの集合体で、アプリケーションに対して、並列に並んでいると考えたほうがよい」のですね。確かにそう考えると、わかりやすいですね。
今後は、標準モジュールに置くか、あるいはそうしないかも、さらにいろいろと考えながらやってみたいと思います。「Excelの標準モジュールを使う場合、単に、標準モジュールに書き込むというよりも、そのモジュールを機能別に別けていくということを考える」というようなことを意識してやってみたいと思います。VBAは「もともと、オブジェクト指向は導入されていません。単なる真似事しかできません。
」のですね。納得しました。

回答No.7

Wendy02

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

シートモジュールで、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

補足

もともとの質問から多少ずれて、シートと標準モジュールの件についてまで、丁寧に答えていただき本当にありがとうございます。いろいろとこまごまとした点で違うものですね。「activesheets.」をいれるかどうかとか「.cells」にするか「cells」にするかで、エラーになったことがありました。そういうことも思い出しました。

回答No.5

mimeu

ゴメンナサイ、No.3 は間違ったコメントでした。
> イキナリ UserForm の中から Module の中の変数なり、
> 定数なりを参照することはできないと思います。

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

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

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

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

お礼

ありがとうございました。

補足

ありがとうございます。何かスゴイ技ですね。こんなこともできるのかと驚きました。でも、確かにこれは使わなくてすめば使いたくない技のように感じます。それよりも、このような複雑なことを覚えていられないような気がします。コードモジュールを書き換えたらすぐに変な部分を参照してしまうし。もっと単純な方法があればいいのになあ、と思いました。それにしてもスゴイですね。何度もありがとうございます。

回答No.4

Wendy02

#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プログラムを書いていて、グローバル変数を何十個も使うと、間違いをしやすい部分がないとも言えないのです。時に、こういう質問は、ある意味、プログラマとしての常識と資質を試されるようなものです。

補足

何度もありがとうございます。素人のよく分からない質問に対して、丁寧にプログラマの方に答えてもらえるのはとてもありがたいです。
いろんなファイルをエクスプローラで見ると階層的に見えますよね。それは階層化した方がよほどわかりやすく整理しやすいからだと思います。階層化していないファイルは管理が大変ですよね。VBEのプロジェクトエクスプローラの表示はそうなりません。ワークシートとユーザーフォームと標準モジュールは始めから別扱いです。イメージとしては、自分でこのプロジェクトエクスプローラの階層化ができたらいいのになあ、という感じです。
「質問者さんが運用上、間違わなければよいだけの話でしょう。」というのはその通りですが、プログラミングはバグとの闘いです。それは間違わないように注意しても間違うのが人間だからです。より間違いにくいような環境が提供されている方が、やはり優れた環境だと思います。私にとっては、階層化を自分自身で構築できる方が、より間違いにくいと感じています。ワークシート上のユーザーフォームとか、あるいは、ユーザーフォーム上のワークシートとか、あるいは、ワークシート上のさらに下位のワークシート上のさらに下位のユーザーフォーム上のさらに下位のワークシートとか。
いろんなことをモジュール化するというのは、基本的に階層化する概念がその中に含まれていると感じます。なので、VBAでそういうことができたらいいなあ、と思って質問をしました。でも、どうやら、僕の考えていたようなことはできなそうですね。ワークシート上の定数や関数をユーザーフォームから何とか参照する方法は皆さんからの回答で解りましたが。
それから、シートモジュールは多用しない方がいい、とありましたが、素人の僕は初めて聞くことで正直驚きました。スコープにしろ何にしろ、その場で必要なもののみを、その場で限定的に有効にして使った方がいいと思っていたので、そのような考えの元で、標準モジュールにはあまり書きたくありませんでした。積極的に、シートモジュールを使っていました。ただ、それは、excelのワークシート表を中心に利用し、あくまでそれらを補助するプログラムを何とかしようという立場からですが。それでも、シートモジュールは多用しない方がよいのでしょうか。またそれはどのような理由によるのでしょうか。

回答No.3

mimeu

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

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

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

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

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

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

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

回答No.2

Wendy02

ワークシート上なら、コンテナスタイルで、このようにすればよいけれども、
　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

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

補足

ありがとうございます。プロシ－ジャの登録やスコープについて、また勉強してみたいと思います。
考えていたのは、特定のワークシート上での入力の補助となるユーザーフォームです。なので、そのワークシート上以外では、そのユーザーフォームは全く使わないのです。ユーザーフォーム内でのみ使うプロシージャや定数、変数はもちろんユーザーフォームに置きます。ただ、元のワークシート上で使っていた定数や関数などもそのユーザーフォームで使いたかったのです。なので、グローバルな定数やプロシージャ変数、という感じではなく、あくまでもそのワークシート上でのみの定数やプロシージャというイメージだったのです。イメージとして、デレクトリ構造（という表現でいいのか解りませんが、階層化構造というか、入れ子構造というか）のような感じで、ワークシートという上位のデレクトリに対して、その中にある下位のユーザーフォームというデレクトリがあるようなものを考えていました。上位デレクトリで定義したもののスコープはそのデレクトリ、及び下位のデレクトリであり、下位のデレクトリで定義したもののスコープはそのデレクトリ内のみで、上位のデレクトリには及ばない、ようなものを考えていました。そのようなことができたら便利だろうなあ、と考えて、質問しました。ずっと以前にPASCAL等を使ってプログラミングしていたとき、プロシージャや関数も入れ子構造で作れてとても便利でなおかつ他への影響もなく見やすくなおかつ使いやすかった記憶があり、VBAでもそんなことができたらいいなあ、と思って質問しました。
とりあえず、コンテナスタイルでいろいろと試してみたいと思います。ありがとうございました。

回答No.1

keithin

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

方法２：
モジュールシート上で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

お礼

ありがとうございました

補足

早速の回答、ありがとうございます。いろいろ試してみたいと思います。