PDA

View Full Version : Error VBA in Excel 64-bits



Xav1050
10-31-2016, 03:33 PM
Hi,

This module don't work since I use a 64 bit version of Excel 2016 (no problem with a 32-bit version of Excel 2016).
I receive the following error message :
"This application requires a minimum of 1 supported sockets"

I modified "Declare" --> "Declare PtrSafe"
I tried to modify "as Long" --> "as LongPtr" but without success...

Thank you for your help!

Xavier


Public Const MIN_SOCKETS_REQD As Long = 1
Public Const WS_VERSION_REQD As Long = &H101
Public Const WS_VERSION_MAJOR As Long = WS_VERSION_REQD \ &H100 And &HFF&
Public Const WS_VERSION_MINOR As Long = WS_VERSION_REQD And &HFF&
Public Const SOCKET_ERROR As Long = -1
Public Const WSADESCRIPTION_LEN = 257
Public Const WSASYS_STATUS_LEN = 129
Public Const MAX_WSADescription = 256
Public Const MAX_WSASYSStatus = 128
Public Type WSADATA
wVersion As Integer
wHighVersion As Integer
szDescription(0 To MAX_WSADescription) As Byte
szSystemStatus(0 To MAX_WSASYSStatus) As Byte
wMaxSockets As Integer
wMaxUDPDG As Integer
dwVendorInfo As Long
End Type
Type WSADataInfo
wVersion As Integer
wHighVersion As Integer
szDescription As String * WSADESCRIPTION_LEN
szSystemStatus As String * WSASYS_STATUS_LEN
iMaxSockets As Integer
iMaxUdpDg As Integer
lpVendorInfo As String
End Type
Public Type HOSTENT
hName As Long
hAliases As Long
hAddrType As Integer
hLen As Integer
hAddrList As Long
End Type
Declare PtrSafe Function WSAStartupInfo Lib "WSOCK32" Alias "WSAStartup" (ByVal wVersionRequested As Integer, lpWSADATA As WSADataInfo) As Long
Declare PtrSafe Function WSACleanup Lib "WSOCK32" () As Long
Declare PtrSafe Function WSAGetLastError Lib "WSOCK32" () As Long
Declare PtrSafe Function WSAStartup Lib "WSOCK32" (ByVal wVersionRequired As Long, lpWSADATA As WSADATA) As Long
Declare PtrSafe Function gethostname Lib "WSOCK32" (ByVal szHost As String, ByVal dwHostLen As LongPtr) As Long
Declare PtrSafe Function gethostbyname Lib "WSOCK32" (ByVal szHost As String) As Long
Declare PtrSafe Sub CopyMemoryIP Lib "kernel32" Alias "RtlMoveMemory" (hpvDest As Any, ByVal hpvSource As LongPtr, ByVal cbCopy As Long)




Public Function GetIPAddress() As String
Dim sHostName As String * 256
Dim lpHost As Long
Dim HOST As HOSTENT
Dim dwIPAddr As Long
Dim tmpIPAddr() As Byte
Dim i As Integer
Dim sIPAddr As String
If Not SocketsInitialize() Then
GetIPAddress = ""
Exit Function
End If
If gethostname(sHostName, 256) = SOCKET_ERROR Then
GetIPAddress = ""
MsgBox "Windows Sockets error " & Str$(WSAGetLastError()) & " has occurred. Unable to successfully get Host Name."
SocketsCleanup
Exit Function
End If
sHostName = Trim$(sHostName)
lpHost = gethostbyname(sHostName)
If lpHost = 0 Then
GetIPAddress = ""
MsgBox "Windows Sockets are not responding. Unable to successfully get Host Name."
SocketsCleanup
Exit Function
End If
CopyMemoryIP HOST, lpHost, Len(HOST)
CopyMemoryIP dwIPAddr, HOST.hAddrList, 4
ReDim tmpIPAddr(1 To HOST.hLen)
CopyMemoryIP tmpIPAddr(1), dwIPAddr, HOST.hLen
For i = 1 To HOST.hLen
sIPAddr = sIPAddr & tmpIPAddr(i) & "."
Next
GetIPAddress = Mid$(sIPAddr, 1, Len(sIPAddr) - 1)
SocketsCleanup
End Function


Public Function GetIPHostName() As String
Dim sHostName As String * 256
If Not SocketsInitialize() Then
GetIPHostName = ""
Exit Function
End If
If gethostname(sHostName, 256) = SOCKET_ERROR Then
GetIPHostName = ""
MsgBox "Windows Sockets error " & Str$(WSAGetLastError()) & " has occurred. Unable to successfully get Host Name."
SocketsCleanup
Exit Function
End If
GetIPHostName = Left$(sHostName, InStr(sHostName, Chr(0)) - 1)
SocketsCleanup
End Function
Public Function HiByte(ByVal wParam As Integer)
HiByte = wParam \ &H100 And &HFF&
End Function
Public Function LoByte(ByVal wParam As Integer)
LoByte = wParam And &HFF&
End Function
Public Sub SocketsCleanup()
If WSACleanup() <> ERROR_SUCCESS Then
MsgBox "Socket error occurred in Cleanup."
End If
End Sub
Public Function SocketsInitialize() As Boolean
Dim WSAD As WSADATA
Dim sLoByte As String
Dim sHiByte As String
If WSAStartup(WS_VERSION_REQD, WSAD) <> ERROR_SUCCESS Then
MsgBox "The 32-bit Windows Socket is not responding."
SocketsInitialize = False
Exit Function
End If
If WSAD.wMaxSockets < MIN_SOCKETS_REQD Then
MsgBox "This application requires a minimum of " & _
CStr(MIN_SOCKETS_REQD) & " supported sockets."
SocketsInitialize = False
Exit Function
End If
If LoByte(WSAD.wVersion) < WS_VERSION_MAJOR Or (LoByte(WSAD.wVersion) = WS_VERSION_MAJOR And HiByte(WSAD.wVersion) < WS_VERSION_MINOR) Then
sHiByte = CStr(HiByte(WSAD.wVersion))
sLoByte = CStr(LoByte(WSAD.wVersion))
MsgBox "Sockets version " & sLoByte _
& "." & sHiByte & " is not supported by 32-bit Windows Sockets."
SocketsInitialize = False
Exit Function
End If
'must be OK, so lets do it
SocketsInitialize = True
End Function


