小規模老人保健施設の栄養士さんのお助けツール。施設利用者データベースを利用して簡単に食札の印刷をするシステムをExcel VBAで作ってみました。データを均一に入力するための入力フォームを作成。

エクセルVBA
この記事は約14分で読めます。

食札管理システム概要

小規模老人保健施設の利用者の給食管理で使用する食札を印刷できるツールです。

サンプルデータが100件ほど入力されていますから、使用感が確認できます。

 

ダウンロード

ベクター様からダウンロードできます。(10日間の試用期間設定あり)

取り扱い説明書

ダウンロードしたファイルを解凍してください。

本製品は”エクセル2013″で作成しています。マクロ付きですから、マクロを有効にしてください。ブックの各シートに設定されている書式やデータはロックしていませんから利用状況に合わせて設定値を変更できますが、セルの列や行を追加、削除するとマクロが正常に機能しなくなる恐れがあります。どうしても追加削除するときはマクロの修正が必要です。マクロはロックされていますが、本製品をお買上時発行されるライセンスキーを使って解除できます。(マクロを編集する前にファイルのコピーを取ってください)

ファイルを開くとSheet1が開きます。

ライセンスキーの入力画面が開きます。(ベクター様から購入いただくとライセンスキーが発行されます)。「後から登録」ボタンを押せばライセンスキーなしでも10日間お試し利用できます。

左上にある「メニュー表示」ボタンをクリックします。

データベースを利用するための下図のメニュー画面が開きます

今のところ、3個の機能が設定されています

①は施設利用者のデータベースを操作する機能です。新規登録と作成済みデータの修正用編集メニューがあります。

 

まず最初は新規登録の説明です。

左側の「新規登録」ボタンをクリックします。

すると、入力画面が表示されますから「編集開始」ボタンをクリックします。

左端の氏名から順に入力していきます。

氏名を入力すると、よみがなが自動的に入力されますから一応読み仮名を確認し間違えがあれば修正してください。

性別はコンボボックスになっていますから、右側の▼をクリックして開いたリストから選択入力します。

生年月日は空欄をクリックすると別ウインドウが開いて年、月、日をそれぞれ選択入力します。年齢はその時自動的に計算されて転記されます。その他順次入力項目を選択入力していきますが、選択食の項目は今のところ何も設定していませんのでスルーしてください。一番最後の登録日の項目は自動的に入力されます。

登録ができたら③の「登録」ボタンをクリックすると画面の情報がSheet1に転記されます。

 

 

次は、「編集(修正)」ボタンの説明です。ボタンをクリックすると、①のインデックスボタンが表示されます。

このファイルにはあらかじめ100件程のサンプルデータが入っていますので、「あ」のボタンをクリックします。

すると、入力してあるデータのよみがな項目のうちから先頭の文字が「あ」行(あいうえお)が含まれるデータをリストした画面が表示されます。

 

ここでは、リストの上から二行目にある「大村様」をクリックしました。

すでに入力されている大村様の各データを表示しますから、必要な個所を修正します。編集が終わったら登録ボタンを押すとそのまま修正が反映されます。

同姓同名の処理について。
あまりないとは思いますが、同姓同名の登録があったときのため、氏名を入力した時点で登録済みのデータの中に同姓同名があったときは、注意を喚起するメッセージを表示するようにしています。また、同姓同名でも入力ができるよう区別するための添え字を自動的に設定します。また、食札のカード印刷でも氏名の頭に★印をつけて同姓同名がいることを知らせるようになっています。
サンプルデータの内容はすべて架空の情報です。システムの動作を確認するために仮に入力しました。実在する氏名と同じであってもなんの関係もないデタラメであることをご承知ください

 

次に、下図の②登録情報を集計するボタンをクリックします。

Sheet1の情報からその日当日の施設利用者を抜き出してSheet2に書き出してその項目の内容を集計したものが下図のように表示します。栄養士さんが厨房へ指示だしするときのデータとして使用できます。

ここの今日の利用者データとは、入所者とデイケア利用者のうち当日の曜日に該当する登録情報を持っている人を抽出集計しています。

 

次に、③の食札カードを印刷するボタンの説明です。

 

上図③をクリックするとウインドウが下に伸びて、食札を印刷する準備をします。

下図の①でインデックスからデータの絞り込みをします。最初はすべての人が②のようにリストされます。

食札カードを印刷する人をクリックします。

 

下図のように、ここでは上から4人目までチェックを入れました。

リストボックスの下にある用紙の選択で、ラベル紙かコピー紙かを選択します。

次に、その下にあるカードを印刷するボタンを押します。

下図のように「印刷します」メッセージが表示しますから、OKボタンをクリックします。

すると、少し待つとエクセルの印刷プレビュー画面が表示されます。

