VBA配列の使い方について

前へ 次へ
このQ&Aのポイント
  • VBA配列の使い方について教えてください。配列を広げ追加したい方法や繰り返しを使わずに一気に書き込む方法、一部をクリアする方法について教えてください。
  • VBAで配列を使う方法について詳しくお教えください。配列の追加、一括書き込み、部分的なクリアなどの操作方法を教えていただきたいです。
  • VBAの配列について質問です。配列を広げて追加する方法、繰り返しを使わずに一気に書き込む方法、一部分をクリアする方法などの操作方法について教えてください。
回答を見る
  • ベストアンサー

VBA 配列について

配列の使い方について教えてください １つの配列をどんどん追加したりしたいので１つの mybox　で追加していきたいと思っています。 （下記コードが実現できればと思います。） (1)配列を広げ追加したい (2)繰返しを使わず一気に書き込みたい (3)一部をクリアしたりしたい のですがよろしくお願いします。 Sub Macro1() Range("A1").Value = "A" Range("A2").Value = "B" Range("A3").Value = "A" o = Range("A1").End(xlDown).Row mybox = Range(Cells(1, 1), Cells(o, 1)).Value 'myBox(1,1)=A 'myBox(2,1)=B 'myBox(3,1)=A ←このような表示になります。 '------------------------------------------------- '(1)配列を広げ追加したい ReDim Preserve mybox(o, 2) For i = 1 To UBound(mybox) If mybox(i, 1) = A Then mybox(i, 2) = 0 Else mybox(i, 2) = 1 End If Next i '------------------------------------------------- '(2)(1)をＣ列に「myBox(?,2)を「繰返しを使わず一気に」書き込みたい 'Transposeは限界（65536個）を超えるので使えません。 Range(Cells(1, 3), Cells(UBound(mybox), 3)) = mybox '(3)配列myBox(？,1)は残したままmyBox(？,2)はクリアにしたい End Sub

  • epko
  • お礼率83% (5/6)

質問者が選んだベストアンサー

  • ベストアンサー
noname#212058
noname#212058
回答No.1

質問者さんが求める機能を満たすことはできません。 VBA はプログラム言語としては機能的に弱い言語です。 厳しい機能制限の中、『理想』と『現実』の落としど ころを探って行く必要があります。 (1)(3) ⇒ 後からリストの拡張・縮小をしたい場合は、配列では 　 なくCollection を使用するのが常套です。(2次元配列 　 の場合は、入れ子にするなり工夫が必要になりますが) 　 ReDim を使うか Collection に詰めなおすか、どちら 　 が良いかは、リスト拡張の頻度やデータ量、処理方式 　 によって違うでしょう (2) ⇒ 繰り返しを全く使用せずに実施するのは無理です。 　 セルに貼付可能な形の配列に詰めなおすしかありません。 　 パフォーマンスを気にされている場合は、セルに For で 　 回して一つ一つ値を書き込むよりも、配列間の値の入れ 　 替えして一気に貼り付けたほうが格段に高速ですので、 　 ここらへんが『落としどころ』になるかと思います。 　 いちいち For 文書くのが面倒だという話であれば、自分 　 で汎用関数を作っておけば良いかと。 VBAがどうしても質問者さんの要求に応えられないのであれ ば、VBAを捨てて他の高機能言語からEXCEL を操作すると いう解決策もあるかもしれません。

epko
質問者

お礼

配列を追加する方法はないようですね。 きれいにできればと思ったのですが、未熟ながら コードを書いていこうと思います。 ありがとうございました。

その他の回答 (1)

  • mindatg
  • ベストアンサー率48% (110/227)
回答No.2

方法はいくつか挙げられます (1)について 横に広がる幅がある程度決まっている場合 　・クラスまたは構造体を作る 　　　内容もほぼ決まっているなら構造体作って、構造体のコレクション作ってそこにぶち込めば操作は楽かと 　　　インターフェイスを作るのが手間ですが、作ってしまえばメンテナンスも楽です 　・事前に配列を確保してしまう 　　　配列の確保だけでメモリを食います。まだコレクションの中にコレクションを作る方がマシかも 横に広がる幅が不定、または大きい場合 　データ構造自体に難があると思います。 (2)について 　Object型の配列変数を作り、そこに(1)の値をループでセットし 　そのRangeオブジェクトをシートに渡せば良いです。 　ループを使わずに、となると最初からObject型で扱えば書き込み先のサイズと一致していたら問題は出ないはずです (3)について 　(1)のクラス・構造体・コレクションでループで特定の要素のみクリア・・で済みます 質問の(1)にあるようなコード（配列を広げた際に条件判定して値を入れている）を見るに データ用のクラスを作成するのが一番良いかと

