カテゴリー: Database

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.

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)

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

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

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

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

　フォーム上のコントロールの未入力のチェックは複数のコマンドボタンから共通して呼び出される機能であるため，共通化したほうがコーディングの重複をなくせます．そのため標準モジュールを追加して関数として実装することにします．下記の関数で戻り値を 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)

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.

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 2003 までは Access® のカレンダーコントロールを流用できたのですが，最近のバージョンではそれもできなくなりました．キーボードからタイプするか，カレンダーを自作するか，関数アドインを利用するしかありません．

　上図のようにフォーム上にテキストボックスを配置します．それぞれ 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によるユーザーフォーム

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

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.

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

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

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

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

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. パスワード入力

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

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

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

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