VBAを使って抵抗率補正係数を求めたい

Question

VBAを用いて下記に添付したRCF（抵抗率補正係数）を求める計算プリグラムを作成したい。

(a=1cm , b=1cm , t=100nm , (xa , xb , xc , xd)=(0, 0.1 ,0.2 ,0.3), (ya , yb , yc , yd)=0 )

以下、作成したプログラムです。
いろいろ変えてみてcoshやsinhやP８でオーバーフローしてしまいます。
expの中身が710を超えるとオーバーフローしてしまうことは既知なんですがそれを変えるために何かいい方法ありませんか。
Sub 演習()

Dim Arg1 As Double
Dim Arg2 As Double
Dim Arg3 As Double
Dim Arg4 As Double
Dim Arg5 As Double
Dim Arg6 As Double
Dim Arg7 As Double
Dim Arg8 As Double
Dim Arg9 As Double
Dim Arg10 As Double
Dim Arg11 As Double
Dim Arg12 As Double
Dim K1 As Double
Dim K2 As Double
Dim K3 As Double
Dim K4 As Double
Dim K5 As Double
Dim K6 As Double
Dim K7 As Double
Dim K8 As Double
Dim K9 As Double
Dim P1 As Double
Dim P2 As Double
Dim P3 As Double
Dim P4 As Double
Dim P5 As Double
Dim P6 As Double
Dim P7 As Double
Dim P8 As Double
Dim P9 As Double
Dim L1 As Double
Dim L2 As Double
Dim L3 As Double
Dim L4 As Double
Dim L5 As Double
Dim L6 As Double
Dim L7 As Double
Dim L8 As Double
Dim L9 As Double
Dim Q As Double
Dim m As Integer
Dim n As Integer
Dim A As Double
Dim b As Double
Dim t As Double
Dim xa As Double
Dim xb As Double
Dim xc As Double
Dim xd As Double
Dim ya As Double
Dim yb As Double
Dim yc As Double
Dim yd As Double
Dim PI As Double


 
A = 1000000000
b = 1000000000
t = 300
xa = 0
xb = 10000000
xc = 20000000
xd = 30000000
ya = 0
yb = 0
yc = 0
yd = 0
m = 1
n = 1
PI = 4 * Atn(1)
Q = (yb - yc) / A




K9 = 0
P9 = 0
L9 = 0


For m = 1 To 10

Arg1 = m * PI / A
Arg2 = (yb + b / 2)
Arg3 = (yc + b / 2)
Arg4 = (ya - b / 2)
Arg5 = (yb - b / 2)
Arg6 = (yc - b / 2)
Arg7 = (yd + b / 2)
Arg8 = n * PI / t
Arg9 = (Arg1) * (Arg1) + (Arg8) * (Arg8)
Arg10 = Sqr(Arg9)




K1 = 2 / m * PI * WorksheetFunction.sinh(b * Arg1)
K2 = Cos(Arg1 * xb) * WorksheetFunction.cosh(Arg1 * Arg2)
K3 = Cos(Arg1 * xc) * WorksheetFunction.cosh(Arg1 * Arg3)
K4 = Cos(Arg1 * xa) * WorksheetFunction.cosh(Arg1 * Arg4)
K5 = Cos(Arg1 * xb) * WorksheetFunction.cosh(Arg1 * Arg5)
K6 = Cos(Arg1 * xc) * WorksheetFunction.cosh(Arg1 * Arg6)
K7 = Cos(Arg1 * xd) * WorksheetFunction.cosh(Arg1 * Arg7)

K8 = K1 * ((K2 - K3) * K4 - (K5 - K6) * K7)


K9 = K9 + K8
   
Next m

