Excel VBA 二次元配列

Question

値の貼り付けを各シートに1行ずつ貼り付けをしていて(Do Loop文を使用して繰り返し文でセルに値をはりつけています。)

遅い原因になっているので
二次元配列を使用して高速化したいのですが書き方がわかりません。

１０００行あります。

どうすれば二次元配列に値を記憶させるのでしょうか・・・？

質問の仕方も初心者でわかりづらくなってしまい申し訳ございません。

追記
テキストファイルからinstr関数を使用したあと文字の抽出mid関数などを用いてString文を読み取りそれをセルに挿入します。

Comments

[ttyp03]

まずは現状のコードを提示してください。

[papinianus]

やりたいことを平たく説明していただけませんか？1セルずつ、1行ずつコピーするのは必須でしょうか？

[hatena19]

せっかくコードを追記したのなら、中途半端なのものでなく、
テキストファイルを読み込む部分のコード、
それをシート上のセルへ代入する部分のコード、
を含む動作するコードを提示してください。
あと、コード部分は、選択してから<code>ボタンをクリックしてください。

Answer 1

テキストファイルからinstr関数を使用したあと文字の抽出mid関数などを用いてString文を読み取りそれをセルに挿入します。

テキストファイルと一括りでいっても様々な種類があるんですが、
とりあえずエクセルでもテキストファイルが開けます。
単にエクセルでテキストファイルを開くと、どうなりますか？

それから、エクセルには、「テキストファイルのインポート」という機能も
付いてますので、そちらで上手くエクセルに取り込めないでしょうか？

それで上手く行くようなら、その操作をマクロ化したほうが
コードが簡単になります。

Comments

[mmm_x]

その点に関してですが、とても良案ですが、今回は二次元配列で値を挿入が絶対の条件としたいです・・・。
.log のテキストファイルを読み取っています。

[hatena19]

そのテキストファイルのデータ例も出してもらえますか。
csvフォーマットなのか、そうでないのか、によっても方法が異なりますので。

[mmm_x]

一応、テキストファイルのデータ抽出には成功しています。
csvフォーマットではないです。
. . 値 "AAAA"をダウンロードしています

Answer 2

配列を使って高速化したいということですね。

とりあえず、下記を理解して、自分でできるとこまでコードを書いてください。
話はそれからです。

VBA 高速化テクニック ～ 配列とセルの相互転記 - t-hom’s diary

---

提示のコードをみても、
テキストデータがどのようなもので、それをどのようにしたいのか結局分かりませんので、
概要を回答しておきます。

テキストファイルを1行ずつではなく、一気に読み込みます。
（1行ずつだと何行なのか分からないので、二次元配列のサイズを決定できないため）
FileSystemObjectのReadAllを使うといいでしょう。

読み込んだテキストを、Split関数で改行で分割して配列にします。
Splitの配列は一次元配列なので、
別にSplitの要素数×2列の二次元配列を確保します。

Splitの一次元配列をループで1行ずつ読み込んで、必要データを取り出して、二次元配列に格納します。

二次元配列を、シートの指定範囲に代入します。

上記の方法にすれば、シートへのアクセスは1回ですみ、それ以外はメモリ上の操作ですので高速になります。

---

コメントにテキストデータ例が出たので、そこから必要データを取り出してセルに出力するコード例

テキストファイル　C:\test\test.log
データ

. . 値 "AA"をダウンロードしています
. . 値 "AB"をダウンロードしています
. . 値 "AC"をダウンロードしています
. . 値 "AD"をダウンロードしています

値 " と " の間のテキストを取り出して、A2セル以降に出力するコード例

