【L-RTK】MB出力処理VBA高速化とバグ取り<VBAも結構早くなる>

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

 

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

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

Range(Cells(1, 1), Cells(nd, 2)) = 配列変数名 ‘ 実際のセルへの書き込み 
たった一行で、1MBのデータも数秒でシートへコピーしてくれました。

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

 

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

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

 

Private Sub CommandButton1_Click()

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

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

‘ファイルサイズが0バイトの場合も処理終了
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 & ” – セル書き込みスタート”
‘配列変数から各シートへHEX書き込み
Worksheets(“Sheet1”).Range(Worksheets(“Sheet1”).Cells(1, 1), Worksheets(“Sheet1”).Cells(L, 100)) = Rdata ‘Sheet1へ受信データのHEX文字形式でセルへの書き込み
Worksheets(“PVT”).Range(Worksheets(“PVT”).Cells(1, 1), Worksheets(“PVT”).Cells(Npvt, 100)) = PVT ‘Sheet1へ受信データのHEX文字形式でセルへの書き込み
Worksheets(“RELPOSNED”).Range(Worksheets(“RELPOSNED”).Cells(1, 1), Worksheets(“RELPOSNED”).Cells(Nrel, 100)) = REL ‘Sheet1へ受信データのHEX文字形式でセルへの書き込み
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

 

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

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

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でなければ正のLONG
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


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

コメントを残す

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