変数を保持して呼び出す方法

質問

変数を保持する方法
2022/02/10 15:36
変数が受け継がれない
2022/02/10 15:24
sub A(),subB()と複数のプロシージャをModule1に配置。

Sub Aでターゲットファイル(T_File)を指定して
Sub Bで同じT_Fileを呼び出そうとしたのですが
Subの前に配列は宣言しているのでPrivateのハズなのに
変数が受け継がれません

T_Fileが”””となります。

多分、Sub A()が終了した時点で一度マクロが終了して
新たにSub B()を呼び出すので上手く変数が受け継がれないのだと思います。

何処かのシートのセルに変数を保持して呼び出す方法が考えられますが
そのほかに変数を保持する方法は有りませんか？
（できればシートのセルに保持しない方法があれば教えて下さい。）

以下コード（コードが長いので必要と思われる所だけを記載しています。）
’------------------------------------

Option Explicit

Dim dlg As FileDialog
Dim T_File As String

Sub A()
Set dlg = Application.FileDialog(msoFileDialogFilePicker)
If dlg.Show = False Then
MsgBox "処理はキャンセルされました。"
Exit Sub
Else
End If

'指定テキストファイル読み込み
T_File = dlg.SelectedItems(1)

'(途中のコード省略）

If rc = vbNo Then
MsgBox "処理を中止します。", vbCritical
Exit Sub
Else
MsgBox "処理が終了しました。", vbInformation
End If

End Sub

Sub B()

’T_File = dlg.SelectedItems(1)

Folder_Name = CreateObject("Scripting.FileSystemObject").GetParentFolderName(T_File)

End　Sub

回答No.14

kkkkkm · ベストアンサー

利用した各プロシージャの最後に
Set C_Ws = Nothing
は入れておいてください。

入れておかないと、一度実行して正常にプロシージャから抜けた後でシート名(コードではなく実際のシート名)を変更した場合、そのまま次のプロシージャを実行するとSetとシート名が違うのにエラーになりません。

Setでシート名ではなくシートのオブジェクト名がSetされるためだと思われます。
シート名で探してオブジェクト名でSetする感じ。
なので
Set C_Ws = Nothing
で開放しておくと、その後の実行時に新しくSetするのでシート名が違う場合ちゃんとエラーになります。

回答No.13

kkkkkm

> 利用した各プロシージャの最後に
> Set C_Ws = Nothing
> を書く代わりに
>
> クラスのコードの最後に

代わりではなくて
利用した各プロシージャの最後に
Set C_Ws = Nothing
としてなおかつ
クラスのコードの最後に追加
です。
Private Sub Class_Terminate()
は
Set C_Ws = Nothing
が実行されたときに実行されます。

が、「クラスのコードの最後に」のコードは多分必要は無いと思います。
エラーの原因が分からなかったので、あくまでも念のためだけなので。

お礼

kkkkkmさん、ありがとうございます。

利用した各プロシージャの最後に「Set C_Ws = Nothing」と
クラスのコードの最後に「Private Sub Class_Terminate()」がセットで
利用すること説明してもらって理解できました。

>> 以下のようにSetを省いても1行にしてもコード的には問題ない　？
>> Dim dlg As Application.FileDialog(msoFileDialogFilePicker)

>書けないのではないでしょうか。

同じように２行を１行にする「Dim C_Ws As New Class1」とは、違うのですね。
どう違うのかは、判りませんがそう言うものだと何となく納得しました。

教えてもらったコードに変更してテストコードでマクロ起動＞問題なく最後まで処理できました。

お礼申し上げます。

回答No.12

kkkkkm

> Set C_Ws = New Class1
> は、「プロシージャの外では無効です。」とエラーになります。
> それを避けるために、
> Dim C_Ws As New Class1
> と1行にまとめたとの事でしょうか　？

はいそうです。

> 以下のようにSetを省いても1行にしてもコード的には問題ない　？
> Dim dlg As Application.FileDialog(msoFileDialogFilePicker)

書けないのではないでしょうか。

> 標準モジュールのコードの最終部で以下のように開放する必要がありますか　？
> Set C_Ws = Nothing

なくても大丈夫(終了時に解放されると思います)だと思いますが、念のために
Setはプロシージャ外では使えないので、利用した各プロシージャの最後に
Set C_Ws = Nothing
としておいて

クラスのコードの最後に(これは無くても上記を設定しておくと開放はされます)

Private Sub Class_Terminate()
' 後始末
Set Ws1 = Nothing
Set Ws2 = Nothing
Set Ws3 = Nothing
End Sub

を追加しておいてもいいかもしれません。