Public Sub テキストインポート()
    Dim FSO As Object, Log As String
    Set FSO = CreateObject("Scripting.FileSystemObject")
    With FSO.GetFile("C:\test\test.log").OpenAsTextStream
        Log = .ReadAll 'Log変数に全文読み込み
        MsgBox buf
        .Close
    End With
    Set FSO = Nothing
    
    Dim ar As Variant
    ar = Split(Log, vbCrLf) '改行で分割して一次元配列に変換
    
    For i = LBound(ar) To UBound(ar)
        ar(i) = Split(Split(ar(i), "値 """)(1), """")(0) '値 " と " の間を取り出す
    Next
    
    '一次元配列を2次元配列に変換してA2以降に出力
    Range("A2").Resize(UBound(ar) + 1).Value = WorksheetFunction.Transpose(ar)    
End Sub

Comments

[mmm_x]

かなり近い内容のページでした！
しかしこのようなサンプルコードを元に作成
したところ配列の次元が一致していませんとでて、唸っております。

[hatena19]

その作成したコード全体を回答に追記してください。
一部分ではどこで間違えているか、わかりません。

[mmm_x]

追記しました。かなり変数をしようしています＞＜

[mmm_x]

テキストファイルを一行ずつ読み込む場合二次元配列は使用できないということでしょうか？

[mmm_x]

. . 値 "AAAA"をダウンロードしています　
テキストファイル内に書かれている文ですが
. . 値 "AAAA"をダウンロードしています
が1000行ほど羅列してあり
"AAAA"を抜きだしセルにAAAAを入れようとしています。

[hatena19]

> テキストファイルを一行ずつ読み込む場合二次元配列は使用できないということでしょうか？
そうするより一気に読み込んで、Splitで行分割すれば、行数を取得できますので、その方が効率的だという意味です。

データ例をもう少し実際のものに近いいくつかの例を出してください。

値 " と " の間を抜き出したいということですか。値の部分は一種類で固定ですか。
また、抜き出すデータは、1行につき1つですか。

[mmm_x]

. . 値 "AA"をダウンロードしています
. . 値 "AB"をダウンロードしています
. . 値 "AC"をダウンロードしています
. . 値 "AD"をダウンロードしています
..
..
..
と続いており
instrで". . 値"と"をダウンロードしています"
を検索し、Mid関数で一行ずつAA,AB,ACを抽出します。
セルには　A列の2行目にAA A列の3行目にABという風に
A列固定で下に値を追記していくような感じです。

[hatena19]

ということは印ボートするデータは1列(A列のみ)ということですね。
なら、一次元配列でいいのでは。二次元配列する必要はないです。

ただし、一次元配列はセルに代入すると横になるので、WorksheetFunction.Transpose で二次元配列に変換すれば縦に代入できます。

[hatena19]

上記のコード例を回答に追記しておきました。

[mmm_x]

ありがとうございます。抽象的な質問が多く困らせてしまいすみませんでした。お陰様で助かりました。

Answer 3

質問者さんが感じてますように、VBAのセルへの書き込みはかなりおそいです。

元のコードとは離れてしまいそうですが、
100列、1000行の範囲にセル番地を書き込む処理の場合の例を提示しておきます。
手元の環境だとtest1に比べてtest2は10分の1程度の時間ですみます。

' 普通にループ
Sub test1()
    Dim startTime As Double
    Dim processTime As Double
    Dim row As Long
    Dim col As Long
    
    ActiveSheet.Cells.Clear
    startTime = Timer
    
    For row = 1 To 1000
        For col = 1 To 100
            Cells(row, col).Value = Cells(row, col).Address(False, False)
        Next col
    Next row
    
    processTime = Timer - startTime
    MsgBox "Time: " & processTime & " sec."
End Sub

' 配列に入れて書き込みを1回にした場合
Sub test2()
    Dim startTime As Double
    Dim processTime As Double
    Dim row As Long
    Dim col As Long
    
    ActiveSheet.Cells.Clear
    startTime = Timer
    
    ' 配列をRedimで1000 x 100のサイズに再定義
    Dim arrayData() As String
    ReDim arrayData(1 To 1000, 1 To 100)
    
    For row = 1 To 1000
        For col = 1 To 100
            arrayData(row, col) = Cells(row, col).Address(False, False)
        Next col
    Next row
    
    ' 1000行、100列のRangeオブジェクトを取得してArrayを代入
    Range(Cells(1, 1), Cells(1000, 100)).Value = arrayData
    
    processTime = Timer - startTime
    MsgBox "Time: " & processTime & " sec."
End Sub

後はすでにやっているかもしれませんが、
Application.ScreenUpdating = False しておくとかでしょうか。

Comments

[mmm_x]

Dim arrayData() As String
ReDim arrayData(1 To 1000, 1 To 100)

For row = 1 To 1000
For col = 1 To 100
arrayData(row, col) = Cells(row, col).Address(False, False)
Next col
Next row

' 1000行、100列のRangeオブジェクトを取得してArrayを代入
Range(Cells(1, 1), Cells(1000, 100)).Value = arrayData

この部分ですね、ありがとうございます。

Answer 4

Excel VBA 二次元配列

私のお勧めは、Dictionaryオブジェクト（連想配列）ですが、二次元配列ならこんな感じで。

  '二次元配列
  Dim strName() As String
  ReDim strName(N, N)
  '値を入れていく
  strName(0, 0) = Worksheets("Sheet2").Cells(2, 2).Value

お勧めはDictionaryオブジェクト（キーの名称で値を取得出来ます）

  Dim objName As Object
  Set objName = CreateObject(“Scripting.Dictionary”)
  objName.Add "キーの名称", 値

  '取り出しループ
  For Each Var In myDic
    str = str & Var & " : " & objName.Item(Var) & vbCrLf
  Next Var