サブプロシージャの自動生成？

質問

たとえば質問No4576980であげたような、特定のキーワードで検索をかけて対象となったデータに対して印刷なり何なりの動きをかけるような一連のプロシージャの組み合わせがあるとします。

このとき、後々検索対処となる配列要素が増えたとします。まだ勉強中ですがReDimを使えば無理やりでも要素は増やせそうな気がしますが、それ以降の一連の動きを追加してやるには何か方法がありますでしょうか？

例に挙げた質問の中では配列が循環すればいけそうな書き方になっていますが、たとえば印刷先のフォーマットは当然配列の増えた日にできるものであって最初からしてできるものではありませんし、現在いろいろと進めて最初の検索対象配列に対してselect caseで分岐させさらに違う列に対して別の検索配列を適用してデータ行の特定を行っていますので、最初の配列に追加した要素にも同様に2つ目の検索対象となる配列要素を付け加えたい場合もありますし、何より追加分に対してのcase "" ~ 条件式の一文を追加してやらなければ動作がつながりません。

回答No.2

Wendy02 · ベストアンサー

こんばんは。

私は長くサポートはできませんが、もう少しご質問の困っている点をまとめたほうが良いかと思います。ただ、総体的なアドバイスとすれば、そんなに急がないことですね。先の勉強をすれば、必ず解答があるというか、tatekentaさんは、もう、それだけの力があるはずです。ただ、前回の回答は、ある程度の含みを持たせていました。システムに近くなると、テクニックそのものよりも、組み立てのほうが大事になると思います。

>後々検索対処となる配列要素が増えたとします。まだ勉強中ですがReDimを使えば無理やりでも要素は増やせそうな気がします

その検索要素は、動的配列ではあるけれども、セルに書くなりして、それを　Split 関数で分けて入れます。

>印刷先のフォーマットは当然配列の増えた日にできるものであって最初からしてできるものではありません

具体性がありませんが、それは、臨時のシートに一旦出力して、プールさせればよいのではありませんか？その状態で、再び、検索するとかすればよいと思います。自信があれば、変数の中で処理してもよいと思います。

お礼

回答ありがとうございます。

質問に具体性が無く、至らなくてすみません。No1さんへのご返事の部分で多少補足させていただいています。

No２さんのご指摘のとおり、組み立て方を考えなければならないことはわかってはいるのですが、イメージする力が足りないためそこにたどり着けずにいます。

なんとなく、分岐条件と最終的な結果を反映させる動作（今回は印刷作業ですが）の種類が初期条件のみでなく流動性ががある場合、それぞれの要素をストックするテーブルを用意してそれらをつなぎ合わせる構造を作るのがベストなんだろうなとはぼんやりと考えられるのですが、ある程度の規模のコードのまとまりを見たことが無いのでパーツ（プロシージャ郡）の繋ぎの妙みたいなものがいまいちイメージできません。

この辺は、経験だといわれてしまえばそれまでですが・・・

なるべく、さまざまな例をweb上で見つけて試行してみようとは思います。

回答No.5

Wendy02

こんばんは。

>コードフォーマットがあればそれをクラス内に新規で追加してくれるような動作は無いものだろうか？

Excelのクラスは、多くは、決められたオブジェクトにイベントを増設するのですが、インスタンスを設けたオブジェクトを使うだけですから、そんなに万能ではありません。

前回の質問を含めて、入り口だけの話で、最終的にどういう結果になるのか、ということが見えてこないので、回答側としては、総論的というか一般論でしかありませんが、もし個々に結果が分かれるというなら、個々のマクロで対処するのではなく、ユーザー選択が可能な、ダイアログを作ればよいと思います。

>必要なデータ郡に取り分けて分割処理するのがオーソドックスだということだと受け取れますがあっていますでしょうか？

そのとおりなのですが、掲示板では、分散した個々のコードを聞いたほうが、解答が得られやすいということもいえます。例えば、Find メソッドで、二重に検索するとかいう方法は、そんなに難しいものではありません。

Find533(r As Integer, c As Integer, 他必要変数)

このように、個々に、多くのサブプロシージャが分かれるのなら、サブプロシージャの意味がありません。同じ動作をまとめて、マクロ化したほうがよいです。

昨年の10月ごろからの、ご質問は見ていますが、今の所、まだ、あまりイメージとして描けません。たぶん、「Findステートメントで別なブックの検索」4416154　あたりが鍵になるだろうとは見ていますが。

お礼

回答ありがとうございます。

＞サブプロシージャの意味がありません。マクロ化したほうがよいです。

