엑셀에서 간단한 네트워크 게임을 구현해볼 때 유용하게 사용할 수 있을 것 같다.
네트워크 모듈과 샘플 코드를 작성했다.
MS 오피스 2010에서 테스트 결과 정상이고, 2007에서는 오류 타입 몇 개를 수정해야 한다.
참고한 사이트 가기
네트워크 모듈
<![CDATA[
Option Explicit
Public Const SOCKET_ERROR As Long = -1
Public Const IPPROTO_IP As Long = 0
Public Const IPPROTO_UDP As Long = 17
Public Const IP_ADD_MEMBERSHIP As Long = 12
Public Const IP_DROP_MEMBERSHIP As Long = 13
Public Const AF_INET = 2
Public Const SOCK_DGRAM = 2
Public Const FD_SETSIZE = 64
Public Const SOCKADDR_SIZE = 16
Public Const SOCKADDR_IN_SIZE = 16
Public Const WS_VERSION_REQD As Long = &H101
Public Const IP_SUCCESS As Long = 0
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 Hostent
h_name As Long
h_aliases As Long
h_addrtype As Integer
h_length As Integer
h_addr_list As Long
End Type
Public Type SOCKADDR
sin_family As Integer
sin_zero As String * 14
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 LongPtr
fd_array(FD_SETSIZE) As Long
End Type
Public Type timeval
tv_sec As Long
tv_usec As Long
End Type
Public Type ip_mreq
imr_multiaddr As Long
imr_interface As Long
End Type
Public listenAddr As SOCKADDR_IN
Public m_socket As Long
Public Declare Function WSAStartup Lib "wsock32.dll" (ByVal wVersionRequired&, lpWSADATA As WSAData) As Long
Public Declare Function WSACleanup Lib "wsock32.dll" () As Long
Public Declare Function gethostbyname Lib "wsock32.dll" (ByVal host_name As String) As Long
Public Declare Function gethostname Lib "wsock32.dll" (ByVal host_name As String, ByVal namelen As Integer) As Integer
Public Declare Function setsockopt Lib "wsock32.dll" (ByVal s As LongPtr, ByVal level As LongPtr, ByVal optname As LongPtr, optval As Any, ByVal optlen As LongPtr) As Long
Public Declare Function w_socket Lib "wsock32.dll" Alias "socket" (ByVal lngAf As LongPtr, ByVal lngType As LongPtr, ByVal lngProtocol As LongPtr) As Long
Public Declare Function w_closesocket Lib "wsock32.dll" Alias "closesocket" (ByVal socketHandle As LongPtr) As Long
Public Declare Function w_bind Lib "wsock32.dll" Alias "bind" (ByVal socket As LongPtr, Name As SOCKADDR_IN, ByVal namelen As LongPtr) As Long
Public Declare Function w_connect Lib "wsock32.dll" Alias "connect" (ByVal socket As LongPtr, Name As SOCKADDR_IN, ByVal namelen As LongPtr) As Long
Public Declare Function w_send Lib "wsock32.dll" Alias "send" (ByVal socket As LongPtr, buf As Any, ByVal length As LongPtr, ByVal Flags As LongPtr) As Long
Public Declare Function w_sendTo Lib "wsock32.dll" Alias "sendto" (ByVal socket As LongPtr, buf As Any, ByVal length As LongPtr, ByVal Flags As LongPtr, remoteAddr As SOCKADDR_IN, ByVal remoteAddrSize As LongPtr) As Long
Public Declare Function w_recv Lib "wsock32.dll" Alias "recv" (ByVal socket As LongPtr, buf As Any, ByVal length As LongPtr, ByVal Flags As LongPtr) As Long
Public Declare Function w_recvFrom Lib "wsock32.dll" Alias "recvfrom" (ByVal socket As LongPtr, buf As Any, ByVal length As LongPtr, ByVal Flags As Long, fromAddr As SOCKADDR_IN, fromAddrSize 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 w_getLastError Lib "wsock32.dll" Alias "WSAGetLastError" () As Integer
Public Declare Function htons Lib "wsock32.dll" (ByVal hostshort As Long) As Long
Public Declare Function ntohl Lib "wsock32.dll" (ByVal netlong As Long) As Long
Public Declare Function inet_addr Lib "wsock32.dll" (ByVal cp As String) As Long
Public Declare Function ioctlsocket Lib "wsock32.dll" (ByVal s As Long, ByVal cmd As Long, argp As Long) As Long
Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (hpvDest As Any, hpvSource As Any, ByVal cbCopy As Long)
Public Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal Buffer As String, Size As Long) As Long
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
'---ioctl Constants
'Public Const FIONREAD = &H8004667F
Public Const FIONBIO = &H8004667E&
'Public Const FIOASYNC = &H8004667D
'---async notification constants
Public Const FD_ACCEPT = &H8&
Public Const FD_CLOSE = &H20&
Public Const FD_CONNECT = &H10&
Public Const FD_READ = &H1&
Public Const FD_WRITE = &H2&
Private Const WSAEINTR = 10004
Private Const WSAEACCES = 10013
Private Const WSAEFAULT = 10014
Private Const WSAEINVAL = 10022
Private Const WSAEMFILE = 10024
Private Const WSAEWOULDBLOCK = 10035
Private Const WSAEINPROGRESS = 10036
Private Const WSAEALREADY = 10037
Private Const WSAENOTSOCK = 10038
Private Const WSAEDESTADDRREQ = 10039
Private Const WSAEMSGSIZE = 10040
Private Const WSAEPROTOTYPE = 10041
Private Const WSAENOPROTOOPT = 10042
Private Const WSAEPROTONOSUPPORT = 10043
Private Const WSAESOCKTNOSUPPORT = 10044
Private Const WSAEOPNOTSUPP = 10045
Private Const WSAEPFNOSUPPORT = 10046
Private Const WSAEAFNOSUPPORT = 10047
Private Const WSAEADDRINUSE = 10048
Private Const WSAEADDRNOTAVAIL = 10049
Private Const WSAENETDOWN = 10050
Private Const WSAENETUNREACH = 10051
Private Const WSAENETRESET = 10052
Private Const WSAECONNABORTED = 10053
Private Const WSAECONNRESET = 10054
Private Const WSAENOBUFS = 10055
Private Const WSAEISCONN = 10056
Private Const WSAENOTCONN = 10057
Private Const WSAESHUTDOWN = 10058
Private Const WSAETOOMANYREFS = 10059
Private Const WSAETIMEDOUT = 10060
Private Const WSAECONNREFUSED = 10061
Private Const WSAEHOSTDOWN = 10064
Private Const WSAEHOSTUNREACH = 10065
Private Const WSAEPROCLIM = 10067
Sub startServer(listenPort As Long)
Dim socket As Long
'FinalizeSilverlightConnection
If (Not SocketsInitialize()) Then
MsgBox "Error initializing WinSock"
Return
End If
socket = w_socket(AF_INET, SOCK_DGRAM, IPPROTO_UDP)
listenAddr.sin_family = AF_INET
'listenAddr.sin_addr = inet_addr(ip)
listenAddr.sin_addr = 0
listenAddr.sin_port = UnsignedLongToInteger(htons(listenPort))
Dim bindResult As Long
bindResult = w_bind(socket, listenAddr, SOCKADDR_IN_SIZE)
If bindResult = SOCKET_ERROR Then
MsgBox "Error binding listener socket: " & CStr(Err.LastDllError)
GoTo EXIT_POINT
End If
'ioctlsocket
Dim lngRet As Long
Dim Enabled As Long: Enabled = 1
lngRet = ioctlsocket(socket, FIONBIO, Enabled)
If lngRet = SOCKET_ERROR Then
MsgBox "Error nonblocking mode Setting"
GoTo EXIT_POINT
End If
m_socket = socket
Exit Sub
EXIT_POINT:
SocketsCleanup
End Sub
Sub startClient()
If (Not SocketsInitialize()) Then
MsgBox "Error initializing WinSock"
Return
End If
Dim socket As Long
socket = w_socket(AF_INET, SOCK_DGRAM, IPPROTO_UDP)
Dim Enabled As Long: Enabled = 1
If ioctlsocket(socket, FIONBIO, Enabled) = SOCKET_ERROR Then
GoTo EXIT_POINT
End If
m_socket = socket
Exit Sub
EXIT_POINT:
SocketsCleanup
End Sub
Public Function SocketsInitialize() As Boolean
Dim WSAD As WSAData
SocketsInitialize = WSAStartup(WS_VERSION_REQD, WSAD) = IP_SUCCESS
End Function
Public Sub SocketsCleanup()
w_closesocket (m_socket)
If WSACleanup() <> 0 Then
MsgBox "Windows Sockets error occurred in Cleanup.", vbExclamation
End If
End Sub
Function UDPRecv(ByRef recvBuffer As String, recvLen As Long, ByRef remoteAddr As SOCKADDR_IN) As Long
Dim recvresult As Long
recvresult = w_recvFrom(m_socket, ByVal recvBuffer, recvLen, 0, remoteAddr, SOCKADDR_IN_SIZE)
If (recvresult > 0) Then
UDPRecv = recvresult
ElseIf recvresult = SOCKET_ERROR Then
If Not Err.LastDllError = WSAEWOULDBLOCK Then
GoTo EXIT_POINT:
Else
UDPRecv = 0
End If
End If
Exit Function
EXIT_POINT:
SocketsCleanup
UDPRecv = -1
End Function
Function UDPSend(ByRef remoteAddr As SOCKADDR_IN, ByRef sendBuffer As String, sendLen As Long) As Long
Dim sendresult As Long
sendresult = w_sendTo(m_socket, ByVal sendBuffer, sendLen, 0, remoteAddr, SOCKADDR_IN_SIZE)
If (sendresult > 0) Then
UDPSend = sendresult
ElseIf sendresult = SOCKET_ERROR Then
If Not Err.LastDllError = WSAEWOULDBLOCK Then
GoTo EXIT_POINT:
Else
UDPSend = 0
End If
End If
Exit Function
EXIT_POINT:
SocketsCleanup
UDPSend = -1
End Function
Public Function UnsignedLongToInteger(uLong As Long) As Integer
If uLong > 32767 Then
UnsignedLongToInteger = uLong - 65536
Else
UnsignedLongToInteger = uLong
End If
End Function
]]>
샘플
<![CDATA[
Option Explicit
Const SERVER_IP As String = "172.20.44.173"
Const SERVER_PORT As Long = 65500
Const PACKET_LOGIN As String = "hello"
Sub startServer_Click()
Network.startServer SERVER_PORT
Debug.Print "start Server"
While True
Dim recvBuffer As String * 1024
Dim recvLen As Long
recvLen = 1024
Dim remoteAddr As Network.SOCKADDR_IN
recvLen = Network.UDPRecv(recvBuffer, recvLen, remoteAddr)
If recvLen > 0 Then
If Left(recvBuffer, recvLen) = PACKET_LOGIN Then
Debug.Print recvBuffer
Network.UDPSend remoteAddr, PACKET_LOGIN, Len(PACKET_LOGIN)
GoTo EXIT_POINT
End If
ElseIf recvLen = -1 Then
GoTo EXIT_POINT
End If
DoEvents
Wend
EXIT_POINT:
Network.SocketsCleanup
Debug.Print "finish Server"
End Sub
Sub startClient_Click()
Network.startClient
Debug.Print "start Client"
Dim remoteAddr As Network.SOCKADDR_IN
remoteAddr.sin_family = Network.AF_INET
remoteAddr.sin_addr = Network.inet_addr(SERVER_IP)
remoteAddr.sin_port = Network.UnsignedLongToInteger(Network.htons(SERVER_PORT))
Network.UDPSend remoteAddr, PACKET_LOGIN, Len(PACKET_LOGIN)
While True
Dim recvBuffer As String * 1024
Dim recvLen As Long
recvLen = 1024
recvLen = Network.UDPRecv(recvBuffer, recvLen, remoteAddr)
If recvLen > 0 Then
If Left(recvBuffer, recvLen) = PACKET_LOGIN Then
Debug.Print recvBuffer
GoTo EXIT_POINT
End If
ElseIf recvLen = -1 Then
GoTo EXIT_POINT
End If
DoEvents
Wend
EXIT_POINT:
Network.SocketsCleanup
Debug.Print "finish Client"
End Sub
]]>
