none
Excel2016 バージョン1901にてセルの入力規則が正常に動作しない。 RRS feed

  • 質問

  • ①問題のはじまり

    Windows10 64Bit(1803)にてEXCEL2016(ビルド1901)にアップデータしたところ、

    今まで正常に動作していたExcelのVBAが以下のエラーで止まるようになってしまいました。

    (エラー内容)

    1004:アプリケーション定義またはオブジェクト定義のエラーです。

    ②プログラム処理内容

    データベースから名前を取得し、セルに格納してセルの入力規則によりリストから選択するという内容です。

    以下にソースを記載します。

    Sub setMitumoriTantoList()
    Dim cn As New ADODB.Connection, rs As New ADODB.Recordset
    Dim query As String, strList As String
        
        Range("B4").Validation.Delete
        
        query = "EXEC [SQLストアドにてSELECTにて名前を取得する処理内容]"
        If sqlCnOpen(cn) = False Then Exit Sub
        Set rs = cn.Execute(query)
        
        If rs.EOF = False Then
            Do Until rs.EOF
                strList = strList & IIf(strList <> "", ",", "") & StrConv(rs.Fields(0), vbNarrow)
                rs.MoveNext
            Loop
            rs.Close: Set rs = Nothing
            With Range("B4").Validation
                .Add Type:=xlValidateList, _
                Formula1:=strList
            End With
        End If
        cn.Close: Set cn = Nothing
    End Sub

    ③エラー箇所

    以下の.Add Type部分で1004のエラーが発生します。

     With Range("B4").Validation
        .Add Type:=xlValidateList, _
              Formula1:=strList
     End With

    ④問題回避のため、試した処理内容について

    .Add Typeの直前に

    Validation.Deleteを実行したり、セルをSelectして

    みましたが、同じエラーが発生します。

    また、シートが保護されてるので保護を解除してみたところ同じエラーが発生します。

    PC固有の問題ではなく、Windows10 64Bit(1803)のEXCEL2016(ビルド1901)のPCであれば

    現象が発生することが確認しています。

    これはEXCEL2016(ビルド1901)の不具合なのでしょうか?

    同じようなエラーがでている方はいないでしょうか?

    何卒よろしくおねがいします。

    2019年2月5日 6:52

回答

  • taro ozaki さん、

    原因が分かってよかったですね。
    Formula1:= の文字列が 255までというのは知りませんでした。(今、MS の公式文書で確認しました)

    で、255文字を超えた場合の対応ですが、こんな方法はどうでしょう?
    文字列変数を使わずに、Excel シート上に書き込んでから、その範囲を指定します。
    VBA 上の記述としてはこんな感じになると思います。
      Formula1:=Range("X1:X100")

    試したところ、1600 文字を超えても問題なし、でした。
      
    ボタン [Create List] 内のコードはこんな感じです:
    Private Sub btn_CreateList_Click()
        Dim myLength As Integer: myLength = 0
        Dim myRow As Integer
        ' --- 文字数: A列:最終行までループして加算
        For myRow = 1 To lastRow
            myLength = myLength + Len(Range("A268").Text)
        Next
        Range("C3").Value = myLength    ' -- 合計文字数を表示
        ' --- 入力規則
        With Range("B1").Validation
            .Delete
            .Add Type:=xlValidateList, _
                Formula1:="=A1:A268"
        End With
    End Sub
    ご参考になれば幸いです。

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    • 回答としてマーク taro ozaki 2019年2月7日 8:55
    2019年2月6日 11:26