プレビュー画面の左端にある印刷アイコンをクリックして印刷を開始してください

 

用紙選択について
10面ラベル紙はA-oneさんの10面カットラベル紙に合わせて設定してあります。印刷するリストが10人以下の時はラベルが無駄になってしまいます。(次回、人数に合わせた印刷ができるように設定します)臨時での応急的に手書きするときにご利用してください。コピー紙への印刷では外枠線を表示印刷しますから、ハサミでカットしてご使用ください。

 

データを均一にするための入力フォーム

 

このシステムでは100名前後までの極小規模な老人保健施設などの給食施設で利用されることを前提に入居者用(給食利用者)名簿データベースを作っています。

 

スタイルとしては、下図の様なごく一般的なシートに一行に多項目のレコードを積み重ねる形です。

一行にA~Xまで14の項目を持っています。施設によってはもっと多くの項目を設定する必要があるかもしれませんが、その時は少しマクロを変更する必要があるかもしれません。

 

 

データを入力する際は入力する人によって形式が変わらないように、データの形式を定型化して均一に入力できるよう工夫しました。

入力する項目は下図の様な別のシートにまとめ、そのデータを引用する形でリストから選択する形式にしました。後から項目の内容を増減できるよう工夫しています。

 

下図はデータ入力の際、表示される入力フォームの一部です。

氏名読、み仮名等手入力する項目もありますが定型化できるところはコンボボックスから選択入力できるようにしたり、項目を選択したとき別表示されるリストから選択して入力できる部分もあります。同姓同名のチェック機能も付けました。

 

 

具体的コードの解説

 

コンボボックスのリストを設定する

 

左図の様な、別シートのQ列にある「副食形態」の項目の赤丸囲みしたところのデータを、右図のコンボボックスにセットします

    

 

 

comboAddsetの引数にはフォームのコントロールと設定シートのどのデータを使うのかを指定する列位置が指示されます

 

 

コレクションの作成

 

上図の様な入力フォームに張り付けた多くのコントロールを効率よくプログラムで利用するにはコレクションという機能を利用します。

 

例えば、ここで使用している入力用フォームには、多くのコンボボックスやテキストボックスを使用しています。その多種類のコントロールをコード中に表現するにはいちいちCombobox1、Combobox2、TextBox1、TextBox2…とそれぞれのコントロールの「オブジェクト名」を記述しなければなりませんが、そのフォームで使用する様々なオブジェクトをひとまとめにしてコレクション化すると一個のオブジェクト名  インデックスで表現できるようになります。これで繰り返し処理などで種々のコントロールをひとまとめにしてインデックスで表すと、インデックスを変数として扱えば効率よく処理できるので大変便利です。

 

以下はそのコレクションを作成するコードを表します

 

①でコレクション変数を宣言して

②で具体的にコレクション変数に、コントロールのオブジェクトを登録します。

 

このコードの場合コレクション「controrunarabi 」に登録されたコントロールの順番に1、2、3とインデックスが割り振られます。これを呼び出して利用するには controrunarabi (1)controrunarabi (2)controrunarabi (3) ・・・などとします。

 

下図で使われている controrunarabi (3) はフォームの「Tyomigana」とオブジェクト名を付けたTextBoxを表しています。

 

 

 

コントロールの利用  For…Next(繰り返し処理)で使う

 

’————————————————–

For i=1 to 10

       controrunarabi (i) = ”テキスト、テストデータ” & i

Next i

’————————————————–

等の様に使います。

例えば、コレクションを使用しないで同じ処理をしようとすると、コントロールのオブジェクト名を10個分記述しなければなりません。実際は20個以上のコントロールがありますから、これをたった一行で多くのコントロールを表現できるのですから大変便利です。

 

下図のプロシージャはこのシステムの実際のコードです。

入力フォームで新規に利用者のデータを登録する処理です。コントロールに入力されたデータをシートの入力できる行を見つけて、各項目列のセルに順次張り付ける処理をしています。

同じ繰り返し処理で使用しますが上の例とは違って For…Earth…In を使っています。

 

 

ポイント① 書き込みできる最終行を調べる

このプロシージャでまず、ポイントになるのがデータをどこに書き込むかを知る必要があるということです。エクセルでデータベースを作る時によく使うテクニックですから覚えておきましょう。

普通、データベースは行と列のデータの塊ですが、データとデータの間に空白の行や空白の列がないことが条件です。そして、データ群のデータの書き込まれた最終行以降はすべて空白のセルであることが必要です。

入力済みの既存データがある行が最終行ですから、その一個下のセルが新規行位置です。

それを表現したのが

gyou = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row + 1 です。

 

赤色文字の部分...Cells(Rows.Count, 1) について。

