Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(指定なし : 指定なし)
インデックスが有効範囲にありませんのエラーについて
投稿日時: 22/04/16 18:15:49
投稿者: TATSUYA.ich

  
以下の記述で、エクセルファイルに、他のエクセルファイル又はテキストを開いて得た情報を転記するためのマクロを組んでいますが、テキストデータを配列に格納するところでインデックスが有効範囲にありませんとエラーが出ますが理由がわかりません。
 
どなたか教えて頂けませんでしょうか。
 
よろしくお願いいたします。
 
  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

回答
投稿日時: 22/04/16 18:58:56
投稿者: WinArrow
投稿者のウェブサイトに移動

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

投稿日時: 22/04/16 19:58:49
投稿者: TATSUYA.ich

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

回答
投稿日時: 22/04/16 20:12:10
投稿者: WinArrow
投稿者のウェブサイトに移動

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

回答
投稿日時: 22/04/16 20:15:34
投稿者: WinArrow
投稿者のウェブサイトに移動

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

回答
投稿日時: 22/04/16 20:30:50
投稿者: simple

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

Sub test2()
    Dim ary As Variant
    ary(0, 0) = 1
End Sub
と同じエラーになるはずです。
理由は既に指摘いただいているとおりです。
 
動的配列にする手もありますが、transposeしたり面倒なので、
少し大きめの二次元の固定配列(大きさを決めたもの)を用意しておいて、
それに書き込むのが楽かもしれません。
まあ、そのあとに何をするかによりますね。

回答
投稿日時: 22/04/16 20:46:53
投稿者: simple

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

回答
投稿日時: 22/04/16 21:05:44
投稿者: WinArrow
投稿者のウェブサイトに移動

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

回答
投稿日時: 22/04/17 08:22:04
投稿者: simple

動的配列を使う例です。下記のコードを参考にしてください。
 
動的配列は、最後の次元に沿ってしか大きさを調整できませんから、
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

なお、Application.Transposeは、
行、列いずれかが2^16=65536 を超えるとエラーになることに注意してください。
 
# なお、既に書いたように、動的配列にするよりも、
# 少し余裕を持たせた固定配列を確保しておいて、読み込んだ行数、項目数は分かりますから、
# Worksheets("Sheet1").Range("A1").Resize(行数, 項目数) = ary
# としたほうが簡単です。未使用の部分は切り捨てられますから。
 

上記のように、ファイルを直接開いて操作してもよいのですが、
・項目自体にカンマが含まれているとその対応が必要
・日本語の文字コードの問題
などの留意点があります。
 
Excelにはファイルを読み込むツールが既定で用意されており、
そのあたりもオプションが用意されていて柔軟です。
それらを活用したほうが、標準化されるので安定しますし、開発に関する時間節約にもなります。
 
一例として下記の例を挙げておきます。
 
Excelのバージョンが記載されていないのですが、
少し前のバージョンのものが使いやすいかもしれません。
オプション設定の「レガシーデータインポートウイザード」で
「テキストから(レガシ)」を選択すると利用できます。
(詳細はネットで、「テキストから(レガシ)」を検索してください)
 
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

(記録されたマクロには上記のほか多数のオプション指定が記載されます。
  適宜捨象しましたので、なにか不都合があるかもしれません。
  その場合は見直してください。
  また、最後に .deleteを追加しています。)

回答
投稿日時: 22/04/17 09:09:25
投稿者: simple

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

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

とまあ、こうした作業をデバッグ(作業)というわけですが、
それに使う道具や、そのやり方もひととおり確認しておいて、
いつでも使えるようにしておくとよいでしょう。

投稿日時: 22/04/17 12:03:43
投稿者: TATSUYA.ich

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

回答
投稿日時: 22/04/17 12:35:14
投稿者: simple

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

投稿日時: 22/04/17 21:21:05
投稿者: TATSUYA.ich

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

回答
投稿日時: 22/04/17 22:07:36
投稿者: simple

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 ...と並べることになっていると思います。

投稿日時: 22/04/17 22:20:48
投稿者: TATSUYA.ich

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

回答
投稿日時: 22/04/18 11:50:50
投稿者: simple

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

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

とのことでした。
違います。行や列の個数の話ではありません。
それで、22/04/17 22:07:36 に追加投稿しています。
やりとりを進めていただきたいと思います。

回答
投稿日時: 22/04/19 18:23:26
投稿者: WinArrow
投稿者のウェブサイトに移動

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

回答
投稿日時: 22/04/19 18:39:40
投稿者: WinArrow
投稿者のウェブサイトに移動

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

回答
投稿日時: 22/04/21 18:38:01
投稿者: WinArrow
投稿者のウェブサイトに移動

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

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

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

    With CreateObject("Scripting.FilesystemObject")
        Set TextFile = .OpenTextFile("D:\TEST\TEST.csv")
        BUF = TextFile.ReadAll    '1度に全データを読み込む
        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:
'1次元を2次元に変換
    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

※1次元→2次元変換処理追加
 

トピックに返信