3Dの分子モデルをPowerPointで表現するには

 学会の季節ともなると PowerPoint でのスライド作りが忙しくなってきます.蛋白質の分子構造を表現する方法をいくつか紹介します.

制約条件

発表時に自分の端末を使用できるか?

 これが重要な分岐点になります.大きな学会になると発表専用の PC 端末が準備されており,カスタマイズは不可能です.これは大きな制約条件になります.

ネットに繋がるか?

 動画を扱う場合,ファイルサイズが非常に大きなものになり,場合によっては YouTube などへのリンクを貼り付けるに留める必要があるかも知れません.その場合,発表用の端末がネットに繋がるかどうかも制約条件の一つとなります.

自分の端末を使用できない場合

 この場合はあまり効果的な視覚効果は期待できません. GIF アニメーションを見せるくらいがせいぜいです.

蛋白質データバンクからpdbファイルをダウンロードする

 Protein Data Bank というデータベースがあります.蛋白質の立体構造を位置情報込みで登録したデータベースで,誰でも無料で利用できます.

QuteMolで回転アニメーションに変換する

 フリーウェアの QuteMol というソフトウェアがあります.ダウンロードした pdb ファイルをこのソフトで開き,GIF アニメーションで保存すると,回転するアニメーションで保存できます.

アデノシン三リン酸の gif アニメーション

自分の端末を利用できる場合

Discovery Studio Visualizerをインストールしよう

 フリーソフトウェアです.プラグインとして Discovery Studio ActiveX Control がインストールされます.これを PowerPoint から流用します.詳細はPowerPointやPDFにいろいろなマルチメディアを埋め込む(3) を参照してください.

分子モデルが手動でグリグリ動く

 やってみると病みつきになります.ただし,巨大な分子になるとコンピュータのパワーがそれなりに必要になります.

PowerPointで蛋白質を描く

 手動で描くわけですから ,これはある意味,力技になります.細胞膜に浮かぶ蛋白質の断面や奥にあるドメインなどを楕円形の組み合わせで描いていきます.

ポイントはグラデーション

 錯覚を利用します.断面は平面的に,ドメインは立体的に見えればよいのですから,断面に当たる楕円形のグラデーションを線形に,ドメインは放射状を用います.光源と影の角度に注意します.

蛋白質を手動で描く.断面のグラデーションを線形に,奥のドメインのグラデーションを放射状にすることで錯覚が生じる

3Dソフトウェアが使えるなら

pdb ファイルを VRML ファイルに変換

 DS Visualizer や Chimera が使える方なら VRML ファイルに変換し,一般の CG ソフトウェアに読み込ませることで本格的な立体モデルが作成できます.このあたりの詳細は下記の書籍を参考にしてください.

How to create scalar function of SQL Server in order to define nPNA?

The Japanese Society for Dialysis Therapy (JSDT) recommends PCR as an indicator of protein intake. Otherwise K/DOQQI recommends nPNA. If you calculate Kt/V with Daugirdas’ method, you can also define nPNA.

\displaystyle \mathrm{nPNA} = \frac{C_0}{36.3 + 5.48\times\mathrm{Kt/V} + 53.5/\mathrm{Kt/V}} + 0.168 \\  = \frac{\mathrm{preBUN}}{36.3 + 5.48\times\mathrm{Kt/V} + 53.5 / \mathrm{Kt/V}} + 0.168 \cdots(4)

Execute the following procedure.

CREATE FUNCTION Function_nPNA 
(		@preBUN		DEC(4, 1)
	,	@postBUN	DEC(4, 1)
	,	@preWeight	DEC(4, 1)
	,	@postWeight	DEC(4, 1)
	,	@DialysisDuration	int
)
RETURNS DEC(3, 2)
AS
BEGIN
	DECLARE @nPNA	DEC(3, 2)
	SELECT	@nPNA = @preBUN
	/	(36.3 + 5.48 * (dbo.Function_KtV(@preBUN, @postBUN, @preWeight, @postWeight, @DialysisDuration))
	+			53.5 / (dbo.Function_KtV(@preBUN, @postBUN, @preWeight, @postWeight, @DialysisDuration)))
	+	0.168
	RETURN	@nPNA
END

Reference: Simplified nutritional screening tools for patients on maintenance hemodialysis

SQL Serverのスカラー値関数としてnPNAを定義するには

 たんぱく質摂取量の指標として日本透析医学会では PCR を採用しています.一方 K/DOQQI では nPNA を採用しています.Daugirdas の方法で Kt/V を計算すると nPNA も計算できます.

\displaystyle \mathrm{nPNA} = \frac{C_0}{36.3 + 5.48\times\mathrm{Kt/V} + 53.5/\mathrm{Kt/V}} + 0.168 \\  = \frac{\mathrm{preBUN}}{36.3 + 5.48\times\mathrm{Kt/V} + 53.5 / \mathrm{Kt/V}} + 0.168 \cdots(4)

 下記プロシージャを実行して関数を作成します.

CREATE FUNCTION Function_nPNA 
(		@preBUN		DEC(4, 1)
	,	@postBUN	DEC(4, 1)
	,	@preWeight	DEC(4, 1)
	,	@postWeight	DEC(4, 1)
	,	@DialysisDuration	int
)
RETURNS DEC(3, 2)
AS
BEGIN
	DECLARE @nPNA	DEC(3, 2)
	SELECT	@nPNA = @preBUN
	/	(36.3 + 5.48 * (dbo.Function_KtV(@preBUN, @postBUN, @preWeight, @postWeight, @DialysisDuration))
	+			53.5 / (dbo.Function_KtV(@preBUN, @postBUN, @preWeight, @postWeight, @DialysisDuration)))
	+	0.168
	RETURN	@nPNA
END

参照:維持透析患者のための簡易栄養スクリーニングツール

How to define Kt/V, an indicator of the efficiency of dialysis, as scalar function of SQL Server?

In Japan, Shinzato’s fomula for calculating Kt/V, an indicator of efficiency of dialysis, is recommended by JSDT. Since integral equation is used to solve Shinzato’s method, you couldn’t solve algebraically. In K/DOQQI, it is usual to solve Kt/V with Daugirdas’ method. Shinzato has described that Daugirdas’ Kt/V is similar to Shinzato’s Kt/V.

\displaystyle \mathrm{Kt/V} = - LN( R - 0.08 \times t ) + \left[ 4 - \left( 3.5 \times R \right) \right] \times\frac{\mathrm{UF}}{\mathrm{W}}\\  = - LN \left( \frac{\mathrm{postBUN}}{\mathrm{preBUN}} - 0.008 \times t \right) + \left[ 4 - \left( 3.5 \times \frac{\mathrm{postBUN}}{\mathrm{preBUN}} \right) \right] \times \frac{\mathrm{preWeight} - \mathrm{postWeight}}{\mathrm{postWeight}} \cdots(1)
\displaystyle \mathrm{Gw} = \mathrm{G}\cdot\mathrm{Tw} = \mathrm{Kd}\int_{0}^{Td}C_1dt + \mathrm{Kd}\int_{0}^{Td}C_2dt + \mathrm{Kd}\int_{0}^{Td}C_3dt \cdots(2)
\displaystyle \mathrm{Ce} = \mathrm{Cs} Exp\left( - \frac{\mathrm{Kt}}{\mathrm{V}} \right) + \frac{\mathrm{G}}{\mathrm{K}}\left[ 1 - Exp\left( - \frac{\mathrm{Kt}}{\mathrm{V}} \right) \right] \cdots(3)

Execute the procedure as following;

CREATE FUNCTION Function_KtV 
(		@preBUN DEC(4, 1)
	,	@postBUN DEC(4, 1)
	,	@preWeight	DEC(4, 1)
	,	@postWeight DEC(4, 1)
	,	@DialysisDuration	int
)
RETURNS DEC(3,2)
AS
BEGIN
	DECLARE	@KtV DEC(3, 2)
	SELECT	@KtV = - LOG(@postBUN / @preBUN - 0.008 * @DialysisDuration / 60) 
					+ (4 - (3.5 * @postBUN / @preBUN))
					* ((@preWeight - @postWeight) / @postWeight)
	RETURN	@KtV
END
GO

References: JSDT 29 (12): 1511-1516, 1996

Second Generation Logarithmic Estimates of Single-Pool Variable Volume

SQL Serverのスカラー値関数としてKt/Vを定義するには

 透析患者の透析効率を計算するには日本透析医学会の推奨する新里式があります.原著論文を見ると積分方程式を解く必要があり,一般的な数学の知識では歯が立ちません.ここでは Daugirdas による Kt/V をデータベース内で計算する方法を述べます.新里自身も Daugirdas による Kt/V と新里式による Kt/V とはかなり一致していると述べており,ほぼ代用可能ではないかと思われます.(1) が Daugirdas の方法で (2) と (3) を解くと新里式になります.

