エクセル雑感
Variant仮引数にRange.Valueを配列で渡す方法

ExcelマクロVBAとエクセル関数についての私的雑感
最終更新日:2020-07-05

Variant仮引数にRange.Valueを配列で渡す方法


ツイッターで出したVBAのお題です。
Variant型は、どんなデータ型も受け入れることができます。
RangeオブジェクトのValueを配列で受け取るにはVariant変数が必要です。
ただし、実引数にRangeオブジェクトを直接指定する場合には注意が必要です。


お題のツイート

https://twitter.com/yamaoka_ss/status/1279311729231462400

【VBA問題】
Sub Q1()
 Range("A1:A6") = 1
 MsgBox fnc(Range("A1:A2"), (Range("A5:A6")), Range("A3:A4").Value)
End Sub
Function fnc(v1, v2, v3)
 Range("B1:B2") = v1
 Range("B3:B4") = v2
 Range("B5:B6") = v3
 fnc = WorksheetFunction.Sum(Range("B1:B6"))
End Function
Q1実行結果は?
・0
・2
・4

・6

VBA マクロ Variant Range.Value 配列

解説のツイート

以下で一連の解説ツイートをしています。

https://twitter.com/yamaoka_ss/status/1279771918011252738


複数セルのValueを配列にするにはVariant変数に代入します。
Variant変数 = Rangeオブジェクト.Value
※.Valueは省略可
※単一セルの場合は配列になりません。
Rangeを配列としてFunctionの引数に渡す場合はどうしたら良いでしょうか。
いったんVariant変数に入れてから渡せば問題ありません。


では直接引数にRangeを記述したらどうなるでしょうか。
実引数にRangeを指定し仮引数がVariantの場合、
関数側ではRangeオブジェクトのまま受け取ります。
今回の場合の変数v1がこれになります。
Variant変数v1はRangeオブジェクトです。
Range = Range
この代入ではValueはコピーされません。


左辺のRangeのValueがクリアされてしまいます。
これがエラーにならないのは要注意です。
この代入結果を期待してVBAを書くことはありえないと思います。
複数セルのRangeオブジェクトのValueをコピーするには、
右辺のRangeにValueを指定する必要があります。



通常は左辺右辺の両方にValueを指定して、
Range.Value = Range.Value
これでValueだけが一括でコピーされます。
では、実引数にRangeを指定し仮引数のVariantに配列を入れるにはどうしたら良いでしょうか。
実引数のRangeを配列にしてから渡すようにします。
次のような方法があります。


・Valueを指定する
・()で囲む
Valueプロパティを指定することで配列になります。
これが最も一般的な方法でしょう。
他の方法として、()で囲むことでRange既定のValueが取得されるので配列になります。
※CVar(Range(…))としても配列になりません。


今回の問題では、関数fncが受け取った時点では、
v1はRangeオブジェクト → 代入で左辺のValueがクリアされる
v2はValueの配列 → 代入で右辺の配列が左辺の.Valueに入る
v3はValueの配列 → 代入で右辺の配列が左辺の.Valueに入る
結果は、
B1:B2は空欄。
B3:B4は1が入ります。
B5:B6は1が入ります。


従って、答えは4です。
Sum(Range("B1:B6")) → 4
6の回答が圧倒的に多かったですが、
v1はRangeオブジェクトであり、
Range = Range
これではValueが入らないところが要点です。

Variant変数の一般的な説明は、
「VBAのVariant型について」
https://excel-ubara.com/excelvba4/EXCEL_VBA_441.html
Variantデータ型は、他の何らかのデータ型として明示的に宣言されていない変数で、全てのデータ型を入れることができます。Variantデータ型には型宣言文字はありません。Variant型は、特別な値Empty、Error、Nothing、Nullを格納することもできます。




同じテーマ「エクセル雑感」の記事

【超難問】エクセル数式問題
IFステートメントの判定
日付の謎:IsDateとCDate
ツイッター投稿用に文字数と特定文字で区切る
マクロ記録での色のマイナス数値について
VBAのString型の最大文字数について
Variantの数値型と文字列型の比較
Variant仮引数にRange.Valueを配列で渡す方法
Variant仮引数のByRefとByValの挙動違い
100桁の正の整数値の足し算
「VBA Match関数の限界」についての誤解


新着記事NEW ・・・新着記事一覧を見る

多階層フォルダ(ディレクトリ)の作成|VBAサンプル集(7月31日)
VBAのインデントについて|VBA技術解説(7月16日)
「VBA Match関数の限界」についての誤解|エクセル雑感(7月15日)
省略可能なVariant引数の参照不可をラップ関数で利用|VBA技術解説(7月12日)
100桁の正の整数値の足し算|エクセル雑感(7月9日)
LSetとユーザー定義型のコピー(100桁の足し算)|VBA技術解説(7月9日)
Variant仮引数のByRefとByValの挙動違い|エクセル雑感(7月5日)
Variant仮引数にRange.Valueを配列で渡す方法|エクセル雑感(7月5日)
Variantの数値型と文字列型の比較|エクセル雑感(7月1日)
VBAのVariant型について|VBA技術解説(6月30日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.繰り返し処理(For Next)|VBA入門
7.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