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

すべての返信

• 

  

  0

  サインインして投票

  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

  返信

  |

  引用
• 

  

  0

  サインインして投票

  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

  返信

  |

  引用
• 

  

  0

  サインインして投票

  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

  返信

  |

  引用
• 

  

  0

  サインインして投票

  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

  返信

  |

  引用
• 

  

  0

  サインインして投票

  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

  返信

  |

  引用
• 

  

  0

  サインインして投票

  Ashidacchiさん

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

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

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

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

  2019年2月7日 8:59

  返信

  |

  引用