【L-RTK】MB出力処理ＶＢＡ高速化とバグ取り＜VBAも結構早くなる＞

2月 8, 2020
dj1711572002

F9PMovingBaseモードで　フィールド測定の回数が増えてきました。UBXログデータのバイナリファイルをたくさんため込んだのですが、現在のＶＢＡプログラムでは1MB程度のファイルをシートに展開するのに10分以上かかってしまって、とても、たくさんのＵＢＸログファイルを処理するわけにはいきませんでした。
そこで、ＶＢＡの高速化に取り組みました。

 

●シート書き込みが遅い原因と対策
①バイナリファイルを読み込んでからセンテンス区分けしながら
for next ループでセル1個ずつに書き込んでいた。
対策①＝＞センテンス区分けした結果は、センテンス毎に2次元配列変数にしてメモリーに保存した状態にしてから各種処理をする。

対策➁＝＞変数をシートへ一括で書き込むコマンドをつかう
こちらのブログにやり方が書いてあって大感謝です。
https://brain.cc.kogakuin.ac.jp/~kanamaru/lecture/vba2003/11-summary02.html

Range(Cells(1, 1), Cells(nd, 2)) = 配列変数名 ‘ 実際のセルへの書き込み　

＝＞ＶＢＡもセルとかシートを使わないで通常のＣ言語のように使えば、コンパイラほどではありませんが、そこそこの速度がでることを実感しました。しかし、限界があって
シリアル受信では、115200bpsで、100msec程度の速度しかでません。これがコンパイラ言語のように早くなれば、他の言語いらないのにといつも思ってます。
ＶＢ．ＮＥＴなら早いですがＥＸＣＥＬとリンクするのがＶＢＡのようにはいかないので
データを処理した後ＥＸＣＥＬで読み込まないといけないのが面倒です。

 

●読み込みバグとった
当初ＧＮＳＳの寸断でデータが飛んでいたと思っていたのですが、あまりにも頻繁に
飛びが発生するので、読み込みプログラムを疑ってみたら、大バグがありました。
バイナリーファイルは、デリミターがないので、どこからセンテンスの始まりかをヘッダの数値を探す以外にないのですが、ＵＢＸの場合はＢ５で始まります。
ＵＢＸの仕様は、これです。
https://www.u-blox.com/sites/default/files/u-blox_ZED-F9P_InterfaceDescription_%28UBX-18010854%29.pdf
そこで、Ｂ５を見つけてそこからスタートをかけて次のＢ５がでるところで１センテンスが終了したとみなしていたのですが、データ内にＢ５が発生している箇所が結構あって、読み間違ってました。