プロシージャの最後で開放しても、次にC_Wsが参照されるとSetされるので今回の動作では問題は無いと思います。
ただし、Set C_Ws = Nothingの後でC_Wsの存在を確認する
If C_Ws Is Nothing Then
何かの作業
End If
では何かの作業は実行されません。

> 上記のコードを標準モジュールにコピペして実行後
> エラーが出ているマクロを再度実行しましたが
> 又同じエラーが出ます。

これについては「まっとうな方法かどうかは不明」な部分もあり残念ながら理由が分かりません。

お礼

kkkkkmさん、何度もありがとうございます。

利用した各プロシージャの最後に
Set C_Ws = Nothing
を書く代わりに

クラスのコードの最後に

Private Sub Class_Terminate()
' 後始末
Set Ws1 = Nothing
Set Ws2 = Nothing
Set Ws3 = Nothing
End Sub

を書けば良いのですね。

５つ有る各プロシージャの最後にNothingのコードを書くより
一つの場所（クラス）に書いてしまうほうがスマートなような気がしています。

>これについては「まっとうな方法かどうかは不明」な部分もあり残念ながら理由が分かりません。

先に記載しましたように原因は
　「DETA」によるシート名の不備が原因なので私のミスが全てです。

回答No.11

kkkkkm

> 肝心のクラスモジュールでは、
> C_Wsは何処にも出てきません。

Dim C_Ws As New Class1
の原型は
Dim C_Ws As Class1
Set C_Ws = New Class1
で
Dim dlg As FileDialog
Set dlg = Application.FileDialog(msoFileDialogFilePicker)
と同じパターンになります。

それを一行に収めた記載方法で、宣言とセットを一度で済ませています。
C_WsをClass1のオブジェクト変数にして、ついでにセットしてね。です。

> C_Ws.Ws3.Activate
> インデックスが有効範囲にありません。

これは、実際のシート名とSetしたシート名が違ったりしたら出ると思います。
エラーになる手順をはっきりとは説明できませんが
Dim C_Ws As New Class1
一度実行されるとそのClass1の内容を覚えているので、最初の実行時にエラーになってシート名を正しく変更したけどエラーになる、もしくはシート名を変更したのにエラーにならないなどが出るかもしれません。
後者は再現できたのですが、前者を再現することができなかったので前者は無いかもしれません。

ので、テスト中は実行後に変更を加えた時

Sub TestNothing()
Set C_Ws = Nothing
End Sub

上記のようなコードで一度開放したほうがいいと思います。

お礼

すいません。

エラー原因が見つからないし
シートがおかしくなっていると思い新規に作成している途中で
シート名”DATA"すべきを何を思ったのか”DETA"とするミスを犯していました。

"DATA"に変更してSub TestNothing()を行って
最初のマクロで問題のエラー無く最後まで処理出来るのを確認しました。

何度も同じミスで我ながら飽きれてしまいました。

補足

Dim C_Ws As New Class1
は、
Dim C_Ws As Class1
Set C_Ws = New Class1
と同じ意味だと言うのは、説明で理解できたのですが、

標準モジュールの一番先頭の宣言セクション内で
Dim C_Ws As New Class1
の代わりに
Dim C_Ws As Class1
Set C_Ws = New Class1
と書いても
Set C_Ws = New Class1
は、「プロシージャの外では無効です。」とエラーになります。
それを避けるために、
Dim C_Ws As New Class1
と1行にまとめたとの事でしょうか　？

Set C_Ws = New Class1　なら
標準モジュールのコードの最終部で以下のように開放する必要がありますか　？
Set C_Ws = Nothing

>Dim dlg As FileDialog
>Set dlg = Application.FileDialog(msoFileDialogFilePicker)
>と同じパターンになります。

以下のようにSetを省いても1行にしてもコード的には問題ない　？
Dim dlg As Application.FileDialog(msoFileDialogFilePicker)

その場合標準モジュールのコード最終部に以下は必要？必要ない？
　　Set dlg ＝ Nothing

’---------------------------------
>実際のシート名とSetしたシート名が違ったりしたら出ると思います。

正にそのとうりで
実際のシート名とSetしたシート名が違っていました。
すぐに気づいて同じに修正しました。

>Sub TestNothing()
> Set C_Ws = Nothing
>End Sub

上記のコードを標準モジュールにコピペして実行後
エラーが出ているマクロを再度実行しましたが
又同じエラーが出ます。

回答No.10

kkkkkm

> 変数の宣言については以下の解釈でいいのですね。

そうですね、簡単に考えると目線がどこからかだと思います。

プロシージャ側の目線だと
Private はプロシージャ内
Public はプロシージャの外を含む
(ただしプロシージャ内でPrivate Public宣言はできないので、Private Publicに関してはプロシージャ側の目線は無しだと思います)

