教えてください。
下記にxのコードとyのコードとwのコードとを記載しました。
この内、
wのコード、xのコードでは(3)も含めノーエラーで終わるのですが、
yのコードでは(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ベストアンサー
- 回答日時:
大丈夫な気がして試すと確かにエラーに
ローカルウィンドウを出しながらステップ実行したらわかりました
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の一次元の範囲を設定することになりますが
上記ヘルプの1~2行目に引っかかります。
で、なんで二次元配列に、Evaluate("row(A1:A10)") でなるかというと・・・
推測にすぎませんが、内部的には、RowとColumn関数を一緒に扱っていて
「だったら二次元配列でいいんじゃない?とMS社の人は考えた」となっているのかな?と思います。
No.5
- 回答日時:
#2、3、cjです。
訂正が2件あります。
例示の記述にミスがありました。正しくは
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:
あ、いえ、わざわざ、すみません。
こちらこそ、ずっと以前から勉強になることばかりですから、、、。
また参考にさせて頂くことも多いと思いますので
変わらずご活躍くださいませ。よろしくお願いします。 cj
No.4
- 回答日時:
なるほど~^10
AccessにEval関数てのがありまして、
ExcelのEvaluate も似たようなモンと思い込みしてました。。。
あらためてヘルプを読んでます。
非常にスッキリしました。ありがとうございます。
No.3
- 回答日時:
> 例外的に
> Evaluate("ROW(A1)")
> これ↑が何故か一次元で返ってしまうのは、私にも説明できませんが、
あー、これ、
結果的に二次元配列を返すけれど、過程ではジャグ配列、
という例の説明でいいみたいですね。
この点は私よりもNouble さんの方がお詳しいことと存じます。
// でも、二次元で(統一して)返してもらった方が親切ですよねー。
恐縮の余り入れそうな穴を探してしまいます 滝汗
>でも、二次元で(統一して)返してもらった方が親切ですよねー。
そう私も思います。
No.2
- 回答日時:
こんにちは。
お邪魔します。#1さんのご回答で解決に必要な情報は得られていると思います。
私は応用の部分で参考になりそうなことを書いてみます。
注意点として
Excel.SpreadSheetと配列変数の受け渡しをする時は
Excelから返される配列が必ず 1オリジン だということに注意してください。
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 とモジュール単位で指定してあれば別ですが、
その場合は、0オリジンで扱われる筈の他の配列変数がある場合に
逆の意味での注意が必要ということになります。
或いはもしかして、質問文に 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 は二次元配列ではなくスカラー。とか。
ご提示のコードで意図したように配列をリサイズする方法を2例。
Re8111406arr:
Evaluateの文字列式によって一次元配列へ置換しておいて
リサイズ→行列置換
Re8111406mtx:
Evaluateはそのまま二次元を返すようにして
行列置換→リサイズ→行列置換
XSize は変更できても YSize は変更できない、ならば、
XとYを行列置換してからリサイズしてしまおう、というようなお話。
2例とも実用コードとしてインプット、アウトプットがある形で書いています。
今回は行列置換の方法を解り易くする為に
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による1次元化など
私では到底おぼつかない内容…
とても楽しく拝見しました。
またバカなことばかりお伺いするとは思いますが、
今後ともどうぞ宜しくお願い致します。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) ExcelVBAに関する質問 3 2023/02/17 10:47
- Excel(エクセル) Excelにて、フォルダ内のTextファイルをマクロで統合すると文字化けしてしまう時の解消コード 4 2023/01/01 07:32
- Visual Basic(VBA) Dateserialで データ抽出 2 2022/06/26 21:07
- Visual Basic(VBA) userformでSheetを選択して開くコード 1 2023/05/15 16:27
- Visual Basic(VBA) ExcelVBAで質問です。離れた二次元配列を一つにしたい 4 2022/07/26 19:06
- Visual Basic(VBA) EXCEL VBAにて動的にCheckBOXを複数作成し、同BOXにイベントを追加したい 1 2023/03/16 07:05
- Visual Basic(VBA) ①ExcelVBAでカレンダーを作り、別のユザーフォームで日付を入力したいのですがエラーになります。 1 2023/02/17 18:39
- その他(Microsoft Office) マクロVBAについて 1 2022/09/06 18:12
- Visual Basic(VBA) VBAでのMATCH関数 3 2022/10/17 19:06
- Visual Basic(VBA) エクセル VBA 処理スピードを上げたいのですが。 6 2023/03/31 20:52
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
配列数式の解除
-
VBA 1次元配列を2次元に追加する
-
subの配列引数をoptionalで使う...
-
ListViewで、非表示列って作れ...
-
Excel-VBAの配列「Public Const...
-
2つ以上の変数を比較して最大数...
-
delphiで配列を、コピーするには。
-
2次元動的配列の第一引数のみを...
-
《エクセル2000》A列・B列の商...
-
VB6 配列を初期化したい
-
配列変数の添字が範囲外ですと...
-
[Excel2000_VBA] 型が一致しま...
-
Dim は何の略ですか?
-
VLOOKUP関数で、一番下...
-
for each の現在の配列ポインタ...
-
Access 2007 複数のテキストボ...
-
fortranのwrite文について
-
配列を任意の数値で埋める方法
-
エクセルで最小値から0を除く方法
-
VBのコントロール配列を二次元...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
配列数式の解除
-
2つ以上の変数を比較して最大数...
-
VBA 1次元配列を2次元に追加する
-
特定のセル範囲で4文字以上入力...
-
for each の現在の配列ポインタ...
-
VBのFunctionで、配列を引数...
-
subの配列引数をoptionalで使う...
-
VB6 配列を初期化したい
-
ListViewで、非表示列って作れ...
-
配列変数の添字が範囲外ですと...
-
Excel-VBAの配列「Public Const...
-
2次元動的配列の第一引数のみを...
-
VBAで近似曲線の係数取得
-
VLOOKUP関数で、一番下...
-
配列に同じ値を入れる方法
-
エクセルで最小値から0を除く方法
-
linest関数に配列を渡す
-
配列を任意の数値で埋める方法
-
Dim は何の略ですか?
-
配列内の内容を全て表示する方法
おすすめ情報