Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(指定なし : 指定なし)
多数の配列処理
投稿日時: 21/12/26 10:51:02
投稿者: manatsu

例えば、UserForm 上の複数の Control をiをループさせ
UserForm1.Controls("TextBox" & i)
のように指示できるように、多数の二次元配列(10個くらいを想定)を操作したいのです。
 
●配列を定義する
Dim arr1 As Variant
Dim arr2 As Variant
(以下略)
 
●配列への格納
Select Case i
  Case 1
    arr1=〇〇
  Case 2
    arr2=〇〇
(以下略)
End Select
 
とやったのですが、配列からの呼び出しもあまりにも冗長になりかっこ悪い。
こういうときのスマートな処理はどうするのでしょうか。よろしくお願いします。

回答
投稿日時: 21/12/26 14:46:44
投稿者: 半平太

すみませんが、何が二次元になっているのですか?
 
典型的な二次元なら縦x横ですが、説明の中にそんな2つの系がない様に思えるのですが。

投稿日時: 21/12/26 17:16:21
投稿者: manatsu

半平太さん
失礼しました
Worksheet を配列にいれるつもりだったので二次元と書いてしまいました。
 
その都度変化する Sheets(1) に設定した AdvancedFilter の Criteria の履歴をつくり、世代管理しようという問題意識です。
 
Sub Sample(i as long)
 Dim rng As Range: Set rng ThisWorkbook.Sheets(1).Range("A1").CurrentRegion
 Select Case i
  Case 1
     arr1 = rng
  Case 2
    arr2 = rng
 《略》
 End Select
End Sub
 
配列の数だけ羅列することになるのでなにかスマートなやりかたがあるんじゃないかというのが
教えていただきたかったのです。
 
 

回答
投稿日時: 21/12/26 19:00:03
投稿者: WinArrow
投稿者のウェブサイトに移動

引数「i」は、世代ということでしょう
 
ヒントのコード
 
 

Type 配列
    Arr1 As Variant
End Type

Dim arr0() As 配列
Dim i As Long

Sub test()
    ReDim arr0(1 To 1)
    For i = 1 To 3
        Range("DATAA").Value = i
        ReDim Preserve arr0(1 To i)
        arr0(i).Arr1 = Range("DATAA").Value
    Next
    Stop
End Sub

回答
投稿日時: 21/12/26 21:14:38
投稿者: simple

横からすみません。

引用:
AdvancedFilter の Criteria の履歴をつくり、世代管理しようという問題意識です。
とのこと。
(1)
なにか配列というものに過大な期待をお持ちのように感じます。
どのようなタイムスパンで世代管理という言葉を使っているのか不明ですが、
変数に持っても、それがずっと使える保証もないですよね。
 
今後とも何度も使う予定のものであれば、
ワークシートに、名前を付けたセル範囲として持つのがよいと思います。
モジュールレベル変数やStatic変数を使うよりも、ワークシートを使うほうが安全だと
マイクロソフト社も言っています。↓参照。
http://web.archive.org/web/20090909235324/http://support.microsoft.com/kb/408871/ja
(米国の有名なアーカイブです、怪しくはありません。時間が少しかかると思います)
 
(2)短期的な、例えば、ブックを開いてから閉じるまでという期間と考える場合、
配列を使うのであれば、こんな使い方ができます。
Sub test1()
    Dim m(1 To 2) As Variant
    m(1) = Range("A1:B3").Value
    m(2) = Range("A11:B13").Value
    
    '使う場面の例
    Range("C1:D3") = m(1)
End Sub
配列mの要素に配列を持たせることもできます。
(こうしたものをジャグ配列(Jag配列)と呼ぶことがあります。ネットで検索してみてください。)
これの難点は、配列だから当然ですが、何番目という指定しかできないことです。
 
(3)別の入れ物もあります。CollectionやDictionaryというものも使えます。
これなら、文字列で指定することも可能です。下記はCollectionの例です。
Sub test2()
    Dim col As New Collection
    col.Add Item:=Range("A1:B3").Value, Key:="範囲1"
    col.Add Item:=Range("A11:B13").Value, Key:="範囲2"
    
    '使う場面
    Range("C1:D3") = col("範囲1")
     'または
    Range("C1:D3") = col(1)
End Sub

投稿日時: 21/12/27 07:10:19
投稿者: manatsu

WinArrowさん
simpleさん
親切で丁寧な回答ありがとうございます。
 
(>simpleさん)世代管理のタイムスパンということでは「短期的=ブック使用中」ということです。
 
Type、ジャグ配列、Collection、Dictionary、いずれもチラリと目にしたことはあるような気がしますが、私のレベルでは…と敬して遠ざけけてきたという感じです。
これを機会に勉強してみるつもりです。今回のお二人に教えていただいたコードもまず解析に時間がかかってしまいそうですが(だから閉じないでおきますが)やってみて、またご報告いたしますのでよろしく。
 

回答
投稿日時: 21/12/27 15:43:38
投稿者: mattuwan44

>Worksheet を配列にいれるつもりだったので二次元と書いてしまいました。
 
シートが二次元ならば、ブックは三次元です。
 
ただ、この場合は、ジャグ配列というものが該当するかも知れません。
 

Sub test()
    Dim vvv() As Variant
    Dim i As Long
    
    ReDim vvv(1 To Worksheets.Count)
    
    For i = 1 To UBound(vvv)
        vvv(i) = Worksheets(i).Range("A1").CurrentRegion.Value
    Next
    
    Stop
End Sub

 
ストップで止まったら、ローカルウィンドウで変数の中身を確認してください。
頭の「+」をクリックで展開できます。

回答
投稿日時: 21/12/27 15:56:55
投稿者: simple

>AdvancedFilter の Criteriaの履歴
ということだそうですね。
どの程度の話なのか、単なる一例ということかもしれませんが、
ちょっと考えが足りなかったので、補足しておきます。
 
私の案では、値を配列にもつこととしていましたが、
値では足りないことがありますね。
・ ="=文字列"といった、完全一致を目的としたものや、
・ 数式による条件判定(見出しを""にして行うもの)などでは、
値だけでなく、数式が必要になります。
 
そう考えると、やはり、シート上に数式を含む条件を保持しておき、
・Foramulaの文字列を持つか
・そのセル範囲文字列を持つか
・Rangeオブジェクトそのものを保持する
必要があります。
例えば、最後のものであれば、

    Dim m(1 To 2) As Range
    Set m(1) = Range("A1:B3")
のような書き方になりますね。(配列を静的なものにするか、動的なものにするかは別として)
 
補足でした。

投稿日時: 21/12/28 17:17:48
投稿者: manatsu

みなさんに教えていただいたコードを勉強してみました。
すこしは理解できたつもりなで、その方向でやれそうかなといったんは思ったのですが。
simple さんが紹介してくださったマイクロソフトのアーカイブに「Public 変数がアプリケーション終了時まで有効であることを期待する VBA マクロの実装は、推奨されません」とありました。
ユーザーフォームから入力して Criteria を作成するのですが、履歴といってもいま考えているのはせいぜい5または10世代前くらいまでを遡るというものです。simple さんが示唆されていた、Worksheet を一枚用意して10個のテーブルをおけば、ブックを閉じても次にひきつげるし、その方法でやってみようと思っています。私の質問で示しコードが配列だったので、そちらにそった回答を皆さんからいただいたのに、なんだか申し訳ないのですが。
私の中に、Worksheet に記述するより 配列などで処理したほうがかっこいい、という先入観がありました。
半平太さん、WinArrowさん、simpleさん、mattuwan44さん、ありがとうございました。
またよろしくお願いします。