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 回答日時： 2005/10/21 10:30

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さんの理解の深さに驚くばかりです。（プログラミングをお仕事とされている方と想像します）

お礼日時：2005/10/21 11:01

No.4 回答者： BLUEPIXY 回答日時： 2005/10/21 01:34

#1ですが、n+2しないとダメですね。

間違えました

この回答へのお礼

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

お礼日時：2005/10/21 08:57

No.3 回答者： Wendy02 回答日時： 2005/10/20 21:54

こんばんは。

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

この回答へのお礼

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

お礼日時：2005/10/21 09:09

No.2 回答者： merlionXX 回答日時： 2005/10/20 19:14

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

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

この回答へのお礼

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

お礼日時：2005/10/21 08:29

No.1 回答者： BLUEPIXY 回答日時： 2005/10/20 18:53

n=2 '２次式

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

この回答へのお礼

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

お礼日時：2005/10/21 08:27