Post a Comment On: Scott's Journal

I needed a way to communicate from VBA to Java, this is VBA in the Office 2000 runtime, so it's fairly old. I thought that the easiest way would be to use socket communications. However, there is no built in socket library for VBA 2000. VBA, does let you map functions from DLLs and this is what I did:

Module mSocket

Option Compare Database
Option Explicit

Private isSocketServerRunning As Boolean

Public Const AF_INET = 2
Public Const SOCK_STREAM = 1
Public Const SOCKET_ERROR = 1
Public Const FD_SETSIZE = 64
Public Const FIONBIO = 2147772030#
Public Const SOCKADDR_IN_SIZE = 16
Public Const FORMAT_MESSAGE_FROM_SYSTEM = &H1000

Public Address As String
Public Port As Integer
Public SocketHandle As Long

Public Type WSADATA
wVersion As Integer
wHighVersion As Integer
szDescription As String * 257
szSystemStatus As String * 129
iMaxSockets As Integer
iMaxUdpDg As Integer
lpVendorInfo As Long
End Type

Public Type SOCKADDR_IN
sin_family As Integer
sin_port As Integer
sin_addr As Long
sin_zero As String * 8
End Type

Public Type fd_set
fd_count As Long
fd_array(FD_SETSIZE) As Long
End Type

Public Type timeval
tv_sec As Long
tv_usec As Long
End Type

Public Declare Function WSAStartup Lib "wsock32.dll" (ByVal intVersionRequested As Integer, lpWSAData As WSADATA) As Long
Public Declare Function WSACleanup Lib "wsock32.dll" () As Long
Public Declare Function w_socket Lib "wsock32.dll" Alias "socket" (ByVal lngAf As Long, ByVal lngType As Long, ByVal lngProtocol As Long) As Long
Public Declare Function w_closesocket Lib "wsock32.dll" Alias "closesocket" (ByVal SocketHandle As Long) As Long
Public Declare Function w_bind Lib "wsock32.dll" Alias "bind" (ByVal socket As Long, Name As SOCKADDR_IN, ByVal namelen As Long) As Long
Public Declare Function w_connect Lib "wsock32.dll" Alias "connect" (ByVal socket As Long, Name As SOCKADDR_IN, ByVal namelen As Long) As Long
Public Declare Function w_send Lib "wsock32.dll" Alias "send" (ByVal socket As Long, buf As Any, ByVal Length As Long, ByVal Flags As Long) As Long
Public Declare Function w_recv Lib "wsock32.dll" Alias "recv" (ByVal socket As Long, buf As Any, ByVal Length As Long, ByVal Flags As Long) As Long
Public Declare Function w_select Lib "wsock32.dll" Alias "select" (ByVal nfds As Long, readfds As fd_set, writefds As fd_set, exceptfds As fd_set, timeout As timeval) As Long
Public Declare Function htons Lib "wsock32.dll" (ByVal hostshort As Integer) As Integer
Public Declare Function ntohl Lib "wsock32.dll" (ByVal netlong As Long) As Long
Public Declare Function inet_addr Lib "wsock32.dll" (ByVal Address As String) As Long
Public Declare Function ioctlsocket Lib "wsock32.dll" (ByVal socket As Long, ByVal cmd As Long, argp As Long) As Long
Public Declare Function FormatMessage Lib "kernel32" Alias "FormatMessageA" (ByVal dwFlags As Long, lpSource As Any, ByVal dwMessageId As Long, ByVal dwLanguageId As Long, ByVal lpBuffer As String, ByVal nSize As Long, Arguments As Long) As Long

Public Function sendMessage(message As String) As Integer
Dim wd As WSADATA
Dim socketHNDL As Long
Dim serverAddress As SOCKADDR_IN
Dim ret As Integer

WSAStartup &H101, wd
socketHNDL = w_socket(AF_INET, SOCK_STREAM, 0)

serverAddress.sin_family = AF_INET
serverAddress.sin_port = htons(9999) 'port number
serverAddress.sin_addr = inet_addr("127.0.0.1")

ret = w_connect(socketHNDL, serverAddress, SOCKADDR_IN_SIZE)
If (ret > -1) Then
ret = w_send(socketHNDL, ByVal message, Len(message), 0)
End If
w_closesocket socketHNDL
sendMessage = ret
End Function

Public Function startSocketServer()
On Error GoTo errorHandler:
Dim result As Double
If (sendMessage("ping") = -1) Then
result = Shell("javaws http://programs.onyxtop.com/JavaPrograms/WebStart/socketServer.jnlp", vbMaximizedFocus)
If (result = 0) Then
GoTo errorHandler:
Else
isSocketServerRunning = True
End If
Else
isSocketServerRunning = True
End If

Exit Function
errorHandler:
mError.recordError Err.Description, "mSocketServer.startSocketServer", _
"Starting Java Socket Server", mError.FATAL
MsgBox "Unable to start Java Socket Server. Functionality may be limited.", vbOKOnly, "Socket Error"
End Function

Public Function checkSocketServer()
If (Not isSocketServerRunning) Then
startSocketServer
iSleepy 5000
End If
End Function

The Java side is fairly simple. You basically start a ServerSocket listening on localhost and the port used in the VBA. Now, VBA can communicate with your Java program without the need for polling.