For n = 1 To 100
Arg1 = m * PI / A
Arg2 = (yb + b / 2)
Arg3 = (yc + b / 2)
Arg4 = (ya - b / 2)
Arg5 = (yb - b / 2)
Arg6 = (yc - b / 2)
Arg7 = (yd + b / 2)
Arg8 = n * PI / t
Arg9 = (Arg1) * (Arg1) + (Arg8) * (Arg8)
Arg10 = Sqr(Arg9)
P1 = 2 / A * Arg8 * sinh(b * Arg8)
P2 = cosh(Arg8 * Arg2)
P3 = cosh(Arg8 * Arg3)
P4 = cosh(Arg8 * Arg4)
P5 = cosh(Arg8 * Arg5)
P6 = cosh(Arg8 * Arg6)
P7 = cosh(Arg8 * Arg7)

P8 = P1 * ((P2 - P3) * P4 - (P5 - P6) * P7)
P9 = P9 + P8
Next n

For m = 1 To 100
 For n = 1 To 100
Arg1 = m * PI / A
Arg2 = (yb + b / 2)
Arg3 = (yc + b / 2)
Arg4 = (ya - b / 2)
Arg5 = (yb - b / 2)
Arg6 = (yc - b / 2)
Arg7 = (yd + b / 2)
Arg8 = n * PI / t
Arg9 = (Arg1) * (Arg1) + (Arg8) * (Arg8)
Arg10 = Sqr(Arg9)


 L1 = 4 / A * Arg10 * WorksheetFunction.sinh(b * Arg10)
L2 = Cos(Arg1 * xb) * WorksheetFunction.cosh(Arg10 * Arg2)
L3 = Cos(Arg1 * xc) * WorksheetFunction.cosh(Arg10 * Arg3)
L4 = Cos(Arg1 * xa) * WorksheetFunction.cosh(Arg10 * Arg4)
L5 = Cos(Arg1 * xb) * WorksheetFunction.cosh(Arg10 * Arg5)
L6 = Cos(Arg1 * xc) * WorksheetFunction.cosh(Arg10 * Arg6)
L7 = Cos(Arg1 * xd) * WorksheetFunction.cosh(Arg10 * Arg7)

L8 = L1 * ((L2 - L3) * L4 - (L5 - L6) * L7)

L9 = L9 + L8

Next n
Next m

 
Cells(5, 5) = K9
Cells(5, 6) = P9
Cells(5, 7) = L9

End Sub

Function sinh(x As Double)
Dim A As Double
Dim i As Long
Dim G As Double
Dim H As Double
Dim K As Double
Dim J As Double

H = 1
For i = 1 To 10000000

G = Exp(x / 10000000)
H = (G * H) / 10000000



Next i

J = 1
For i = 1 To 10000000

K = Exp(-x / 10000000)
J = (J * K) / 1000


Next i


sinh = (H - J) / 2

End Function

Function cosh(x As Double)
Dim A As Double
Dim v As Long
Dim G As Double
Dim H As Double
Dim K As Double
Dim J As Double

H = 1
For v = 1 To 10000000

G = Exp(x / 10000000)
H = G * H / 1000000000

Next v

J = 1
For v = 1 To 10000000

K = Exp(-x / 10000000)
J = K * J / 10000

Next v


cosh = (H + J) / 2

End Function

Comments

[ExcelVBAer]

演習！？ まさかの宿題ですか？？

[ExcelVBAer]

コードは『<code>』ボタンを使って記載してください

Answer 1

計算が得意な他の言語でやるべきじゃない？
よく分からないけど、R や Python(ﾊﾟｲｿﾝ) とか。

あと、自作関数の cosh , sinh の処理内容を見ましたが、
やりたい事は分かるけど、計算の方針自体が違うから、
文系？って思っちゃいますよ。。。

あと、自作しても、Exp(1)で e の正確な値が
取得できないみたいなので、
誤差が出ちゃうみたいです。

それでもVBAでっていうのであれば、
Double型やCurrency型を使って、
cosh,sinh,Exp を自作する方向で頑張ってください。

Answer 2

