エクセルでwinsock APIを使って サーバー、クライアントを作る
公開日：2022/2/28
今回はエクセルのVBAで、WindowsのAPIを使った一般的なソケット通信をする方法を紹介します。 核となるのは、winsock2のライブラリ(wsock32.dll)なので、真面目に作ればちゃんとしたサーバーも作れます。 ソケット通信なので、他の機器とも通信可能です。エクセルから、別PCのエクセルは勿論、Android端末、PLCなんかにも通信出来ます。 今回は、一枚のエクセルシートに、クライアントと、サーバーの両方を作り、そのシートをコピーして、別のパソコンと通信させてみましょう。 使い方次第では、便利なツールになると思うので、是非参考にしてみて下さい！
エクセルの開発タブを表示させる
他のページでも言及してますが、初めてVBAをさわる人は、エクセルに開発のタブがないと思うので まず設定を一部変えます。設定の変更方法は、Excelで簡単FFT で紹介しているので参考にして下さい。
エクセルシート上にEtherNET通信用の操作部品を配置する
初めに、クライアントと、サーバーをそれぞれ操作するためのボタンと、接続先識別用のIPアドレスと、ポート番号を
任意で設定できるような、ユーザーインターフェース（もどき）を作成します。
エクセルの場合セル自体が値の変数になるため、適当な場所を、設定用のインターフェースに見立てます。
まずは、下記写真を参考に、操作用のボタンと値設定用のセルを適当に配置してください。
※難しければ、写真をそっくりそのまま真似してみて下さい。
また、挿入するコントロールのボタンは、ActiveXのボタンです。
プロジェクトツリーから標準モジュールを追加する
ViaualBasicタブから、VBAのウィンドウを立ち上げます。 新規で立ち上げた、エクセルシートの場合、デフォルトで生成されている、シートのオブジェクトしかありませんので、 EtherNet通信(以降ソケット通信と呼びます) 処理を行うコードを書く為のモジュールを追加します。 追加する方法は、プロジェクトツリー内で、右クリックを押すと、VBA専用のコンテキストメニュー（右クリックメニュー） が表示されますので、メニュー欄から『挿入』選択すると、『ユーザーフォーム』『標準モジュール』『クラスモジュール』の 3つの選択肢が現れますので、『標準モジュール』選択し追加します。 基本的に、通信用の処理を行うプログラムは、全てこっちに記述します。 後で、色々なエクセルに簡単に移植出来るように、なるべく抽象化して書いていきます。
ソケット通信用のAPIを使うための準備をする
先程追加した標準モジュール内に、Windowsの標準で持っている通信機能を使うための、おまじないを記述します。
(ざっくりいうと、Windows用のソケット通信を使うための宣言をします)
良くわからない人は、とりあえずこういうもん、と思ってもらって大丈夫です。宣言なので全く同じように真似するだけで大丈夫です。
※かなり量が多いですが、全部記述してください。
※2行にわたって定義されているAPIには、_ で区切らている事に注意してください。 また、どうしてもうまくいかない、模写が厳しいというかたは、 当ホームページの、プライバシーポリシー の問い合わせフォーム、もしくはメールアドレスまでご連絡いただければ、 今回使用したエクセルファイルを差し上げます。
1本で3役！差し込み不要！超便利マグネットで簡単脱着充電！
準備したAPIの実体関数を作る
APIの宣言ができたら、ソケット通信に必要な関数が使えるようになりますので、先程と同じく標準モジュール内に、APIを実行するためのプログラムを記述していきます。
ソケット通信は、大きく分けると下記のアクションを実行します。
・ソケットオープン
・ソケット接続
・データ送信
・データ受信
・ソケット切断
・サーバー待機
・サーバー受入
ソケット接続は、クライアント側で、サーバー待機、受入は、文字通りサーバー側でのみ実行します。
他の関数は、クライアントとサーバーでも使用するので、抽象化を意識した記述が必要になります。
詳しくは後述します。
ますは、下記の写真を関数を作ってください。 中に2つのAPIがある事に注目してください。
ソケットオープンは、通信条件を設定した変数を、APIの引数ににセットして
実行することで通信に必要なハードウェア識別情報をが帰ってきます。APIが失敗すると、-1が返ってきます。
成功すると、ソケットのハンドル(接続先ハードウェア情報の識別子)が、返ってくるので構造体で宣言した
変数にセットしておきます。以降、接続、送信、受信、切断で必要になってくる重要な識別子になります。
ソケット接続API(ｸﾗｲｱﾝﾄ接続開始)では、ソケットクライアントが、接続先対のサーバーに接続を開始します。
APIには、オープン時に取得したハンドルと、接続先IPアドレス、ポート番号をセットして実行します。
失敗すると、-1が戻ってきます。
次はﾃﾞｰﾀの送信用のAPIを記述します。
データ送信では、接続先に送信したいデータ(バイナリ)を引数にセットして、
送信処理を実行する関数(API)です。実際の命令コマンドはこの関数内では記述しません。
コマンドを呼び出す元側で事前に作って、この関数の引数にセットして使う形になります。
次はﾃﾞｰﾀの受信用のAPIを記述します。
データ受信では、ソケットのハンドルと、受信したデータを一時的に格納するための、バイト型配列の変数をセットします。
バイト型の配列は、想定する受信データの最大バイト数の配列をとりあえず確保しておきます。APIの戻り値は、
実際に受信したデータのバイト数になります。なので配列を多くの宣言しても、実際に受信するデータは少なくても大丈夫です。
※凄まじい量のデータを受信する場合は、別途小細工が必要なのですが、ここで説明すると大変なので割愛します。
次はソケット切断のAPIを記述します。
ソケット切断では、サーバー側にこちらから明示的に、通信を終了させることを通知します。
これを行わないでエクセルを閉じたりすると、サーバー側は通信が終了したことがわからず、
死んでいるポートを占有し続けることになるので問題です。
面倒ですが必ず作るようにしましょう。
次ｻｰﾊﾞｰ側がｸﾗｲｱﾝﾄｿｹｯﾄを受け入れる為のAPIを記述します。
サーバー待機関数は、サーバー専用の関数になります。実際は、クライアントのソケットオープン処理と同じく
ソケットオープンでサーバーハンドルを取得した後、クライアントからの接続待機状態になるための準備を行います。
次は、サーバー受け入れ用のAPIです。
前述したサーバー待機状態からクライアントの接続要求があった場合に、
接続受け入れを許可する為の関数になります。ただ、少し困った事に、クライアントからの接続要求は、どのタイミングで来るかはわかりません。
その為、少し特殊事をする必要あります。詳しくは次の項目で解説します。
その準備としてまずは、以下の WndProc という名の関数を作ってください。
WindowsのOSからメッセージが来た時の処理について事前にルールを決めます。 現時点では意味がわからないと思うもうので、
そのまま記述してください。
最後に WndProc が実際に呼ばれた時の処理について記述します。
WindowsのOSからメッセージが来た時は、大きく3つの処理があります。
・ｸﾗｲｱﾝﾄ受け入れ
・データ受信
・ｸﾗｲｱﾝﾄから切断
それぞれメッセージが来た時の処理を実行するための分岐になります。
標準モジュールに記述するコードは以上になります。
ソケットサーバー起動様の関数を作る
次にクライアント受け入れ用の、サーバーインスタンスを生成する為の関数を作ります。
サーバーは、クライアントと違って受動的な立場になるので、作り方に少し工夫が必要です。
クライアントの場合、接続、切断、送信、※受信は、クライアントとなるユーザーが能動的に実施するのに対し、
サーバーは、クライアントからの接続要求や、データの受信タイミングが分からないので、
イベント呼び出し、関数呼び出しを、スイッチ等を使って実行するのは、実用的ではありません。
これを、解決するためには、ウィンドウプロシージャと呼ばれる、Windowsからのメッセージを
フックするための、特別な関数を作って対応します。
ここで面倒なのですが、エクセルVBAの場合、通常のシートや、標準モジュールではサブクラス化をつかった、
メッセージ呼び出しが出来ない為、ダミーのフォームウィンドウを作って、そこからメッセージ呼び出し用の関数を
作る必要があります。
一見ややこしいですが、記述コード自体は少ないので、面倒ではありますが難しくはないので安心してください。
順番に説明していきます。
マスツーの常識が変わる！ 一緒にツーリングしている仲間の心に直接話しかける
今、あなたの心に直接語りかけています・・・
プロジェクトツリーからユーザーフォームを追加する
VBAのプロジェクトツリーより、標準モジュールを追加した要領で、ユーザーフォームを
新たに追加します。追加する手順は、冒頭で説明した標準モジュールの追加方法をと同じです。
追加されると、UserFrom1 というオブジェクトが生成されるので、選択すると空のフォームウィンドウが表示れます。
この空のフォームウィンドウが、前述したダミーウィンドウになります。ダミーウィンドウは、メインのシートから
特定の条件下のみに呼び出しを実行します。今回は、ダミーウィンドウ上に、『サーバー受入開始』と、『サーバー終了』
ボタンを配置して、サーバー開始と終了の、トリガーを作成します。
VBAのツールボックスから、『テキストボタン』を選択して、下記写真を参考にボタンを配置してください。
ボタンの配置ができたら、VBAのプロジェクトツリー内で右クリックをして、コンテキストメニューから、『コードの表示』を選択し、 UserForm1のコード編集画面より、下記3つの、関数を記述します。
※CommandButton1_Click は、デザイン画面にて配置して、コマンドボタンをダブルクリックすると自動で追加されます。
コードの記述ができたら、ユーザーフォーム内の準備は完了です。
エクセルシートのデータを実際に操作をする為の関数を記述する
最後に、実際にエクセルシート上のデータを、クライアントからｻｰﾊﾞｰ、サーバーからｸﾗｲｱﾝﾄに向けて
データを送信するきっかけを与えるための関数を記述します。
まずは、クライアント側の、接続、切断を操作するための関数を記述します。
CommandButton1がクライアント接続、CommandButton2がｸﾗｲｱﾝﾄ切断に対応しています。
これらのボタンは、エクセルシート上配置した、GUIをダブルクリックすると初回は自動で空の関数を自動生成してくれます。
※IPアドレスとポート番号は適当です。 自分のパソコンの環境に合わせて設定します。
次に、クライアント側でﾃﾞｰﾀを送信する時に使う関数と、受信する時に使う関数を記述します。
Button3が送信で、Button4が受信に対応してます。
最後に、サーバー起動の関数と、サーバーが受け入れ対象のｸﾗｲｱﾝﾄ側にﾃﾞｰﾀを送るための関数を記述します。
ｻｰﾊﾞｰ起動は先ほど、UserForm1で記述した、プログラムを呼び出す大元の関数になります。
ｻｰﾊﾞｰﾃﾞｰﾀ送信は、サーバーが受け入れ許可をしているｸﾗｲｱﾝﾄに、個別にﾃﾞｰﾀを送るための関数になります。
これですべての準備ができましたので、次は実際にﾃﾞｰﾀの送受信ができるか確認してみましょう。
二つのパソコンを用意して、実際に送受信を行ってみよう
最後に実際に通信をおこなってみましょう。
一番良いのは二つのパソコンを用意して、どちらかをｻｰﾊﾞｰ、どちらかをｸﾗｲｱﾝﾄに見立てて、実施するとよいのですが、
パソコンが一つしかない場合、しょうがないので、一枚のエクセルシートで、サーバークライアントに見立てて、自分自身に
ﾃﾞｰﾀを送ってテストを実施してみます。
次に記す写真は、1枚のエクセルシートで実施した例ですが、前述した2台のパソコンで実施する方法と手順に殆ど違いがないので
今回は、1台のパソコンで実施している写真で紹介します。
まずはじめに、『サーバー起動』 ボタンをクリックして、ダミーウィンドウを起動させたらそのまま 『サーバー受入開始』 ボタンクリックします。
ｻｰﾊﾞｰオープンスステータスの右横の、表示が、『Sever Listen・・・』 になると思うので、
そのまま、『ｸﾗｲｱﾝﾄ接続』 ボタンクリックして、クライアント接続を開始します。 問題がなければ、クライアント接続状況の右横の表示が、
『OK』となり、ｻｰﾊﾞｰオープンステーターすの横は、『Server Accsept』 の表示なれば無事成功です。
では次に、実際にお互いでデータを送りあってみましょう。
写真を参考に、クライアント側の送信データセルの右側に、適当な半角文字をセットして、『ｸﾗｲｱﾝﾄ送信』 をクリックしてみて下さい。
正常にデータが飛ぶと、ｻｰﾊﾞｰ側の受信データセルの右側に、同じ文字が表れてくるはずです。
次に同じ要領で、ｻｰﾊﾞｰ側の送信データ右セルに半角文字をセットして、『サーバー送信』を実行した後、『ｸﾗｲｱﾝﾄ受信』してみて下さい。
クライアント側にも文字が表れてくるはずです。
※ｸﾗｲｱﾝﾄ側は、Windowsのメッセージをフックするサブクラス化を行っていないので、データを受信バッファに受け取ってもメッセージが取れないので
『ｸﾗｲｱﾝﾄ受信』を手動で行う必要があります。
どうでしたか？ うまく、クライアント、ｻｰﾊﾞｰの生成を行いﾃﾞｰﾀの送受信は行えましたでしょうか？
今回紹介した方法は、WindowsのAPIを使った、一般的なソケット通信の方法で、C言語（C++）や、C＃などでも
関数の型や引数等は同じものになります。（まぁ、だからAPIというのですが・・・）文法を違いますが、同じ手順で、構築することができます。
また、このソケット通信は、所謂一般的な手法になるため有線は当然、Wi-Fi接続も可能ですし、Android端末や、
PLCやバーコードリーダー等の、他のソケット通信が可能なハードウェアとも通信が可能です。
また、AndroidでPLC接続のページでは、Android端末で
ソケット通信を行う方法にも触れていますので、組み合わせることで、Android⇔Excel の通信も可能ですので、
もし興味があればそちらも参考にしてみて下さい。やり方次第では、様々な方法で業務に応用できると思います。
貴方の業務に少しでもお役に立てたら幸いです。
