エクセル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

回答No.4

Wendy02 · ベストアンサー

こんにちは。

今回は、配列がテーマですから、配列のお答えをしなくてはならないと思います。

>ar = Range("A1:D1").Value

通称、ジャグ配列といいます。２次元配列の変形したものです。
本来は、参考の部分のように、1次元側と、2次元側、両方を調べなくてはなりません。

Set で、Rangeオブジェクトにした場合は、配列変数ではありません。あくまでも、ar が、Variant だから、Rangeオブジェクトを変数として可能になるだけです。

Sub test2()
　　Dim ar As Variant
　　Dim n As Integer
　　Dim i As Integer
　　Dim j As Integer

　　ar = Range("A1:D1").Value
　　'参考
　　i = UBound(ar, 1)
　　j = UBound(ar, 2)

　　For n = LBound(ar, 2) To UBound(ar, 2)
　　　　Cells(n + 1, 5).Value = ar(1, n)
　　Next n
End Sub

セルの値は、とびとびでも、同じことです。

なお、2次元までが精一杯で、それ以上の次元はややこしくてしょうがないです。
また、動的配列の場合は、1次元側を触ることは出来ません。2次元側で処理します。

お礼

i = UBound(ar, 1)
j = UBound(ar, 2)

一次元と二次元で違うことがわかりました。
有難うございました。

回答No.13

kobouzu_su

お師匠さん、流石です。
昨夜の本場薩摩の芋焼酎が少々お手伝いをしたのでせうかねぇ。

昨夜午前０時少し前、
「VisibleRange,SmallScroll等を調べてみたらどうでしょう」
と途中まで書きかけていたのですが、
たぶんその時分はほろ酔いだろうとボツにしたところでした。

それにしてもほんとに上達が速いですねぇ。びっくりします。
これからは、「VBAエキスパートMerlion様」とお呼びせねばなりますまいねぇ。(^o^)/~~

これからも初級者、中級者（時には上級者）の方にも、
目から鱗の回答をしながらご自身でもますます精進してくださいな。

-----------------------------------------------------
>「てんがらもん」とは当地の方言
そう言われれば、そんなこと口走ったような。。。
忘れっぽい性格はなかなか治りませぬよ。