Sub Form_Load()
MsgBox Chr(10) & _
"IP-address: " + GetIPAddress & Chr(10) & Chr(10) & _
"Numéro de pc : " + GetIPHostName
End Sub

Bob Phillips
10-31-2016, 04:08 PM
I have never used these APIs myself, but just looking at the code, it immediately calls SocketInitialize, which checks WSAD (which is an instance of WSADATA), but I cannot see anything that populates that data type, so maxsockets will be in its initial value, 0, so the code fails.

GTO
11-02-2016, 03:09 AM
I have not used these APIs and have a shallower understanding by far, but FWIW...

The code looks similar to ALLAPI's example at: http://allapi.mentalis.org/apilist/07CCCC8728276405F3111B9A7262DDF2.html

If this is the source, I noted that ERROR_SUCCESS is not defined/assigned a value in either the example or your code. I believe it should equal zero.


Public Const ERROR_SUCCESS = 0


I would stress my "rookie" status, but if I am understanding what I read, I would think:


'Declare PtrSafe Function gethostname Lib "WSOCK32" (ByVal szHost As String, ByVal dwHostLen As LongPtr) As Long
'Declare PtrSafe Function gethostbyname Lib "WSOCK32" (ByVal szHost As String) As Long
Declare PtrSafe Function gethostname Lib "WSOCK32" (ByVal szHost As String, ByVal dwHostLen As Long) As Long
Declare PtrSafe Function gethostbyname Lib "WSOCK32" (ByVal szHost As String) As LongPtr


...as I understand dwHostLen to be a buffer length and not a pointer. Conversely, I believe gethostbyname returns a pointer. Finally, in WSADATA, I think that dwVendorInfo (referred to as lpVendorInfo in other examples) returns a pointer as well and should be As LngPtr.

For me (in 32-bit), it returns the IP and computer name with or without the "corrections", but I only have access to Excel installed in 32-bit mode, so I cannot test properly.

My looking around included:
https://msdn.microsoft.com/en-us/library/windows/desktop/ms741563(v=vs.85).aspx

Which includes the note:
Note An application should ignore the iMaxsockets, iMaxUdpDg, and lpVendorInfo members in WSADATA if the value in wVersion after a successful call to WSAStartup is at least 2. This is because the architecture of Windows Sockets changed in version 2 to support multiple providers, and WSADATA no longer applies to a single vendor's stack. Two new socket options are introduced to supply provider-specific information: SO_MAX_MSG_SIZE (replaces the iMaxUdpDg member) and PVD_CONFIG (allows any other provider-specific configuration to occur).

From that note, I wonder about using wMaxSockets (which returned 257 for me), as wMaxUDPDG and wMaxSockets are unsigned shorts.

https://msdn.microsoft.com/en-us/library/windows/desktop/ms742213(v=vs.85).aspx
http://allapi.mentalis.org/apilist/WSAStartup.shtml
http://allapi.mentalis.org/apilist/gethostname.shtml
http://allapi.mentalis.org/apilist/gethostbyname.shtml

Well, as I said, I cannot test what mods I think may be appropriate; please be cautious, but hopefully that helps?

Mark

Bob Phillips
11-02-2016, 03:39 AM
Again, not looked deeply, but taking Mark's comments.

If you change gethostbyname to LongPtr, you also have to dimension lpHost in function GetIPAddress as LongPtr.

But, I did this on a 64-bit Excel, and still get the same error message. I even added a call to WSAStartup and got that message.

GTO
11-02-2016, 03:54 AM
Again, not looked deeply, but taking Mark's comments.

If you change gethostbyname to LongPtr, you also have to dimension lpHost in function GetIPAddress as LongPtr.

:doh:Oops... I certainly missed that one.


But, I did this on a 64-bit Excel, and still get the same error message. I even added a call to WSAStartup and got that message.

Well Sigh... it was worth a shot. Thanks so much for testing Bob. :hi:

Bob Phillips
11-02-2016, 06:28 AM
I think the answer probably lies in those links Mark, but I don't have the incentive to go through it all.

Xav1050
11-03-2016, 10:31 AM
Thank you for your answers !

The code came indeed from allapi.net.
I forgot to note it after I clean the url to post the code. My apologies!
I will test as soon as possible (very busy for the moment).

thank you guys!