スキルアップ・問題解決なら、モーグにおまかせ！

  
以下の記述で、エクセルファイルに、他のエクセルファイル又はテキストを開いて得た情報を転記するためのマクロを組んでいますが、テキストデータを配列に格納するところでインデックスが有効範囲にありませんとエラーが出ますが理由がわかりません。
 
どなたか教えて頂けませんでしょうか。
 
よろしくお願いいたします。
 
　 Dim r1 As Long, r2 As Long, r3 As Long, r4 As Long
    Dim c1 As Long, c2 As Long, c3 As Long, c4 As Long
     
    Dim buf As String
    Dim tmp As Variant, ary As Variant
    Dim extension As String
     
    extension = ThisWorkbook.Sheets(1).Cells(3, 4).Value
     
    Dim LastRow As Long
    Dim MaxCol As Long
    Dim iLine
     
    If extension = ".xlsx" Or extension = ".xlsm" Or extension = ".xls" Then
     
        Workbooks.Open ThisWorkbook.Path & "\対象ファイル\" & ThisWorkbook.Sheets(1).Cells(3, 5).Value
        Set Main_File = Workbooks(ThisWorkbook.Sheets(1).Cells(3, 5).Value)
        LastRow = Main_File.Sheets(1).Cells(1, 1).End(xlDown).Row
        MaxCol = Main_File.Sheets(1).Cells(1, 1).End(xlToRight).Column
         
        For r1 = 1 To LastRow
             
            For c1 = 1 To MaxCol
             
                ThisWorkbook.Sheets(2).Cells(r1, c1).Value = Main_File.Sheets(1).Cells(r1, c1).Value
                 
            Next c1
         
        Next r1
         
        Worksheets("sheet2").Name = ThisWorkbook.Sheets(1).Cells(3, 2).Value
         
    ElseIf extension = ".csv" Then
         
        Open ThisWorkbook.Path & "\対象ファイル\" & ThisWorkbook.Sheets(1).Cells(3, 5).Value For Input As #1
         
             Do Until EOF(1)
              
              Line Input #1, buf
             
              tmp = Split(buf, ",")
               
                r1 = 0
               
                 For c1 = 0 To UBound(tmp)
                  
                    ary(r1, c1) = tmp(c1)←エラーが発生する場所
                     
                 Next c1
                  
                 r1 = r1 + 1
              
             Loop
         
        Close #1
         
    End If

ステップ実行で、変数(tmp,BUf)の内容を確認してみてください。

WinArrow様
 
お返事ありがとうございます。
 
下記頂いた内容で確認してみました。
 
BufはCSVのデータが入っておりBufはn行目のデータをSplitで分割したものであることはわかるのですがそれで何故エラーが発生するのかは理解できませんでした。
 
もう少し詳細を教えて頂けませんでしょうか。
 
よろしくお願いいたします。

通常、デバッグは、
エラーになった時点の変数に入っているデータ（値など）を確認し、
意図した内容になっているかを検証していきます。
このような作業を、その前、その前とさかのぼって行きます。
面倒でも、このようなステップは省略できません。
 
回答者は、あなたのＰＣの画面は見えませんから、
このステップを質問者にお願いします。
意味がないとお考えならば、これ以上のかかわりは、パスします。
 
 

分かたような気がします。
 
変数：Ary
が配列になっていないのではないでしょうか？

投稿いただいたのは、実際に動かしたものですか？
「型が一致しません」というエラーになるはずなんですが。

Sub test2()
    Dim ary As Variant
    ary(0, 0) = 1
End Sub

# 横からすみません。
 
CSVファイルを開いて、特定のシートに転記するのであれば、
「外部データの取り込み」（をマクロ記録して作ったマクロ）を使って
特定のシートに直接読み込んだほうが楽じゃないでしょうか。
 
通常のブックのほうも、セルをひとつひとつ処理せずに、
まとめてコピーペイストしたほうが良いと思います。