epko
質問者

お礼

回答ありがとうございます。 助言を元に作成していこうと思います。 とても参考になりました。

  1. カテゴリ
  2. [技術者向] コンピューター
  3. プログラミング・開発
  4. Visual Basic

関連するQ&A

  • エクセルVBAで配列の追加

    エクセル2000です。 1行4列のセル範囲のデータを配列に取り込んで、後から別の1行4列のセル範囲のデータを配列に追加し、2次元配列として吐き出そうと思います。 最初の範囲がA1:D1、追加範囲がA4:D4とした場合、こんな不細工なコードになってしまいました。 これでも動きますが、どう修正すべきでしょうか？ Sub test() Dim myAr() myAr = Application.Transpose(Range("A1:D1").Value) ReDim Preserve myAr(1 To 4, 1 To 2) For i = 1 To 4 myAr(i, 2) = Cells(4, i) Next i Range("F1").Resize(UBound(myAr, 2), UBound(myAr, 1)).Value = Application.Transpose(myAr) End Sub

  • EXCEL VBAの配列でわかりません。

    こんなコードがあるのですが、最後の他のシート（作業中シート）に書き込もうとするとエラーになってしまいます。”Sheets("作業中").”を抜くと同じシートに結果は返ってくるのですが…。コードの内容は、ある範囲のある列から空白ではないセルを探し出してその行のデータを配列で汲み取り、他のシートに一括で洗い出すというものです。 Sub 作業中() Dim myRow As Long Dim Data As Variant Dim WC() As Variant Dim WCE() As Variant myRow = Range("H1").CurrentRegion.Rows.Count Data = Range("H1:M" & myRow).Value For i = 1 To myRow If Data(i, 5) <> "" Then a = a + 1 Else b = b + 1 End If Next ReDim WC(a) ReDim WCE(b) c = 0 d = 0 For i = 1 To myRow If Data(i, 5) <> "" Then WC(c) = Range("H" & i & ":K" & i).Value c = c + 1 Else WCE(d) = Range(Cells(i, 8), Cells(i, 11)).Value d = d + 1 End If Next For i = 0 To a Range(Cells(i + 1, 15), Cells(i + 1, 18)).Value = WC(i) Next For i = 0 To b Range(Cells(i + 1, 19), Cells(i + 1, 22)).Value = WCE(i) Next e = Range(Cells(1, 15), Cells(a, 18)).Value Sheets("作業中").Range(Cells(1, 1), Cells(a, 4)).Value = e End Sub ちなみに同じシートから↓のコードを実行するとうまくいきます。 なぜ～？？わからな～い？？おしえてくださーい！！ Sub test() Dim a As Variant a = Range("H1:K4") Sheets("作業中").Range("N1:Q4") = a End Sub

  • エクセルVBAで配列内に空白データを入れる場合

    エクセル2000です。 ある大きな表のうち、0値を非表示ではなく完全に削除するために以下のようなマクロを書いてみました。 一旦配列に取り込んでいるのは高速化のためです。 これで見た目には目的を達しているのですが、実際には0値が長さ０の文字列に変わっただけで完全な空白にはなっていません。 配列にとりこまず、セルをループして0値のセルをClearすれば解決するのはわかるのですが、ほかにいい方法はないでしょうか？ Sub TEST0値() Dim myAr With ActiveSheet x = .Range("A" & Rows.Count).End(xlUp).Row myAr = .Range("A4:AP" & x).Value For i = LBound(myAr, 1) To UBound(myAr, 1) For n = LBound(myAr, 2) To UBound(myAr, 2) If myAr(i, n) = 0 Then myAr(i, n) = "" Next n Next i .Range("A4:AP" & x).Value = myAr End With End Sub

  • エクセルVBAでTransposeの不思議

    MS Officeのエクセル2000です。 下記Sub test01はRange("A1:I1")に文字列を入力し、一旦配列に取り込んでからワークシートに貼り付けるものです。 試験用のコードですので意味はありません。 このコードで255文字まではまったく問題はありません。 ところが、256文字以上の場合、横に貼り付けは問題ないのですが、 Transposeで縦に変換すると型が一致しません。(Error 13)となります。 どうしてでしょうか？ 試行錯誤の結果、Sub test02のように一旦横に貼ったデータをコピーしてTransposeして貼り付けるのは大丈夫のようですので不思議でしょがありません。 またこの方法は列数256より要素が多い配列には使えないので解決策にはなりません。 ご教示くださいませ。 Sub test01() Dim myAr As Variant Dim i As Integer, n As Integer n = 256 '文字数 With ActiveSheet .UsedRange.ClearContents For i = 1 To 9 .Cells(1, i).Value = Application.Rept(Left(.Cells(1, i).Address(0, 0), 1), n) Next myAr = .Range("A1:I1").Value .Range("A3").Resize(, UBound(myAr, 2)).Value = myAr .Range("A5").Resize(UBound(myAr, 2)).Value = Application.Transpose(myAr) '256文字の場合エラー End With End Sub Sub test02() Dim myAr As Variant Dim i As Integer, n As Integer n = 256 '文字数 With ActiveSheet .UsedRange.ClearContents For i = 1 To 9 .Cells(1, i).Value = Application.Rept(Left(.Cells(1, i).Address(0, 0), 1), n) Next myAr = .Range("A1:I1").Value .Range("A3").Resize(, UBound(myAr, 2)).Value = myAr .Range("A3").Resize(, UBound(myAr, 2)).Copy .Range("A5").PasteSpecial Paste:=xlValues, Transpose:=True '256文字の場合もOK Application.CutCopyMode = False End With End Sub

  • 配列に格納したデータを指定行以下に転記する方法

    excel2000を使っています。 以下のコードだと最終行にデータが転記されます。これを４行目に確定して、転記したいのです。常に４行目つまりＡ列４行目以下に上書きしたいのです。 その場合コードをどのように変更すべきでしょうか？ Sub 配列() With ActiveSheet ' 配列に格納 --------------------------- Dim i As Integer Dim LastRow As Long Dim SaleAry As Variant ' 配列に格納 --------------------------- SaleAry = Array(.Range("t4"), .Range("e5"), .Range("ｇ5"), .Range("o5")) End With ' 転記 --------------------------- With Worksheets("daityou") LastRow = .Range("A65536").End(xlUp).Row For i = 0 To UBound(SaleAry) .Cells(LastRow + 1, i + 1).Value = SaleAry(i) ' Next i End With Set SaleAry = Nothing End Sub

  • エクセルVBAで配列？

    以下は、文字列"t", "e", "s", "t"を配列に取り込み、セルに表示する例ですが、 ar = Array("t", "e", "s", "t") なら作動しますが、セル範囲から取り込もうと、 ar = Range("A1:D1").Value とするとエラーになります。 どうしてでしょうか？ Sub test() Dim ar As Variant Dim n As Integer ar = Array("t", "e", "s", "t") 'ar = Range("A1:D1").Value For n = LBound(ar) To UBound(ar) Cells(n + 1, 5) = ar(n) Next n End Sub

  • VBA　For Eachでセル内の文字列を一個ずつ取り出すには

    エクセル2000です。 たとえばA1セル内の文字列を一個ずつ取り出す場合、 Sub test01() For i = 1 To Len(Range("A1").Value) Cells(i, "B").Value = Range("A1").Characters(i, 1).Text Next End Sub このように最初から最後の文字まで何番目で指定することはわかるのですが、これをFor　Each で回すにはどうしたらよいでしょうか？ （⌒o⌒)？　お教えください。 Sub test02() For Each ch In Range("A1").Characters i = i + 1 Cells(i, "B").Value = ｃｈ Next End Sub ではエラーになります。

  • ２つのVBAを組み合わせる方法

    お世話になります、２つのVBAを組み合わせる方法で迷っています。 １つ目が Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Long, k As Long, myNum As Long If Intersect(Target, Range("C1,B9:B39")) Is Nothing Or Target.Count > 1 Then Exit Sub Application.EnableEvents = False With Target If .Column = 3 Then myNum = WorksheetFunction.Max(Range("B9:B39")) If IsDate(.Value) Then For i = 9 To 39 If Cells(i, "A").Value = "" Then Cells(i, "B").Value = "" Else Cells(i, "B") = myNum + i - 8 End If Next i End If Else i = .Row If .Value = "" Then Range(Cells(i + 1, "B"), Cells(39, "B")).ClearContents Else For k = i + 1 To 39 If Cells(k, "A").Value = "" Then Cells(k, "B").Value = "" Else Cells(k, "B") = Cells(k - 1, "B") + 1 End If Next k End If End If End With Application.EnableEvents = True End Sub です。 ２つめが Private Sub Worksheet_Change(ByVal Target As Range)　 Application.EnableEvents = True If Intersect(Target, Range("R8:R38")) Is Nothing Then Exit Sub Application.EnableEvents = False Range(Cells(Target.Row, 18), Cells(39, 18)).Value = Target.Value Application.EnableEvents = True End Sub です。２つのPrivate Sub Worksheet_Change(ByVal Target As Range)イベントのVBAですが、どのようにして組み合わせれば良いのでしょうか？

  • 配列のメリットは？

    下記のコードは同じ動作をします。 Sub 普通() Dim moji1 As String Dim moji2 As String moji1 = Range("a1").Value moji2 = Range("a2").Value MsgBox moji1 & moji2 End Sub --------------------------------------------- Sub 配列() Dim moji(2) As String moji(1) = Range("a1").Value moji(2) = Range("a2").Value MsgBox moji(1) & moji(2) End Sub こういう場合は配列を使ったほうがいいのでしょうか？ それとも配列は避けたほうがいいのでしょうか？

  • [VBA] 連想配列について

    こちらの識者の方々にはいつもお世話になっています。 VBAの質問です。 環境は下記になります。 OS=windowsXP SP3 Office=Excel2003(11.8347.8403) SP3 データ量が多いのでVLOOKUPの処理を連想配列を使ってやりたいのですが、できなくて困っています。 言葉では説明しずらいので、やりたいことのコードを提示します。 :Sheet2 品名　　1月　　2月 りんご　2　　　3 ばなな　4　　　5 下記、VBAコード ---------------------------------------------------- Option Explicit Sub test() 　Dim oDict As Object 　Dim Ary As Variant 　Dim EndRow As Long 　Dim i As Long 　Ary = Sheets("Sheet2").Range("A2:C3").Value 　Set oDict = CreateObject("Scripting.Dictionary") 　For i = 1 To UBound(Ary) 　　oDict(Ary(i, 1)) = Ary(i, 2) 　　oDict(Ary(i, 2)) = Ary(i, 3) 　Next i 　With ThisWorkbook.Sheets("Sheet1") 　　EndRow = .Cells(Rows.Count, 1).End(xlUp).Row 　　Ary = .Range("A1:Y" & EndRow).Value 　　For i = 2 To EndRow 　　　Ary(i, 24) = oDict(Ary(i, 1)) 　　　Ary(i, 25) = oDict(Ary(i, 2)) 　　Next i 　　.Range("A1:Y" & EndRow) = Ary 　End With 　MsgBox "done" End Sub ---------------------------------------------------- というコードなのですが、 　For i = 1 To UBound(Ary) 　　oDict(Ary(i, 1)) = Ary(i, 2) 　　oDict(Ary(i, 2)) = Ary(i, 3) 　Next i が、oDict(Ary(i, 1)) = Ary(i, 2)だけ配列に格納される状態です。 指定の文字列(Key)に対応するItemを返すのが連想配列だと思うのですが、上記コードで、 oDict(Ary(1, 1))=2 oDict(Ary(2, 1))=4 oDict(Ary(1, 2))=3 oDict(Ary(2, 2))=5 という配列に格納されない理由がわかりません。 Scripting.Dictionaryで辞書に格納できるデータになにかしらの制限があるのでしょうか？ 質問に不備不足等ございましたらご指摘ください。 ご面倒お掛けしますがよろしくお願いします。

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する