VBAでセルに式を入力するにはfomulaプロパティを使います。
セルに式を入れるなら、
「普通にExcel上で式を書けば良いのでは？」
「fomulaって誰得？」
というのが、正直な感想でした。
じゃあなぜ使うのかと言ったら、参照するセルがその時々で違うときに活躍してくれるからです。
でも、この子、変数を入れるのめちゃくちゃ、めんどくさい。
というより、分かりづらい。
同じ悩みを持つ方のため、一肌脱いでみる。（自分のメモにもなるし）
今回やりたい事
実験で取得した値から近似曲線を取得する（データ取るだけのため割愛）
範囲が変わる、近似曲線の係数を求める。（指数関数）
これをfomulaプロパティを使って実現する。
VBAで指数関数の係数を求める
y=a ebの係数aとbを求めます。
最初は計算式で係数を求めようとしましたが、無理じゃーとなって挫折。
エクセルの関数を使えば、簡単に係数が求まるというベストアンサーを見かけたので、まるっとコピペしたら本当に係数が算出できた。
その式がこちら。
=EXP(INTERCEPT(LN($B$38:$B$43), $A$38:$A$43))
=SLOPE(LN($B$38:$B$43), $A$38:$A$43)
$$マークのところは自分の求めたいセルの値で良いですよ。
じゃあ、セルにこの式書けば良いんですけど、
私が求めているのは、セルの参照範囲が変わっても対応できるモノ。
参照範囲を可変にする、すなわち、変数が必要です。
では、VBAで書きましょう、そうしましょう。
で、書いたのがこちら。
最初は、セルの値が入ればいいんだろと、RangeとCellsで指定していますが、勿論エラーではじかれます。
そりゃ、シート関数にこんな関数はありませんからね。
ましてや、セルの値を求めようとしてますね。
それに気づかず、なんでできないのかと悩んで三日。
'入力された値から行数を取得
i = Range("N4").Value
p = Range(Cells(i + 25, 2), Cells(i + 30, 2))
q = Range(Cells(i + 25, 1), Cells(i + 30, 1))
Range("Z11").Formula = "=EXP(INTERCEPT(LN("p"), "q"))" '係数a
Range("AA11").Formula = "=SLOPE(LN("p"), "q")" '係数b
ふとお昼寝から目が覚めて、セルのアドレスが欲しいということに気が付きました。
ということで、セルのアドレスを求めて、変数に代入、確認のために書き出してみると無事、アドレスがゲットで来ている。
よっしゃーい！
で、あとはこいつをformulaの中に入れれば完成。
なんですが、変数が文字列としてそのまま、セルに書き出されています。
なんてこったい、Googleせんせーい！
と助けを求めて、formulaでは変数を＆で囲むということが分かりました。
完成したプログラムがこちら。
'入力された値から行数を取得
i = Range("N4").Value
p = Range(Cells(i + 25, 2), Cells(i + 30, 2)).Address
q = Range(Cells(i + 25, 1), Cells(i + 30, 1)).Address
Range("Z11").Formula = "=EXP(INTERCEPT(LN(" & p & "), " & q & "))" '係数a
Range("AA11").Formula = "=SLOPE(LN(" & p & "), " & q & ")" '係数b
+25とかは私の作った表に対応させているだけですので、あまり意味はありません。
おそらく、もっと簡単にできるのでしょうが、初心者にはこれが限界。
答えは出てるし、いいよね。
指数近似の係数を求める（範囲が可変）でマクロを組みたい方の助けになりましたかね。
めっちゃ、ピンポイントなマクロ作成でした。
間違っていたらごめんね！
コメント