I hope you have noticed that inter-operability features large on this blog. Interop is easy with C# because it has an excellent COM interop library. Python and other languages also have COM APIs (not always as comprehensive as C#). But Java and Ruby do not have COM APIs callable from VBA. But this month we have shown that it is possible to use Sockets to connect with other processes (in the demos on the same machine but in real life potentially on remote machines). Thus, using sockets Excel VBA call anything, literally anything (so long the target langauge has a sockets API).
In the previous two articles I wrote sockets servers in Ruby then Java which ran simple calculations. They were just warm-up and test-beds to be honest. What I really wanted to show was VBA calling Ruby and Java.
So in the code below if you run the procedure TestWS2SendAndReceive() at line 159 then you can see the code will attempt to call the Java and Ruby servers, make sure you have these running. If all works then you should get output in the Immediate window
1+3 = 4.0
6*7 = 42.0
modVBASocketsClient Standard Module
- Option Explicit
- Option Private Module
- 'reference Windows Sockets 2 - Windows applications _ Microsoft Docs
- 'http://msdn.microsoft.com/en-us/library/windows/desktop/ms740673(v=vs.85).aspx
- Private Const INVALID_SOCKET = -1
- Private Const WSADESCRIPTION_LEN = 256
- Private Const SOCKET_ERROR As Long = -1 'const #define SOCKET_ERROR (-1)
- Private Enum AF
- AF_UNSPEC = 0
- AF_INET = 2
- AF_IPX = 6
- AF_APPLETALK = 16
- AF_NETBIOS = 17
- AF_INET6 = 23
- AF_IRDA = 26
- AF_BTH = 32
- End Enum
- Private Enum sock_type
- SOCK_STREAM = 1
- SOCK_DGRAM = 2
- SOCK_RAW = 3
- SOCK_RDM = 4
- SOCK_SEQPACKET = 5
- End Enum
- Private Enum Protocol
- IPPROTO_ICMP = 1
- IPPROTO_IGMP = 2
- BTHPROTO_RFCOMM = 3
- IPPROTO_TCP = 6
- IPPROTO_UDP = 17
- IPPROTO_ICMPV6 = 58
- IPPROTO_RM = 113
- End Enum
- 'Private Type sockaddr
- ' sa_family As Integer
- ' sa_data(0 To 13) As Byte
- 'End Type
- Private Type sockaddr_in
- sin_family As Integer
- sin_port(0 To 1) As Byte
- sin_addr(0 To 3) As Byte
- sin_zero(0 To 7) As Byte
- End Type
- 'typedef UINT_PTR SOCKET;
- Private Type udtSOCKET
- pointer As Long
- End Type
- ' typedef struct WSAData {
- ' WORD wVersion;
- ' WORD wHighVersion;
- ' char szDescription[WSADESCRIPTION_LEN+1];
- ' char szSystemStatus[WSASYS_STATUS_LEN+1];
- ' unsigned short iMaxSockets;
- ' unsigned short iMaxUdpDg;
- ' char FAR *lpVendorInfo;
- '} WSADATA, *LPWSADATA;
- Private Type udtWSADATA
- wVersion As Integer
- wHighVersion As Integer
- szDescription(0 To WSADESCRIPTION_LEN) As Byte
- szSystemStatus(0 To WSADESCRIPTION_LEN) As Byte
- iMaxSockets As Integer
- iMaxUdpDg As Integer
- lpVendorInfo As Long
- End Type
- 'int errorno = WSAGetLastError()
- Private Declare Function WSAGetLastError Lib "Ws2_32" () As Integer
- ' int WSAStartup(
- ' __in WORD wVersionRequested,
- ' __out LPWSADATA lpWSAData
- ');
- Private Declare Function WSAStartup Lib "Ws2_32" _
- (ByVal wVersionRequested As Integer, ByRef lpWSAData As udtWSADATA) As Long 'winsockErrorCodes2
- ' SOCKET WSAAPI socket(
- ' __in int af,
- ' __in int type,
- ' __in int protocol
- ');
- Private Declare Function ws2_socket Lib "Ws2_32" Alias "socket" _
- (ByVal AF As Long, ByVal stype As Long, ByVal Protocol As Long) As LongPtr
- Private Declare Function ws2_closesocket Lib "Ws2_32" Alias "closesocket" _
- (ByVal socket As Long) As Long
- 'int recv(
- ' SOCKET s,
- ' char *buf,
- ' int len,
- ' int flags
- ');
- Private Declare Function ws2_recv Lib "Ws2_32" Alias "recv" _
- (ByVal socket As Long, ByVal buf As LongPtr,
- ByVal length As Long, ByVal flags As Long) As Long
- 'int WSAAPI connect(
- ' SOCKET s,
- ' const sockaddr *name,
- ' int namelen
- ');
- Private Declare Function ws2_connect Lib "Ws2_32" Alias "connect" _
- (ByVal S As LongPtr, ByRef name As sockaddr_in, ByVal namelen As Long) As Long
- 'int WSAAPI send(
- ' SOCKET s,
- ' const char *buf,
- ' int len,
- ' int flags
- ');
- Private Declare Function ws2_send Lib "Ws2_32" Alias "send" _
- (ByVal S As LongPtr, ByVal buf As LongPtr, ByVal buflen As Long, ByVal flags As Long) As Long
- Private Declare Function ws2_shutdown Lib "Ws2_32" Alias "shutdown" _
- (ByVal S As Long, ByVal how As Long) As Long
- Private Declare Sub WSACleanup Lib "Ws2_32" ()
- Private Enum eShutdownConstants
- SD_RECEIVE = 0 '#define SD_RECEIVE 0x00
- SD_SEND = 1 '#define SD_SEND 0x01
- SD_BOTH = 2 '#define SD_BOTH 0x02
- End Enum
- Sub TestPortLongToBytes()
- 'redis is on port number 6379
- Dim abytPortAsBytes() As Byte
- abytPortAsBytes() = PortLongToBytes(6379)
- Debug.Assert abytPortAsBytes(0) = 24
- Debug.Assert abytPortAsBytes(1) = 235
- End Sub
- Private Function PortLongToBytes(ByVal lPort As Integer) As Byte()
- ReDim abytReturn(0 To 1) As Byte
- abytReturn(0) = lPort \ 256
- abytReturn(1) = lPort Mod 256
- PortLongToBytes = abytReturn()
- End Function
- Private Sub TestWS2SendAndReceive()
- Dim sResponse As String
- Const clJavaPort As Long = 6666
- Const clRubyPort As Long = 3000
- If WS2SendAndReceive(clJavaPort, "1+3" & vbCrLf, sResponse) Then
- Debug.Print sResponse
- End If
- If WS2SendAndReceive(clRubyPort, "6*7" & vbCrLf, sResponse) Then
- Debug.Print sResponse
- End If
- End Sub
- Public Function WS2SendAndReceive(ByVal lPort As Long,
- ByVal sText As String, ByRef psResponse As String) As Boolean
- 'https://docs.microsoft.com/en-gb/windows/desktop/api/winsock/nf-winsock-recv
- If Right$(sText, 2) <> vbCrLf Then Err.Raise vbObjectError, , "Best suffix your sends with a new line (vbCrLf)"
- psResponse = ""
- '//----------------------
- '// Declare and initialize variables.
- Dim iResult As Integer : iResult = 0
- Dim wsaData As udtWSADATA
- Dim ConnectSocket As LongPtr
- Dim clientService As sockaddr_in
- Dim sendBuf() As Byte
- sendBuf = StrConv(sText, vbFromUnicode)
- Const recvbuflen As Long = 512
- Dim recvbuf(0 To recvbuflen - 1) As Byte
- '//----------------------
- '// Initialize Winsock
- Dim eResult As Long 'winsockErrorCodes2
- eResult = WSAStartup(&H202, wsaData)
- If eResult <> 0 Then
- Debug.Print "WSAStartup failed with error: " & eResult
- WS2SendAndReceive = False
- GoTo SingleExit
- End If
- '//----------------------
- '// Create a SOCKET for connecting to server
- ConnectSocket = ws2_socket(AF_INET, SOCK_STREAM, IPPROTO_TCP)
- If ConnectSocket = INVALID_SOCKET Then
- Dim eLastError As Long 'winsockErrorCodes2
- eLastError = WSAGetLastError()
- Debug.Print "socket failed with error: " & eLastError
- Call WSACleanup
- WS2SendAndReceive = False
- GoTo SingleExit
- End If
- '//----------------------
- '// The sockaddr_in structure specifies the address family,
- '// IP address, and port of the server to be connected to.
- clientService.sin_family = AF_INET
- clientService.sin_addr(0) = 127
- clientService.sin_addr(1) = 0
- clientService.sin_addr(2) = 0
- clientService.sin_addr(3) = 1
- Dim abytPortAsBytes() As Byte
- abytPortAsBytes() = PortLongToBytes(lPort)
- clientService.sin_port(1) = 235 '* 6379
- clientService.sin_port(0) = 24
- clientService.sin_port(1) = abytPortAsBytes(1)
- clientService.sin_port(0) = abytPortAsBytes(0)
- '//----------------------
- '// Connect to server.
- iResult = ws2_connect(ConnectSocket, clientService, LenB(clientService))
- If (iResult = SOCKET_ERROR) Then
- eLastError = WSAGetLastError()
- Debug.Print "connect failed with error: " & eLastError
- Call ws2_closesocket(ConnectSocket)
- Call WSACleanup
- WS2SendAndReceive = False
- GoTo SingleExit
- End If
- '//----------------------
- '// Send an initial buffer
- Dim sendbuflen As Long
- sendbuflen = UBound(sendBuf) - LBound(sendBuf) + 1
- iResult = ws2_send(ConnectSocket, VarPtr(sendBuf(0)), sendbuflen, 0)
- If (iResult = SOCKET_ERROR) Then
- eLastError = WSAGetLastError()
- Debug.Print "send failed with error: " & eLastError
- Call ws2_closesocket(ConnectSocket)
- Call WSACleanup
- WS2SendAndReceive = False
- GoTo SingleExit
- End If
- 'Debug.Print "Bytes Sent: ", iResult
- '// shutdown the connection since no more data will be sent
- iResult = ws2_shutdown(ConnectSocket, SD_SEND)
- If (iResult = SOCKET_ERROR) Then
- eLastError = WSAGetLastError()
- Debug.Print "shutdown failed with error: " & eLastError
- Call ws2_closesocket(ConnectSocket)
- Call WSACleanup
- WS2SendAndReceive = False
- GoTo SingleExit
- End If
- ' receive only one message (TODO handle when buffer is not large enough)
- iResult = ws2_recv(ConnectSocket, VarPtr(recvbuf(0)), recvbuflen, 0)
- If (iResult > 0) Then
- 'Debug.Print "Bytes received: ", iResult
- ElseIf (iResult = 0) Then
- Debug.Print "Connection closed"
- WS2SendAndReceive = False
- Call ws2_closesocket(ConnectSocket)
- Call WSACleanup
- GoTo SingleExit
- Else
- eLastError = WSAGetLastError()
- Debug.Print "recv failed with error: " & eLastError
- End If
- psResponse = Left$(StrConv(recvbuf, vbUnicode), iResult)
- 'Debug.Print psResponse
- '// close the socket
- iResult = ws2_closesocket(ConnectSocket)
- If (iResult = SOCKET_ERROR) Then
- eLastError = WSAGetLastError()
- Debug.Print "close failed with error: " & eLastError
- Call WSACleanup
- WS2SendAndReceive = False
- GoTo SingleExit
- End If
- Call WSACleanup
- WS2SendAndReceive = True
- SingleExit:
- Exit Function
- ErrHand:
- End Function
No comments:
Post a Comment