●連休は３泊４日の予定で京都旅行です♪
「それがどうした！」と言われれば、二の句が継げませんが。(^^;;;

お礼

ありがとうございました。
鹿児島にはまだ行ったことありませんが、薩摩の芋焼酎、美味しいですね！苦瓜も好きですよ。

いえいえ、まだまだ初心者の域を脱しきれずにおります。
お教え、有難うございました。

良いご旅行を。(^o^)/

回答No.12

Wendy02

こんにちは。Wendy02です。

>ワークシート関数でもVBAでも配列は難しいですねえ・・・・。

ご存知でしたら、余計なことかもしれませんが、VBAの配列に関しては、大村あつし氏の『かんたんプログラミング　Excel　VBA 応用編』で、出てきます。VBAでは、配列に関連するものは、多次元配列,　動的配列,　Collection,　Dictonary　（パラメータ配列は別）ぐらいを抑えておけばよいと思います。

ただ、私は、ワークシートのほうが遥かに難しいとは思います。以前の質問の回答の中で、文字列の並べ替えをするというものがありました。それを、関数だけで出来るとは夢にも思いませんでした。確かに、出来上がったものは分かっても、発想自体が、関数の配列の取り扱い方が見えてこない限りは、自分自身では作れないような気がします。

私は、ある程度、ユーザー定義関数で、配列の関数は出来ますが、既存のものを解読するのは、なかなか難しいです。

お礼

Wendy02さま、重ね重ね有難うございます。
今後ともご指導のほど、よろしくお願いいたします。

回答No.11

kobouzu_su

いまお暇モードなのでまたまた登場。

納得されたようで何より。

序に一言。

「VBAで特定のテキストボックスを画面中央に来るように表示したい」

この質問の回答見ました。おお、といふ感じです。
お暇なときにでも、画面中央というところまでコードにしてみたらどうでしょう。
勉強になると思います。

ところで最近VBAの回答者にお師匠さんの名前をよく見かけるようになりました。
頼もしい限りです。
その調子でますます精進してくださいな。

＞今夜は芋焼酎で祝杯をあげようかなあ

まさかここで芋焼酎が出てくるとは、びっくりくりくりくりくり坊主。

びっくりした理由　↓↓↓

　　Kobouzu lives in Kagoshima City....(^o^)/~~

お礼

スクロールでテキストボックスを画面の中央にですか？
テキストボックスを画面中央に移動させるのならなんとかできそうですが、そうやったらレイアウトが崩れてしまいますよね？う～ん、難しいです。

芋焼酎はわたしの好物です。 （＾∇＾）
で、存じてたから書いたんですよ～。以前の
>「てんがらもん」とは当地の方言
でわかりました。

補足

遅くなったので読んでいただけるかどうか・・・。

> 画面中央というところまでコードにしてみたらどうでしょう。

なんとか出来ました！
↓こんな感じでどうでしょうか？

Sub test03()

Dim myRng As Range
Dim wc As Integer, hc As Integer, rc As Integer, cc As Integer

With ActiveSheet.TextBoxes("テキスト 1")

Set myRng = Range(.TopLeftCell, .BottomRightCell)

End With

wc = myRng.Columns.Count
hc = myRng.Rows.Count

Application.Goto Reference:=myRng, Scroll:=True

With ActiveWindow

rc = .VisibleRange.Rows.Count
cc = .VisibleRange.Columns.Count

.SmallScroll Down:=Int((hc - rc) / 2)
.SmallScroll ToRight:=Int((wc - cc) / 2)

End With

End Sub

もう締め切っているので質問No2943282 の方に伝えられないのが残念ですが・・・・。

回答No.10

Wendy02

こんにちは。

>test001がうまく行って、002がエラーも出ないのにRange("C1:C10")に代入されない理由です。

私が、書いている最中に、解決したようですね。一応、そのまま、載せておきます。

それは、br(10, 1) の配列のサイズが違っていますね。
br(0 to 10, 0 to 1) ということになっているので、その構造に、Rangeオブジェクトを反映させます。

Range("C1:D11").Value = br

エラーが出ないのは、2列目側に入っているからです。だから、2列目の2行目から出力されるはずです。

または、

モジュールの先頭に、
Option Base 1

としてあげればよいです。あまり、このオプションは使うことはないと思います。このほかにも、２次元配列独特にテクニックはあるのですが、面倒な作業ばかりですから、避けて通れるものなら、避けて通っても、困りもしないと思います。

お礼

ありがとうございます。
ワークシート関数でもVBAでも配列は難しいですねえ・・・・。

回答No.9

kobouzu_su

ある時は、kobouzu、またある時は、ta.....（笑）

ちょっとこんがらがってしまったようですね。
今度はちゃんと説明します。

---------------------------------------------
　ar = Array("t", "e", "s", "t")

配列宣言したとすると、Dim ar(3)

For i=LBound(ar) to UBound(ar)
For i=0 to 3

--------------------------------------------
　ar = Range("A1:D1")

配列宣言したとすると　Dim ar(1,4)　　←　▲後で説明あり

For i=LBound(ar,1) to UBound(ar,1)
　　For j=LBound(ar,2) to Ubound(ar,2)

For i=1 to 1
　　For j=1 to 4
-----------------------------------------------

どうですか、お気づきになったでしょうか。
セル範囲の場合カウンターのスタートが"０"からではなくて"１"から始まってますよね。

ですから、上記では、Dim ar(行、列）で、

　Dim ar(1,4)　と書きましたが、実際は

　Dim ar(1 to 1, 1 to 4)

と各次元の添字のOption Base が１になっているのです。

ふつう、Dim ar(1,4) は、Dim(0 to 1, 0 to 4) ですよね。

●まとめ●

（１）セル範囲を配列変数に入れた場合は、２次元配列になる。
（２）各次元の添字は、普通の配列とは違って、０からではなく、１から始まる。

尚、LBound(ar,1)とLBound(ar)の違いは、ヘルプをご覧あれ。

口で説明すると簡単に済むのですが、文章ではなかなか。。。(^^;;;
以上です。

お礼

ありがとうございます。
おかげさまでやっと納得できました。

#8のお礼で再質問した件も、
> ふつう、Dim ar(1,4) は、Dim(0 to 1, 0 to 4) ですよね。
のおかげで原因がわかり、解決できました。

いつもおせわになり有難うございます。
今夜は芋焼酎で祝杯をあげようかなあ　o(^-^)o

回答No.8

Wendy02

こんにちは。

>２次元配列のRangeは、arを一次元配列としてみると、行方向しかカウントしないのでしょうか、

こういう話を突っ込んでいくと、おっしゃっている話は、VBAを遥かに超えて、マシン語の世界に入ってしまうと思います。

イメージで、２次元配列を縦横のマトリックスに捉えるのが楽だからするのであって、本来、メモリ上は、1次元の世界だそうです。VBAのLboundとか、Uboundの関数自体が、そういうような取得をするだけであって、その関数は、そういう書式(フォーマット）を読み取るだけのものなのだと思うのです。

以下は、一次元配列ではなくて、一次側の配列の下限・上限ですね。

Set rng = Range("A1:D2")
ar = rng.Value
i = LBound(ar)
' i =Lbound(ar, 1)
j = UBound(ar)
'j = UBound(ar, 1)

RangeオブジェクトのValueから、配列を取り出すときと、Rangeオブジェクトは、構造的には、まったく違ったものです。

こういう表を作って、読み出しの実験をしてみるとよいです。

　A　　　B　　　C　　　D
1,1　　1,2　　1,3　　1,4
2,1　　2,2　　2,3　　2,4

Set rng = Range("A1:D2")
ar = rng.Value
For Each v In ar
　n = n + 1
　Debug.Print n, v
Next v

ところが、これは、rng 変数を以下のようにループしたものとは、数は同じでも、その読み出し方が違います。

Set rng = Range("A1:D2")
For Each c In rng
　n = n + 1
　Debug.Print n, c.Value
Next c

こんなことを書いても、それで理解されると思いません。どちらかというと、ワークシートとは切り分けて考えたほうがよいのかもしれませんね。

お礼

何度も有難うございます。
なるほど、配列は縦方向に、範囲は横に、読み出していますね！
ご指摘の通り、ほとんど理解できていません、済みません。

最後にこれだけ教えていただけませんか？
test001がうまく行って、002がエラーも出ないのにRange("C1:C10")に代入されない理由です。

Sub test001()
Dim ar
Dim br
ar = Range("A1:B10").Value
br = Range("C1:C10").Value
For i = LBound(ar, 1) To UBound(ar, 1)
br(i, 1) = (ar(i, 1) = ar(i, 2))
'Debug.Print br(i, 1)
Next
Range("C1:C10").Value = br
End Sub

Sub test002()
Dim ar
Dim br(10, 1)
ar = Range("A1:B10").Value
For i = LBound(ar, 1) To UBound(ar, 1)
br(i, 1) = (ar(i, 1) = ar(i, 2))
'Debug.Print br(i, 1)
Next
Range("C1:C10").Value = br
End Sub

補足

Wendy02さま、解決しました。
Dim br(10, 1)ではいけなかったのですね。(9, 0)だったのですね。
以下で成功しました。

Sub test003()
Dim ar
Dim br(9, 0)
ar = Range("A1:B10").Value
For i = LBound(ar, 1) To UBound(ar, 1)
br(i - 1, 0) = (ar(i, 1) = ar(i, 2))
'Debug.Print br(i, 1)
Next
Range("C1:C10").Value = br
End Sub

回答No.7

kobouzu_su

お師匠さん、こんばんは。
あまり難しく考えずに、

ar = Range("A1:D1").Value　のようにセルの値を代入した場合は
必ず２次元配列になる。　
Dim ar(行、列)　

と単純に覚えたらどうでしょう。

'--------------------------------------------------------　
Sub Test()
　Dim ar As Variant
　Dim R As Long
　Dim C As Long

　ar = Range("A1:D1").Value

　MsgBox "行　" & LBound(ar, 1) & " to " & UBound(ar, 1) & Chr(10) _
　　　　& "列　" & LBound(ar, 2) & " to " & UBound(ar, 2)

　For R = LBound(ar, 1) To UBound(ar, 1)
　　　For C = LBound(ar, 2) To UBound(ar, 2)
　　　　　Range("G1").Offset(R, C) = ar(R, C)
　　　Next C
　Next R

End Sub
'------------------------------------------------------------

データがどういうふうに取り込まれているか確認するためには
取り込むデータと同じに列方向に出力した方が確認しやすいですね。
上記コードではそうしてあります。

また、ar=Range("A1:D2").valueと複数行がどう取り込まれるかも確認するとなお分かり易いと思います。

おっと大事なことを忘れてました。最重要事項？

　ar = Array("t", "e", "s", "t")　のL,UBound(ar)と
　ar = Range("A1:D1")　のL,Ubound(ar)を比べてみてください

重要な違いに気づくことでしょう。
この違いに気づかなければ本当に理解したとは言えません。

などと偉そうなことを言ってみたくなる花散らしの雨の夜。(^^;;;　
以上です。

お礼

> ar = Range("A1:D1").Value　のようにセルの値を代入した場合は
> 必ず２次元配列になる。

了解です。

> ar = Array("t", "e", "s", "t")　のL,UBound(ar)と
> ar = Range("A1:D1")　のL,Ubound(ar)を比べてみてください

Arrayは0-3、Rangeは1-1でした。
UBound(ar)って、覚えたてですが、arを一次元配列としてみているのですよね？だからもともと1次元の"t", "e", "s", "t"は、０から３までの４つ、

２次元配列のRangeは、arを一次元配列としてみると、行方向しかカウントしないのでしょうか、１－１でした。よくわかりません。（ar = Range("A1:D2")なら１－２を返しました）

でもkobouzu_suさん？　ん？

回答No.6

hana-hana3

＞実行時エラー13で型が一致しませんとなりました。

失礼しました。
下記のようにしてみて下さい。

Sub test()
Dim ar As Variant
Dim n As Integer
ar = Range("A1:D1")
For n = LBound(ar, 2) To UBound(ar, 2)
Cells(n + 1, 5) = ar(1, n)
Next n
End Sub

お礼

(ar, 2)が「二次元配列」という意味だとわかり理解できました。
有難うございました。

回答No.5

Wendy02

#4 に足しておきます。

参考までに、オブジェクトと配列の違いについては、このような結果になって現れます。
オブジェクトを確保しなければ、test3 は、出力することは出来ません。しかし、test4 は、オブジェクトは必要ではありません。すでに、配列変数として確保しているからです。

Sub test3()
'オブジェクト
　　Dim ar As Variant
　　Dim n As Integer

　　Set ar = Range("A1:D1")
　　'Range("A1:D1").Delete　'これを入れるとエラーが発生する

　　For n = 1 To ar.Columns.Count
　　　　Cells(n + 1, 5).Value = ar(1, n)
　　Next n
End Sub

Sub test4()
'配列
　　Dim ar As Variant
　　Dim n As Integer

　　ar = Range("A1:D1").Value
　　Range("A1:D1").Delete　'エラーは起こりません。

　　For n = LBound(ar, 2) To UBound(ar, 2)
　　　　Cells(n + 1, 5).Value = ar(1, n)
　　Next n
End Sub

飛び飛びのセルの場合は、Area というものが入ってくるので、ややこしくなりますが、配列ですと、一旦変数に確保すれば簡単です。

Sub Test1_Area()
Dim r As Range
Dim i As Integer
Dim j As Integer
Dim n As Integer
　Set r = Range("A1:C1,E1")
　n = 1
　For j = 1 To r.Areas.Count
　　For i = 1 To r.Areas(j).Cells.Count
　　 Cells(n + 1, 5).Value = r.Areas(j).Cells(i).Value
　　 n = n + 1
　　Next i
　Next j
End Sub

Sub Test1_Array()
Dim r As Range
Dim c As Variant
Dim i As Integer
Dim n As Integer
Dim ar() As Variant
　Set r = Range("A1:C1,E1")
　ReDim ar(r.Count - 1)

　For Each c In r
　　ar(i) = c.Value
　　i = i + 1
　Next c
　For n = LBound(ar) To UBound(ar)
　　 Cells(n + 2, 5).Value = ar(n)
　Next n
End Sub

お礼

いつもお世話になります。
わたしには難しすぎて一度に理解できそうもありませんが、徐々に覚えていこうと思います。
有難うございました。

回答No.3

zap35

私が回答するのはおこがましいのですが…　実験したところ以下なら動きました。

Sub test1()
Dim ar As Variant
Dim n As Integer
　Set ar = Range("A1:D1")
　For n = 1 To Range("A1:D1").Count
　　Cells(n, 5) = ar(n)
　Next n
End Sub

またご存じと思いますが
　Set ar = Range("A1")
の後では、Cells(i,j)　と　ar(i,j)　は同じ意味になります

ものの本には「Rangeオブジェクトは内部的には配列で管理されているためどこかのセルをバリアント型変数に格納するだけで配列ができてしまう」とありました。

ただし配列の起点からの連続した範囲が必要ですので　Range("A1:D1")　や　Range("A1:A4")　は動きますが、Range("A1:C1,E1")のように範囲が離散するとダメでした。より正当な解説は他の皆様に期待します

お礼

有難うございました。
「配列」と言うのがよくわかってなくて・・・。

難しいですねえ。