In this post I revisit the matter of the serialization of VBA Variant arrays to bytes and back again. This can be used VBA to VBA or interoperating with other technologies that can be sequence bytes, in this post I demonstrate JavaScript to VBA. I incorporate a better serialization technique code that uses Windows OS Named Pipes as written by VBForums.com contributor Olaf Schmidt.
Background
This is a revisit of an earlier article where I saved and loaded a Variant array to disk using VBA’s Open File For Binary, Put and Get statements. Whilst the code did what I wanted, I had complained that my code required a disk operation which carries a performance penalty.
I am indebted to a commenter (who gives no name) who tipped me off as to some code on VBForums written by Olaf Schmidt; Olaf’s code serializes using Windows OS Named Pipes and not a disk write. The Named Pipes are purely in memory and so this makes his code faster.
Moreover, the Named Pipes serialization yields a leading sequence of bytes that describes the number of dimensions in the array and the size and bounds of each dimension. This was something formally missing from disk write version my code and which I had had to implement manually, something of a nuisance.
I am thus doubly indebted to Olaf Schmidt’s code and to the commenter who tipped me off. Thank you. Do please keep the comments coming.
VBA Class Module - cPipedVariants
So with Olaf Schmidt's code as a starting point I have modified it to handle the use case of VBA variant arrays, i.e. a two dimensional array which is ready for pasting onto a worksheet. Olaf's original code demonstrated the serialization of user-defined types and these data structures are more prevalent in Visual Basic 6 (VB6) whereas Excel developers (I would argue) are more likely to deal with grids drawn from a worksheet or grids to be pasted onto a worksheet.
If you want the original version that deals with the serialization of UDTs it is on this link here to vb6forums.com.
So what follows in cPipedVariants, a modification on Olaf's original class cPipedUDTs. Much of the code is easy to follow but I will comment on the ‘secret sauce’ of the InitializePipe function.
The two key lines of code are the call to CreateNamedPipeW and then the Open "\\.\pipe\foo" For Binary statement. If I switch the order of these two around then the code fails. Internally, in its implementation the Open For Binary Statement must have a special case where it identifies the "\\.\pipe\ " prefix and then looks up in the list of created named pipes. This is not documented in any Microsoft documentation, or indeed StackOverflow. Only the VB6Forums.com users and specifically Olaf Schmidt understand this lore, it must be a throw back to the era of VB6. Anyway, it does work and I am grateful.
Add a class to your VBA project, call it cPipedVariants and then paste in the following code
Option Explicit
'* Pipe-based helper to serialize/deserialize VB-Variants InMemory ... [based on original code by Olaf Schmidt 2015]
'* Based on code by Olaf Schmidt 2015, http://www.vbforums.com/showthread.php?807205-VB6-pipe-based-UDT-serializing-deserializing-InMemory
'* https://docs.microsoft.com/en-us/windows/win32/api/winbase/nf-winbase-createnamedpipea
Private Declare Function CreateNamedPipeW& Lib "kernel32" (ByVal lpName As Long, ByVal dwOpenMode&, ByVal dwPipeMode&, _
ByVal nMaxInstances&, ByVal nOutBufferSize&, ByVal nInBufferSize&, _
ByVal nDefaultTimeOut&, ByVal lpSecurityAttributes&)
'* https://docs.microsoft.com/en-us/windows/win32/api/fileapi/nf-fileapi-writefile
Private Declare Function WriteFile& Lib "kernel32" (ByVal hFile&, lpBuffer As Any, _
ByVal nNumberOfBytesToWrite&, lpNumberOfBytesWritten&, ByVal lpOverlapped&)
'* https://docs.microsoft.com/en-us/windows/win32/api/fileapi/nf-fileapi-readfile
Private Declare Function ReadFile& Lib "kernel32" (ByVal hFile&, lpBuffer As Any, _
ByVal nNumberOfBytesToRead&, lpNumberOfBytesRead&, ByVal lpOverlapped&)
'* https://docs.microsoft.com/en-us/windows/win32/api/namedpipeapi/nf-namedpipeapi-peeknamedpipe
Private Declare Function PeekNamedPipe& Lib "kernel32" (ByVal hNamedPipe&, lpBuffer As Any, _
ByVal nBufferSize&, lpBytesRead&, lpTotalBytesAvail&, lpBytesLeftThisMessage&)
'* https://docs.microsoft.com/en-us/windows/win32/api/namedpipeapi/nf-namedpipeapi-disconnectnamedpipe
Private Declare Function DisconnectNamedPipe& Lib "kernel32" (ByVal hPipe&)
Private Declare Function CloseHandle& Lib "kernel32" (ByVal hObject&)
Private mhPipe As Long
Private mlFileNumber As Long
Private mabytSerialized() As Byte
Private Enum eOpenMode
PIPE_ACCESS_INBOUND = 1
PIPE_ACCESS_OUTBOUND = 2
PIPE_ACCESS_DUPLEX = 3
End Enum
Private Enum ePipeMode
PIPE_TYPE_BYTE = 0
PIPE_TYPE_MESSAGE = 4
PIPE_READMODE_BYTE = 0
PIPE_READMODE_MESSAGE = 2
PIPE_WAIT = 0
PIPE_NOWAIT = 1
End Enum
Private Enum ePipeInstances
PIPE_UNLIMITED_INSTANCES = 255
End Enum
Public Function InitializePipe(Optional sPipeNameSuffix As String = "vbaPipedVariantArrays") As Boolean
Const csPipeNamePrefix As String = "\\.\pipe\"
CleanUp
Dim sPipeName As String
sPipeName = csPipeNamePrefix & sPipeNameSuffix
'* Must call CreateNamedPipe first before calling Open <<pathname>> For Binary otherwise you get bad file number
mhPipe = CreateNamedPipeW(StrPtr(sPipeName), PIPE_ACCESS_DUPLEX, PIPE_TYPE_BYTE + PIPE_READMODE_BYTE + PIPE_WAIT, _
PIPE_UNLIMITED_INSTANCES, -1, -1, 0, 0)
If mhPipe = -1 Then mhPipe = 0 'reset from InvalidHandleValue to "no Handle"
If mhPipe Then
'* only try to find a free VB-FileNumber when mhPipe is valid (i.e. pipe has been created)
mlFileNumber = FreeFile
If mlFileNumber Then
Open sPipeName For Binary As mlFileNumber 'open only, when we got an mlFileNumber
End If
End If
InitializePipe = mhPipe <> 0 And mlFileNumber <> 0
End Function
Public Function SerializeToBytes(ByRef vSrc As Variant, ByRef pabytSerialized() As Byte) As Long
Dim lBytesAvail As Long
Debug.Assert IsArray(vSrc)
If mlFileNumber <> 0 Then
'* this next line writes the Variant array to the pipe
Put mlFileNumber, 1, vSrc
'* we should now have some bytes to read out of the pipe, use PeekNamedPipe to verify there are bytes available
PeekNamedPipe mhPipe, ByVal 0&, 0, ByVal 0&, lBytesAvail, 0
If lBytesAvail > 0 Then
'* so now we can dimension the byte array
ReDim Preserve pabytSerialized(0 To lBytesAvail - 1)
'* and now we can read the bytes out of the pipe and into the byte array
ReadFile mhPipe, pabytSerialized(0), lBytesAvail, lBytesAvail, ByVal 0&
'* return number of bytes as a courtesy (not actually required)
SerializeToBytes = lBytesAvail
End If
End If
End Function
Public Function DeserializeFromBytes(ByRef abytSerialized() As Byte, ByRef pvDest As Variant) As Long
Dim lBytesWritten As Long
If mhPipe <> 0 And mlFileNumber <> 0 Then
'* write the byte array to the pipe
WriteFile mhPipe, abytSerialized(0), UBound(abytSerialized) + 1, lBytesWritten, 0
If lBytesWritten = UBound(abytSerialized) + 1 Then
'* the pipe contains a byte array serialization of a variant array
'* we can use VBA's Get statement to read it directly into a variant array variable
Get mlFileNumber, 1, pvDest
'* report the amount of deserialized Bytes as a courtesy (not actually required)
DeserializeFromBytes = Loc(mlFileNumber)
End If
End If
End Function
Private Sub CleanUp()
If mlFileNumber Then Close mlFileNumber: mlFileNumber = 0
If mhPipe Then DisconnectNamedPipe mhPipe
If mhPipe Then CloseHandle mhPipe: mhPipe = 0
End Sub
Private Sub Class_Terminate()
CleanUp
End SubVBA Standard Module - tstPipedVariants
So now we need some client code. Add a standard module to your VBA project and paste in the following code. I called this module tstPipedVariants.
Sub SamePipeForSerializeAndDeserialize()
Dim oPipe As cPipedVariants
Set oPipe = New cPipedVariants
If oPipe.InitializePipe Then
Dim vSource As Variant
vSource = TestData
Dim abytSerialized() As Byte
Call oPipe.SerializeToBytes(vSource, abytSerialized)
Stop '* at this point vSource is populated but vDestination is empty
Dim vDestination As Variant
oPipe.DeserializeFromBytes abytSerialized, vDestination
Stop
End If
End Sub
Function TestData() As Variant
Dim vSource(1 To 2, 1 To 4) As Variant
vSource(1, 1) = "Hello World"
vSource(1, 2) = True
vSource(1, 3) = False
vSource(1, 4) = Null
vSource(2, 1) = 65535
vSource(2, 2) = 7.5
vSource(2, 3) = CDate("12:00:00 16-Sep-1989") 'now()
vSource(2, 4) = CVErr(xlErrNA)
TestData = vSource
End FunctionIn the module tstPipedVariants run the test code subroutine SamePipeForSerializeAndDeserialize() by navigating and pressing F5 to reach the first Stop statement. On the first Stop statement vSource is populated but vDestination isn’t.
However, the byte array abytSerialized() is populated and we can go inspect this. The first twenty bytes are similar to SafeArray and SafeArrayBounds structures. The first two bytes represent a vbVarType of vbArray+vbVariant in low byte, high byte order. Next, two bytes gives the count of dimensions. Then for each dimension there are 8 bytes, giving a 4 byte dimension size and a 4 byte lower bound. This abridged SafeArray descriptor is most welcome. When VBA code writes a variant array to disk it omits such a descriptor which meant I had to augment the code to manually write in the dimensions. I am very much pleased that the Named Pipes implementation does this automatically for me.
After the first twenty bytes of abridged SafeArray descriptor the rest of the data follows. I wrote this up in the original blog post so I’ll refer you to that and skip the rest.
Returning to the test code, press F5 again to get the second Stop statement and behold in the Locals window the vDestination variable is now populated exactly the same as the vSource variable. Note how we did not need to dimension the vDestination variable before populating it, excellent!
This completes the VBA to VBA demo. We can move onto the JavaScript to VBA use case.
Revisiting the Javascript to VBA use case
JavaScript Changes
In the original article I gave some Javascript code to serialize a Javascript array to a byte array that can then be deserialized to a VBA variant array. This JavaScript code needs modifying to interoperate with the new Named Pipes VBA code given above. The change required is to give a correctly formatted abridged safe array descriptor. This is a simple change found at the top of the JavaScriptToVBAVariantArray.prototype.persistGrid function. All other code remains the same, so no further explanation is required. The JavaScript module remains something loadable into both browser and server JavaScript environments. The Node.js project given in the original blog post can still be used.
I have only included the function that has changed, JavaScriptToVBAVariantArray.prototype.persistGrid; for rest of the JavaScript module listing see the original blog post.
JavaScriptToVBAVariantArray.prototype.persistGrid = function persistGrid(grid, rows, columns) {
try {
/* Opening sequence of bytes is a reduced form of SAFEARRAY and SAFEARRAYBOUND
* SAFEARRAY https://docs.microsoft.com/en-gb/windows/win32/api/oaidl/ns-oaidl-safearray
* SAFEARRAYBOUND https://docs.microsoft.com/en-gb/windows/win32/api/oaidl/ns-oaidl-safearraybound
*/
var payloadEncoded = new Uint8Array(20);
// vbArray + vbVariant, lo byte, hi byte
payloadEncoded[0] = 12; payloadEncoded[1] = 32;
// number of dimensions, lo byte, hi byte
payloadEncoded[2] = 2; payloadEncoded[3] = 0;
// number of columns, 4 bytes, least significant byte first
payloadEncoded[4] = columns % 256; payloadEncoded[5] = Math.floor(columns / 256);
payloadEncoded[6] = 0; payloadEncoded[7] = 0;
// columns lower bound (safearray)
payloadEncoded[8] = 1; payloadEncoded[9] = 0;
payloadEncoded[10] = 0; payloadEncoded[11] = 0;
// number of rows, 4 bytes, least significant byte first
payloadEncoded[12] = rows % 256; payloadEncoded[13] = Math.floor(rows / 256);
payloadEncoded[14] = 0; payloadEncoded[15] = 0;
// rows lower bound (safearray)
payloadEncoded[16] = 1; payloadEncoded[17] = 0;
payloadEncoded[18] = 0; payloadEncoded[19] = 0;
var elementBytes;
for (var colIdx = 0; colIdx < columns; colIdx++) {
for (var rowIdx = 0; rowIdx < rows; rowIdx++) {
elementBytes = this.persistVar(grid[rowIdx][colIdx]);
var arr = [payloadEncoded, elementBytes];
payloadEncoded = this.concatArrays(arr); // Browser
}
}
return payloadEncoded;
}
catch (err) {
console.log(err.message);
}
};VBA web client code
Turning to the client VBA code we can greatly simplify the code now that the dimensioning is done for us. The resulting code is now trivial, here it is below. Add the following code to the tstPipedVariants module you added earlier. This code below requires you to add a Tools->Reference to Microsoft WinHTTP Services, version 5.1...Sub TestByWinHTTP()
'* this calls the Node.js project with the new JavaScript serialization
Dim oWinHttp As WinHttp.WinHttpRequest '* Tools->References->Microsoft WinHTTP Services, version 5.1
Set oWinHttp = New WinHttp.WinHttpRequest
oWinHttp.Open "GET", "http://localhost:1337/", False
oWinHttp.send
If oWinHttp.Status = 200 Then
If IsEmpty(oWinHttp.ResponseBody) Then Err.Raise vbObjectError, , "No bytes returned!"
If UBound(oWinHttp.ResponseBody) = 0 Then Err.Raise vbObjectError, , "No bytes returned!"
Dim oPipedVariants As cPipedVariants
Set oPipedVariants = New cPipedVariants
If oPipedVariants.InitializePipe Then
Dim vGrid As Variant
oPipedVariants.DeserializeFromBytes oWinHttp.ResponseBody, vGrid
Stop '* Observe vGrid in the Locals window
'* vGrid is now ready to paste on a worksheet
End If
End If
End SubSo run this code with the Node.js project running and the vGrid should be populated. That's all folks. Enjoy!
Thanks again
Thanks once again to Olaf Schmidt and the anonymous tipper! Getting help to fix my code is very welcome.