すべての返信

  • taro ozaki さん、

    仮想環境ですが、Windows 7, 10、Excel 2010, 2013, 2016 で試してみました。
    いずれもダメ(実行時エラー 1004)でした。
    Range("B4").Validation の最初に .Delete を入れれば、問題なく完了することに気付きました。

    なので、ループの最終回では、strList 末尾にカンマを付けないようにすればエラーは発生しないと思います。

    テストした内容は次のとおりです。
    シート画面: 
         
    シート上のボタンをクリックすると動作する VBA コード:

    Private Sub btn_AddValidation_Click()
        Dim strList As String: strList = ""
        Dim idx As Integer
        ' --- concatinate validation list
        For idx = 1 To 10
            If (idx = 10) Then
                strList = strList & CStr(idx)
            Else
                strList = strList & CStr(idx) & ","
            End If
        Next
        ' --- validation Delete & Add
        With Range("B4").Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:=strList
        End With
    End Sub
    お試しください。

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    2019年2月5日 10:20
  • Ashidacchiさん 回答ありがとうございます。

    今回のトラブルなのですが、基本的にEXCELのビルドが上がる前には正常に動作していたものが

    突然動かなくなったトラブルになります。

    試しにカンマの処理やDelete処理を変えて、処理内容を単純化してみたものを作成してみましたが、

    Windows7 Excel2013 ビルド15.0.5101.1002

    では動作しますが、

    Windows10 64Bit(1803)にてEXCEL2016(ビルド1901)

    では動作しません。

    1004:アプリケーション定義またはオブジェクト定義のエラーです。

    というエラーが発生します。

    読み出すデータベースは最初のレコードはNULLが含まれ、残りは氏名が入っています。

    1レコード目 NULL

    2レコード目 徳川 家康

    3レコード目 織田 信長

    このような内容です。

    以下にカンマの処理やDelete処理を変えて、処理内容を単純化したものを記載します。

    参照設定はmicrosoft activex data objects 6.0を使用しています。

    (2.8でも試しましたが、同じ現象でした。)

    Private Sub CommandButton1_Click()
        Dim cn As New ADODB.Connection, rs As New ADODB.Recordset
        Dim query As String, strList As String
        Dim var As Variant
        Set cn = New ADODB.Connection
         
        cn.Provider = "SQLOLEDB"
        cn.ConnectionString = "Data Source = XXX.XXX.XXX.XXX;" & _
                              "Initial Catalog = YYYYYYYY;" & _
                              "User ID = AAAAAAAAAA;" & _
                              "Password = BBBBBBBBB;"
        cn.Open
        
        query = "EXEC [氏名を取ってくる]"
        
        Set rs = cn.Execute(query)

        If rs.EOF = False Then
            Do Until rs.EOF
                If IsNull(rs.Fields(0)) Then
                    '1レコード目はNULLなので、飛ばします。
                Else
                    '単純に前にカンマをつける処理に変更
                    strList = strList & "," & CStr(rs.Fields(0))
                End If
                rs.MoveNext
            Loop
        End If

        rs.Close: Set rs = Nothing
        cn.Close: Set cn = Nothing
        
        '1文字目のカンマをとる。
        strList = Mid(strList, 2)
        
        With Sheet1.Range("B4").Validation
    'デリート処理追加
            .Delete
            .Add Type:=xlValidateList, _
            Formula1:=strList
        End With

    End Sub

    それと、単純に入力規則をつける内容では問題がありません。

    下記にソースを記載します。

    Private Sub CommandButton2_Click()
        Dim strList As String
        
        strList = "AAA,BBB,CCC"
        
        With Sheet1.Range("B4").Validation
            .Delete
            .Add Type:=xlValidateList, _
            Formula1:=strList
        End With
    End Sub

    おそらくSQLサーバーからのデータを入力規則にいれるときのみに発生するのではないかと

    思われます。

    2019年2月6日 8:43
  • taro ozaki さん、

    (1) エラーが出たときの strList の内容を、Code Block(上の、HTML の右にあるボタン)に入れてご提示いただけますでしょうか?
    (2) Excel は 32-bit 版でしょうか?

    以上です。

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    2019年2月6日 8:49
  • Ashidacchiさん

    こちらでいろいろ調べてみたところ、strListが256文字以上の場合にエラーとなります。

    SQLデータベースは関係ありませんでした。

    入力規則の制限上255文字までなので、今までのバージョンで256文字文字以上入っていたのが

    逆にバグではないかと思われます。

    使用しているEXCELは32Bitです。

    下記のようなソースでも再現しました。

    Private Sub CommandButton2_Click()

        Dim strList As String
        
        strList = 256文字以上
        
        With Sheet1.Range("B4").Validation
            .Delete
            .Add Type:=xlValidateList, _
            Formula1:=strList
        End With
    End Sub

    今回のデータベースの場合は名前の重複があるので、DISTINCTの重複排除で

    なんとかなりそうです。

    ご協力ありがとうございました。

    2019年2月6日 9:45
  • taro ozaki さん、

    原因が分かってよかったですね。
    Formula1:= の文字列が 255までというのは知りませんでした。(今、MS の公式文書で確認しました)

    で、255文字を超えた場合の対応ですが、こんな方法はどうでしょう?
    文字列変数を使わずに、Excel シート上に書き込んでから、その範囲を指定します。
    VBA 上の記述としてはこんな感じになると思います。
      Formula1:=Range("X1:X100")

    試したところ、1600 文字を超えても問題なし、でした。
      
    ボタン [Create List] 内のコードはこんな感じです:
    Private Sub btn_CreateList_Click()
        Dim myLength As Integer: myLength = 0
        Dim myRow As Integer
        ' --- 文字数: A列:最終行までループして加算
        For myRow = 1 To lastRow
            myLength = myLength + Len(Range("A268").Text)
        Next
        Range("C3").Value = myLength    ' -- 合計文字数を表示
        ' --- 入力規則
        With Range("B1").Validation
            .Delete
            .Add Type:=xlValidateList, _
                Formula1:="=A1:A268"
        End With
    End Sub
    ご参考になれば幸いです。

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    • 回答としてマーク taro ozaki 2019年2月7日 8:55
    2019年2月6日 11:26
  • Ashidacchiさん

    なるほど、一度セルにいれてそこから選ぶのですね。

    人が増えればいつかは足りなくなりますので、いずれ処理を変更します。

    最終的な解決策まで提示していただいてありがとうございました。

    この件はこれで終了させていただきます。

    2019年2月7日 8:59