Excel VBAで、ワークシート関数に変数を使いたいのですが？

質問

VBA初心者です。
例えば1次式y=mx+bのmとbを取得するために、
Selection.FormulaArray = "=LINEST(A2:C2,A3:C3)"
と記述した場合、例えば範囲A3:C3のC3を変数として指定する事はできるのでしょうか？
2次式の場合は、
Selection.FormulaArray = "=LINEST(A2:C2,A3:C4)"
3次式の場合は、
Selection.FormulaArray = "=LINEST(A2:C2,A3:C5)"
となるので,
If N次式 then
Selection.FormulaArray = "=LINEST(A2:C2,A3:C?)"
End If
の?に適切な値を変数を使って入れたいのですが・・・。
質問が解りにくくて申し訳ないですが、よろしくお願いします。

回答No.5

Wendy02 · ベストアンサー

mumu001様 #3 のWendy02です。

最初に、以下は、間違いでした。みなさんのコードとあわせるために、オンラインで訂正したのが、いけませんでした。

訂正前 7行目の
×　 Addr = Range("A3:C3").Resize(Cnt).Address(0, 0)
　　　↓
　 Addr = Range("A3:C3").Resize(n).Address(0, 0)

となります。これは、説明する必要もありませんが、行の範囲を、次数で、Resizeで広げています。

　 Ret = Evaluate("LinEst(A2:C2," & Addr & ")")

　このRet の解は、配列で出力されます。なぜ、WorksheetFunction で行わないかというと、そのまま、Rangeオブジェクトを入れたらエラーが出ました。おそらく、引数自体も、1次元配列を取るのではないでしょうか？そうすると、Rangeオブジェクトは、Value化しても、2次元配列ですから、1次元に戻す変換コードが必要になってしまいます。その手間を省くために、配列がそのままでもよい、Evaluate で、ワークシートの関数を使いました。

　　 i = UBound(Ret) - LBound(Ret) + 1

　これは、その配列を取り出すために、解の数を数えています。
　通常は、UBoundで、添え字の上限に、「+1」を足せばよいのですが、場合によって、Option Base 1 の添え字の下限の数を変えている可能性がありますから、そのために、そのような式になっています。

　 ActiveCell.Resize(i).Value = Ret

Ret の中には、複数の解が入って、それは配列状態になっていますから、それを、1つずつセルに収めるには、その範囲を取って貼り付けてやらなくてはなりません。そのために、Resizeで、行の範囲を広げています。

これは、Excelのシートに書き出しているのですが、解が1つなら1行、解が2つなら2行というようにしています。

余計に話がややこしくなったかもしれませんね。

お礼

丁寧な説明ありがとうございました。
一読しただけでは、到底理解できそうもありませんのでじっくり調べてみたいと思います。ただ、Wendy02さんの理解の深さに驚くばかりです。（プログラミングをお仕事とされている方と想像します）

回答No.4

BLUEPIXY

#1ですが、n+2しないとダメですね。
間違えました

お礼

訂正のお返事ありがとうございました。
これからも質問することがあると思いますが、よろしくお願いします。

回答No.3

Wendy02

こんばんは。

VBAなら、そのまま、VBAで出力すればよいと思いますね。
目的が違うのでしょうか？ユーザー定義関数という手もないわけではありませんが・・・。

Sub LinestTest()
Dim Addr As String
Dim n As Integer
Dim Ret As Variant
Dim i As Long
　 n = 2 '次数
　 Addr = Range("A3:C3").Resize(Cnt).Address(0, 0)
　 Ret = Evaluate("LinEst(A2:C2," & Addr & ")")
　 i = UBound(Ret) - LBound(Ret) + 1
　 ActiveCell.Resize(i).Value = Ret
End Sub

お礼

お返事ありがとうございます。
なにぶん初心者なもので、処理のプロセスが理解できません。ヘルプ等で調べてみますが、お手数でなければ解説していただけませんでしょうか？

回答No.2

merlionXX

Ａ１セルにNが入っているものとします。

Sub test01()
n = Range("A1").Value
MsgBox n & "次式"
Selection.FormulaArray = "=LINEST(A2:C2,A3:C" & n + 2 & ")"
End Sub

お礼

返事が遅くなってすみません。
おかげさまで目的とする結果を得ることができました。
本当にありがとうございました。

回答No.1

BLUEPIXY

n=2 '２次式
…
Selection.FormulaArray = "=LINEST(A2:C2,A3:C" & n & ")"
みたいなかんじでどうでしょう

お礼

返事が遅くなってすみません。
アドバイスを参考に記述して、目的とする結果を得ることができました。ありがとうございました。