モジュール側の目線だと
Private は宣言したモジュール内
Public は宣言したモジュールの外を含む

モジュールの宣言セクション内の宣言は「モジュール側の目線」になると思います。

> 宣言セクションで「Dim C_Ws As New Class1」と宣言

これはそれでOKです。

後は

標準モジュールを作る時と同じ操作で
「挿入」の「クラスモジュール」でクラスモジュールを作成します。
Class1ができます。この名前を変更した場合は
Dim C_Ws As New Class1のClass1を変更した名前に合わせてください。

クラスモジュールに
Option Explicit

Public Ws1 As Worksheet

Private Sub Class_Initialize()
Set Ws1 = Worksheets("DATA")
End Sub
とします。

この場合、Ws1がDATAシートだというのが標準モジュールを見ているときに分からない(見えない)ので
Dim C_Ws As New Class1の下にコメントで
'Ws1 = Worksheets("DATA")
とかしておいた方がいいかもしれません。

あとは、使いたいプロシージャでSet無しで
MsgBox C_Ws.Ws1.Range("B1").Value
のようにして使えます。
ちなみに、オプションで「自動メンバー表示」にチェックが入っていると、C_Wsの後でドットを打った時に「Ws1」が選択肢として表示されます。

また、ひとつのクラスに2個以上セットすることもできます。

Public Ws1 As Worksheet
Public Ws2 As Worksheet

Private Sub Class_Initialize()
Set Ws1 = Worksheets("DATA")
Set Ws2 = Worksheets("Sheet2")
End Sub

お礼

解説を受けて、以下のように理解しました。

クラスモジュールは、全く利用したことが無かったので
最初、解説が良く判りませんでしたが何とか今回の件では
利用できる事が出来ました。

１）クラスモジュールを挿入する。
「Class1」のクラスモジュールが作成される
（挿入順番で「Class2」などと成るのは標準モジュールと同じ）

クラスモジュール(Class1)に以下を追加

Option Explicit

Public Ws1 As Worksheet
Public Ws2 As Worksheet
Public Ws3 As Worksheet

Private Sub Class_Initialize()
Set Ws1 = Worksheets("DATA")
Set Ws2 = Worksheets("Chapter")
Set Ws3 = Worksheets("単独型を集計型へ")
End Sub

２）標準モジュールの最初の宣言セクションで
Dim C_Ws As New Class1
'Set Ws1 = Worksheets("DATA") by Class_Module
'Set Ws2 = Worksheets("Chapter") by Class_Module
'Set Ws1 = Worksheets("単独型を集計型へ") by Class_Module

この時、標準モジュールとクラスモジュールではモジュールが違うので
「Dim C_Ws As New Class1」と宣言の下にクラスモジュールでの宣言を
コメントで記載したほうが判りやすい。

Dim C_Ws As New xxxx のXXXXは、
クラスモジュールの「Class1」,「Class2」の名前と合わせる。

3)Sub A()などで利用するには、
MsgBox C_Ws.Ws1.Range("B1").Value　などと
「Ws1.Range("B1").Value」の前に「C_Ws.」を付加する。
（改めて「SET Ws1=Worksheets("DATA")」 の記述は必要ない）

’--------------------------------
で不思議に思ったのは、
標準モジュールで「Dim C_Ws As New Class1」は
C_Wsはクラスモジュール（Class1)で宣言されてますの意味だと思いますが

肝心のクラスモジュールでは、
C_Wsは何処にも出てきません。
（Ws1,Ws2,Ws3はありますが、C_Wsはありません。）

これは、どう考えたら良いのでしょうか　？

補足

すいません。

’　Set Ws1 = Worksheets("単独型を集計型へ") by Class_Module
は、
’　Set Ws3 = Worksheets("単独型を集計型へ") by Class_Module
の間違いです。

上手く処理できそうなので実際にマクロを書き換えて動かしてみました。

しかし、
　早速、エラーが出ました。

C_Ws.Ws3.Activate
インデックスが有効範囲にありません。

回答No.9

kkkkkm

> 、Publicのように1回登録すれば
> 同じモジュールで利用できるようになりませんか？

クラスを使えばできますが、まっとうな方法かどうかは不明です…。
あと、変数宣言と同時にNewするのはいかがなものかで意見が分かれてたりします。

標準モジュールなど
Option Explicit

Dim C_Ws As New Class1

Sub Test()
MsgBox C_Ws.Ws1.Range("A1").Value
End Sub

Sub Test2()
MsgBox C_Ws.Ws1.Range("B1").Value
End Sub

