Basically, put the form code into a class module and, in a standard module, create a module-level object variable that points to an instance of the class and which will exist throughout the Excel session. Here's how, including a few supporting properties and methods.
Put the following code in a new
class module, renamed to
CWinsock:
Code:
'CWinsock. Class for OstroSoft Winsock Component, based on http://www.ostrosoft.com/oswinsck/oswinsck_vba.asp
Option Explicit
Private WithEvents wsTCP As OSWINSCK.Winsock
Private pUpdateCell As Range 'cell to be updated when data is received
Private Sub Class_Initialize()
Set wsTCP = New OSWINSCK.Winsock
Set pUpdateCell = Nothing
End Sub
Private Sub Class_Terminate()
If wsTCP.State = sckConnected Then
wsTCP.CloseWinsock
End If
Set wsTCP = Nothing
Set pUpdateCell = Nothing
Debug.Print "Finished"
End Sub
'-------------- Methods -------------
Public Sub Connect(host As String, port As Long)
wsTCP.Connect host, port
End Sub
Public Sub Disconnect()
If wsTCP.State = sckConnected Then
wsTCP.CloseWinsock
End If
End Sub
'-------------- Properties -------------
Public Property Set UpdateCell(cell As Range)
Set pUpdateCell = cell
End Property
Public Property Get UpdateCell() As Range
Set UpdateCell = pUpdateCell
End Property
'-------------- OSWINSCK.Winsock events -------------
Private Sub wsTCP_OnClose()
If wsTCP.State = sckOpen Then
wsTCP.SendData "Closing"
End If
wsTCP.CloseWinsock
End Sub
Private Sub wsTCP_OnConnect()
Debug.Print "OnConnect"
End Sub
Private Sub wsTCP_OnDataArrival(ByVal bytesTotal As Long)
Dim sBuffer As String
wsTCP.GetData sBuffer
If Not pUpdateCell Is Nothing Then
pUpdateCell.Value = pUpdateCell.Value & sBuffer
Else
MsgBox "UpdateCell property not set"
End If
End Sub
Private Sub wsTCP_OnError(ByVal Number As Integer, _
Description As String, ByVal Scode As Long, ByVal Source As String, _
ByVal HelpFile As String, ByVal HelpContext As Long, _
CancelDisplay As Boolean)
MsgBox "OSWINSCK.Winsock error " & Number & ": " & Description
End Sub
Private Sub wsTCP_OnStatusChanged(ByVal Status As String)
Debug.Print Status
End Sub
Put the following code in
standard module renamed to
modReceiver, though its name doesn't matter:
Code:
'VBA project needs reference to OstroSoft Winsock Component - http://www.ostrosoft.com/oswinsck.asp
Option Explicit
Private WsReceiver As CWinsock
Public Sub Start_Receiver()
If WsReceiver Is Nothing Then
Set WsReceiver = New CWinsock
End If
'Set the cell to be updated when data is received
Set WsReceiver.UpdateCell = Sheets("Sheet1").Range("A1")
WsReceiver.UpdateCell.Value = ""
'Connect to remote host on specific port
WsReceiver.Connect "localhost", 79
End Sub
Public Sub Stop_Receiver()
If Not WsReceiver Is Nothing Then
WsReceiver.Disconnect
End If
Set WsReceiver = Nothing
End Sub
If you want the receiver to be ready and waiting for data when you open the workbook and stop when you close the workbook, then put the following code in the
ThisWorkbook module:
Code:
Option Explicit
Private Sub Workbook_Open()
Start_Receiver
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Stop_Receiver
End Sub
Otherwise call Start_Receiver from your own procedure, e.g. a command button click event. My Start_Receiver reads data from localhost on port 79 and I used Microsoft HyperTerminal to test it.