確かにそうかもしれません。ここで締め切って考えなおしてみます。ちなみに、昨年の10月ころのものは取り組みとしては別のところで動いていたものでした。

回答No.4

end-u

すみません。#3のコード、１箇所修正してください。
Sub test1() の
>：
>msg(0) = "検索条件"
>With Sheets("test")
With Sheets("key")
"test"を"key"に。

回答No.3

end-u

ExcelVBAの場合は、Excelの基本機能を使う事によってVBAの組み方が大きく変わる場合もあります。
以下、一例に過ぎませんが

まず、新規Bookの標準モジュールに以下のコードをコピーペーストして Sub 準備() を実行してください。
コード内容は、"データ"、"key"、"抽出"の３シートを追加してサンプルを作成するものです。

Option Explicit

Sub 準備()
　　With Sheets.Add
　　　　.Name = "データ"
　　　　.Range("A1:C4").Value = [{"A","B","C";533,"a",1;533,"a",2;534,"a",3}]
　　End With
　　With Sheets.Add
　　　　.Name = "key"
　　　　.Range("A1:B3").Value = [{1,2;533,"a";534,"b"}]
　　End With
　　Sheets.Add.Name = "抽出"
End Sub
'------------------------------------------------
Sub test1()
　　Dim rng As Range
　　Dim r1　As Range
　　Dim r2　As Range
　　Dim r3　As Range
　　Dim cx　As Long
　　Dim msg(3) As String '確認用（本来不要）

　　With Sheets("データ")
　　　　.AutoFilterMode = False
　　　　.Range("A1").CurrentRegion.AutoFilter
　　　　Set rng = .AutoFilter.Range
　　End With
　　msg(0) = "検索条件"
　　With Sheets("test")
　　　　cx = .Cells(1, .Columns.Count).End(xlToLeft).Column
　　　　For Each r1 In .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
　　　　　　rng.AutoFilter Field:=.Cells(1).Value, Criteria1:=r1.Value
　　　　　　msg(1) = .Cells(1).Value & "列目 " & r1.Value
　　　　　　If cx = 1 Then
　　　　　　　　MsgBox Join(msg, vbLf) '確認用
　　　　　　　　Call test2(rng)
　　　　　　Else
　　　　　　　　For Each r2 In .Range(.Cells(2, 2), .Cells(.Rows.Count, 2).End(xlUp))
　　　　　　　　　　rng.AutoFilter Field:=.Cells(2).Value, Criteria1:=r2.Value
　　　　　　　　　　msg(2) = .Cells(2).Value & "列目 " & r2.Value
　　　　　　　　　　If cx = 2 Then
　　　　　　　　　　　　MsgBox Join(msg, vbLf) '確認用
　　　　　　　　　　　　Call test2(rng)
　　　　　　　　　　Else
　　　　　　　　　　　　For Each r3 In .Range(.Cells(2, 3), .Cells(.Rows.Count, 3).End(xlUp))
　　　　　　　　　　　　　　rng.AutoFilter Field:=.Cells(3).Value, Criteria1:=r3.Value
　　　　　　　　　　　　　　msg(3) = .Cells(3).Value & "列目 " & r3.Value
　　　　　　　　　　　　　　MsgBox Join(msg, vbLf) '確認用
　　　　　　　　　　　　　　Call test2(rng)
　　　　　　　　　　　　Next

　　　　　　　　　　End If
　　　　　　　　Next
　　　　　　End If
　　　　Next
　　End With

　　Set rng = Nothing
End Sub
'------------------------------------------------
Sub test2(rng As Range)
　　Dim cnt As Long
　　Dim i　 As Long

　　cnt = rng.Columns(1).SpecialCells(xlCellTypeVisible).Count
　　MsgBox "抽出件数 " & cnt - 1 & " 件" '確認用
　　If cnt > 1 Then
　　　　With Sheets("抽出")
　　　　　　.UsedRange.ClearContents
　　　　　　rng.Copy .Range("A1")
　　　　　　For i = 2 To cnt
　　　　　　　　'実際にはここで印刷処理へ
　　　　　　　　MsgBox .Cells(i, 1).Value & .Cells(i, 2).Value & .Cells(i, 3).Value '確認用
　　　　　　Next
　　　　End With
　　End If
End Sub
'------------------------------------------------

その後 Sub test1() を実行してください。

AutoFilterメソッドを使って、keyシートの内容を検索条件にして抽出します。
（１行目が検索列、２行目以降が検索条件）
検索条件が最大３つまでならば、再帰処理せず上記のような組み方で済みます。

