VBA練習問題
VBA100本ノック 31本目:入力規則

VBAを100本の練習問題で鍛えます
最終更新日:2021-01-13

VBA100本ノック 31本目:入力規則


セルに入力規則を設定する問題です。
ブックのシート一覧のリストにします。


ツイッター連動企画です。
ツイートでの見やすさを考慮して、ブック・シート指定等を適宜省略しています。

VBAテスト用のサンプルデータはご自身でご用意ください。


出題

出題ツイートへのリンク

#VBA100本ノック 31本目
アクティブシートのA1セルに、入力規則の「リスト」を設定します。
「リスト」の「元の値」には、ブックの全シートのシート名を設定してください。
A1セルに間違った値を入力した場合は分かり易い警告文がでるようにしてください。
※再実行を考慮
※ブックの指定は任意

マクロ VBA 100本ノック


頂いた回答

解説

入力規則はRangeのValidationオブジェクトです。
設定する場合はDeleteしてから新しい規則を設定します。
リストはAddメソッドのFormula1にカンマ(,)区切りの文字列で設定します。
数値のシート名012等で頭の0が消えないように表示形式を文字列に変更しています。

Sub VBA100_31_01()
  Dim ws As Worksheet
  Set ws = ActiveSheet
  Dim wb As Workbook
  Set wb = ws.Parent
  
  Dim ary() As String
  ReDim ary(1 To wb.Sheets.Count)
  
  Dim i As Long
  For i = 1 To wb.Sheets.Count
    ary(i) = wb.Sheets(i).Name
  Next
  
  ws.Range("A1").NumberFormat = "@"
  With ws.Range("A1").Validation
    .Delete
    .Add Type:=xlValidateList, _
       AlertStyle:=xlValidAlertStop, _
       Formula1:=Join(ary, ",")
    .ErrorTitle = "入力値不正"
    .ErrorMessage = "ドロップダウンリストから選択してください。"
    .ShowError = True
  End With
End Sub


リストはカンマ区切りなので、シート名にカンマが含まれていると分割されてしまいます。
リストを文字列で設定する場合はいくつかの制限があります。
制限を気にせずにリスト設定するには、セル範囲をリストに設定するようにします。
このVBAは記事補足に掲載しました。


補足

リストを直接文字列指定した場合の制限
入力規則のリスト文字列はカンマ(,)区切りなので、シート名にカンマが含まれていると分断してしまいます。
その他、リストに文字列で設定する場合は、いろいろと制限があります。

先頭シートが=で始まる場合
https://twitter.com/audiobookrunner/status/1330825517176524800

リスト文字列が255文字を超える場合
https://twitter.com/ExcelQBag/status/1330850173476454401

このような場合は、上記VBAでは正しく設定できません。
これらに対応するには、セル範囲をリストに設定するようにします。
ただし、さすがに無いとは思いますがリストの要素数も32,767が上限となります。

リストに直接文字列を指定するのは、数も少なく、変更も少ない場合に限定したほうが良いと思います。
実務としては以下のように、シートを利用するようにした方が無難かつ柔軟に対応できると思います。


セル範囲を指定して対応
Sub VBA100_31_02()
  Dim wb As Workbook, ws As Worksheet
  Set ws = ActiveSheet
  Set wb = ws.Parent
  
  Dim ws設定 As Worksheet
  Set ws設定 = getSheet(wb, "設定")
  
  Dim i As Long, cnt As Long
  ws設定.Columns(1).Clear
  ws設定.Columns(1).NumberFormat = "@"
  cnt = 0
  For i = 1 To wb.Sheets.Count
    If wb.Worksheets(i).Visible <> xlSheetVeryHidden Then
      cnt = cnt + 1
      ws設定.Cells(cnt, 1) = wb.Sheets(i).Name
    End If
  Next
  
  ws.Range("A1").NumberFormat = "@"
  With ws.Range("A1").Validation
    .Delete
    .Add Type:=xlValidateList, _
       AlertStyle:=xlValidAlertStop, _
       Formula1:="=" & ws設定.Range("A1").Resize(cnt).Address(external:=True)
    .ErrorTitle = "入力値不正"
    .ErrorMessage = "ドロップダウンリストから選択してください。"
    .ShowError = True
  End With
