VBA　配列

お世話になっております。
配列について学習を進めておりますが、イマイチどのようにデーターが格納されているくな分からず、意図した処理ができない状態です。

D列に"No"が合った場合、B列～D列を配列に入れ、B11以降にコピーするというコードですが、実行しローカルウィンドウで確認してみると、配列の中には何も格納されていない状態です。

どなたかご教授下さい。

vba
1Sub test3()
2Dim a(10) As String
3Dim i As Long
4Dim c_Cnt As Long
5
6c_Cnt = 2
7For i = 0 To 7
8    If Cells(c_Cnt, "D").Value = "No" Then
9        a(i) = Range(Cells(c_Cnt, "B"), Cells(c_Cnt, "D")).Value
10     c_Cnt = c_Cnt + 1
11    End If
12
13Next i
14
15Range(Cells(11, "B"), Cells(11, "D")).Value = a(0)
16
17End Sub

行動規範の内容に同意します

回答4件

ソースコードに何点か問題点を感じましたので、直接の回答ではないのですが、pipiさんはデバッグ機能は活用されていますでしょうか？

とりあえず、同じような感じで、データの配置等を行ってみました。

実行するとおっしゃるとおり、配列には何も入りません。
その動きはデバッグ実行してみるとIfの中に入らないのがわかります。

ブレークポイントを設定しても良いですし、Sub test3()を表示している状態で、F8キーを押していきなりステップインで実行してもよいです。
試しに、F8を押していくと1ステップずつ進むのがわかります。

デバッグ実行中に変数にカーソルを合わせると、値が確認できます。

これを追って見ているだけでも、c_Cntが変化しないことがわかりますので、sleepsheepさんのおっしゃるとおり、IFの中にあるのが原因だとわかります。

ちなみに、カーソルを合わせたままだと面倒な場合もありますので、「ウォッチ」しておくと便利です。
ウォッチしたい変数なりを選択して、右クリックし、コンテキストメニューから[ウォッチ式の追加]をクリックします。

次のようなダイアログが表示されます。特に修正を加える必要が無ければ、そのまま[OK]します。

ウォッチウィンドウを表示してなければ、次のウィンドウがどこかしらに表示されます。

応用としては例えば、次のようにして現在のアドレスがどうなっているかなど確認できます。（ついでにカウンタのiも加えておきます）

こうすることで、メインのループのiのカウンタの状態、c_Cntの状態、また、今見ているセルのアドレスが確認できますので、どこで動きが間違えているかなどがわかりやすくなると思います。

さて、では、c_Cntが正しくカウントアップされるようにIFの外側に出します。
早速実行してみますが、残念ながらエラーがでました。

Range(Cells(c_Cnt, "B"), Cells(c_Cnt, "D")).Valueをウォッチしてみます。
]

型の情報がVariant/Variant(1 to 1, 1 to 3)となっています。
aの配列はString型なので、エラーメッセージ通り型の不一致です。
Rangeオブジェクトを取得する際に、"B"から"D"列を範囲指定していますので、2次元配列となっているからです。

試しに、aの配列の型をVariantにしてみます。

Dim a(10) As Variant

実行すると、エラー無く終わりますが、Excelのシートには結果が張り付きません。
貼り付ける直前の場所にブレークポイントを設定して、aの中身を見てみます。
a(5)とa(6)に値が入っています。

値はちゃんと配列に入っていますので、貼り付ける側の指定の問題だと判断し、コードをよく見てみます。

Range(Cells(11, "B"), Cells(11, "D")).Value = a(0)

a(0)としていので、何も無いです。
例えば、a(5)とすれば、値は張り付きます。
もっとも、これでは不完全ですが、値が張り付くことはわかるかと思いますので、とりあえずここまでの回答とさせてください。

投稿2017/01/15 08:13

編集2017/01/15 08:15

rrryutaro

総合スコア146

退会済みユーザー

2017/01/15 08:49

ご丁寧に有難うございます。私自身も、そろそろデバッグ方法もしっかりと身につけようと思っていたところです。

seastar3

2017/01/15 09:16

パソコン雑誌のカラー記事のような丁寧な解説で、参考になります。

shigeru

2018/03/08 23:10

とても親切な回答だと思いました。

行動規範の内容に同意します

ベストアンサー

変数とは

変数とは、値を格納しておく入れ物です。よく箱などに例えられます。

一口に箱といっても色々な種類があり、目的によって使い分けられます。
塩には塩の入れ物、醤油には醤油の入れ物があるように、変数も入れる物（数値・文字列・セル範囲）によって使い分けます。
これが変数の型です。

配列とは

配列とは、同じ箱を複数セットで用意するようなものです。

