excel vbaでのexcel関数の利用

シンプルな関数はVBA側から使用できます。

Sub TEST01()

Dim data As Variant

NX = 3
ReDim data(1 To NX)

data(1) = 1
data(2) = 3
data(3) = 2

Debug.Print Application.Max(data)
Debug.Print Application.Small(data, 1)
Debug.Print Application.Small(data, 3)
Debug.Print Application.PercentRank(data, 3, 3)

Dim AA(8) As Variant
Dim BB(3) As Variant
Dim CC() As Variant

AA(1) = 1
AA(2) = 4
AA(3) = 3
AA(4) = 2
AA(5) = 3
AA(6) = 5
AA(7) = 6
AA(8) = 7

BB(1) = 3
BB(2) = 6
BB(3) = 9

CC = Application.Frequency(AA, BB)

End Sub

linestのvbからの使い方

以下の方法で、VBAからlinestを使って最小２乗法でparameterを作成できます。
以下は２次でのときです。

Dim x As Variant, y As Variant, rslt As Variant, i As Long
ReDim x(1 To 5, 1 To 2)
ReDim y(1 To 5, 1 To 1)
For i = 1 To 5
x(i, 1) = Sheets("LINEST").Cells(i + 3, 11)
x(i, 2) = x(i, 1) ^ 2
y(i, 1) = Sheets("LINEST").Cells(i + 3, 13)
Next i
rslt = Application.LinEst(y, x, True, True)
Debug.Print rslt(1, 1), rslt(1, 2), rslt(1, 3)