()内のカンマの左側の、Rows.Count とはSheet1のシートの行数のことです。シートの行数はエクセルのバージョンによって違っています。筆者が現在使っているExcel2013で最終行の番号を見てみると、1048576となっています。もちろんこの実数を使ってもいいのでしょうが、汎用的にはRows.Countとするのがいいようです。そしてカンマの右側のは列のことで、一列目、A列のことです。

 

つまり、1列目のセルの1048576行目のことです。

 

そして、 gyou = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row + 1 の

 

赤文字のところ、...End(xlUp).Row + 1 について。

1列目の1048576行目セルから上方向に向かって最初にデータがあるセル の行下 を意味します。(シートの最終行のセルを選択して、Ctrl + 上矢印キーを押したときと同じ動作)

 

何もデータが書き込まれていないシートでセルA10に文字列 ”ABC” を書き込んでおきます。

gyou = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row + 1 を実行すると変数gyouには 11が代入されます。

このgyouを使って Cells(gyou, 1) = ”DEF” などと利用することになります。

 

Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select

ActiveCell = ”DEF” などでも上と同じことができますね。

 

エクセルには様々な機能が数多くあります。、何をどのよう動かしたいか考えたら、大抵のことはほぼ全て実現できます。

 

ポイント② コントロールの利用 For…Earth…In(繰り返し処理)で使う

 

上で作ったコレクションを使って下のコードを実行してみます。

For Eachで使う赤文字の変数(data)はVariant で宣言しなければならないと書いてありましたが、宣言は省略できるとも書いてありましたので、ここでは宣言を省略しました。

In に指定した コレクション controrunarabi には、フォームに張り付けたコントロールが24個が登録されています。

そして、繰り返すたびにcontrorunarabiに登録された順にオブジェクトが取り出されdataに渡されます。

 

Dim i As Integer

i=0

For Each data In controrunarabi

i = i + 1

Cells(i, 1) = data

Next data

 

すなわち、一回目の繰り返しではCells(1, 1)

コレクションの一番目のオブジェクトLid(ラベル)に書き込まれた値が転記され

 

二回目の繰り返しではCells(2, 1)

コレクションの二番目ののオブジェクトTsimei(テキストボックス)に書き込まれた値が転記され

となるわけです。

 

For Eachではコレクションから最後のオブジェクトが取り出されると繰り返しが終了しますが、何回繰り返したかを知る必要があったので、別途カンター用の変数 i を宣言して利用しました)

 

 

10面カットラベル紙へ印刷

       

 

枠線なし印刷の様子

 

入居者の食札を作る時は、カードに直接印刷するのは難しいので、ラベル紙に印刷させる方法をとりました。ここではラベル紙メーカー「Aone」で出している10面カットラベル紙にサイズを合わせて印刷できるようにしました。10面カット用の用紙なので、人数などによっては、10カット丁度にならず半端で印刷が終わる場合もあると思い、残りのカット用紙が無駄にならないように、次回印刷の時残っているカット用紙の状態を選択して印刷を開始できるように工夫しました。

 

下図はその選択画面です。

 

又、カットラベル紙なのでデータに枠線を設定していませんが、あえて枠線をつけて印刷ができるようにもしています。その場合は普通のコピー紙などに枠線付きで印刷して枠線に合わせてハサミでカットして使うなどもできるようにしました。

 

利用される施設のやり方に合わせて使用してみてください。

 

 

まだまだあります。

 

食札情報をセルに埋め込んで印刷する

 

 

 

 

 

 

編集後記

 

コロナ禍、未だ続く自粛生活ですが、もともと透析治療中ですから、どこにも出かける予定もありません。鮎斗君は散歩にも連れて行けないのでストレスが溜まっているようだけど何とかやり過ごしています。

毎回、病床で透析の治療を受けながら、プログラミングしているけど、最近はめっきり集中力が持続しなくなってしまった。午前中3時間もすると疲れ切って眠ってしまうので一つのプロシージャを完成させるのに3日もかかってしまうこともあるんです。

今回のデータベースも15日もかかってしまった。まだまだ満足の行くものには程遠いが、まずは使用できる段階までにはなったので公開しました。

 

追伸

約1年前に買ったキーボード( ANNE PRO2)の調子がよくない。XPS13にBluetooth接続で使っていたけど、そのBluetooth接続が切れることが度々起きる、Win10のデバイス設定でBluetoothの再接続設定をしても3分と持たずに切れる。ドライバーを入れなおしたり、色々やったけどいっこうに改善しない。中華製とはこんなものなのか?他の5000円程度の安物と比べても結構高かったけど気に入っていたのに残念だ。

(この部分だけで4回切れた。)

コメント

タイトルとURLをコピーしました