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

エクセルVBA100本ノック

この記事から得るもの

Join関数の便利さに気づける。


１　今回のお題

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

f:id:bimori466:20201201141656p:plain

excel-ubara.com

２　今回のお題の意図

入力リストの配列を文字列で返す。

３　回答

今回のお題で重要な点は、「配列に取得したSheet名をどのように文字列で返すか」だと感じました。

私の回答

Sub ノック31本目_1()

Dim SheetAry() As String: ReDim SheetAry(1 To ThisWorkbook.Sheets.Count)

'配列にSheet名を取得
For i = 1 To ThisWorkbook.Sheets.Count
    SheetAry(i) = ThisWorkbook.Sheets(i).Name
Next


'A1セルに入力規則設定。
With Range("A1").Validation
    .Delete
    .Add Type:=xlValidateList, Operator:=xlEqual, Formula1:=Join(SheetAry, ",")
    .ErrorTitle = "不正な入力です。"
    .ErrorMessage = "ドロップダウンリストから選択してください。"
    .ShowError = True
End With

End Sub

「Formula1:=Join(SheetAry, ",")」ここが一番重要だと感じました。
ネットでリストの設定をする例をみると、「Formula1:="ロンドン,東京,ニューヨーク"」と文字列をカンマ（,）で区切っています。配列でSheet名を取得した後はどうしようかと考えていました。配列をループして、カンマで区切った文字列を作ろうかとも考えましたが、Join関数を使えばそんなループは不要でした。

４　感想

Rangeのプロパティに入力規則の設定（Validation）があることも知りませんでした(;^ω^)。
そして今回は、Joinの使いどころにすごく気づかされました。
こんなんどこで使うねん！？と思っていたらここでした。


ではでは、この辺で(^^)/~~~