End Sub

Function getSheet(ByVal wb As Workbook, ByVal aName As String) As Worksheet
  On Error Resume Next
  Set getSheet = wb.Worksheets(aName)
  If Err Then
    Set getSheet = wb.Worksheets.Add
    getSheet.Visible = xlSheetVeryHidden
    getSheet.Name = aName
  End If
  On Error GoTo 0
End Function

セルにシート一覧を出力して、そのセル範囲を指定すれば上記の問題点は解消されます。

「設定」シートをxlSheetVeryHiddenで作成して、A列にシート一覧を出力しています。
実務的には、「設定」シートはあらかじめ用意しておき、ユーザーに操作させたくない各種設定値を入れておくことに使ったりします。
xlSheetVeryHiddenはユーザーに触れさせない為のものなので、上記VBAでは一覧から除外しています。


リストの区切り文字のカンマ(,)は何処で設定されているのか
マクロ VBA 100本ノック

マクロ VBA サンプル画像

この区切り文字が使われているようです。
これを別の文字、例えば/に変更すると入力規則も/区切りになります。
ただし、これは他でも使われているので変更しない方が良いです。


サイト内関連ページ

第126回.入力規則(Validation)
入力規則は、Validationオブジェクトになります、ワークシート範囲の入力規則を表します、入力規則は、シート上で設定しておいた方が良い場合が多いですが、マクロVBAで設定する必要も出てきます。入力規則を設定するには、Validationオブジェクトを使います。
第112回.動的配列(ReDim)
マクロVBAにおける配列の説明として最初に静的配列を解説しました。静的配列では要素数は宣言時点で決められていました。しかし、プログラミングをする上で、実行時点で要素数を決めたい場合や、実行途中で要素数を増減させたい場合が多く出てきます。
第113回.配列に関連する関数
マクロVBAで配列を使う上で、必要となるVBA関数がいくつかあります。より便利に配列を活用するために必須となるVBA関数、・LBound関数 ・UBound関数 ・Array関数 ・IsArray関数 ・Join関数 ・Filter関数 以上のVBA関数について解説します。




同じテーマ「VBA100本ノック」の記事

28本目:シートをブックに分割
29本目:画像の挿入
30本目:名札作成(段組み)
31本目:入力規則
32本目:Excel終了とテキストファイル出力
33本目:マクロ記録の改修
34本目:配列の左右回転
35本目:条件付き書式
36本目:列の並べ替え
37本目:グラフの色設定
38本目:1シートを複数シートに振り分け


新着記事NEW ・・・新着記事一覧を見る

PropertyのSetはLetでも良い|VBA技術解説(2021-03-31)
エクセル麻雀ミニゲーム|VBAサンプル集(2021-03-09)
VBA100本ノック 100本目:WEBから100本ノックのリストを取得|VBA練習問題(2021-03-03)
VBA100本ノック 魔球編:2桁の最小公倍数|VBA練習問題(2021-02-02)
Select Caseでの短絡評価(ショートサーキット)の使い方|VBA技術解説(2021-01-03)
VBA100本ノック 迷宮編:巡回セル問題|VBA練習問題(2020-12-31)
VBA100本ノック 魔球編:閉領域の塗り潰し|VBA練習問題(2020-12-16)
VBA100本ノック 魔球編:組み合わせ問題|VBA練習問題(2020-12-02)
将棋とプログラミングについて~そこには型がある~|エクセル雑感(2020-11-22)
VBA100本ノック 1本目:セルのコピー|VBA練習問題(2020-10-19)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.Excelショートカットキー一覧|Excelリファレンス
6.繰り返し処理(For Next)|VBA入門
7.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
8.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
9.セルに文字を入れるとは(Range,Value)|VBA入門
10.とにかく書いてみよう(Sub,End Sub)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