VBAでcsvファイルを開く

 VBAでcsvファイルを開く

QueryTableで開くと速いという記事を見つけたので試してみた。別に早くない。区切り文字が決まっているならline inputで1行づつ読んでsplitして配列に入れて貼り付けるほうが早い。

sub open_csv()

    Dim msh, fname

    fname ="C:\local\test.csv"

    msh ="test"

    Sheets.Add(After:=Sheets(Sheets.Count)).Name  = msh

    Set ws = Sheets(msh)

    Set qt = ws.QueryTables.Add(Connection:="TEXT;" & fname, Destination:=ws.Range("A1")) 

    With qt

        .TextFilePlatform = 932          ' 文字コードを指定

        .TextFileParseType = xlDelimited ' 区切り文字の形式

        .TextFileCommaDelimiter = True   ' カンマ区切り

        .RefreshStyle = xlOverwriteCells ' セルに上書き

        .Refresh                         ' データを表示

        .Delete                          ' CSV との接続を解除

    End With

End Sub

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)