expの中身が710を超えるとオーバーフローしてしまうことは既知なんですがそれを変えるために何かいい方法ありませんか。

Exp 関数

上記によると戻り値は Double です。引数に 709.782712893以上を与えるとエラーになると書いてあります。

イミディエイトウィンドウで確認

?Exp(709.782712893)
 1.7976931341721E+308 

?Exp(709.782712894)
'オバーフローエラー

Doubleを使う限りは不可能です。また、VBAでDoubleの範囲より大きい数を扱える型はありません。

1.7976931341721E+308 って308桁の数値ということです。
そのような巨大な数値以上になるということ自体が現実的でないような気がします。
抵抗率補正係数というものについては全く専門外なのであくまで感想ですが。
途中の式に間違いがないのか検討されては。

補足

下記のスレッドの続きだと思いますので、回答をつける場合は参照しておくといいかと。

VBA - VBAを使ってsinh,coshを使いたい。(133368)｜teratail

Answer 3

取り敢えず型を揃えてみてはどうでしょうか。

全てDoubleで定義してみて下さい。
Functionの戻り値や演算で使用している数値も同様にDoubleで。

Function cosh(x As Double) As Double

Function sinh(x As Double) As Double

2だとInteger扱いですが、2#のように記述するとDoubleとして扱ってくれます。

Doubleは誤差が生じるので、問題が有るようなら、Currency型も検討してみて下さい。

追記

hatenaさんの回答を見て、cosh()やsinh()を試してみましたが、Expの限界になる前にオーバーフローしました。
exp()を使用しているのはこのfanctionだけでしたので、ここを対処すれば良いと思いますが、
無駄なロジック多いし、そもそも何してんのか不明です。幾つかパラメータの桁数を変えて確認しましたが0しか返却しませんし。

上記とは別に、VBAで処理する際、除算は誤差を生むので、行うとしてもまとめて最後で行うことを良くしますが、オーバーフローに関しても途中でオーバーフローしないように、式を分解して展開するのが必要な箇所があるのかもしれません。

Comments

[hatena19]

主さんは、710以上を引数に与えるとオバーフローになるということはわかっていて、それを超える方法はないかとの質問だと思います。
HSin(X) = (Exp(X) - Exp(-X)) / 2
HCos(X) = (Exp(X) + Exp(-X)) / 2
というExpを使った簡単な式に置き換えられますのが、これを展開して、
オーパーフローしないような式に分解できればいいのですが、どうでしょうか。

[sazi]

目的は、「抵抗率補正係数を求めたい」なのであって、過程でオーバーフローが発生してなんとかしたいというのは副次的な目的と捉えています。
hatenaさんも書かれているように、オーバーフローする数値自体が計算過程に於いて必要なのかが疑問なのです。

function自体も意図が不明なので、回答としては、そもそもオーバーフローするような数値になってるのは、どこか誤りがあるんじゃないの？というのが回答の趣旨です。

sinh()やcosh()は、sin()やcos()の亜種なのかと思ってsinh()やcosh()でオーバーフローする値を与えてあげても、オーバーフローしないし、幾つかのパターンを与えても結果が0というのも、どこか誤りがあるのではと思うので、今のコードのsinh()やcosh()がターゲットになるかどうかすら不明な状態です。

[hatena19]

HSin(X) は (Exp(X) - Exp(-X)) / 2 に置き換えられます。下記リンク参照
超越関数 https://msdn.microsoft.com/ja-jp/library/cc392350.aspx
主さんの sinh() や cosh() は意味不明です。
どちらにしても、主さんのコードに間違いがあるか、与える引数の値が間違っているかだと思ってます。

[sazi]

成程。sinh()やcosh()は、双曲線コサインや双曲線サインを求めようとしてるってことですね。
元の数式のどこかって言われても分からないですけどｗ
展開については、Exp(X) - Exp(-X)はそもそも0じゃないの？というレベルですからｗ、分かりません。