I am trying to write a stream of data with minimal delay between buffer transfers in VBA. The data may be acquired with maximal 30~35 usec precision in VBA. But because of continuous data stream, I need to write it in buffered chunks of limited size. So every native VBA write call results in a hundreds msec delay. Since VBA is a one-threaded synchronous envireonment, I loose the input data.
My idea is using the Overlapped write with WriteFileEx, or WriteFile system calls for writing assynchronously. I tried to implement it as below, but failed, because of little experience in converting C notation from manuals to VBA. the hardest part seems submitting there an lpOverlapped struture, because it cannot be directly initialized in VBA the same way as in C. But there may be more obstackes which I do not understand.
Can you please help me to make a working assynchronous WriteFileEx call? Here is my current variant for Excel 2019 32bit, for starter, which does not work as intended. It writes the data one time, in interlocking IO. I.e. NOT parallel. And after the program has finished, any file IO in Excel results in crashing.
Declarations `
Public Declare Function CloseHandle Lib "kernel32" _
(ByVal hObject As Long) As Long
Public Declare Function CreateFile Lib "kernel32" _
Alias "CreateFileA" ( _
ByVal lpFileName As String, _
ByVal dwDesiredAccess As Long, _
ByVal dwShareMode As Long, _
lpSecurityAttributes As SECURITY_ATTRIBUTES, _
ByVal dwCreationDisposition As Long, _
ByVal dwFlagsAndAttributes As Long, _
ByVal hTemplateFile As Long _
) As Long
Public Declare Function WriteFileEx Lib "kernel32" ( _
ByVal hFile As Long, _
lpBuffer As Any, _
ByVal nNumberOfBytesToWrite As Long, _
lpOverlapped As OVERLAPPED, _
lpCompletionRoutine As Long _
) As Long
Public Declare Function WriteFile Lib "kernel32" ( _
ByVal hFile As Long, _
lpBuffer As Any, _
ByVal nNumberOfBytesToWrite As Long, _
lpNumberOfBytesWritten As Long, _
lpOverlapped As OVERLAPPED _
) As Long
Public Const GENERIC_WRITE As Long = &H40000000
Public Const FILE_SHARE_READ As Long = &H1
Public Const CREATE_ALWAYS As Long = &H2
Public Const FILE_FLAG_OVERLAPPED = &H40000000
Type Long64
Low As Long
High As Long
End Type
Type SECURITY_ATTRIBUTES
nLength As Long ' Long=4 -> .nLength=14
lpSecurityDescriptor As Long64 'Long64=8,
bInheritHandle As Boolean 'Boolean=2
End Type
Type OVERLAPPED
Internal As Long
InternalHigh As Long
Offset As Long
OffsetHigh As Long
hEvent As Long
End Type
`
The "program"
Dim WAPI32path As String
WAPI32path = "\\.\D:\test.txt"
Dim hFile As Long
Dim i As Long
Dim Dummy As Long
Dim SecAtr As SECURITY_ATTRIBUTES
SecAtr.bInheritHandle = True
SecAtr.lpSecurityDescriptor.High = &H0
SecAtr.lpSecurityDescriptor.Low = &H0
SecAtr.nLength = 14
Dim Overlap As OVERLAPPED
Overlap.hEvent = &H0
Overlap.Internal = &H0
Overlap.InternalHigh = &H0
Overlap.Offset = &H0
Overlap.OffsetHigh = &H0
Dim str As String
str = "agnqevo oapgnfvfrwo!"
For i = 1 To 24
str = str & vbCrLf & str
Next
hFile = CreateFile(WAPI32path, GENERIC_WRITE, FILE_SHARE_READ, SecAtr, CREATE_ALWAYS, FILE_FLAG_OVERLAPPED, &H0)
Debug.Print vbCrLf & "hFile=" & CStr(hFile)
Debug.Print "=====STARTING WRITE OP, LOOK FOR DELAY, THERE SHOULD NTO BE ANY======="
DoEvents
Dummy= WriteFileEx(hFile, ByVal StrPtr(str), LenB(str), Overlap, ByVal &H0)
DoEvents
Debug.Print vbCrLf & "WriteFile=" & Dummy
Debug.Print "=====Shoud have finished in no time====="
Application.Wait (Now + TimeValue("00:00:5")) 'giving it time to flush buffers without checking the Overlap status.
Dummy = CloseHandle(hFile)
Debug.Print vbCrLf & "CloseHandle=" & Dummy
When I change WriteFileEx to WriteFile, it does not crash that easily, but still freeses while writing:
Dummy = WriteFile(hFile, ByVal StrPtr(str), LenB(str), ByVal &H0, Overlap)