前記事の訂正
去年のカレンダーで、
下記のような関数を使って、テーブルN行の値を、各変数に割り当てていました。
Call DD(AR(R,N),年,機械,連番,製品番号,製品名)
Sub DD(AA, ParamArray AP())
Dim J: For J = 0 To UBound(AP)
AP(J) = AA(J + 1)
Next J
End Sub
Function AR(R, Optional M = 0, Optional N = 0, Optional S = "")
On Error Resume Next
M = IFZS(M, MM(R))
N = IFZS(N, R.Rows.Count)
ReDim S(1 To N): Dim J: For J = 1 To N: S(J) = DI(R, M, J): Next J
AR = S
End Function
しかし、Rangeと配列は同じように扱えることがわかったので、わざわざ配列にする必要はないので、
DDを捨てて下記の関数を使うようになりました。
Call DR(R,I,年,機械,連番,製品番号,製品名)
Sub DR(R, I, ParamArray PA()) ' DATA READ
Dim J: For J = 0 To UBound(PA)
PA(J) = DI(R, I, J + 1)
Next J
End Sub
やってみたら上手くいきました。
関数MI はWorksheetFunction.Matchの再定義で、RにはRangeが入るのが期待されるていますが、
Excelでスピルを使う方法で、一次元の配列を入れてみるとちゃんと値を返してくれました。
Rangeと配列は、大体同じじゃない?
Function TI(R, V, Optional S = "") ' TABLE INDEX
S = MI(R, V) ' 配列にMatch が使えるなんて驚きだ!
If S <> "" Then S = S - 1 ' 配列にあわせるために-1
TI = S
End Function
Function MI(R, V, Optional S = "") ' MATCH INDEX
On Error Resume Next
S = WorksheetFunction.Match(V, R, 0)
MI = S
End Function
スピル時代のExcel VBA
次期Excelでスピルが有効になると、Rangeと配列が双方向にデータのやり取りが可能となり、
ユーザ定義関数が格段に書き易くなります。
無理にvslookupのような既存の関数を組み合わせてスパゲッティにするバッドノウハウよりも、
ユーザ定義関数をきれいに書くという作法を身に着けさせるように、
Excelの入門書を書き直す必要があるかと思います。
ではでは。