\displaystyle \mathrm{Kt/V} = - LN( R - 0.008 \times t ) + \left[ 4 - \left( 3.5 \times R \right) \right] \times\frac{\mathrm{UF}}{\mathrm{W}}\\  = - LN \left( \frac{\mathrm{postBUN}}{\mathrm{preBUN}} - 0.008 \times t \right) + \left[ 4 - \left( 3.5 \times \frac{\mathrm{postBUN}}{\mathrm{preBUN}} \right) \right] \times \frac{\mathrm{preWeight} - \mathrm{postWeight}}{\mathrm{postWeight}} \cdots(1)
\displaystyle \mathrm{Gw} = \mathrm{G}\cdot\mathrm{Tw} = \mathrm{Kd}\int_{0}^{Td}C_1dt + \mathrm{Kd}\int_{0}^{Td}C_2dt + \mathrm{Kd}\int_{0}^{Td}C_3dt \cdots(2)
\displaystyle \mathrm{Ce} = \mathrm{Cs} Exp\left( - \frac{\mathrm{Kt}}{\mathrm{V}} \right) + \frac{\mathrm{G}}{\mathrm{K}}\left[ 1 - Exp\left( - \frac{\mathrm{Kt}}{\mathrm{V}} \right) \right] \cdots(3)

 下記プロシージャを実行して関数を作成します.

CREATE FUNCTION Function_KtV 
(		@preBUN DEC(4, 1)
	,	@postBUN DEC(4, 1)
	,	@preWeight	DEC(4, 1)
	,	@postWeight DEC(4, 1)
	,	@DialysisDuration	int
)
RETURNS DEC(3,2)
AS
BEGIN
	DECLARE	@KtV DEC(3, 2)
	SELECT	@KtV = - LOG(@postBUN / @preBUN - 0.008 * @DialysisDuration / 60) 
					+ (4 - (3.5 * @postBUN / @preBUN))
					* ((@preWeight - @postWeight) / @postWeight)
	RETURN	@KtV
END
GO

参照:透析会誌 29 (12): 1511-1516, 1996

Second Generation Logarithmic Estimates of Single-Pool Variable Volume

How to create scalar function of SQL Server in order to calculate GNRI?

Malnutrition in elder people increases the risk of death. Geriatric Nutrition Risk Index (GNRI) is the tool to detect malnutrition easily in hemodialysis patients, too. The definition is as following;

\displaystyle \mathrm{GNRI} = 14.89\times\mathrm{Albumin (g/dL)} + 41.7\times\frac{\mathrm{Body Weight}}{\mathrm{Ideal Body Weight}}

where ideal body weight is given by multiplying 22 the square of height. But body weight should be replaced with ideal body weight if body weight is greater than ideal body weight. Then the second term is equal to 1.

Table is defined as following procedure. It is based on the survey list of the Japanese Society for Dialysis Therapy in 2013.