>ところで私が言いたかったのはどういうことかと言うと、...
>...それを代入した同様のコードを生成するのが簡単だと考えました。
コードを生成するよりも、仕様を決めて、変数を準備してコードを書いておくほうが簡単です。
>それぞれの要素をストックするテーブルを用意してそれらをつなぎ合わせる構造を作る...
で良いと思います。

お礼

回答ありがとうございます。

まだ、作りかけですが似たような形のものも作成中でした。私のものよりシンプルなので参考になります。

回答No.1

end-u

>さらに違う列に対して別の検索配列を適用して...
とは、例えば

[1]
１列目　３列目
　533　　A
　534　　B
　535

[2]
１列目　３列目　５列目
　533　　A　　　　x
　534　　B　　　　y
　535　　 　　　　z

このように検索条件がタテにもヨコにも変動するものに対応できるようにしたいという事でしょうか？
その場合はAND検索ですか？
つまり、[2]の例だと
１列目が 533 かつ ３列目が A かつ ５列目が x のものを抽出。
１列目が 533 かつ ３列目が A かつ ５列目が y のものを抽出。
１列目が 533 かつ ３列目が A かつ ５列目が z のものを抽出。
１列目が 533 かつ ３列目が B かつ ５列目が x のものを抽出。
：
と検索キーワードの配列の全組み合わせに対して抽出をしたいという事になりますか？

以下、そうだと仮定しての話です。
AND検索が必要な場合、全組み合わせを作成してからLoop処理を行えば取り組み易いかもしれません。
ただ、この組み合わせの列挙に関してはちょっと難易度が上がります。
『VBA 再帰処理』をキーワードにNet検索してください。
理解できるようであればその方針ですすめてみてください。

いずれにしても、上記例のように、検索列と検索条件を書き出したシートを準備するなど、
コード外にパラメータとして設定して、そのデータを読み込んで処理する方法がオーソドックスかと思われます。

>それ以降の一連の動きを追加してやるには何か方法がありますでしょうか？
こちらも工夫しだいです。
読み込んだ検索条件を元に、各列に対してAdvancedFilterメソッドやAutoFilterメソッドを使って
抽出したりする方法が考えられます。

または、対象のワークシートに作業列を追加して検索対象列のデータをまとめてしまえば
現状のコード方針でも可能です。
533 A x
533 A y
：
などのように対象データの対象列を別の列にまとめて、その列を検索対象にするわけです。
（検索条件が部分一致条件だと厳しいかもしれませんが）

お礼

回答ありがとうございます。

No2さんのご指摘どおり、私の質問の具体性が足りなかったようです。

基本的にはお二方の回答内容は、まず必要なデータ郡に取り分けて分割処理するのがオーソドックスだということだと受け取れますがあっていますでしょうか？

現状のコードを開示していなかったのも悪かったのですが、実際A列に検索をかけた後、たとえば533の.Row分だけ
Sub FindNum(N As Variant)
.
.
.
Select Case TargetStr
Case "533"
Find533
.
.
End sub
------------------------------------------------------------------
Find533(r As Integer, c As Integer, 他必要変数)

Range(Cells(r, 1), Cells(c, 10)).Copy
Sheet16.Select
Range("A1").PasteSpecial
Application.CutCopyMode = False
Range("1:1").Insert
以下、他列検索ループ印刷プロシージャ呼び出し等

として別のシートにコピーして、C列なりE列なり新たに検索をかけてそれぞれの検索条件を三つ程度まで重ね合わせた結果が反映されて印刷されるようにはしています。

AdvancedFilterメソッドメソッドは知りませんでした。これを使えばかなりダイエットができそうです。ありがとうございます。

ところで私が言いたかったのはどういうことかと言うと、私のコードでは結局Case分岐のところだけが違いそれ以降の処理でも検索文字列だけが違うけれども、一つ目の分岐キーワードとは1対1の関係にありしかもその組み合わせは私が決められないし、指示もできないためそのつど「追加」してやらなければなりません。となると、Select Case~以降のコード自体をコード追加フォームみたいなものをつくり新規の検索文字列1~3、新規の検索結果のプリントアウトフォーマットのシートNoを入力してそれを代入した同様のコードを生成するのが簡単だと考えました。

しかしながら、コード自体の書き込みをしてくれる動きが無ければ私が書くという作業が欠かせなくなってしまいます。そこで、コードフォーマットがあればそれをクラス内に新規で追加してくれるような動作は無いものだろうか？ということでした。