VBA Winsock Not Working with Longer Port Length (sin_port)

Question

I've created the below Excel VBA code which uses the Winsock API to connect to an IP address, send along a text string from an Excel cell and receive a text string in return.

My code was originally pointed to the IP address 127.0.0.1 with port 80 and had no issues. However, I've since had to update the destination port to 60401 which also required changing the port input variable sin_port to Long, as the new port exceeds the max length of a VBA Integer. After these updates the code still compiles, but the Winsock API doesn't process anything??

I think the error may be related to the sin_zero variable which might be buffering too many zeros with the port length increase? I've tried adjusting this variable and diagnosing the code elsewhere, but it still doesn't process after several hours of tinkering with the code.

All help is really appreciated. Thank you.

---

Original Code - Port 80 - Compiles and processes successfully

Type WSAData
   wVersion As Integer
   wHighVersion As Integer
   szDescription(0 To 255) As Byte
   szSystemStatus(0 To 128) As Byte
   iMaxSockets As Integer
   iMaxUdpDg As Integer
   lpVendorInfo As Long
End Type

Type sockaddr_in
    sin_family As Integer
    sin_port As Integer
    sin_addr As Long
    sin_zero(0 to 7) As Byte
End Type

Public Declare Function WSAStartup Lib "ws2_32" ( _
    ByVal wVersionRequired As Integer, ByRef lpWSAData As WSAData) As Long

Public Declare Function WSAGetLastError Lib "ws2_32" () As Long

Public Declare Function socket Lib "ws2_32" ( _
    ByVal af As Long, ByVal socktype As Long, ByVal protocol As Long) As Long

Public Declare Function connect Lib "ws2_32" ( _
    ByVal sock As Long, ByRef name As sockaddr_in, ByVal namelen As Integer) As Long

Public Declare Function send Lib "ws2_32" ( _
    ByVal sock As Long, ByVal buf As String, ByVal bufLen As Long, ByVal flags As Long) As Long

Public Declare Function recv Lib "ws2_32" ( _
    ByVal sock As Long, ByRef buf As Byte, ByVal bufLen As Long, ByVal flags As Long) As Long

Public Declare Function inet_addr Lib "ws2_32" ( _
    ByVal s As String) As Long

Public Declare Function htons Lib "ws2_32" ( _
    ByVal hostshort As Long) As Long


Function FetchData() As String
   Dim iReturn As Long
   Dim wsaDat As WSAData
   iReturn = WSAStartup(&H202, wsaDat)

   If iReturn <> 0 Then
      MsgBox "WSAStartup failed", 0, ""

   End If

   Dim sock As Long
   Dim sock1 As Long
   Dim lasterr As Long
   Dim i As Long
   Dim buf(10) As Byte
   Dim s As String
   Dim j As Integer

   sock = socket(2, 1, 6)

   Dim addr As sockaddr_in
   addr.sin_family = 2
   addr.sin_port = htons(80)
   addr.sin_addr = inet_addr("127.0.0.1")

   i = connect(sock, addr, LenB(addr))
   i = send(sock, "*SRTF" & vbCr, 6, 0)
   i = recv(sock, buf(0), 10, 0)

   For j = 0 To i - 1
     s = s & Chr(buf(j))
   Next
   FetchData = s
End Function

Sub Button2_Click()
    Range("C3").Formula = FetchData()
End Sub

---

New Code - Port 60401 - Compiles, but doesn't process?

Type WSAData
   wVersion As Integer
   wHighVersion As Integer
   szDescription(0 To 255) As Byte
   szSystemStatus(0 To 128) As Byte
   iMaxSockets As Integer
   iMaxUdpDg As Integer
   lpVendorInfo As Long
End Type

Type sockaddr_in
    sin_family As Integer
    sin_port As Long
    sin_addr As Long
    sin_zero(0 to 7) As Byte
End Type

Public Declare Function WSAStartup Lib "ws2_32" ( _
    ByVal wVersionRequired As Integer, ByRef lpWSAData As WSAData) As Long

Public Declare Function WSAGetLastError Lib "ws2_32" () As Long

Public Declare Function socket Lib "ws2_32" ( _
    ByVal af As Long, ByVal socktype As Long, ByVal protocol As Long) As Long

Public Declare Function connect Lib "ws2_32" ( _
    ByVal sock As Long, ByRef name As sockaddr_in, ByVal namelen As Integer) As Long

Public Declare Function send Lib "ws2_32" ( _
    ByVal sock As Long, ByVal buf As String, ByVal bufLen As Long, ByVal flags As Long) As Long

Public Declare Function recv Lib "ws2_32" ( _
    ByVal sock As Long, ByRef buf As Byte, ByVal bufLen As Long, ByVal flags As Long) As Long

Public Declare Function inet_addr Lib "ws2_32" ( _
    ByVal s As String) As Long

Public Declare Function htons Lib "ws2_32" ( _
    ByVal hostshort As Long) As Long


Function FetchData() As String
   Dim iReturn As Long
   Dim wsaDat As WSAData
   iReturn = WSAStartup(&H202, wsaDat)

   If iReturn <> 0 Then
      MsgBox "WSAStartup failed", 0, ""

   End If

   Dim sock As Long
   Dim sock1 As Long
   Dim lasterr As Long
   Dim i As Long
   Dim buf(10) As Byte
   Dim s As String
   Dim j As Integer

   sock = socket(2, 1, 6)

   Dim addr As sockaddr_in
   addr.sin_family = 2
   addr.sin_port = htons(60401)
   addr.sin_addr = inet_addr("127.0.01")

   i = connect(sock, addr, LenB(addr))
   i = send(sock, "*SRTF" & vbCr, 6, 0)
   i = recv(sock, buf(0), 10, 0)

   For j = 0 To i - 1
     s = s & Chr(buf(j))
   Next
   FetchData = s
End Function

Sub Button2_Click()
    Range("C3").Formula = FetchData()
End Sub

Answer 1

You altered your definition of sockaddr_in to use a larger data type for the sin_port field. You can't do that. You need to restore your original definition to remain compatible with Winsock.

Your definition of htons() is also wrong. The real htons() function in ws2_32 operates on 16-bit numbers, not 32-bit numbers as you have defined (htonl() operates on 32-bit numbers).

The real problem you are running into is that VBA's Integer type is signed, the highest value it can hold is 32767. If you attempt to use a higher value, it will wrap to negative.

Winsock's actual sockaddr_in struct (and htons() function) uses an 16-bit unsigned type for the sin_port field. VBA simply does not have a 16-bit unsigned type. So you have to live with the limitations of the 16-bit signed Integer.

You need to fix your definitions:

Type sockaddr_in
  sin_family As Integer
  sin_port As Integer
  sin_addr As Long
  sin_zero(0 to 7) As Byte
End Type

Public Declare Function htons Lib "ws2_32" ( _ ByVal hostshort As Integer) As Integer

Now, that being said, the unsigned number 60401 is hex 0xEBF1. That is the same value as the signed number -5135. Which becomes 0xF1EB (-3605) when byte swapped by htons().

So, try one of these instead:

addr.sin_port = htons(&HEBF1)

addr.sin_port = htons(-5135)

addr.sin_port = -3605

Furthermore, inet_addr("127.0.01") should read inet_addr("127.0.0.1").