Class1のコード Class1の名前はプロパティのオブジェクト名で変更できます。
Option Explicit

Public Ws1 As Worksheet

Private Sub Class_Initialize()
Set Ws1 = Worksheets("DATA")
End Sub

お礼

kkkkkmさん、変数の宣言については以下の解釈でいいのですね。
私の認識もまんざら間違いでは無いようです。

モジュールの一番先頭の宣言セクション内に置いて、モジュール レベルの変数を作成することもできます。

ステートメントがモジュールの宣言セクション内にあると、
変数はそのモジュール内のすべてのプロシージャで使用できますが、
プロジェクト内の他のモジュールにあるプロシージャでは使用できません。

モジュール レベルで使用した場合、Dim ステートメントは Private ステートメントと等価です。
コードを読みやすく、解釈しやすくするには、Private ステートメントを使用できます。

この変数をプロジェクト内のすべてのプロシージャで使用可能にするには、
変数の前に Public ステートメントを置きます。

’-----------------------
宣言セクションでWs1をDIMで宣言して
各プロシジャの複数回必要なSet Ws1 = Worksheets("DATA")を1回で済ませられないかの回答ですが

スキル不足で解説された事が理解できないでいます。
つまり、以下の解釈でいいですか　？

宣言セクションで「Dim C_Ws As New Class1」と宣言

Sub A()で「Set Ws1=Worksheets("DATA")」と宣言して
使い時は、ws1の前にC_Ws.を配置して
例えば「MsgBox C_Ws.Ws1.Range("A1").Value」のようにコードを書く。

Sub B()では、同じようにコードを書けば
Sub B()で「Set Ws1=Worksheets("DATA")」の宣言は必要ない。

回答No.8

kkkkkm

> まあ、お勧めはしません。

このあたりは私の回答ではないので…

> ミスの元にもなりそうなのでPublicに変更しておきます。

Sub A()
の中の
Dim T_File As String
を削除して正常になったのでしたら、Dimのままでいいと思います。

他のモジュールから参照することが無いのでしたらPublicにすることはないと思いますし、Publicにすると他のモジュールでうっかり同じ変数名で宣言無しで利用したら「あれ？なんか変」な事になるかもしれません。
以下はVBでの説明になっていますがほぼ同じ感覚でいいと思います。
変数を宣言する
https://docs.microsoft.com/ja-jp/office/vba/language/concepts/getting-started/declaring-variables

回答No.7

kkkkkm

と再度宣言してないですね。
以下に訂正
と再度宣言してないですよね。

お礼

>Sub A()
>の中で
>Dim T_File As String
>と再度宣言してないですね。

>と再度宣言してないですね。
>以下に訂正
>と再度宣言してないですよね。

kkkkkmさん、仰せの通りりの事やっちゃってました。

Option Explicit も外さずに、
DIMもPublicに変更せずに
sub A()を削除したら、上手くT_Fileが値が引き継がれました。

>まあ、お勧めはしません。

転ばぬ前の杖、
ミスの元にもなりそうなのでPublicに変更しておきます。

補足

もうひとつ、疑問点が

以下のようにWs1を定義して
Sub A()でSet文でオブジェクトを格納するオブジェクト変数を決めるコードを書いたとして
sub B()でも同じSet Ws1 = Worksheets("DATA")を書かないと
エラーが出るような気がしますが、Publicのように1回登録すれば
同じモジュールで利用できるようになりませんか？

Option Explicit

Public Ws1 As Worksheet

sub A()
Set Ws1 = Worksheets("DATA")
End　Sub

sub B()
Set Ws1 = Worksheets("DATA")

回答No.6

kkkkkm

ふと思ったのですが

Sub A()
の中で
Dim T_File As String
と再度宣言してないですね。

回答No.5

kkkkkm

No.3一部訂正

(Sub A()に宣言が無いのでそのままだとT_Fileの定義が無いというエラーになりますよね)
は
(Sub A()に宣言が無いのでプロシージャの外での宣言が有効では無い場合、T_Fileの定義が無いというエラーになりますよね)

回答No.4

asciiz

＞DIMで配列宣言をすれば、Public と書かなくても
＞それは、Publicとして扱われると思っていたのですが
＞私の認識が間違っているいるのでしょうか？

Option Explicit という「厳密に解釈する」オプションがついていますから、グローバル変数(※Microsoftは「パブリック変数」と呼ぶのですね)はきちんとグローバル変数として宣言する必要があります。

＞Option Explicit ステートメント (VBA) | Microsoft Docs
＞https://docs.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/option-explicit-statement

それを外せば、モジュール外で宣言した変数はすべてグローバル(パブリック)変数になるんじゃないでしょうか。
まあ、お勧めはしません。