例えば
Dim A(10) As String
とすると文字列を入れる箱Aが10個用意され、それぞれの箱に違う値を入れることができます。

A(0)="れもん"
A(1)="いちご"
・・・
A(9)="きうい"

多次元配列とは

多次元配列とは、配列をさらにグループ分けして管理したいときに使います。

例えば
Dim A(3, 10) As String
とすると文字列を入れる箱Aが10x3個用意されます。

'グループ０にはかな名を格納
A(0, 0)="れもん"
A(0, 1)="いちご"
A(0, 9)="きうい"
'グループ１には漢字名を格納
A(1, 0)="檸檬"
A(1, 1)="苺"
A(1, 9)="彌猴桃"
'グループ２には単価表示を格納
A(2, 0)="\200"
A(2, 1)="\500"
A(2, 9)="\300"

といった具合に使えます。

上記は２次元配列ですが、さらに３次元、４次元とグループ分けすることもできます。
当然ですが、高次元配列になるほど複雑になるので管理が大変になります。

アドバイス

今回やりたいことは、正直いうと配列を使うほどのことではありません。
おそらく学習のために敢えて配列を使用しているものと思いますので、配列を使う前提は崩さずにアドバイスさせていただきます。

まず他の方からも指摘があるとおり、いくつかバグといえる部分が存在します。

①条件を満たさないとカウンタをインクリメントしない
⇒カウンタのインクリメントは条件文の外で行う必要があります。

②複数セルを指定したRangeからValue取得している
⇒複数セルを指定した場合、Range.Valueは取得できません。範囲内のセルのそれぞれの値はRange.Value2に配列として格納されています。

③ループ６周目ではじめて"No"を見つけた場合でも、a(6)に値をセットしている。
⇒出力時はa(0)の内容を出力しようとしているので、最初に見つけた"No"の行の内容をa(0)に格納したいのではないでしょうか？

上記①はすぐにも修正できると思います。
しかし②③を修正するためには、まず配列でどのように値を管理したいのかを整理する必要がありそうです。

今回の場合、配列で管理したい内容は２種類あります。
１つはセルの値で、これを３列分覚えておきたいものと思います。
その３列１セットの値を、行毎に覚えておきたい、これが２つめの要素です。

実際にそれぞれどう管理するかというと、いくつか方法があります。

例① 1次元配列を３つ用意する
名前の違う配列を３種類用意して、B列・C列・D列それぞれの値を覚えさせる方法です。

Dim b(10) As String     'B列の値格納用の文字列型配列
Dim c(10) As String     'C列の値格納用の文字列型配列
Dim d(10) As String     'D列の値格納用の文字列型配列
Dim iFind As Integer    'Noを見つけた数
Dim c_Cnt As Integer    '対象行

iFind = 0
c_Cnt = 2

For i = 0 To 7
    If Cells(c_Cnt, "D").Value = "No" Then
        b(iFind) = Cells(c_Cnt, "B")
        c(iFind) = Cells(c_Cnt, "C")
        d(iFind) = Cells(c_Cnt, "D")
        iFind = iFind + 1
    End If
    c_Cnt = c_Cnt + 1
Next

For i = 0 To 7
    If d(i) <> "No" Then Exit For   '配列dに"No"が格納されていなければループ終了
    Cells(11 + i, "B") = b(i)
    Cells(11 + i, "C") = c(i)
    Cells(11 + i, "D") = d(i)
Next

例② ２次元配列を利用する
２次元配列を１つ用意して、要素１で列を管理する方法です。

Dim a(3, 10) As String  '文字列型の2次元配列
Dim iFind As Integer    'Noを見つけた数
Dim c_Cnt As Integer    '対象行

iFind = 0
c_Cnt = 2

'チェック
For i = 0 To 7
    If Cells(c_Cnt, "D").Value = "No" Then
        a(0, iFind) = Cells(c_Cnt, "B")     'グループ０にはB列の値を格納
        a(1, iFind) = Cells(c_Cnt, "C")     'グループ１にはC列の値を格納
        a(2, iFind) = Cells(c_Cnt, "D")     'グループ２にはD列の値を格納
        iFind = iFind + 1
    End If
    c_Cnt = c_Cnt + 1
Next

'出力
For i = 0 To 7
    If a(2, i) <> "No" Then Exit For   'グループ２に"No"が格納されていなければループ終了
    Cells(11 + i, "B") = a(0, i)
    Cells(11 + i, "C") = a(1, i)
    Cells(11 + i, "D") = a(2, i)
Next

例③ 配列に文字列ではなくRangeオブジェクトを格納する