勝手に推測します。
CSVデータを配列(ary：但し、「ary」が配列になっていない）に取込んだあと
どのようにするのかまでコードは掲示されていないし、説明もない。
しかし、
その配列をシートに転記すると考えた場合、
simpleさんのレスにもありますように、別の方法もあります。（多分、そちらの方が早い）
 
aryを動的配列にする方法で、コードを検証すると、
(1)どこで、動的配列にするか？
(2)
>r1 = 0
は、意図した配列にならない。と思います。
 

動的配列を使う例です。下記のコードを参考にしてください。
 
動的配列は、最後の次元に沿ってしか大きさを調整できませんから、
aryは、縦に項目数、横に元データの行数をとって保存し、
最後に、Application.Transposeで行と列を転置させます。
 

Sub test()
    Dim fname$
    Dim ary(), tmp
    Dim r&, c&, k&, num&
    Dim buf$

    fname = "test.csv"          '■　パスは適宜修正のこと。
    Open fname For Input As #1
    Do Until EOF(1)
        k = k + 1
        Line Input #1, buf
        tmp = Split(buf, ",")
        If k = 1 Then num = UBound(tmp) + 1 '要素の個数
        
        '配列再定義
        r = r + 1
        If k = 1 Then
            ReDim ary(1 To num, 1 To r)
        Else
            ReDim Preserve ary(1 To num, 1 To r)
        End If
        
        '配列への入力
        For c = 0 To UBound(tmp)
            ary(c + 1, r) = tmp(c)
        Next
    Loop
    Close #1
    ary = Application.Transpose(ary)
    Worksheets("Sheet1").Range("A1").Resize(UBound(ary, 1), UBound(ary, 2)) = ary
End Sub

Sub Macro1()
    Dim filename As String
    filename = "D:\test.csv"                ' ■要修正
    With Worksheets("Sheet1").QueryTables.Add(Connection:= _
        "TEXT;" & filename, Destination:=Range("$A$1"))  'シート、セル位置の指定可
        .RefreshStyle = xlInsertDeleteCells
        .TextFilePlatform = 932             ' 文字コード指定
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
        .Delete
    End With
End Sub

訂正：
QuaryTableを使ったコードで、読み込み位置の指定にワークシート指定が漏れていました。
Worksheets("Sheet1").　を追加してください。
 
先走って、リカバリー策を勝手に書き連ねましたが、
皆さんの指摘も踏まえ、改めて確認することをまとめると、次のようなことです。
 

・ローカルウインドウで、aryはどのようなものになっているか。
  配列になっているかどうかは、ローカルウインドウで一目瞭然、
　大きさ（次元）、それらの要素内容も確認できます。
・同様にtmpの内容もローカルウインドウで確認してください。
・r1,c1の値はなんですか？
・イミディエイトウインドウで
  ?tmp(c1)
  として何が返るか確認してください。
  エラーになるならなぜか、と確認していきます。
・その値が返っているなら、  
  ary(r1,c1) = その値
  をイミディエイトウインドウで実行してみます。どうなりますか？

Simple様
WinArrow様
 
お世話になります。
お返事が大変遅れてしまい申し訳ございません。
 
大変ご丁寧に解説頂き有難うございます。
 
私自身配列についてまるで理解ができていないため、変なコードを記述してしまいお手間をかけさせてしまいました。
 
 
頂いたコメントの内容を確認させて頂き、いろんなパターンで処理できるように試行錯誤してみたいと思います。
 
また内容について不明点があれば質問させて頂くこともあるかと存じますが、引き続きよろしくお願い致します。
 
有難うございました！

丁寧な返事をいただきました。
それはそれでありがたいのですが、事実関係をもう少し書いていただくとありがたい。
 
・提示されたコードと実際にエラーが発生するコードには、差異があったのか、
・それとも、提示したコードで実際にそのエラーが出るのか、
などについて、コメントしてください。
急ぎませんが。

Simple様
 
お世話になります。
 
いずれの方法でもきちんとデータを作成することができました！
有難うございます！！
 
ただ、
 
動的配列は、最後の次元に沿ってしか大きさを調整できませんから、
aryは、縦に項目数、横に元データの行数をとって保存し、
最後に、Application.Transposeで行と列を転置させます。
 
この部分の意味がよく理解できませんでした。
 
配列の最大値は列の数量の最大値で決まることから、一般的にデータ数の
多い行を配列の列側（2次元側）に持ってきて配列を作成するという理解で
よろしいでしょうか。
 
最後にこの部分を確認させて頂きたいです。
 
よろしくお願いいたします。

https://koukimra.com/archives/1365
などが参考になるかもしれません。
 
(1)
CSVの項目数が3つとします。
動的配列aryを作り、既存のデータをこわさずに、データを追加していこうとして、
ary(0 To 0 , 0 To 2)
ary(0 To 1 , 0 To 2)
ary(0 To 2 , 0 To 2)
・・・・
と行数を増やしていければよいですが、こうしたことはできません。
動的配列は、最後の次元に沿ってしか増やしていけない決まりになっています。
（理由は下記(*参考））
 
(2)したがって、
ary(0 To 2 , 0 To 0)
ary(0 To 2 , 0 To 1)
ary(0 To 2 , 0 To 2)
ary(0 To 2 , 0 To 3)
というように、最後の次元の要素数を増やすように配列を拡大していかざるを
得ないのです。(列を追加していくイメージ）
 
(3)そして、最後に
ary(0 To 2 , 0 To 3)をary(0 To 3 , 0 To 2)に転置させれば、
効果としては、行を増やしていったのと同じことになるわけです。
 
とまあ、こういう話です。
 
（*参考）
これは、次のような二次元の配列
[ 1 4 7
  2 5 8
  3 6 9 ]
を保持しようとすると、メモリーというものは一次元なので、
メモリーには、
1 2 3 4 5 6 7 8 9 と並べるきまりになっているのです。(言語によって異なります *2 )
したがって、
・このあとに、10 11 12 のように列を増やすのは簡単（メモリの最後に追加して
  いくだけでいい）ですが、
・行を増やそうとすると、大変です。
  3と4の間、6と7の間になどに間隔をあけて、そこに挿入していかないといけないので、
  負荷がかかります。
・そのため、列を増やすようにしかできませんよ、という決まりにしたのです。
 
(*2)これはプログラム言語によって異なります。
C言語などは、VB6と異なり、行方向を優先することになっています。
つまり、1 4 7 2 5 8 3 6 9 ...と並べることになっていると思います。

Simple様
 
申し訳ございません。
最後のメッセージを見落としておりました。
 
最初に頂いたご指摘については、Simple様がおっしゃられていたとおり
型が一致しません。というエラーが発生しておりました。
 
申し訳ございません。
 
何度も修正する中で、インデックスが有効範囲にありません、オーバーフローしました、型が一致しませんなどいくつもエラーが発生しており、その中で、他のエラーが発生した状態のコードを記述してしまっておりました。
 
aryは配列になっておらず、tmpは1行のデータがきちんと格納されていました。
 
r1,c1については、特に意味はなく、r,cと同じ意味です。すみません。
 
以上、今後ともよろしくお願いいたします！

説明ご苦労様でした。
事情はわかりましたが、こうした話はムードで話をしても、相手には通じません。
特にこうしたプログラムに関係した話は、細かいですので、
正確にしていただかないと、皆さんからのコメントも無駄になってしまい、
無駄な時間をお互いに消費してしまいます。
今後とも頑張って正確な説明をしていただきたいと思いました。
 
ところで、

引用:
この部分の意味がよく理解できませんでした。
 
配列の最大値は列の数量の最大値で決まることから、一般的にデータ数の
多い行を配列の列側（2次元側）に持ってきて配列を作成するという理解で
よろしいでしょうか。

ＣＳＶデータを配列に格納する
に固執されているようですが、
配列に格納したあと、そのデータを、
どの様に使うのかが説明されていませんね・・・・・
   
たとえば、目的が
「Excelブックとして保存する」
ならば、simpleさん提案の方法があります。
この方法は、配列も行も列も意識する必要がありません。
 
同様の方法となりますが、
ＣＳＶデータをそのままシートのＡ列に書き出し、「区切り位置」を使って、分解する方法もあります。
この方法も、配列、行、列を意識する必要がありません。

余計な心配かもしれませんが、
 
CSVデータの中に、次のようなデータがありませんか？
(1)「前ゼロ」の数字：0010100
   配列には、この通り格納されますが、シートに転記する段階で、「前ゼロ」が消えてしまう可能性があります。
(2)例「1-1」「3-10」のようにな文字列データ
　配列には、この通り格納されますが、シートに転記する段階で、日付と解釈されてしまいます。
 
このように、Excelの解釈で、意図しない形になるようなデータは、
　配列から、セル範囲に格納する前に、列にデータ型を設定する必要があります。

ＣＳＶデータを行ごとに配列に格納し、
シートのＡ列に貼付け、
「区切り位置」を使って、シートに転記するサンプルコードを掲示しておきます。
 

Sub test()
Dim BUF As String, TextFile As Object, data, CNT As Long, valINFO, data2

    'D:\TEST\TEST.CSV" の項目数は６、１番目と６番目を文字列にするという前提です。

    With CreateObject("Scripting.FilesystemObject")
        Set TextFile = .OpenTextFile("D:\TEST\TEST.csv")
        BUF = TextFile.ReadAll    '１度に全データを読み込む
        Set TextFile = Nothing
    End With
    data = Split(BUF, vbCrLf)   '行ごとに分割して配列へ
    GoSub SetFIELDINFO
    GoSub HENKAN
    With ActiveSheet
        .Cells.EntireRow.Delete
        .Range("A1").Resize(UBound(data) + 1, 1).Value = data2
        .UsedRange.TextToColumns _
            Destination:=Range("A1"), _
            DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, _
            ConsecutiveDelimiter:=False, _
            Comma:=True, _
            Space:=False, _
            Other:=False, _
            FieldInfo:=valINFO, _
            TrailingMinusNumbers:=True
    End With
    Exit Sub

SetFIELDINFO:
Dim j As Long
    j = UBound(Split(data(0), ","))
    ReDim valINFO(0 To j, 0 To 1)
    For CNT = 0 To j
        valINFO(CNT, 0) = CNT + 1
        Select Case CNT
            Case 0, 5
                valINFO(CNT, 1) = xlTextFormat
            Case Else
                valINFO(CNT, 1) = xlGeneralFormat
        End Select
    Next
    Return

HENKAN:
'１次元を２次元に変換
    ReDim data2(1 To UBound(data) + 1, 1 To 1)
    For CNT = LBound(data) To UBound(data)
        data2(CNT + 1, 1) = data(CNT)
    Next
    Return
End Sub