Excel2003VBA Variantについて

質問

教えてください。
下記にxのコードとyのコードとwのコードとを記載しました。

この内、
wのコード、xのコードでは(3)も含めノーエラーで終わるのですが、
ｙのコードでは(3)でエラーになってしまいます。

エラー内容は
「インデックスが有効範囲にありません。」
です。

どうしてでしょうか？
ご教示をお願い致します。

　　　　　　　　　　　　　　　記
Sub　w()
Dim　B　As　Variant
　　　　ReDim　B(1)　'・・・(1)
　　　　ReDim　B(5)　'・・・(2)
　　　　ReDim　B(10)
　　　　ReDim　Preserve　B(15)　'・・・(3)
End　Sub

Sub　x()
Dim　B　As　Variant
　　　　ReDim　B(1)
　　　　B　=　Split("B4:C15,D4:E15,F4:G15,B4:G15",　",")　'・・・(1)
　　　　ReDim　Preserve　B(5)　'・・・(2)
　　　　ReDim　B(10)
　　　　ReDim　Preserve　B(15)　'・・・(3)
End　Sub

Sub　y()
Dim　B　As　Variant
　　　　ReDim　B(1)　'・・・(1)
　　　　ReDim　B(5)　'・・・(2)
　　　　B　=　Evaluate("row(A1:A10)")
　　　　ReDim　Preserve　B(15)　'・・・(3)
End　Sub
　　　　　　　　　　　　　　　　　　　　　　　　　以上

回答No.1

nicotinism · ベストアンサー

大丈夫な気がして試すと確かにエラーに
ローカルウィンドウを出しながらステップ実行したらわかりました

Sub　y()
Dim　B　As　Variant
　　　　ReDim　B(1)　'・・・(1)・・・・・・・・A
　　　　ReDim　B(5)　'・・・(2)・・・・・・・・B
　　　　B　=　Evaluate("row(A1:A10)") '・・・・C
　　　　ReDim　Preserve　B(15)　'・・・(3) ・・D
End　Sub

Aを通った直後に、B の型は Variant/Variant(0 to 1)
Bでは、Variant/Variant(0 to 5)
Cでは、Variant/Variant(1 to 10,1 to 1) ←これが問題の引き金
になります。
DでRedim Preserve しようとするとエラーになるのはヘルプに載ってます。

以下抜粋
キーワード Preserve を指定した場合、変更できるのは、動的配列の最後の次元のサイズに限られます。
また、次元数は変更できません。
たとえば、次元が 1 つしかない動的配列の場合、その次元は最後のただ 1 つの次元なので、
その次元のサイズを変更できます。
次元が 2 つ以上ある動的配列の場合、
最後の次元のサイズのみを変更でき、その配列に格納されている値は保持されます。
ただし、ほかの次元の大きさは変更できません。
--ここまで

ReDim　Preserve　B(15) は、0～15の一次元の範囲を設定することになりますが
上記ヘルプの１～２行目に引っかかります。

で、なんで二次元配列に、Evaluate("row(A1:A10)") でなるかというと・・・
推測にすぎませんが、内部的には、RowとColumn関数を一緒に扱っていて
「だったら二次元配列でいいんじゃない？とMS社の人は考えた」となっているのかな？と思います。

お礼

有り難うございます。

なるほど！
配列の構造が違うのですね
迂闊でした。
助かりました。

回答No.5

cj_mover

＃２、３、ｃｊです。
訂正が２件あります。

例示の記述にミスがありました。正しくは
　　Dim B
　　ReDim B(1 To ub)
　　B(1)　B(2)　B(3)　B(4)　...　B(ub)
　　ReDim Preserve B(ub)
　　　↓　　　↓　　　↓　　　↓　　　　　↓
　　B(0)　B(1)　B(2)　B(3)　...　B(ub - 1)　B(ub) = Empty
でした。

Re8111406mtxのコメントが間違っていました。正しくは
　　B = Evaluate("row(A1:A" & nRowsCn & ")")　　'　　B(1 To nRowsCn, 1 To 1)
　　B = Application.Transpose(B)　　'　　B(1 To nRowsCn)
　　ReDim Preserve B(1 To 15)　　'　　B(1 To 15)
　　B = Application.Transpose(B)　　'　　B(1 To 15, 1 To 1)
でした。
以上訂正お願いします。

/// Re:
あ、いえ、わざわざ、すみません。
こちらこそ、ずっと以前から勉強になることばかりですから、、、。
また参考にさせて頂くことも多いと思いますので
変わらずご活躍くださいませ。よろしくお願いします。　　　　　ｃｊ

お礼

了解致しました、訂正有り難うございます。

回答No.4

nicotinism

なるほど～^10
AccessにEval関数てのがありまして、
ExcelのEvaluate も似たようなモンと思い込みしてました。。。
あらためてヘルプを読んでます。
非常にスッキリしました。ありがとうございます。

お礼

私のような者の発言がお役に立てたご様子、
光栄に思います。

回答No.3

cj_mover

> 例外的に
> Evaluate("ROW(A1)")
> これ↑が何故か一次元で返ってしまうのは、私にも説明できませんが、

あー、これ、
結果的に二次元配列を返すけれど、過程ではジャグ配列、
という例の説明でいいみたいですね。
この点は私よりもNouble さんの方がお詳しいことと存じます。
// でも、二次元で（統一して）返してもらった方が親切ですよねー。

お礼

恐縮の余り入れそうな穴を探してしまいます　滝汗

＞でも、二次元で（統一して）返してもらった方が親切ですよねー。
そう私も思います。

回答No.2

cj_mover

こんにちは。お邪魔します。
＃１さんのご回答で解決に必要な情報は得られていると思います。