配列の管理を検討する上で例①②はある意味正攻法なやり方ですが、ここで紹介する方法はRangeオブジェクトの.Value2に欲しい３つの値がそろっていることを利用したExcel特化の方法です。
持っている情報をうまく使えば管理が簡単になる例としてご紹介します。

Dim a(10) As Range      'Range型の配列
Dim iFind As Integer    'Noを見つけた数
Dim c_Cnt As Integer    '対象行

iFind = 0
c_Cnt = 2

'チェック
For i = 0 To 7
    If Cells(c_Cnt, "D").Value = "No" Then
        Set a(iFind) = Range(Cells(c_Cnt, "B"), Cells(c_Cnt, "D"))  '配列にB列～D列のRangeを格納
        iFind = iFind + 1
    End If
    c_Cnt = c_Cnt + 1
Next

'出力
For i = 0 To 7
    If a(i) Is Nothing Then Exit For   '値が格納されていなければループ終了
    Range(Cells(11 + i, "B"), Cells(11 + i, "D")).Value2 = a(i).Value2  '３列分の値がまとめて.Value2に格納されているので、これを出力する
Next

長くなってしまいましたが、以上です。
参考になれば幸いです。

投稿2017/01/17 08:45

jawa

総合スコア3013

退会済みユーザー

2017/01/17 12:38

丁寧な解説有難うございます！！

行動規範の内容に同意します

if文中の ”c_Cnt = c_Cnt + 1” をend ifの後ろに置けばよいと思ったのですが、その次は配列への格納の方法がエラーとなってしまいます。

a(i) = Range(Cells(c_Cnt, "B"), Cells(c_Cnt, "D")).Value
↑
セルB2～D2をまとめた内容をa(i)に格納しようとしていますが、配列の考え方としては
・B2を格納する箱
・C2を格納する箱
・D2を格納する箱
といったように、欲しい情報の数だけ配列を定義する必要があります。

a(i)=Cells(c_Cnt, "B")
a(i+1)=Cells(c_Cnt, "C")
a(i+2)=Cells(c_Cnt, "D")

としてもいい気はしますが、ワークシートの構成がB2～D7と2次元構成になっているため、配列も2次元配列にした方が処理コードを組みやすいかと思います。

サンプルコードを作ってみたので記載します。
参考になれば幸いです。
##サンプルコード：

Dim a() As Variant     ←動的配列に変更
Dim i As Long
Dim c_Cnt As Long

i = 0
ReDim a(2, i)          ←最初の配列範囲を定義（a(0,0)～a(2,0)）

For c_Cnt = 2 To 8
    If Cells(c_Cnt, "D").Value = "No" Then
        a(0, i) = Cells(c_Cnt, "B").Value
        a(1, i) = Cells(c_Cnt, "C").Value
        a(2, i) = Cells(c_Cnt, "D").Value
        i = i + 1      ←ワークシートで他の行にも"No"があるかもしれないので箱を増やしておく
        ReDim Preserve a(2, i)     ←配列範囲の定義を変更（a(0,0)～a(2,1)まで）
    End If
Next c_Cnt

Range(Cells(11, "B"), Cells(11 + i, "D")).Value = WorksheetFunction.Transpose(a)
↑
左辺のD列側の行を"11 + i"に変更（配列範囲とワークシートの貼付範囲が一致しないとエラーとなるため）
右辺をWorksheetFunction.Transpose(a)に変更（配列内の並びだと縦と横が逆になっているため）

投稿2017/01/15 08:38

退会済みユーザー

総合スコア0

退会済みユーザー

2017/01/16 13:19

すいません。。。ご丁寧に解説いただいているのに・・・全然理解できない状態です。色々考えて、やってみましたが、結局エラーになってしまいます。。。。二次元配列・・・死にたいほど分からないです。。。a(y,x)のyは列ですか？そしてxは行でしょうか？動きがイマイチ分かりません。。。そもそもExcelのcell自体が二次元配列の様なきがしてcells(y,x).valueと書くように。。。 Dim i As Long Dim c_Cnt As Long Dim No_Cnt As Long Dim x As Long Dim y As Long i = 0 No_Cnt = WorksheetFunction.CountIfs(Range("D2:D9"), "No") ReDim a(No_Cnt * 3) For c_Cnt = 2 To 8 If Cells(c_Cnt, "D").Value = "No" Then a(i) = Cells(c_Cnt, "B").Value a(i + 1) = Cells(c_Cnt, "C").Value a(i + 2) = Cells(c_Cnt, "D").Value End If i = i + 1 Next

行動規範の内容に同意します

c_Cntの値が変わっていないので、２行目のD列しか見ていないのでは？

「If Cells(c_Cnt, "D").Value = "No" Then」の条件がtrueになることが無いからだと思います。

投稿2017/01/15 02:28

編集2017/01/15 02:30