ExcelVBA ユーザ定義関数と定数配列

質問

2010以降のExcelVBAです
ユーザー定義関数として使うfunction
此の引数として
幾つかの項に配列数式、又は定数配列を与えたいのです

が、
此の際の
引数の受け方と処理の仕方
此が判りません

例を交え
お教え願えないでしょうか？
宜しくお願い致します。

回答No.1

ushi2015 · ベストアンサー

こんにちは

Sub test()
Dim a As Variant
Dim d As Variant
a = Array(1, 2, 3, 4, 5)
d = test_func(a)
Debug.Print d
End Sub
Function test_func(v As Variant) As Variant
Dim i As Long
For i = LBound(v) To UBound(v)
test_func = test_func + v(i)
Next
End Function

とか、ParamArray 使うとかかと思いますけど、配列数式を与えるという
配列数式とはどのようなものですか？

お礼

有り難うございます。

配列数式ですが、
一例です
=mid("有り難うございます。",row(indirect("a1:a"&len("有り難うございます。"))))
結果は["有","り","難","う","ご","ざ","い","ま","す","。"]

詳しくは此方が解り易いかと
http://pc.nikkeibp.co.jp/pc21/special/hr/

補足

求めるものは
例として
MultiMatch({検査値｜定数配列又は配列数式又はレンジ},{捜索対象｜定数配列又は配列数式又はレンジ}）
動作：捜索対象中の複数の検査値を探し
　　　どの検査値が何番目に該当するか
　　　全て返す

戻り値
単一の値又は2次元定数配列

追伸
お礼コメント中記載の
先の式を訂正致します
正
　式
　　=MID("有り難うございます。",ROW(INDIRECT("a1:a"&LEN("有り難うございます。"))),1)

　結果
　　{"有";"り";"難";"う";"ご";"ざ";"い";"ま";"す";"。"}

誤
　式
　　=mid("有り難うございます。",row(indirect("a1:a"&len("有り難うございます。"))))

　結果
　　["有","り","難","う","ご","ざ","い","ま","す","。"]
謝罪の上謹んで訂正致します
申し訳ありません

回答No.3

ushi2015

こんにちは

難しすぎてついて行けないのですが、

Function MultiMatch(検査値() As Variant, 捜査対象() As Variant) As Variant
Dim i As Long
Dim j As Long
Dim v As Long
Dim s As Long
Dim e As Long

s = LBound(検査値)
e = UBound(検査値)
ReDim d(1 To e, 1 To 1)
For i = 1 To e
d(i, 1) = 検査値(i)
Next
For i = s To e
j = 2
For v = 1 To UBound(捜査対象)
If 捜査対象(v) = 検査値(i) Then
If UBound(d, 2) < j Then
ReDim Preserve d(1 To e, 1 To j)
End If
d(i, j) = v
j = j + 1
End If
Next
Next
MultiMatch = d
End Function

として、セルA1：C5を選択して、数式バーに
=MultiMatch({1,2,3,4,5},{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,1})
と入力して、Ctrl+Shift+Enter とすると
1 1 17
2 2 0
3 3 0
4 4 0
5 5 0
となります。
行列が逆ですけど、結果を配列で返す事は出来ていると思いますけど、
参考になりますか？

お礼

有り難うございます。

まずは謝罪させてください
先のお礼欄に
また誤記がありました、
済みません。

正
が、
型か一致しない
と、

誤
が、
方か一致しない
と、

謹んで訂正致します。

さて、
要点としては
()付きのvaliant型で引数を受ける
と、いう点ですね

回答No.2

ushi2015

こんばんは

済みません、まだどのような処理が希望なのか分かりません。
引数に配列ではなくて配列数式をセットするのですか？

何か全然的外れな気もしますが、配列かセル範囲を引数として、

Sub test()
Dim v As Variant
Call 準備
v = MultiMatch(Array("Dummy3", "Dummy8"), Range("A1:A10"))
v = MultiMatch(Array("Dummy3", "Dummy8"), Range("A1:J1"))
End Sub
Sub 準備()
Dim i As Long
For i = 1 To 10
Cells(i, 1) = "Dummy" & i
Cells(1, i) = "Dummy" & i
Next
End Sub
Function MultiMatch(検査値 As Variant, 捜査対象 As Variant) As Variant
Dim i As Long
Dim s As Long
Dim e As Long
s = LBound(検査値)
e = UBound(検査値)
ReDim d(e, s + 1)
For i = s To e
d(i, s) = 検査値(i)
d(i, s + 1) = Application.Match(検査値(i), 捜査対象, 0)
Next
MultiMatch = d
End Function

みたいな感じでしょうか？

お礼

有り難うございます。

>引数に配列ではなくて配列数式をセットす…
そうです

用法の一例としては、
=iferoor(
　　offset(
　　　$a$1
　　　,hlookup(
　　　　3
　　　,MultiMatch(
　　　　row(
　　　　　$a$1:$a$5
　　　　)
　　　　,row(
　　　　　$a$1:$a$90
　　　　)
　　　)
　　　,column(
　　　　a1
　　　)
　　　,0
　　　)-1
　　,0
　　,1
　　,1
　)
　,""
)
求めた割には
使い方か
直ぐには思い浮かばず
チープなものしか
書けませんでした
済みません

流れとしては
MultiMatchにより
1から90の内、
1，2，3，4，5，
此等と一致するものを
見出し付きで
一覧データにし、
{1,2,3,4,5;1,2,3,4,5}
其の値を参照テーブルとして
hlookupにより
参照テーブル中の
見出しの値３
此に当たる列を
ｃｏｌｕｍｎで指定して
上から順に読み出し
キチンと読めたら
A1からの相対参照で1セル読み出す
何らかのエラーがあった場合は
””にする
と、言うものになります

此方でもよく質問に上がる
データ検索を配列数式で行う際の
いいツールに
此はなり得ます
よね？

因みに
普通Matchは
検索キー1つに対し
1つ該当を見つければ
其の検索を止めます
が、
MultiMatchは
全ての検索キーに対し
各々においてヒットする全てを返す
です。

こういう事は申し難いのですが
複数の検索値に対し
最初の1つのみを返すのは
現行Matchと変わらないかも
です。

で、話は変わりますが
前回ご回答を受け
練習してみました
が、
方か一致しない
と、エディッターに怒られます。
function Large(配列 As valiant,フラグ As long) As valiant
　Large= Evaluato("Large("　&　配列　&　","　&　フラグ　&　")")
Endfunction

function　main(θ1　As　valiant,θ2　As　valiant)　As　valiant
　main=Large(Arrey(θ1,θ2),2)
End　function

何故でしょう？

追伸
MultiMatchの
検査対象に
何らかの式を微分したもの
此に
(ROW(A1:A1000001)-1)/1000
等で、値を与えて
配列数式に仕立てたもの
検査値に
０
で、
近似的極大極小を見つける
何て事も
出来るかも知れません。

まあ、厳密に言えば0にはならない
ので
何らかの工夫が必要になりますが
ね