私は応用の部分で参考になりそうなことを書いてみます。

注意点として
Excel.SpreadSheetと配列変数の受け渡しをする時は
Excelから返される配列が必ず １オリジン だということに注意してください。
ReDim Preserve での Dimension指定は必ず
(1 To ub1, 1 To ub2) または (1 To ub) のようににします。
ここを間違えて、
(ub1, ub2) または (ub) のように書いてしまうと
　　Dim B(1 To ub)
　　B(1)　B(2)　B(3)　B(4)　...　B(ub)
　　ReDim Preserve B(ub)
　　　↓　　　↓　　　↓　　　↓　　　　　↓
　　B(0)　B(1)　B(2)　B(3)　...　B(ub - 1)　B(ub) = Empty
のように添え字がずれてしまって思わぬ結果になりかねません。
（意図的にそういう使い方をする場合もあるにはあるのですけれど、、、）
また、配列のサイズもメモリのサイズもずれてしまいますので。
Option Base 1 とモジュール単位で指定してあれば別ですが、
その場合は、０オリジンで扱われる筈の他の配列変数がある場合に
逆の意味での注意が必要ということになります。
或いはもしかして、質問文に Option Base 1 と書き忘れた、という話でしたら、
それを小さなミスと考えないほうがよいです。
配列の扱いは、とにもかくにも、Explicit を心掛ける必要がある、という意味です。

Evaluate メソッドの親オブジェクトを省略する場合、
且、引数の文字列式にシートを指定しない場合は、
親オブジェクトを意識して書くようにしましょう。
例えば、Sheet1 モジュールの記述として
　　Debug.Print Application.Evaluate("b2")
　　Debug.Print Evaluate("b2")
前者はアクティブシート
後者はSheet1
が、それぞれの親オブジェクトです。
　　Debug.Print Me.Evaluate("b2")
の省略形なのか
　　Debug.Print Application.Evaluate("b2")
の省略形なのか
で結果が違ってくる場合もありますので。

Evaluate メソッドで評価を期待する文字列式のうち、
ワークシート関数を指定した場合では、
ROW() COLUMN() のように Optionalな引数としてセル範囲を指定できる関数
については、引数を指定した時点で（Evaluate数式では指定が必要なのですが）、
FormulaArray（複数のセル範囲で数式を適用した配列数式）扱いになりますから、
戻り値はスカラーではなく、原則、二次元配列になります。
例外的に
Evaluate("ROW(A1)")
これ↑が何故か一次元で返ってしまうのは、私にも説明できませんが、
Excel.SpreadSheetと配列変数の受け渡しについては、
単一セルの扱いだけは特別な注意が必要ということは、
これに限った話ではありませんので、形で覚える他ないのかな、と。
参考）
　　Dim B
　　nRowsCn = Cells(Rows.Count, 1).End(xlUp).Row　　'　　1
　　B = Cells(1).Resize(nRowsCn).Value
　　→　nRowsCn が 1 だった場合だけ、B は二次元配列ではなくスカラー。とか。

ご提示のコードで意図したように配列をリサイズする方法を２例。

Re8111406arr：
Evaluateの文字列式によって一次元配列へ置換しておいて
リサイズ→行列置換

Re8111406mtx：
Evaluateはそのまま二次元を返すようにして
行列置換→リサイズ→行列置換

　XSize は変更できても YSize　は変更できない、ならば、
　XとYを行列置換してからリサイズしてしまおう、というようなお話。

２例とも実用コードとしてインプット、アウトプットがある形で書いています。
今回は行列置換の方法を解り易くする為に
Application.Transpose や "TRANSPOSE()"　を用いて書いていますが
配列のコンバートは、前にもお話したように、普通にループした方が有利です。

' ' =================================================================

Sub Re8111406arr()
　　Dim nRowsCn As Long
　　Dim B As Variant
　　Dim i As Long
　　nRowsCn = 10 ' Cells(Rows.Count, 1).End(xlUp).Row
　　B = Evaluate("transpose(row(A1:A" & nRowsCn & "))")　　'　　B(1 To nRowsCn)
　　ReDim Preserve B(1 To 15)　　''　　B(1 To nRowsCn)
　　B = Application.Transpose(B)　　'　　B(1 To 15, 1 To 1)
　　For i = nRowsCn + 1 To 15
　　　　B(i, 1) = i
　　Next i
　　With Cells(5).Resize(15)
　　　　.Value = Empty
　　　　.Value = B
　　End With
End Sub

Sub Re8111406mtx()
　　Dim B As Variant
　　Dim nRowsCn As Long
　　Dim i As Long
　　nRowsCn = 10 ' Cells(Rows.Count, 1).End(xlUp).Row
　　B = Evaluate("row(A1:A" & nRowsCn & ")")　　'　　B(1 To nRowsCn, 1 To 1)
　　B = Application.Transpose(B)　　'　　B(1 To 15)
　　ReDim Preserve B(1 To 15)　　'　　B(1 To 15)
　　B = Application.Transpose(B)　　'　　B(1 To 15, 1 To 1)
　　For i = nRowsCn + 1 To 15
　　　　B(i, 1) = i
　　Next i
　　With Cells(5).Resize(15)
　　　　.Value = Empty
　　　　.Value = B
　　End With
End Sub

' ' =================================================================

以上です。

お礼

何時もご教示を有り難うございます。

今回も
＞FormulaArray扱いになりますから、 戻り値は原則、二次元配列になる
の下りや
transposeによる１次元化など

私では到底おぼつかない内容…
とても楽しく拝見しました。

またバカなことばかりお伺いするとは思いますが、
今後ともどうぞ宜しくお願い致します。