EXCEL VBA로 UDP 통신 해보기

엑셀에서 간단한 네트워크 게임을 구현해볼 때 유용하게 사용할 수 있을 것 같다.

네트워크 모듈과 샘플 코드를 작성했다.

MS 오피스 2010에서 테스트 결과 정상이고, 2007에서는 오류 타입 몇 개를 수정해야 한다.

참고한 사이트 가기

네트워크 모듈

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257

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

샘플

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75

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