バグ修正：Ｂ５を見つけたら、センテンス種類を区分けしてそのセンテンスのバイト数だけ一挙に読み込んでしまう。
具体的には、ＭＢモードでの出力は、NAV-PVTとNAV-RELPOSNEDの2センテンスだけなので、どちらかを区別すればいい
＝＞UBX-NAV-PVT は,　ヘッダーがB5,62,01,07で100バイト
＝＞UBX-NAV-RELPOSNEDは、ヘッダーがB5,62,01,3Cで72バイト
なので、次のような読み込みプログラムに作りなおしました
ＵＢＸファイルから読み込んだデータは、bData()配列に収納されています。
それをDo while ループ内でセンテンス区分けして
Rdata(行、桁）配列と個別のＰＶＴ（行、桁）、REl(行、桁）配列に分解します。
全部代入がおわってから、
range .xxxxx=変数名で一挙にシートへ書き込んでＵＢＸファイルをシートへ読み込み完了です。

 

Private Sub CommandButton1_Click()

‘ファイル選択ダイアログでファイルを指定
Dim vFilePath As Variant
vFilePath = Application.GetOpenFilename

‘キャンセルボタンを押されたら、処理終了
If vFilePath = False Then
End
End If

‘ファイルサイズが０バイトの場合も処理終了
Dim nFileLen As Long
nFileLen = FileLen(vFilePath)
If nFileLen = 0 Then
End
End If

‘空いているファイル番号を取得
Dim iFile As Integer
iFile = FreeFile

‘指定されたファイルを取得したファイル番号としてバイナリモードで開く
Open vFilePath For Binary As #iFile

‘ファイルサイズ分のバイト配列を用意
Dim bData() As Byte
ReDim bData(0 To nFileLen – 1)

‘バイト配列に指定ファイルを展開
Get #iFile, , bData
Close #iFile
If bData(3) = 60 Then
startN = 72
‘ MsgBox Hex(bData(startN))

End If
If bData(3) = 7 Then
startN = 0
‘ MsgBox Hex(bData(startN))
End If

‘With Worksheets(“sheet1”)
L = 0
K = 1
N = 0
Npvt = 0
Nrel = 0
UserForm1.Label1.Caption = Str(nFileLen) & “Byte”
Application.ScreenUpdating = False
Debug.Print Time & ” – LOOPスタート”
Do While startN < nFileLen
For i = 0 To 99 ‘PVT=100個　nFileLen

‘Worksheets(“sheet1”).Cells(L, i) = Hex(bData(n))
PVT(Npvt, i) = Hex(bData(startN))
Rdata(L, i) = PVT(Npvt, i)
startN = startN + 1
‘Debug.Print “Rdata(L,i)pvt=”, Rdata(L, i), L, i
Next i

Npvt = Npvt + 1
L = L + 1
For i = 0 To 71 ‘RELPOSNED=72個　nFileLen
‘Worksheets(“sheet1”).Cells(L, i) = Hex(bData(n))
REL(Nrel, i) = Hex(bData(startN))
Rdata(L, i) = REL(Nrel, i)
startN = startN + 1
‘ Debug.Print “Rdata(L,i)rel=”, Rdata(L, i), L, i
Next i
Nrel = Nrel + 1
L = L + 1
If N Mod 1000 = 0 Then

End If
Loop
Debug.Print Time & ” – Rdata配列へ代入完了”
UserForm1.Label2.Caption = Str(L) + “行”
Debug.Print Time & ” – セル書き込みスタート”
‘配列変数から各シートへＨＥＸ書き込み
Worksheets(“Sheet1”).Range(Worksheets(“Sheet1”).Cells(1, 1), Worksheets(“Sheet1”).Cells(L, 100)) = Rdata ‘Sheet1へ受信データのＨＥＸ文字形式でセルへの書き込み
Worksheets(“PVT”).Range(Worksheets(“PVT”).Cells(1, 1), Worksheets(“PVT”).Cells(Npvt, 100)) = PVT ‘Sheet1へ受信データのＨＥＸ文字形式でセルへの書き込み
Worksheets(“RELPOSNED”).Range(Worksheets(“RELPOSNED”).Cells(1, 1), Worksheets(“RELPOSNED”).Cells(Nrel, 100)) = REL ‘Sheet1へ受信データのＨＥＸ文字形式でセルへの書き込み
Label3.Caption = “Sheet1,PVT,RELPOSNED Data Finished”

Debug.Print Time & ” – Sheet1,PVT,RELPOSNEDシートへのセル書き込み入完了”

Application.ScreenUpdating = True

Maxrow = Range(“A1”).End(xlDown).Row
MaxCol = Range(“A1”).End(xlToRight).Column

‘End With

End Sub

 

●以後の処理
ＵＢＸファイルから得られるデータを抽出します。
PVTからは、iTOW（時刻msec）とLongtitude,Latitudeを得ます。
RELPOSNEDからは、iTowと相対位置relN,relE,relD(cm),relHead(deg),
標高relSeaElevation(m),水平精度HAcc（ｍｍ）垂直精度VAcc（ｍｍ）を得ます

このプログラムも備忘録しておきます。

Private Sub CommandButton2_Click()
L = 2
K = 1
‘longD = B2L(“FF”, “FF”, “FF”, “B0”)

With Worksheets(“sheet2”)
.Cells(1, 1) = “iTow”
.Cells(1, 2) = “Longtitude”
.Cells(1, 3) = “Latitude”
.Cells(1, 4) = “reliTOW”
.Cells(1, 5) = “relN”
.Cells(1, 6) = “relE”
.Cells(1, 7) = “relD”
.Cells(1, 8) = “relLen”
.Cells(1, 9) = “relHead”
.Cells(1, 10) = “SeaHeight”
.Cells(1, 11) = “HAcc_mm”
.Cells(1, 12) = “VAcc_mm”
End With

With Worksheets(“Sheet1”)
‘PVT B5620107 ->RELPOSNED B562013C 順で出力される
Maxrow = .Range(“A1”).End(xlDown).Row
MaxCol = .Range(“A1”).End(xlToRight).Column

For i = 1 To Maxrow ‘nFileLen – 1
‘ header = Right(“0” & Hex(bData(i)), 2) + Right(“0” & Hex(bData(i + 1)), 2) + Right(“0” & Hex(bData(i + 2)), 2) + Right(“0” & Hex(bData(i + 3)), 2)

If .Cells(i, 4) = “7” Then ‘PVT
L = L + 1
c1 = .Cells(i, 7)
X1 = CLng(“&H” & .Cells(i, 7))
iTOW = B2L(.Cells(i, 10), .Cells(i, 9), .Cells(i, 8), .Cells(i, 7))
‘iTOW = CLng(“&H” & .Cells(i, 7)) + CLng(“&H” & .Cells(i, 8)) * 256 + CLng(“&H” & .Cells(i, 9)) * 65536 + CLng(“&H” & .Cells(i, 10)) * 16777216
Lon = B2L(.Cells(i, 34), .Cells(i, 33), .Cells(i, 32), .Cells(i, 31))
‘Lon = CLng(“&H” & .Cells(i, 31)) + CLng(“&H” & .Cells(i, 32)) * 256 + CLng(“&H” & .Cells(i, 33)) * 65536 + CLng(“&H” & .Cells(i, 34)) * 16777216
Lat = B2L(.Cells(i, 38), .Cells(i, 37), .Cells(i, 36), .Cells(i, 35))
‘Lat = CLng(“&H” & .Cells(i, 35)) + CLng(“&H” & .Cells(i, 36)) * 256 + CLng(“&H” & .Cells(i, 37)) * 65536 + CLng(“&H” & .Cells(i, 38)) * 16777216
X1 = .Cells(i, 43)
X2 = .Cells(i, 44)
X3 = .Cells(i, 45)
X4 = .Cells(i, 46)

SeaHeight = B2L(.Cells(i, 46), .Cells(i, 45), .Cells(i, 44), .Cells(i, 43))
‘ CLng(“&H” & .Cells(i, 43)) + CLng(“&H” & .Cells(i, 44)) * 256 + CLng(“&H” & .Cells(i, 45)) * 65536 + CLng(“&H” & .Cells(i, 46)) * 16777216
HAcc = B2L(.Cells(i, 50), .Cells(i, 49), .Cells(i, 48), .Cells(i, 47))
‘HAcc = CLng(“&H” & .Cells(i, 47)) + CLng(“&H” & .Cells(i, 48)) * 256 + CLng(“&H” & .Cells(i, 49)) * 65536 + CLng(“&H” & .Cells(i, 50)) * 16777216
VAcc = B2L(.Cells(i, 54), .Cells(i, 53), .Cells(i, 52), .Cells(i, 51))
‘ VAcc = CLng(“&H” & .Cells(i, 51)) + CLng(“&H” & .Cells(i, 52)) * 256 + CLng(“&H” & .Cells(i, 53)) * 65536 + CLng(“&H” & .Cells(i, 54)) * 16777216
Worksheets(“sheet2”).Cells(L, 1) = iTOW
Worksheets(“sheet2”).Cells(L, 2) = Lon * 1.1 ‘cm
Worksheets(“sheet2”).Cells(L, 3) = Lat * 1.1 ‘cm
Worksheets(“sheet2”).Cells(L, 10) = SeaHeight / 1000 ‘cm
Worksheets(“sheet2”).Cells(L, 11) = HAcc ‘cm
Worksheets(“sheet2”).Cells(L, 12) = VAcc ‘cm

End If

If .Cells(i, 4) = “3C” Then ‘RELPOSNED
relTow = B2L(.Cells(i, 14), .Cells(i, 13), .Cells(i, 12), .Cells(i, 11))
‘relTow = CLng(“&H” & .Cells(i, 11)) + CLng(“&H” & .Cells(i, 12)) * 256 + CLng(“&H” & .Cells(i, 13)) * 65536 + CLng(“&H” & .Cells(i, 14)) * 16777216
relN = B2L(.Cells(i, 18), .Cells(i, 17), .Cells(i, 16), .Cells(i, 15))
‘relN = CLng(“&H” & .Cells(i, 15)) + CLng(“&H” & .Cells(i, 16)) * 256 + CLng(“&H” & .Cells(i, 17)) * 65536 + CLng(“&H” & .Cells(i, 18)) * 16777216
relE = B2L(.Cells(i, 22), .Cells(i, 21), .Cells(i, 20), .Cells(i, 19))
‘ relE = CLng(“&H” & .Cells(i, 19)) + CLng(“&H” & .Cells(i, 20)) * 256 + CLng(“&H” & .Cells(i, 21)) * 65536 + CLng(“&H” & .Cells(i, 22)) * 16777216
relD = B2L(.Cells(i, 26), .Cells(i, 25), .Cells(i, 24), .Cells(i, 23))
‘relD = CLng(“&H” & .Cells(i, 23)) + CLng(“&H” & .Cells(i, 24)) * 256 + CLng(“&H” & .Cells(i, 25)) * 65536 + CLng(“&H” & .Cells(i, 26)) * 16777216
relLen = B2L(.Cells(i, 30), .Cells(i, 29), .Cells(i, 28), .Cells(i, 27))
‘relLen = CLng(“&H” & .Cells(i, 27)) + CLng(“&H” & .Cells(i, 28)) * 256 + CLng(“&H” & .Cells(i, 29)) * 65536 + CLng(“&H” & .Cells(i, 30)) * 16777216
relHeadn = B2L(.Cells(i, 34), .Cells(i, 33), .Cells(i, 32), .Cells(i, 31))
‘relHead = CLng(“&H” & .Cells(i, 31)) + CLng(“&H” & .Cells(i, 32)) * 256 + CLng(“&H” & .Cells(i, 33)) * 65536 + CLng(“&H” & .Cells(i, 34)) * 16777216

X1 = CLng(“&H” & .Cells(i, 15))
X2 = CLng(“&H” & .Cells(i, 16))
X3 = CLng(“&H” & .Cells(i, 17))
‘X4 = CLng(“&H” & .Cells(i, 18)) * 16777216

‘ If .Cells(i, 18) <> “FF” Then
‘relN = CLng(“&H” & .Cells(i, 15)) + CLng(“&H” & .Cells(i, 16)) * 256 + CLng(“&H” & .Cells(i, 17)) * 65536 + CLng(“&H” & .Cells(i, 18)) * 16777216
‘If .Cells(i, 18) >= 12 Then
‘ relN = relN – 4294967296#
‘ End If
‘ End If
‘If .Cells(i, 22) <> “FF” Then
‘relE = CLng(“&H” & .Cells(i, 19)) + CLng(“&H” & .Cells(i, 20)) * 256 + CLng(“&H” & .Cells(i, 21)) * 65536 + CLng(“&H” & .Cells(i, 22)) * 16777216
‘ If .Cells(i, 22) >= 128 Then
‘ relE = relE – 4294967296#
‘ End If
‘End If
‘ If .Cells(i, 26) <> “FF” Then
‘ relD = CLng(“&H” & .Cells(i, 23)) + CLng(“&H” & .Cells(i, 24)) * 256 + CLng(“&H” & .Cells(i, 25)) * 65536 + CLng(“&H” & .Cells(i, 26)) * 16777216
‘If .Cells(i, 26) >= 128 Then
‘ relD = relD – 4294967296#
‘End If
‘End If
‘If .Cells(i, 30) <> “FF” Then
‘ relLen = CLng(“&H” & .Cells(i, 27)) + CLng(“&H” & .Cells(i, 28)) * 256 + CLng(“&H” & .Cells(i, 29)) * 65536 + CLng(“&H” & .Cells(i, 30)) * 16777216
‘End If
‘If .Cells(i, 34) <> “FF” Then
‘ relHead = CLng(“&H” & .Cells(i, 31)) + CLng(“&H” & .Cells(i, 32)) * 256 + CLng(“&H” & .Cells(i, 33)) * 65536 + CLng(“&H” & .Cells(i, 34)) * 16777216
‘End If

Worksheets(“sheet2”).Cells(L, 4) = relTow
Worksheets(“sheet2”).Cells(L, 5) = relN ‘cm
Worksheets(“sheet2”).Cells(L, 6) = relE ‘cm
Worksheets(“sheet2”).Cells(L, 7) = relD ‘cm
Worksheets(“sheet2”).Cells(L, 8) = relLen ‘cm
Worksheets(“sheet2”).Cells(L, 9) = relHead / 100000 ‘deg

End If
‘Cells(L, K) = “‘” & Right(“0” & Hex(bData(i + j)), 2)
‘ K = K + 1

Next i

End With

End Sub

Function B2L(ByRef b4 As String, ByRef b3 As String, ByRef b2 As String, ByRef b1 As String) As Long
‘MsgBox Left(b4, 1)
If Left(b4, 1) <> “F” Then ‘最上位Fでなければ正のＬＯＮＧ
B2L = CLng(“&H” & b1) + CLng(“&H” & b2) * 256 + CLng(“&H” & b3) * 256 * 256 + CLng(“&H” & b4) * 256 * 256 * 256

Else
B2L = -(256 – CLng(“&H” & b1)) + (255 – CLng(“&H” & b2)) * 256 + (255 – CLng(“&H” & b3)) * 256 * 256 + (255 – CLng(“&H” & b4)) * 256 * 256 * 256

End If

End Function


●以後
データが正確に読み取って処理できるようになったので、ＭＢモードでの基礎的な精度検証をして、ＭＢモードの理解を深めて、更なる応用をしていきます。

More from my site

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です