CREATE TABLE dbo.T_JSDT(
	ID nchar(8) NOT NULL,
	DATE_Survey date NOT NULL,
	Diabetes nchar(1) NOT NULL,
	Myocardial_Infarction nchar(1) NOT NULL,
	Cerebral_Hemorrhage nchar(1) NOT NULL,
	Cerebral_Infarction nchar(1) NOT NULL,
	Amputation nchar(1) NOT NULL,
	Femoral_Fracture nchar(1) NOT NULL,
	EPS nchar(1) NOT NULL,
	Hypertensive_Agents nchar(1) NOT NULL,
	Smoke nchar(1) NOT NULL,
	Therapy_Mode nchar(10) NOT NULL,
	Combination_PD nchar(1) NOT NULL,
	History_PD nchar(1) NOT NULL,
	Transplantation_COUNT nchar(1) NOT NULL,
	DIalysis_COUNT int NULL,
	Dialysis_Duration int NULL,
	QB int NULL,
	Height decimal(4, 1) NOT NULL,
	preWeight decimal(4, 1) NOT NULL,
	postWeight decimal(4, 1) NULL,
	preBUN decimal(4, 1) NOT NULL,
	postBUN decimal(4, 1) NULL,
	preCre decimal(5, 2) NOT NULL,
	postCre decimal(5, 2) NULL,
	Albumin decimal(3, 1) NOT NULL,
	CRP decimal(4, 2) NOT NULL,
	Ca decimal(3, 1) NOT NULL,
	IP decimal(3, 1) NOT NULL,
	Hemoglobin decimal(3, 1) NOT NULL,
	TIBC decimal(3, 0) NULL,
	Fe decimal(3, 0) NULL,
	Ferritin decimal(5, 1) NULL,
	TCHO decimal(3, 0) NOT NULL,
	HDLC decimal(3, 0) NOT NULL,
	PTH_mode nchar(1) NOT NULL,
	PTH decimal(4, 0) NULL,
	HbA1c decimal(3, 1) NULL,
	GA decimal(3, 1) NULL,
	SBP decimal(3, 0) NOT NULL,
	DBP decimal(3, 0) NOT NULL,
	HR decimal(3, 0) NOT NULL,
	KtV_JSDT decimal(3, 2) NULL,
	nPCR_JSDT decimal(3, 2) NULL,
	[%CRE] decimal(4, 1) NULL,
 CONSTRAINT PK_T_JSDT PRIMARY KEY (ID, DATE_Survey)

We need height, body weight and albumin in the table. Execute following procedure to create function.

CREATE FUNCTION Function_GNRI 
(@Albumin dec(3, 1), @Height dec(4, 1), @Weight dec(4, 1))
RETURNS DEC(5, 2)
AS
BEGIN
	DECLARE @GNRI DEC(5,2)
	SELECT @GNRI = 14.89 * @Albumin + 41.7 * CASE WHEN @Weight > ( 22 * POWER(@Height/100, 2)) THEN ( 22 * POWER(@Height/100, 2)) ELSE @Weight END / ( 22 * POWER(@Height/100, 2))
	RETURN @GNRI
END

Execute following query to calculate GNRI.

WITH	CTE	AS
(SELECT	J.ID		AS ID
	,	J.DATE_Survey	AS DATE_Survey
	,	J.Albumin	AS ALB
	,	J.Height	AS Height
	,	CASE WHEN J.postWeight IS NULL THEN J.preWeight ELSE J.postWeight END	AS Weight
   FROM	dbo.T_JSDT AS J
),	CTE_GNRI AS
(SELECT	CTE.ID		AS ID
	,	CTE.DATE_Survey	AS DATE_Survey
	,	dbo.Function_GNRI(CTE.ALB, CTE.Height, CTE.Weight)	AS GNRI
FROM	CTE)
SELECT	*	FROM	CTE_GNRI;

Reference: Simplified nutritional screening tools for patients on maintenance hemodialysis

SQL Serverのスカラー値関数としてGNRIを定義するには

 高齢者における低栄養は死亡のリスクを高めることが知られています.Geriatric Nutrition Risk Index (GNRI) は低栄養を簡易に検出できるツールです.単に高齢者だけではなく,維持透析患者においても有用です.その定義は以下です.

\displaystyle \mathrm{GNRI} = 14.89\times\mathrm{Albumin (g/dL)} + 41.7\times\frac{\mathrm{Body Weight}}{\mathrm{Ideal Body Weight}}

ここで理想体重 (kg) は身長 (m) の二乗に 22 を乗じて得られます.ただし実体重が理想体重を上回る場合には,実体重を理想体重に置き換えます.つまり第 2 項の分数は 1 に等しくなります.

 データベースのテーブル定義は以下の通りであるとします.日本透析医学会統計調査票の 2013 年版に基いています.

CREATE TABLE dbo.T_JSDT(
	ID nchar(8) NOT NULL,
	DATE_Survey date NOT NULL,
	Diabetes nchar(1) NOT NULL,
	Myocardial_Infarction nchar(1) NOT NULL,
	Cerebral_Hemorrhage nchar(1) NOT NULL,
	Cerebral_Infarction nchar(1) NOT NULL,
	Amputation nchar(1) NOT NULL,
	Femoral_Fracture nchar(1) NOT NULL,
	EPS nchar(1) NOT NULL,
	Hypertensive_Agents nchar(1) NOT NULL,
	Smoke nchar(1) NOT NULL,
	Therapy_Mode nchar(10) NOT NULL,
	Combination_PD nchar(1) NOT NULL,
	History_PD nchar(1) NOT NULL,
	Transplantation_COUNT nchar(1) NOT NULL,
	DIalysis_COUNT int NULL,
	Dialysis_Duration int NULL,
	QB int NULL,
	Height decimal(4, 1) NOT NULL,
	preWeight decimal(4, 1) NOT NULL,
	postWeight decimal(4, 1) NULL,
	preBUN decimal(4, 1) NOT NULL,
	postBUN decimal(4, 1) NULL,
	preCre decimal(5, 2) NOT NULL,
	postCre decimal(5, 2) NULL,
	Albumin decimal(3, 1) NOT NULL,
	CRP decimal(4, 2) NOT NULL,
	Ca decimal(3, 1) NOT NULL,
	IP decimal(3, 1) NOT NULL,
	Hemoglobin decimal(3, 1) NOT NULL,
	TIBC decimal(3, 0) NULL,
	Fe decimal(3, 0) NULL,
	Ferritin decimal(5, 1) NULL,
	TCHO decimal(3, 0) NOT NULL,
	HDLC decimal(3, 0) NOT NULL,
	PTH_mode nchar(1) NOT NULL,
	PTH decimal(4, 0) NULL,
	HbA1c decimal(3, 1) NULL,
	GA decimal(3, 1) NULL,
	SBP decimal(3, 0) NOT NULL,
	DBP decimal(3, 0) NOT NULL,
	HR decimal(3, 0) NOT NULL,
	KtV_JSDT decimal(3, 2) NULL,
	nPCR_JSDT decimal(3, 2) NULL,
	[%CRE] decimal(4, 1) NULL,
 CONSTRAINT PK_T_JSDT PRIMARY KEY (ID, DATE_Survey)

 上記テーブルの中で必要な項目は身長,体重,アルブミン値です.下記プロシージャを実行して関数を作成します.実体重が理想体重を上回る場合には実体重を理想体重に置き換えるという条件は CASE 式の中で評価します.

CREATE FUNCTION Function_GNRI 
(@Albumin dec(3, 1), @Height dec(4, 1), @Weight dec(4, 1))
RETURNS DEC(5, 2)
AS
BEGIN
	DECLARE @GNRI DEC(5,2)
	SELECT @GNRI = 14.89 * @Albumin + 41.7 * CASE WHEN @Weight > ( 22 * POWER(@Height/100, 2)) THEN ( 22 * POWER(@Height/100, 2)) ELSE @Weight END / ( 22 * POWER(@Height/100, 2))
	RETURN @GNRI
END

 下記のクエリを実行して GNRI を求めます.CASE 式の中身は透析後体重が空欄の場合は透析前体重で代用するという意味です.

WITH	CTE	AS
(SELECT	J.ID		AS ID
	,	J.DATE_Survey	AS DATE_Survey
	,	J.Albumin	AS ALB
	,	J.Height	AS Height
	,	CASE WHEN J.postWeight IS NULL THEN J.preWeight ELSE J.postWeight END	AS Weight
   FROM	dbo.T_JSDT AS J
),	CTE_GNRI AS
(SELECT	CTE.ID		AS ID
	,	CTE.DATE_Survey	AS DATE_Survey
	,	dbo.Function_GNRI(CTE.ALB, CTE.Height, CTE.Weight)	AS GNRI
FROM	CTE)
SELECT	*	FROM	CTE_GNRI;

参照:維持透析患者のための簡易栄養スクリーニングツール

How to BULK INSERT from text file to the table, which primary key is IDENTITY-constrained column?

I’d like to describe how to BULK INSERT from text file to the table with IDENTITY-constrained primary key. You should insert a column into worksheet for primary key such as ‘AUTO_ID’.

TEXTfile

Transact-SQL code is following list. [Table] is table name, FullPath is complete file-path, and FileName is name of text file, respectively. WITH option phrase represents the first row is title.

BULK INSERT dbo.[Table] FROM 'FullPath\FileName.txt' WITH (FIRSTROW = 2);

主キーにIDENTITYの指定のあるテーブルにテキストファイルからBULK INSERTする

 SQL Server で作成したデータベースのテーブルにテキストファイルを BULK INSERT する際の備忘録です.主キーとしたい列に IDENTITY を指定すると初期値 1, 増分 1 の整数を主キーとするテーブルになります.列の IDENTITY 指定をしておかないとエラーとなります.

 用意するテキストファイルにも工夫が必要です.主キーの列名を AUTO_ID であると仮定すると,テキストファイルの 1 列目を空白にしておかなくてはなりません.

TEXTfile

 BULK INSERT する際の Transact-SQL コードは以下のようになります.[Table] にはテーブル名,FullPath には完全なファイルパス,FileName にはテキストファイル名を指定します.WITH 句のオプションは 1 行目がタイトル行であることを示しています.

BULK INSERT dbo.[Table] FROM 'FullPath\FileName.txt' WITH (FIRSTROW = 2);

How to fix the text file of the food composition of the diet recipes to the first normal form?

In this article, I’d like to describe how to fix the text file of the food composition of the diet recipes, which a facility have provided for one year, to the first normal form in order to insert into database.

Option Explicit

Sub LoopProcedure()
    Dim Sh  As Worksheet
    For Each Sh In Worksheets
        If Sh.Name Like "Sheet" & "*" Then
            Call TransportFromTxtToCSV(Sh)
        End If
    Next Sh
End Sub

Sub TransportFromTxtToCSV()
    Dim mySht               As Worksheet
    Dim myRng               As Range
    Dim myAr                As Variant
    Dim RecAr()             As String
    Dim i                   As Long
    Dim j                   As Long
    Dim k                   As Long
    Dim Date_Serving_Meal   As Date
    Dim Menu_Name           As String
    Dim tmpStart            As Long
    Dim Meal_Time           As String
    Dim Dish                As String
    Dim RecordNumber        As Long
    
    set mysht = sh
    RecordNumber = Count_Record(mySht)
    ReDim RecAr(RecordNumber - 1, 51)
    Set myRng = mySht.UsedRange
    myAr = myRng
    k = 0
    Date_Serving_Meal = "2011/1/1"
    Menu_Name = myAr(1, 11) & myAr(1, 12) & myAr(1, 13)
    tmpStart = InStr(Menu_Name, ")")
    Menu_Name = Mid(Menu_Name, tmpStart + 1)
    Meal_Time = "朝食"
    For i = LBound(myAr) To UBound(myAr)
        
        Select Case True
            Case myAr(i, 2) = "合      計"
                Date_Serving_Meal = DateAdd("d", 1, Date_Serving_Meal)
            Case myAr(i, 2) = "《朝食》"
                Meal_Time = "朝食"
            Case myAr(i, 2) = "《昼食》"
                Meal_Time = "昼食"
            Case myAr(i, 2) = "《夕食》"
                Meal_Time = "夕食"
            Case myAr(i, 2) = "小      計"
            Case myAr(i, 2) = "^e12【献立"
            Case myAr(i, 2) Like "動蛋比" & "*"
            Case myAr(i, 2) = "・・・・・・・・・・"
            Case myAr(i, 2) = "料理名"
            Case myAr(i, 2) = ""
            Case Else
                Dish = myAr(i, 2)
        End Select
        
        Select Case True
            Case myAr(i, 3) = "・・・・・・・・・・・"
            Case myAr(i, 3) Like "EN比" & "*"
            Case myAr(i, 3) = "食品名"
            Case myAr(i, 3) Like "一覧表】 ^e11" & "*"
            Case myAr(i, 3) = ""
            Case Else
                RecAr(k, 0) = Date_Serving_Meal
                RecAr(k, 1) = Menu_Name
                RecAr(k, 2) = Meal_Time
                RecAr(k, 3) = Dish
                RecAr(k, 4) = myAr(i, 3)
                For j = 5 To 22
                    RecAr(k, j) = myAr(i, j - 1)
                Next j
                For j = 23 To 39
                    RecAr(k, j) = myAr(i + 1, j - 18)
                Next j
                For j = 40 To 51
                    RecAr(k, j) = myAr(i + 2, j - 35)
                Next j
                k = k + 1
        End Select
    Next i
    Set mySht = Worksheets.Add
    With mySht
        .Name = Menu_Name
        .Range(Cells(1, 1), Cells(RecordNumber, 52)) = RecAr
    End With
    
    Set mySht = Nothing
    Set myRng = Nothing
    Erase RecAr
End Sub

Function Count_Record(ByRef Sh As Worksheet) As Long    
    Dim mySht       As Worksheet
    Dim myAr        As Variant
    Dim i           As Long
    Dim j           As Long
    Dim k           As Long
    Set mySht = Sh
    myAr = mySht.UsedRange
    j = 0
    k = 0
    For i = LBound(myAr) To UBound(myAr)
        Select Case True
            Case myAr(i, 2) = "合      計"
            Case myAr(i, 2) = "小      計"
            Case myAr(i, 2) = "《朝食》"
            Case myAr(i, 2) = "《昼食》"
            Case myAr(i, 2) = "《夕食》"
            Case myAr(i, 2) = "^e12【献立"
            Case myAr(i, 2) Like "動蛋比" & "*"
            Case myAr(i, 2) = "・・・・・・・・・・"
            Case myAr(i, 2) = "料理名"
            Case myAr(i, 2) = ""
            Case Else
                j = j + 1
        End Select
        Select Case True
            Case myAr(i, 3) = "・・・・・・・・・・・"
            Case myAr(i, 3) Like "EN比" & "*"
            Case myAr(i, 3) = "食品名"
            Case myAr(i, 3) Like "一覧表】 ^e11" & "*"
            Case myAr(i, 3) = ""
            Case Else
                k = k + 1
        End Select
    Next i
    Count_Record = k
End Function

提供食レシピの食品成分のテキストファイルを第1正規形にするEXCEL VBAコード

 ある施設の1年間で提供した食事のレシピの食品成分のテキストファイルから,データベースに取り込むための前処置としてデータを第1正規形に整形する EXCEL VBA コードです.個人的な備忘録です.

 94行目以降の関数 Count_Record は,アクティブシートをループして必要なレコード数を計測する関数です.104行目以降で料理名の数(B列),119行目以降で食品名の数(C列)に注目しています.サブルーチン TransportFromTxtToCSV から呼び出して,動的配列の要素数を後で決定するのに用います.

Option Explicit

Sub LoopProcedure()
    Dim Sh  As Worksheet
    For Each Sh In Worksheets
        If Sh.Name Like "Sheet" & "*" Then
            Call TransportFromTxtToCSV(Sh)
        End If
    Next Sh
End Sub

Sub TransportFromTxtToCSV()
    Dim mySht               As Worksheet
    Dim myRng               As Range
    Dim myAr                As Variant
    Dim RecAr()             As String
    Dim i                   As Long
    Dim j                   As Long
    Dim k                   As Long
    Dim Date_Serving_Meal   As Date
    Dim Menu_Name           As String
    Dim tmpStart            As Long
    Dim Meal_Time           As String
    Dim Dish                As String
    Dim RecordNumber        As Long
    
    set mysht = sh
    RecordNumber = Count_Record(mySht)
    ReDim RecAr(RecordNumber - 1, 51)
    Set myRng = mySht.UsedRange
    myAr = myRng
    k = 0
    Date_Serving_Meal = "2011/1/1"
    Menu_Name = myAr(1, 11) & myAr(1, 12) & myAr(1, 13)
    tmpStart = InStr(Menu_Name, ")")
    Menu_Name = Mid(Menu_Name, tmpStart + 1)
    Meal_Time = "朝食"
    For i = LBound(myAr) To UBound(myAr)
        
        Select Case True
            Case myAr(i, 2) = "合      計"
                Date_Serving_Meal = DateAdd("d", 1, Date_Serving_Meal)
            Case myAr(i, 2) = "《朝食》"
                Meal_Time = "朝食"
            Case myAr(i, 2) = "《昼食》"
                Meal_Time = "昼食"
            Case myAr(i, 2) = "《夕食》"
                Meal_Time = "夕食"
            Case myAr(i, 2) = "小      計"
            Case myAr(i, 2) = "^e12【献立"
            Case myAr(i, 2) Like "動蛋比" & "*"
            Case myAr(i, 2) = "・・・・・・・・・・"
            Case myAr(i, 2) = "料理名"
            Case myAr(i, 2) = ""
            Case Else
                Dish = myAr(i, 2)
        End Select
        
        Select Case True
            Case myAr(i, 3) = "・・・・・・・・・・・"
            Case myAr(i, 3) Like "EN比" & "*"
            Case myAr(i, 3) = "食品名"
            Case myAr(i, 3) Like "一覧表】 ^e11" & "*"
            Case myAr(i, 3) = ""
            Case Else
                RecAr(k, 0) = Date_Serving_Meal
                RecAr(k, 1) = Menu_Name
                RecAr(k, 2) = Meal_Time
                RecAr(k, 3) = Dish
                RecAr(k, 4) = myAr(i, 3)
                For j = 5 To 22
                    RecAr(k, j) = myAr(i, j - 1)
                Next j
                For j = 23 To 39
                    RecAr(k, j) = myAr(i + 1, j - 18)
                Next j
                For j = 40 To 51
                    RecAr(k, j) = myAr(i + 2, j - 35)
                Next j
                k = k + 1
        End Select
    Next i
    Set mySht = Worksheets.Add
    With mySht
        .Name = Menu_Name
        .Range(Cells(1, 1), Cells(RecordNumber, 52)) = RecAr
    End With
    
    Set mySht = Nothing
    Set myRng = Nothing
    Erase RecAr
End Sub

Function Count_Record(ByRef Sh As Worksheet) As Long    
    Dim mySht       As Worksheet
    Dim myAr        As Variant
    Dim i           As Long
    Dim j           As Long
    Dim k           As Long
    Set mySht = Sh
    myAr = mySht.UsedRange
    j = 0
    k = 0
    For i = LBound(myAr) To UBound(myAr)
        Select Case True
            Case myAr(i, 2) = "合      計"
            Case myAr(i, 2) = "小      計"
            Case myAr(i, 2) = "《朝食》"
            Case myAr(i, 2) = "《昼食》"
            Case myAr(i, 2) = "《夕食》"
            Case myAr(i, 2) = "^e12【献立"
            Case myAr(i, 2) Like "動蛋比" & "*"
            Case myAr(i, 2) = "・・・・・・・・・・"
            Case myAr(i, 2) = "料理名"
            Case myAr(i, 2) = ""
            Case Else
                j = j + 1
        End Select
        Select Case True
            Case myAr(i, 3) = "・・・・・・・・・・・"
            Case myAr(i, 3) Like "EN比" & "*"
            Case myAr(i, 3) = "食品名"
            Case myAr(i, 3) Like "一覧表】 ^e11" & "*"
            Case myAr(i, 3) = ""
            Case Else
                k = k + 1
        End Select
    Next i
    Count_Record = k
End Function

ASCII character code list (0-127)

I’d like to describe ASCII character code. Please refer to this article when you configure string pattern of regular expression. The code number 8, 9, 10 and 13 are control character, which are used as back space, tab, line feed and carriage return, respectively.

Character class of alphanumeric character is expressed as [a-zA-Z0-9] in regular expression, single-byte symbol class is expressed as [!-/:-@[-`{-~]. In character class, ] and \ character need to escape.

Code Character Meta character
0  
1  
2  
3  
4  
5  
6  
7  
8 *1
9 *2
10 *3
11  
12  
13 *4
14  
15  
16  
17  
18  
19  
20  
21  
22  
23  
24  
25  
26  
27  
28  
29  
30
31  
32 [space] 
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  

Reference:
ASCII (Wikipedia)

ASCII文字コード(0-127)一覧表

 ASCII 文字セットのコードと文字を示します.正規表現の文字列パターンを設定する際に参考にして下さい.コード番号 8, 9, 10, 13 はそれぞれ制御文字でバックスペース,タブ,ラインフィード,キャリッジリターンとして用いられ,画面には表示されません.コード番号 128 から 255 まではラテン文字中心の文字コードです.

 正規表現での半角英数字の文字クラスは [a-zA-Z0-9] のパターンで表現しますが,半角記号だとやや長くなり,[!-/:-@[-`{-~] となります.文字クラス内でエスケープが必要な文字は ] と \ だけです.

Code Character Meta character
0    
1    
2    
3    
4    
5    
6    
7    
8 *1  
9 *2  
10 *3  
11    
12    
13 *4  
14    
15    
16    
17    
18    
19    
20    
21    
22    
23    
24    
25    
26    
27    
28    
29    
30
 
31    
32 [space]   
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    

参照:ASCII文字コード

How to validate empty value in controls on user form of Excel VBA?

In this article, I’d like to describe how to validate empty value in controls on UserForm of Excel VBA. It’s list of controls that you can select and enter on form.

  • CheckBox
  • OptionButton
  • ComboBox
  • ListBox
  • TextBox

It’s assumed that OptionButtons and CheckBoxes are placed in Frame and they aren’t placed within one Frame together.

UserForm1

It is needed to designe as public function that validates empty value.

The function that validates empty value of controls on forms is called from multiple CommandButtons. Therefore, it’s reasonable to design as public function. It’s needed to add module for implementing the function. Data type of return value is Variant as following code because it returns name list of empty controls as string. It may be Boolean if you don’t have to present message box.

Option Explicit

Function CheckControls(myForm As MSForms.UserForm) As Variant
    Dim Ctrl        As MSForms.Control
    Dim CheckCnt    As Long
    Dim myCnt       As Long
    Dim CheckStr    As String
    CheckControls = False
    CheckCnt = 0
    myCnt = 0
    CheckStr = ""
    For Each Ctrl In myForm.Controls
        Select Case TypeName(Ctrl)
            Case "ComboBox"
                If Ctrl.ListIndex <> -1 Then
                    myCnt = myCnt + 1
                Else
                    CheckStr = CheckStr & Ctrl.Name & vbCrLf
                End If
            Case "Frame"
                If CheckFrame(Ctrl) Then
                    myCnt = myCnt + 1
                Else
                    CheckStr = CheckStr & Ctrl.Name & vbCrLf
                End If
            Case "ListBox"
                If Ctrl.ListIndex <> -1 Then
                    myCnt = myCnt + 1
                Else
                    CheckStr = CheckStr & Ctrl.Name & vbCrLf
                End If
            Case "TextBox"
                If Ctrl.Text <> "" Then
                    myCnt = myCnt + 1
                Else
                    CheckStr = CheckStr & Ctrl.Name & vbCrLf
                End If
            Case Else
                CheckCnt = CheckCnt - 1
        End Select
        CheckCnt = CheckCnt + 1
    Next Ctrl
    If CheckCnt = myCnt Then
        CheckControls = True
    Else
        CheckControls = CheckStr
    End If
End Function

Function CheckFrame(myFrame As MSForms.Frame) As Boolean
    Dim FrmCnt  As Long
    Dim ChkCnt  As Long
    Dim OptCnt  As Long
    Dim tmpCtrl As Control
    CheckFrame = False
    FrmCnt = 0
    ChkCnt = 0
    OptCnt = 0
    For Each tmpCtrl In myFrame.Controls
        Select Case TypeName(tmpCtrl)
            Case "CheckBox"
                If tmpCtrl.Value Then
                    ChkCnt = ChkCnt + 1
                End If
            Case "OptionButton"
                If tmpCtrl.Value Then
                    OptCnt = OptCnt + 1
                End If
        End Select
    Next tmpCtrl
    FrmCnt = FrmCnt + 1
    If FrmCnt = OptCnt Or FrmCnt <= ChkCnt Then
        CheckFrame = True
    End If
End Function

The code that calls the function

You would write the code on click event as below. You could write the procedure between "If ... Then" and "Else" statements that is activated when it has passed verification.

Option Explicit

Private Sub CommandButton1_Click()
    If TypeName(CheckControls(Me)) = "Boolean" Then
        
    Else
        MsgBox Prompt:=CheckControls(Me) & "Missing value above.", Title:="Empty values!"
        Exit Sub
    End If
End Sub

CheckBox could take NULL

To tell the truth, CheckBox has interesting property. Although OptionButton tekes only TRUE or FALSE, CheckBox takes three-valued logic with NULL. It's very difficult problem for database designers because NULL brings them unexpected results in query. Three-valued logic is the greatest weakness of relational model. TripleState property, its default value is FALSE, could select whether three-valued logic would be allowed or not.

It's assumed that one or more controls are checked in Frame. If the procedure validates naked CheckBox without Frame, what happens? Whatever the value of CheckBox is, it passes verification. You shouldn't use three-valued logic to select TURE or FALSE only.

Three-valued logic and set of choices

Furthermore, to allow multiple choices means that users would be allowed to not select any options more. Relation of 1:0 and 1:n are allowed, respectively. Fortunately, the situation could be avoided with careful design of choices. It's needed to be carefully treated of "Other case" that couldn't be treated as number. It's impossible to isolate and salvage data that is classified as "Others".

References:
Userform of Excel VBA as user interface
Three-valued logic (Wikipedia)

EXCEL VBA でユーザーフォーム上のコントロールの未入力を検証する

 今回はフォーム上のコントロールの形式的検証のうち,未入力のコントロールをチェックするコードを紹介します.フォーム上に入力・選択可能なコントロールとしては以下が挙げられます.

  • チェックボックス
  • オプションボタン
  • コンボボックス
  • リストボックス
  • テキストボックス

 オプションボタンおよびチェックボックスはフレーム内に配置してあるものとし,一つのフレーム内にオプションボタンとチェックボックスは混在していないと仮定しています.

UserForm1

未入力のコントロールを検証する関数を共通化する

 フォーム上のコントロールの未入力のチェックは複数のコマンドボタンから共通して呼び出される機能であるため,共通化したほうがコーディングの重複をなくせます.そのため標準モジュールを追加して関数として実装することにします.下記の関数で戻り値を Variant 型にしているのは未入力のコントロール名を文字列型で受けているためです.メッセージボックスを表示する必要がなければ戻り値を Boolean 型にした方がよいでしょう.また Select case 節でコントロールの種類を群別しているため case else 節でコントロール数を減算する処理を追加していますが,If … Then … ElseIf … 節で群別すればその記述は不要になります.

Option Explicit

Function CheckControls(myForm As MSForms.UserForm) As Variant
    Dim Ctrl        As MSForms.Control
    Dim CheckCnt    As Long
    Dim myCnt       As Long
    Dim CheckStr    As String
    CheckControls = False
    CheckCnt = 0
    myCnt = 0
    CheckStr = ""
    For Each Ctrl In myForm.Controls
        Select Case TypeName(Ctrl)
            Case "ComboBox"
                If Ctrl.ListIndex <> -1 Then
                    myCnt = myCnt + 1
                Else
                    CheckStr = CheckStr & Ctrl.Name & vbCrLf
                End If
            Case "Frame"
                If CheckFrame(Ctrl) Then
                    myCnt = myCnt + 1
                Else
                    CheckStr = CheckStr & Ctrl.Name & vbCrLf
                End If
            Case "ListBox"
                If Ctrl.ListIndex <> -1 Then
                    myCnt = myCnt + 1
                Else
                    CheckStr = CheckStr & Ctrl.Name & vbCrLf
                End If
            Case "TextBox"
                If Ctrl.Text <> "" Then
                    myCnt = myCnt + 1
                Else
                    CheckStr = CheckStr & Ctrl.Name & vbCrLf
                End If
            Case Else
                CheckCnt = CheckCnt - 1
        End Select
        CheckCnt = CheckCnt + 1
    Next Ctrl
    If CheckCnt = myCnt Then
        CheckControls = True
    Else
        CheckControls = CheckStr
    End If
End Function

Function CheckFrame(myFrame As MSForms.Frame) As Boolean
    Dim FrmCnt  As Long
    Dim ChkCnt  As Long
    Dim OptCnt  As Long
    Dim tmpCtrl As Control
    CheckFrame = False
    FrmCnt = 0
    ChkCnt = 0
    OptCnt = 0
    For Each tmpCtrl In myFrame.Controls
        Select Case TypeName(tmpCtrl)
            Case "CheckBox"
                If tmpCtrl.Value Then
                    ChkCnt = ChkCnt + 1
                End If
            Case "OptionButton"
                If tmpCtrl.Value Then
                    OptCnt = OptCnt + 1
                End If
        End Select
    Next tmpCtrl
    FrmCnt = FrmCnt + 1
    If FrmCnt = OptCnt Or FrmCnt <= ChkCnt Then
        CheckFrame = True
    End If
End Function

コマンドボタンに関数を呼び出すコードを記述する

 フォーム上のコマンドボタンには下記のコードを記述します.If ... Then 節には入力値の検証に合格した際の動作を記述します.

Option Explicit

Private Sub CommandButton1_Click()
    If TypeName(CheckControls(Me)) = "Boolean" Then
        
    Else
        MsgBox Prompt:=CheckControls(Me) & "上記項目が未入力です", Title:="未入力エラー"
        Exit Sub
    End If
End Sub

チェックボックスは NULL を取りうる

 さて,実はチェックボックスには面白い性質があります.オプションボタンでは TRUE と FALSE の 2 値しか取りませんが,チェックボックスの場合は更に NULL を加えた 3 値の真理値を取ることができます.しかしデータベース設計者にとって NULL は極めて扱いの難しい真理値であり,クエリが予想外の結果を返すことがあるためなるべく NULL を許容すべきではありません.3 値論理は関係モデルにとって急所なのです.TripleState プロパティで 3 値論理を認めるか否か変更できます.既定値は FALSE となっています.一方でオプションボタンにはそのようなプロパティは存在しません.

 上記の例文ではフレーム内のチェックボックスやリストボックスの選択肢から最低でも 1 つ選択されている,という前提でチェックをかけています.では,フレーム内ではなくフォーム上にチェックボックスが存在する場合はどうでしょうか.この場合,チェックボックスの値にかかわらず検証はパスします.ですが TRUE か FALSE かを必ず選択させるために 3 値論理を導入するのは行き過ぎかと思います.

3 値論理と選択肢の設定

 さらにチェックボックス,リストボックスいずれにも言えることですが,複数選択を許可するということは,どの選択肢も選択しないことをも許容することを意味します.つまり関係モデル上 1:n のリレーションのうち n = 0 も成り立つということです.とは言え,選択肢を注意深く設定することである程度は回避可能です.例えば年齢を 10 歳区分で区切る場合に両端の年代をどのように扱うかや,年収を 100 万円単位で区切る場合に最小群と最大群をどう設定するかなどです.数値で扱えない選択項目の場合,「その他」をどこまで許容するか,慎重に設計しなければなりません.一旦「その他」に放り込まれたデータを後から切り分けて取り出すことは不可能です.必要な項目を網羅した上でこれ以上はどう考えても不要である場合にのみ「その他」とする,つまり漏れがないように選択肢を設定すべきです.

参照:
インターフェースとしてのEXCEL VBAによるユーザーフォーム
Three-valued logic (Wikipedia)

How to execute formal validation of TextBox on user form with EXCEL VBA?

In this article, I’d like to describe how to execute formal validation of TextBox on user form. Data type of TextBox is datetime, numeric and such string as zip code, phone number or mail address, etc. Until Excel 2003, you could divert calendar control of Access®, but after 2007, you couldn’t. You might have to type keyboard, design custom calendar or use Add-In calendar.

validation

As shown in figure above, set TextBoxes on user form, from TextBox1 to TextBox5. And set CommandButton.

Formal validation

Validation means formal validation and semantic validation. In this article, I’d like to describe about formal validation. Formal validation has verification of data type and not entered controls. You could verify not entered controls at last, it might be executed in a batch, when CommandButton for registration was clicked. However, because data type of each controls are different from each other, it might be appropriate to verify data type each time when input is entered.

Semantic validation

Although I would not describe about semantic validation in this article, it’s needed not only verifying input of one control, but also comparing input of multiple controls each other and compareing input with record in database. In the situation, it’s needed to execute validation when registration button was clicked, not to hook individual events of each controls.

Event type

Then, which event should you use? When user have entered incorrect input, you would not confirm input and would not move focus to the next control. Therefore it’s appropriate to use event with cancel. Typical event of TextBox is bellow.

  • BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
  • Change()
  • Exit(ByVal Cancel As MSForms.ReturnBoolean)
  • KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  • KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  • KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

Events with cancel process are BeforeUpdate and Exit. With such other events as Change, you would have to design custom code to stop process. BeforeUpdate event is usually used.

Option Explicit

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

End Sub

Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

End Sub

Private Sub TextBox3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

End Sub

Private Sub TextBox4_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

End Sub

Private Sub TextBox5_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

End Sub

Specified criteria for validation

It’s important that BeforeUpdate event executes cancel process when criteria you described in event procedure has been satisfied. It’s needed to deny criteria expression with Not that allows to input. When criteria has been satisfied, cancel process would be executed.

Validation of data type

Data type of each TextBoxes are datetime, numeric and string. String has mobile phone number, mail address and URL, respectively. After cancel process, following code set focus and select whole input string. In TextBox1, following code verifies whether data type of input is datetime, in textBox2, the code verifies whether data type of input is numeric, respectively. In textBox2, non-negative number would be accepted.

Validation with regular expression

After TextBox3, following code doesn’t verify data type. It’s not needed to verify data type when you deal input as string. Therefore, you would have to verify string itself. In TextBox3, it is allowed to input eleven digit number. In TextBox4, it’s allowed to input string by matching with regular expression as shown in 44 line. It’s so difficult to match e-mail address that following code is incomplete. In TextBox5, it matches with URL. As well as e-mail, it’s incomplete.

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    With TextBox1
        If Not IsDate(.Text) Then
            Cancel = True
            .SetFocus
            .SelStart = 0
            .SelLength = Len(.Text)
        End If
    End With
End Sub

Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    With TextBox2
        If Not (IsNumeric(.Text) And .Text >= 0) Then
            Cancel = True
            .SetFocus
            .SelStart = 0
            .SelLength = Len(.Text)
        End If
    End With
End Sub

Private Sub TextBox3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim myReg       As Object
    Set myReg = CreateObject("VBScript.RegExp")
    With myReg
        .Pattern = "^[0-9]{11}$"
        .IgnoreCase = True
        .Global = True
    End With
    With TextBox3
        If Not myReg.Test(.Text) Then
            Cancel = True
            .SetFocus
            .SelStart = 0
            .SelLength = Len(.Text)
        End If
    End With
End Sub

Private Sub TextBox4_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim myReg       As Object
    Dim Pattern     As String
    Pattern = "^[-a-z0-9]+(\.[-a-z0-9]+)*\@[-a-z0-9]+(\.[-a-z0-9]+)*\.[a-z0-9]{2,6}$"
    Set myReg = CreateObject("VBScript.RegExp")
    With myReg
        .Pattern = Pattern
        .IgnoreCase = True
        .Global = True
    End With
    With TextBox4
        If Not myReg.Test(.Text) Then
            Cancel = True
            .SetFocus
            .SelStart = 0
            .SelLength = Len(.Text)
        End If
    End With
End Sub

Private Sub TextBox5_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim myReg       As Object
    Dim Pattern     As String
    Set myReg = CreateObject("VBScript.RegExp")
    Pattern = "^https?://[a-z0-9][-a-z0-9]{0,62}(\.[a-z0-9][-a-z0-9]{0,62})*\.[a-z0-9][a-z0-9]{0,62}$"
    With myReg
        .Pattern = Pattern
        .IgnoreCase = True
        .Global = True
    End With
    With TextBox5
        If Not myReg.Test(.Text) Then
            Cancel = True
            .SetFocus
            .SelStart = 0
            .SelLength = Len(.Text)
        End If
    End With
End Sub

Refferences:
DOD INTERNET HOST TABLE SPECIFICATION
Requirements for Internet Hosts — Application and Support
Hostname (Wikipedia)
Userform of Excel VBA as user interface

Excel VBAでユーザーフォームのテキストボックスの値の形式的検証を行う

 ユーザーフォーム上のテキストボックスの値を検証します.テキストボックスに入力する値のデータ型としては日付型,数値型,郵便番号や電話番号・メールアドレスなどの文字列型が主体です.日付型に関しては Excel 2003 までは Access® のカレンダーコントロールを流用できたのですが,最近のバージョンではそれもできなくなりました.キーボードからタイプするか,カレンダーを自作するか,関数アドインを利用するしかありません.

validation

 上図のようにフォーム上にテキストボックスを配置します.それぞれ TextBox1, TextBox2, TextBox3, TextBox4, TextBox5 としましょう.またコマンドボタンを配置し,Caption を OK に変更します.

形式的検証

 検証には形式的検証と意味的検証とがあります.まず形式的検証についてです.未入力のチェックとデータ型のチェックが主体となります.両者のチェックのタイミングは別の方がよいでしょう.タイミングが異なるため,異なるイベントにすべきです.未入力のチェックは値が空白であるか否かだけを確認すれば良いため,一括して検証可能でしょう.となると個々のコントロールのイベントで都度チェックするのではなく,最後に登録するコマンドボタンが押された時点でよいということになります.逆にデータ型はコントロールごとに異なります.ですので個々のテキストボックスに入力が発生した時点で都度検証するのがよいでしょう.

意味的検証

 意味的検証についてここでは詳述しませんが,単独のコントロールの入力値を検証するだけでなく,複数のコントロールの入力値同士を比較して検証する必要があったり,データベースに既に登録されたレコードの値と比較したりといった検証が必要になる場合もあります.そのような場合には個々のコントロールのイベントをフックするのではなく,最後に登録するボタンが押された時点で検証するのが妥当だと思います.そういったチェックは Access® の場合ですとテーブルに対するイベントとして登録する必要があります.

イベントの種類

 話をイベントに戻します.では次にどのイベントを用いるべきでしょうか.意図しない入力が発生した場合は入力を確定させず,次のコントロールにフォーカスを移動させたくないのですから,Cancel 処理が入っているイベントが適切です.テキストボックスのイベントで代表的なものは下記のとおりです.

  • BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
  • Change()
  • Exit(ByVal Cancel As MSForms.ReturnBoolean)
  • KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  • KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  • KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

 Cancel 処理を持つのは BeforeUpdate と Exit です.Change イベントを始め他のイベントでは処理を止めるために自前でコーディングする必要があるでしょう.BeforeUpdate と Exit とでいずれが適切かは個々の要件によりますが,通常は BeforeUpdate でよいでしょう.それぞれのコントロールで BeforeUpdate イベントを選択した状態は下記のようになります.

Option Explicit

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

End Sub

Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

End Sub

Private Sub TextBox3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

End Sub

Private Sub TextBox4_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

End Sub

Private Sub TextBox5_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

End Sub

検証の条件指定

 次にデータ型をそれぞれ検証します.ここで強調したいのは,キャンセルする際の条件を満たした場合にキャンセル処理を行うコードを記述することです.その条件には,逆に入力を許可する条件式を指定しておき,最後に Not で条件式全体を否定するというちょっと複雑な処理が必要です.If … Then … ElseIf … と条件分岐を連ねていく方法もありますが,その都度キャンセル処理を書き連ねていく必要があります.ここは好みの問題かもしれませんが,ド・モルガンの法則からすると,And で条件を絞り込み,最後に Not で式全体を否定するのがスマートかと思います.そして Cancel = True としてキャンセル処理を実行します.では逆に入力を満たす条件をそのまま記述し,Cancel = False とすればどうなるでしょうか.これは宿題にしておきましょう.

データ型の検証

 さて,話を戻します.各テキストボックスのデータ型は順に日付型,数値型,電話番号・メールアドレスおよび URL の文字列型です.TextBox1 から TextBox5 までいずれもキャンセル処理後フォーカスを残し,かつ入力した値全体を選択して即座にタイプできるようにしています.TextBox2 では非負の数値のみを受け付けます.

正規表現による文字列の検証

 TextBox3 以降ではデータ型の検証をしていません.文字列型として扱う場合はデータ型を検証する必要がないからです.したがって入力された文字列そのものを検証する必要があります.TextBox3 では 11 桁の半角数値以外の入力を許可していません.TextBox4 においては正規表現を使って 44 行目のようにパターンにマッチさせる文字列を指定します.メールアドレスの正規表現によるマッチングはかなり複雑らしく,例文では不十分です.TextBox5 では URL にマッチさせます.メールアドレス同様,例文の検証法も不十分です.

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    With TextBox1
        If Not IsDate(.Text) Then
            Cancel = True
            .SetFocus
            .SelStart = 0
            .SelLength = Len(.Text)
        End If
    End With
End Sub

Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    With TextBox2
        If Not (IsNumeric(.Text) And .Text >= 0) Then
            Cancel = True
            .SetFocus
            .SelStart = 0
            .SelLength = Len(.Text)
        End If
    End With
End Sub

Private Sub TextBox3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim myReg       As Object
    Set myReg = CreateObject("VBScript.RegExp")
    With myReg
        .Pattern = "^[0-9]{11}$"
        .IgnoreCase = True
        .Global = True
    End With
    With TextBox3
        If Not myReg.Test(.Text) Then
            Cancel = True
            .SetFocus
            .SelStart = 0
            .SelLength = Len(.Text)
        End If
    End With
End Sub

Private Sub TextBox4_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim myReg       As Object
    Dim Pattern     As String
    Pattern = "^[-a-z0-9]+(\.[-a-z0-9]+)*\@[-a-z0-9]+(\.[-a-z0-9]+)*\.[a-z0-9]{2,6}$"
    Set myReg = CreateObject("VBScript.RegExp")
    With myReg
        .Pattern = Pattern
        .IgnoreCase = True
        .Global = True
    End With
    With TextBox4
        If Not myReg.Test(.Text) Then
            Cancel = True
            .SetFocus
            .SelStart = 0
            .SelLength = Len(.Text)
        End If
    End With
End Sub

Private Sub TextBox5_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim myReg       As Object
    Dim Pattern     As String
    Set myReg = CreateObject("VBScript.RegExp")
    Pattern = "^https?://[a-z0-9][-a-z0-9]{0,62}(\.[a-z0-9][-a-z0-9]{0,62})*\.[a-z0-9][a-z0-9]{0,62}$"
    With myReg
        .Pattern = Pattern
        .IgnoreCase = True
        .Global = True
    End With
    With TextBox5
        If Not myReg.Test(.Text) Then
            Cancel = True
            .SetFocus
            .SelStart = 0
            .SelLength = Len(.Text)
        End If
    End With
End Sub

参照:
サイト URL、フォルダ名、ファイル名に使用できない文字 (Microsoft®)
Active Directory 内のコンピューター、ドメイン、サイト、および Ou の名前付け規則 (Microsoft®)
ホスト名 (Wikipedia)
インターフェースとしてのEXCEL VBAによるユーザーフォーム

Userform of Excel VBA as user interface

I’d like to describe about coding of userform in EXCEL VBA as user interface of database. You could set controls for entering data, selecting from choices, and coding procedure, etc. I would be happy with your help if you could understand the characteristics of controls and design a convenient and robust system.

  1. OptionButton, CheckBox, ListBox and ComboBox as a choice
  2. TextBox
  3. CommandButton
  4. Password

1. OptionButton, CheckBox, ListBox and ComboBox as a choice

ExampleForm

1.1 Restriction which control should you select.

You might had to decide which control should you set on userform depending on the situation. First, number of choice is most important. Can you only choose one option? Or are you permitted multiple choices? Next, is there enough area to present the choices? Last, does user have to enter additional choice? Or does administrator have to define the choices?

1.2 Can you select only one? Or are you allowed to select multiple choice?

If choice was one, you could set OptionButtons in Frame or ComboBox. If you need multiple choices, you could set CheckBoxes in Frame or ListBox. I’d like to draw your attention here that multiple selection doesn’t mean unique value and doesn’t become candidate key to identify record in database. It isn’t first normal form because it is array rather than atomic value.

1.3 Enough area for setting control?

If you could have enough area on UserForm to present all choice, you would set OptionButtons or CheckBoxes in Frame. If you could not have enough area, you could set ComboBox  or ListBox. Or you would have to adjust balance each other or expand UserForm area.

1.4 Is it possible that items may increase after system has been launched?

It’s a serious problem in management of database to force users to add items. It inserts items into transaction table, that isn’t exist in master table. It violates referential integrity constraints. I think that user should not be allowed to add items with ComboBox and new items should be added to master table on the other form.

Control Multiple choice One choose Area restriction Required item added
OptionButton      
CheckBox      
ListBox
ComboBox  

2. TextBox

You can enter various data into TextBox, so it’s difficult control to master. It’s typical value list as below.

  • Last name and first name
  • Birthday
  • Gender
  • Organization, department
  • Zip code
  • Address
  • With or without of an attribute

These input values are classified as following data type:

2.1 String

Text data, for example, name of human, organization, department, zip code or address. Zip code should not be treated as numeric. Phone number should not be, so. Although these data have number, they should be treated as string and should be checked length of string or number of characters in validation.

2.2 Datetime

Date time data type, for example, birthday or entry date. Excel can treat date type after January 1, 1900. To tell the truth, Excel has bug in DATEDIFF function, it would not be so big problem for most purposes.

2.3 Numeric

Numeric required calculation such as finance or salary, measurement of experiment and physical quantity such as length or mass. For further classification, integer, decimal and currency. You would need to select appropriate decimal type depend on required accuracy.

2.4 Boolean

You would describe with boolean that you could tell with ‘yes’ or ‘no’, such as a disease status, smoking status or drinking status. But I think you should use Boolean not in TextBox but in CheckBox.

3. CommandButton

On CommandButton you could describe the procedure that is activated by click, such as selecting records from database, updating records in database, inserting records into database or deleting records from database.

  • Select record
  • Update record
  • Insert record
  • Validation
  • Delete record

3.1 Select record

To select and present records from database, you would need to specify search criteria. With such criteria as name, birthday, age or gender entered into TextBox or ListBox, searching with Auto Filter or Find method and presenting result records, you set the code on CommandButton to activate by click. ListBox might be most appropriate control, because obtained records would be more than one. It’s available to use whole match, forward match, backward match and intermediate match in search method, you could choose depend on situation. An error would occur if you couldn’t get records, so you should catch error and display alerts.

3.2 Update record

To update records in database, the data obtained by described as above have to be replicated to such controls that you can edit as TextBox, OptionButton or ListBox. You would set CommandButton to replicate data. You need to set CommandButton, code the procedure writing back the data you had fixed, that is activated by click.

3.3 Insert record

When there isn’t search result, it means that you should insert record into database. When you enter items into controls, system would be required to verify data. When entered data have passed validation, they would be allowed to be registered. If they didn’t pass validation, system should display alert to user.

3.4 Validation

There are formal and semantic validation. Formal validation verifies whether there is control without input, whether data type is appropriate. Semantic validation verifies that magnitude relation of number or context of date are appropriate. If incorrect data have been inserted into database, it’s hard to find it. It’s standard rule to validate data on user form. If you need, you could use regular expression. It’s one of the most important process in order to design robust system.

How to execute formal validation of TextBox on user form with EXCEL VBA?
How to validate empty value in controls on user form of Excel VBA?

3.5 Delete record

It may be required to remove record from database in some situation. When you operate worksheet as database, the record identified by Auto Filter or Find method should be removed by Delete method. “Delete” button should be set far from other buttons because of fail safe and when it’s clicked, system should ask user whether user really wishes to delete the record by message box.

4. Password

EnterPassword

It’s standard that system requires password when you open system as administrator. In order to enter password, you can use Input method or custom form. I’d like to recommend custom form.

Because Input method is easy to implement that Excel has already equipped function, you could trace user’s behavior with setting Variant type as return value. But it’s only and fatal defect that Input method couldn’t mask input value.

It’s true that custom form requires coding effort but it would be much more your benefit that you can mask password by modifying properties of TextBox.

How to validate password with regular expression which requires single-byte alphanumeric characters and symbols?

インターフェースとしてのEXCEL VBAによるユーザーフォーム

 Excel のユーザーフォームをデータベースのインターフェースとして使用する際の注意点などを述べます.ユーザーフォームにはデータを入力するためのコントロール,選択肢から選択させるためのコントロール,動作を記述するためのコントロールなどを配置します.それぞれの特徴を把握し,ユーザーにとって使いやすくかつ堅牢なシステムを構築する手助けとなれば幸いです.

  1. 選択肢としてのオプションボタン,チェックボックス,リストボックスおよびコンボボックス
  2. テキストボックス
  3. コマンドボタン
  4. パスワード入力

1. 選択肢としてのオプションボタン,チェックボックス,リストボックス及びコンボボックス

ExampleForm

1.1 コントロールを決める際の制約条件

 ユーザーフォーム上に選択肢を示す場合にどのコントロールを用いるかは状況に応じて決める必要があります.選択肢が一択なのか複数選択なのかが最も重要な制約条件です.次に選択肢を提示する面積が確保されているか否か,最後に選択肢をユーザー自身が追加する必要があるか否か,逆に言えば管理者が選択肢を定義しておく必要があるか否かでどのコントロールを用いるかが決まります.

1.2 選択肢は一択か複数か

 選択肢が一択である場合はオプションボタンをフレーム内に配置するか,コンボボックスを配置するかします.複数選択が必要であればチェックボックスをフレーム内に配置するか,リストボックスを配置するかします.ここで注意が必要ですが,複数選択を許可するということは値が一意に決まらないということであり,データベースのレコードを特定するためのキーの候補とはなり得ないということでもあります.もっと言えば,原子値ではなく配列ですので第一正規形を満たしません.EXCEL でそこまで考慮する必要があるかは分かりませんが,一応知っておく必要はあると思います.

1.3 配置するための面積は十分か

 選択肢を全て提示するのに必要な面積が十分に確保されていればフレーム内にオプションボタンを配置するか,チェックボックスを配置します.逆に面積が十分に確保できない場合はリストボックスまたはコンボボックスを配置します.複数選択が必要なのに十分な面積が確保できない場合は,他のコントロールとの配置のバランスを変更するか,ユーザーフォームの面積を拡張する必要があります.

1.4 選択項目は運用中に増加するか

 選択項目をユーザー自身に追加させる必要があるというのは,データベースの管理上問題があります.それはマスターテーブルにない項目をトランザクションテーブルに追加することに他ならず,参照整合性制約に違反します.少なくとも別のフォームでマスターテーブルに項目を追加する仕様とし,コンボボックスから新たに項目を追加するのを禁じた方がよいと思います.とはいえ実装ではなかなかそうも行かないのが現状です.

コントロール 複数選択 一択 面積に制約 アイテム追加必要
オプションボタン      
チェックボックス      
リストボックス
コンボボックス  

ドロップダウンリストとコンボボックス

2. テキストボックス

 テキストボックスには様々なデータを入力できます.自由であるかゆえに最も扱いの難しいコントロールでもあります.考えられる入力値として代表的なものは以下の様なものではないでしょうか.

  • 人名
  • 生年月日
  • 性別
  • 組織名・部署名
  • 郵便番号
  • 住所
  • ある属性の有無

 これらの入力値はデータ型として以下のように分類できます.

2.1 文字列型

 人名,組織名・部署名,郵便番号,住所などのテキストデータ.郵便番号を数値型に含めるのは間違いです.先頭に 0 があった場合,数値型では削られてしまいます.同様に電話番号も数値型としてはいけません.これらは数値を使ってはいますが,文字列型として扱うべきであり,また文字数が決まっているため入力値のチェックの際には必ず文字数をチェックすべきです.

2.2 日付型

 生年月日や入社年月日などの日付のデータ.Excel では 1900 年 1 月 1 日以降の日付型を扱えます.DATEDIFF 関数などに多少のバグはあるのですが,通常の使用で問題になることはあまりないでしょう.

2.3 数値型

 数値型として扱うのは給与や財務など計算の必要なデータ,実験の測定値,長さや質量などの物理量などです.さらに細かく分類すると整数型や小数型,通貨型などいろいろあります.特に小数型は必要な精度に応じて適切なデータ型を選択する必要があります.

2.4 ブール型

 ある疾病の有無や既往の有無,喫煙や飲酒の有無など Yes か No で答えられるものについてはブール型で記述します.しかしブール型はテキストボックスよりもチェックボックスで使用した方が良いように思います.

3. コマンドボタン

 コマンドボタンにはクリックイベントにより意図する動作をプロシージャとして記述します.意図する動作とはデータベースからレコードを検索して表示すること,データベースに存在するレコードを修正すること,データベースにないレコードを新規に登録すること,データベースのレコードを削除することです.それぞれ SELECT, UPDATE, INSERT, DELETE に該当します.

  • レコードを検索する
  • レコードを修正する
  • レコードを登録する
  • 入力値のチェック
  • レコードを削除する

3.1 レコードを検索する

 データベースに存在するレコードを検索して表示するには検索条件を指定する必要があります.名前,生年月日,年齢,性別など検索に必要な条件をテキストボックスなりリストボックスなりに入力させ,オートフィルターや Find メソッドを使用して検索し,得られた結果を表示するコードを記述し,コマンドボタンのクリックイベントに登録します.結果を表示するコントロールはリストボックスが良いでしょう.おそらく複数の結果が得られる筈ですから.検索方法には全文一致,前方一致,後方一致,中間一致があり,状況に応じて選択します.結果が得られない場合はエラーが発生するため,エラーをキャッチして適切なアラートを表示させたほうが良いでしょう.

3.2 レコードを修正する

 データベースに存在するレコードを修正するには,前述の方法で得られたレコードをテキストボックスやオプションボタン,リストボックスなど編集可能なコントロールに移す必要があります.そのためのコマンドボタンを用意したほうが良いでしょう.必要な項目を訂正したらレコードをデータベースに書き戻す処理が必要になります.修正ボタンを配置し,コードを記述してクリックイベントに登録します.

3.3 レコードを登録する

 検索結果が存在しない場合とは,レコードを新規に登録する必要があるということです.ID, 名前など所定の項目をコントロールに入力させ,データベースに登録するコードを記述しますが,この際様々なチェックが必要になります.そのチェックに合格した場合にのみレコードを登録するコードを記述し,登録ボタンのクリックイベントに登録します.チェックに引っかかった部分をユーザーにフィードバックする機能があれば尚親切かもしれません.

3.4 入力値のチェック

 チェックの具体的な内容として,入力漏れがないか,適切なデータ型かなどの形式的なチェックと,さらに日付の前後関係や数値の大小関係といったセマンティックな要素も可能な限りチェックを行います.一旦誤ったデータがデータベースに登録されると簡単には見つけられません.ユーザーフォーム上で入力値のチェックを行うのが鉄則です.必要なら正規表現を使用して下さい.この部分の作り込みが使いやすいシステムになるか否かの分岐点になります.よく考えてコーディングして下さい.よくあるのが計算の必要な項目に全角数字を入力してしまったというものです.テキストボックスに半角数値のみの入力を受け付けるよう指定し,さらに入力モードを半角英数字に指定しておくなどの措置をプログラム上で行なっておけば済んだ話です.

Excel VBAでユーザーフォームのテキストボックスの値の形式的検証を行う
EXCEL VBA でユーザーフォーム上のコントロールの未入力を検証する

3.5 レコードを削除する

 データベースからレコードを削除する必要がある場合もあります.Excel のワークシートをデータベースにしている場合はフィルターを使ってレコードを指定し,Delete メソッドを使用して削除するコードをコマンドボタンに記述するのがよいでしょう.先述のレコードを検索して表示されたリストボックスから削除するレコードを選択させるのが安全な方法でしょう.削除ボタンは他のボタンと距離を離し,押し間違いを防ぐために一旦メッセージボックスで確認をとるなどの安全策を講じたほうが良いでしょう.

4. パスワード入力

EnterPassword

 メンテナンスのために管理者としてアプリケーションを開く必要がある場合,パスワードを要求するのが普通です.パスワードの入力には Input メソッドを使用する方法とパスワード入力用のユーザーフォームを自作する方法があります.それぞれメリットとデメリットがありますが,個人的には自作する方をお勧めします.

 Input メソッドは Excel で既に用意された機能ですので実装が容易です.戻り値に Variant 型を指定しておけばユーザーの行動を完全に把握出来ます.しかし唯一かつ致命的な欠点は入力値をマスクできないことです.この点で自作フォームをお勧めします.

 自作フォームではコーディングの手間がかかるデメリットがありますが,入力値をマスクできるメリットのほうが大きいと思います.ユーザーフォームにテキストボックスとコマンドボタンを配置し,テキストボックスのプロパティを設定するだけで入力値をマスク出来ます.

VBScriptの正規表現でパスワードに半角英数字と半角記号が使用されているか検証する