【エクセルVBA】255文字を超えたらどうなるの?入力規則のリスト設定方法あれこれ

vba,validation,255

皆様こんにちは、ノグチです。

エクセルの入力規則は、使いこなせばとても便利な機能ですよね。

職場で使うエクセルツールで使用されることも多いのではないでしょうか。

中でもリストで作成するドロップダウンリストは、ツール使用者に入力させる文字列を限定でき、かつ使用者に文字列をキータイピングで入力させる必要がないので入力が楽、キータイピングが不要=入力間違いがないという点で、結構使い勝手が良いものです。

以前下記の記事で、この入力規則のドロップダウンリストをVBAで作成したり、ドロップダウンリストに表示するテキストの更新方法を紹介しました。

【エクセルVBA】Validation.Addメソッドで入力規則のドロップダウンを作る方法
ValidationオブジェクトのAddメソッドを使って、セルに入力規則のドロップダウンリストを作成する方法をご紹介しています。シートやブックに入力された値によって入力規則の種類を変えたり、ドロップダウンリストに表示する値を変えたい、という時に便利に使えますよ。

この記事の中で、さらっと「設定できる文字列の長さは255文字までですよ!」と言っているのですが、実際にこの文字制限を超えた場合、一体どうなるのか?までは触れていませんでした。

今回の記事では、実際に255文字を超えたらどうなるのか?、試してみましょう。

スポンサーリンク

入力規則のリストで255文字を超えるとどうなるのか?

手動で文字列を指定した場合

まずは、手動で255文字以上の文字列をにセットする場合を試してみます。

入力規則の条件の設定で「リスト」を選択して、「元の値」に適当に文字を入力していくと…

validation,list,manual

aaa,bbb,cccc,dddd…と入力していったところで、キーボード入力を受け付けなくなってしまいました。

どうやら手動でドロップダウンリストの文字列を入力する場合、255文字以上は入力できないようです。

手動で入力する場合は、そもそも255文字以上入力することができないので、255文字以上の文字列を入力してしまったらどうなるか?という心配は不要ですね。

セル指定の場合

お次は、255文字以上の文字列が入力されたセルを、「元の値」にセットした場合です。

エクセルシートのB3セルに、A列のリストを「,」でつなげて255文字以上にした文字列を作成しました。

念のため、B3セルの文字数を、C3セルのLEN関数でカウントしてあります。

文字数は270文字です。

validation,list,cell

このB2セルを、入力規則の「元の値」にセットしてみると…

validation.list,cell,dropdown

ちゃんとセットできますね。

ドロップダウンリストから文字列を選択しても、問題なさそうです。

ただしこの方法だと、セル内の文字列がドロップダウンリストの1行に全てセットされてしまいます。

この方法なら255文字の制限を気にしなくてもよさそうですが、そもそも、1行しか表示できないならドロップダウンリストにする意味が無いですね。

VBAで指定した場合

では、VBAを使って「元の値」を更新する場合はどうなるか?を試してみましょう。

セットする文字列が入力されているセルを指定する場合

こちらのコードで、C5セルに入力規則のリストを作成して、B3セルの文字列を「元の値」に指定してみます。

Sub Create_Validation_List()
Dim strTxt As String

strTxt = ActiveSheet.Cells(3, 2).Value

With ActiveSheet.Range("C5").Validation
    .Delete
    .Add Type:=xlValidateList, _
    AlertStyle:=xlValidAlertStop, _
    Formula1:=strTxt
End With

End Sub

このコードを実行してみると…

validation,list,実行時エラー,vba

出ました。

実行時エラー”1004″。

エラーだけ見ると関係なさそうに見えますが、リストに指定した文字列が255文字を超えているとこのエラーになるようです。

VBA内でドロップダウンリストにセットする文字列を作成する場合

ちなみに、上のコード内でドロップダウンリストに指定する文字列を、以下のようにVBA内で作っても、同じ実行時エラーになってしまいます。

Sub Create_Validation_List()
Dim strTxt As Variant
Dim i As Long

With ActiveSheet
    
    i = 1
    
    Do Until .Cells(i, 1).Value = ""
        If strTxt = "" Then
            strTxt = .Cells(i, 1).Value
        Else
            strTxt = strTxt & "," & .Cells(i, 1).Value
        End If
        
        i = i + 1
    Loop

    With .Range("C5").Validation
        .Delete
        .Add Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, _
        Formula1:=strTxt
        .InCellDropdown = True
    End With
End With

相変わらずのエラーの分かりにくさですが、VBAでも文字列を直接「元の値」にセットする場合、255文字を超えていると受け付けられないようですね。

OFFSET関数で指定した場合

ならばOFFSET関数を使った場合はどうでしょう。validation,list,offset

A列のリストを、OFFSET関数で指定してみると…

validation,list,offset,dropdown,sheet

 

ちゃんとドロップダウンリストにA列の文字列達が表示されます。

この方法も、255文字の制限は気にしなくてOKですね。

セル範囲を指定する場合

では、上のシートのA列を、セル範囲でセットしてみるとどうでしょうか。

validation,list,range

A列1~21行目の文字列の合計が255文字以上になるように、リストを作ってみました。

validation,range,dropdown

このリストのセル範囲を、入力規則の「元の値」に指定すると…

validation,list,range.dropdown

エラーなく、ドロップダウンリストにA列の文字列をセットできていますね。

名前付き範囲を指定する場合

エクセルにはセルの範囲に任意の名前を付けて使用することができる、名前付き範囲という機能がありますね。

この名前付き範囲を使ってみます。

validation,list,255

シートのA2セルからA21セルまでの範囲に「文字列テスト」という名前を付けて…

validation,list,名前付き範囲,セット

入力規則の「元の値」に名前付き範囲に指定した名称をセットすると…

validation,list,名前付き範囲

この通り、ドロップダウンリストにA列の文字列がセットされていますね。

A列の文字数のトータルが255文字を超えていても問題なしです。

結果まとめ

色々な方法で入力規則のドロップダウンリストを作って検証してみましたが、結果、「元の値」に指定する文字列が255文字に収まっていればOKなようです。

なので255文字の制限を意識するのは、

  • ドロップダウンリストに表示したい文字列を、入力規則作成画面の「元の値」に手動で入力する場合
  • VBAでドロップダウンリストに表示する文字列を作成して設定する場合
  • VBAでドロップダウンリストに表示する文字列を、既存セル指定によって設定する場合

の3パターンのようですね。

  • セル範囲を指定する
  • 名前付き範囲を指定する
  • OFFSET関数を指定する

の場合は、文字数制限は気にしなくてよさそうです。

この中で最も簡単なのはOFFSET関数を使う方法でしょうか。

しかし、エクセル内で関数を多用するとエクセルの動作が遅くなることがありますので、要注意です。

個人的には、VBAで入力規則の更新をするなら、コードの分り易さも考えて、名前付き範囲を利用するのが良いと思っています。

名前付き範囲を指定する方法なら、文字列のリスト範囲が変わっても名前付き範囲を更新すればよいですし、名前付き範囲は多用してもエクセルの動作に関数ほどは影響を与えませんからね。

最後に

今回は、入力規則のドロップダウンリストの「元の値」に255文字以上を指定するとどうなるか?を色々な方法で検証してみました。

色々な指定の仕方がありますが、エクセルツールの内容や使い方によって、ドロップダウンリストを指定する方法を変えてみるとよいですね。

それでは、最後までお読みいただきありがとうございました!

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