VBAで複素数の計算とSparameterの変換

Excel VBAで複素数の計算

vbaで複素数を扱う場合、WorkSheetFunctionを使うより自分で関数を作ったほうがかなり速い。

'複素数の型宣言

Type Complex

    re As Double

    im As Double

End Type

'VBA 複素数の定義

Function CPX(a As Double, b As Double) As Complex

    CPX.re = a

    CPX.im = b

End Function

Function CPXR(a As Complex) As Double

    CPXR = a.re

End Function

Function CPXI(a As Complex) As Double

    CPXI = a.im

End Function

Function CPXABS(z As Complex) As Double

    CPXABS = Sqr(z.re ^ 2 + z.im ^ 2)

End Function

Function CPXSUM(z1 As Complex, z2 As Complex) As Complex

    Dim z As Complex

    z.re = z1.re + z2.re

    z.im = z1.im + z2.im

    CPXSUM = z

End Function

Function CPXSUB(z1 As Complex, z2 As Complex) As Complex

    Dim z As Complex

    z.re = z1.re - z2.re

    z.im = z1.im - z2.im

    CPXSUB = z

End Function

Function CPXPRD(z1 As Complex, z2 As Complex) As Complex

    Dim z As Complex

    z.re = z1.re * z2.re - z1.im * z2.im

    z.im = z1.re * z2.im + z1.im * z2.re

    CPXPRD = z

End Function

Function CPXDIV(z1 As Complex, z2 As Complex) As Complex

    Dim z As Complex

    Dim k As Double

    k = z2.re ^ 2 + z2.im ^ 2

    z.re = (z1.re * z2.re + z1.im * z2.im) / k

    z.im = (z1.im * z2.re - z1.re * z2.im) / k

    CPXDIV = z

End Function

vbaでSパラメータを処理するときのフォーマット変換とk値やMAGの算出

Function RItoDB(r, i)

    RItoDB = 20 * Log(Sqr((r ^ 2) + (i ^ 2))) / Log(10)

End Function

Function RItoMAG(r, i)

    RItoMAG = Sqr((r ^ 2) + (i ^ 2))

End Function

Function RItoANG(r, i)

    RItoANG = WorksheetFunction.Degrees(WorksheetFunction.Atan2(r, i))

End Function

Function LINtoDB(mag)

    LINtoDB = 20 * Log(mag) / Log(10)

End Function

s11 = CPX(S_11R, S_11I)

s21 = CPX(S_21R, S_21I)

s12 = CPX(S_12R, S_12I)

s22 = CPX(S_22R, S_22I)

' calculation MAG k

d = CPXABS(CPXSUB(CPXPRD(s11, s22), CPXPRD(s12, s21)))

bb = (1 + CPXABS(s11) * CPXABS(s11) - CPXABS(s22) * CPXABS(s22) - d * d)

k = (1 - CPXABS(s11) * CPXABS(s11) - CPXABS(s22) * CPXABS(s22) + d * d) / (2 * CPXABS(CPXPRD(s12, s21)))

If (1 - CPXABS(s11)) <> 0 Then vswr1 = (1 + CPXABS(s11)) / (1 - CPXABS(s11))

If (1 - CPXABS(s22)) <> 0 Then vswr2 = (1 + CPXABS(s22)) / (1 - CPXABS(s22))

msg = 10 * (Log(CPXABS(s21) / CPXABS(s12)) / Log(10))

If bb > 0 Then mag = msg - 10 * (Log(k * 1 + Sqr(k * k - 1))) / Log(10)