Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Excel 2010)
オブジェクトを変数にまとめるとエラーが発生する場合の対策
投稿日時: 19/11/11 00:17:06
投稿者: ななつぼし

 
 オブジェクトを変数にまとめると、エラーが発生する場合があり対策がわからず困っています。
 よろしくお願いいたします。
 
【内容】
 @ TextBox を変数に代入してまとめて管理する。                                                                                    
 A TextBox のイベントをまとめて検知する。
 B イベントを検知した TextBox の名前と値を取得して表示する。
 
初期値
 TextBox1.Text = "A1"
 TextBox2.Text = "A2"
 TextBox3.Text = "A3"
 
ダブルクリックで取得した名前と値を代入する。
 With UserForm1
  .TextBox4.Text = Box.Name
  .TextBox5.Text = Box
  End With
 
このコードではエラーが発生してしまう。
 BoxDim(4).Text = Box.Name
 BoxDim(5).Text = Box.Text
 
 
 
以下は、実際のコードです。
 
' --------------------------------------------------------------------------------------
  Option Explicit
' 変数宣言
    Dim Form As UserForm1
    Dim Ctrl As Control
    Dim WithEvents Box As MSForms.TextBox
    Dim BoxCollection As Collection
    Dim BoxDim(5) As Object
    Dim S As String
    Dim N As Single
    Dim I As Single
' --------------------------------------------------------------------------------------
  Friend Sub Init_Box(ByVal TextBox As MSForms.TextBox)
    Set Box = TextBox
  End Sub
' --------------------------------------------------------------------------------------
  Private Sub UserForm_Initialize()
' 初期設定
    If Not Me Is UserForm1 Then Exit Sub
    Set BoxCollection = New Collection
    With Me.Controls
      For Each Ctrl In Me.Controls
        Set Form = New UserForm1
        S = Replace(Ctrl.Name, "TextBox", "") ' 添え数字を取得
        If IsNumeric(S) = True Then
          N = Val(S) ' 添え数字を数値化
          If N >= 0 And Int(N) = N Then ' 正の整数
            Form.Init_Box .Item(Ctrl.Name)
            BoxCollection.Add Form
            Set BoxDim(N) = Ctrl
                BoxDim(N).Text = "A" & Replace(Ctrl.Name, "TextBox", "")
          End If
        End If
        Set Form = Nothing
      Next
    End With
' ▼エラーは発生しない
    BoxDim(4).Text = ""
    BoxDim(5).Text = ""
   End Sub
' --------------------------------------------------------------------------------------
  Private Sub Box_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
' 選択したボックス
    With UserForm1
      .TextBox4.Text = Box.Name
      .TextBox5.Text = Box.Text
    End With
 
    MsgBox "[OK]をクリックして継続すると、下記のエラーが発生します。" & _
            vbLf & vbLf & _
           " 実行時エラー '91'" & vbLf & _
           " オブジェクト変数または With ブロック変数が設定されていません。", _
            vbInformation, "警告"
     BoxDim(4).Text = Box.Name
     BoxDim(5).Text = Box.Text
   
  End Sub
' --------------------------------------------------------------------------------------
  Private Sub CommandButton1_Click()
' [クリア]
' ▼エラーは発生しない
    For I = 1 To 3
      BoxDim(I).Text = ""
    Next I
  End Sub
' --------------------------------------------------------------------------------------
 

回答
投稿日時: 19/11/11 12:06:49
投稿者: WinArrow
投稿者のウェブサイトに移動

 
コード内容がややこしいことになっていませんか?
  
↓のコードが起因していると思いますが。
  
 > Set Form = New UserForm1
   
 BoxDimがすべて Nothing になっています。
  
もっと、コードをシンプルにしましょう。
  
>Form
なんて変数必要ないと思いますが・・・
Meを使えばよいので・・・・削除
 

投稿日時: 19/11/11 17:36:43
投稿者: ななつぼし

 WinArrow 様
 
 ご回答ありがとうございます。
 作成中のコードの 一部を抜き出して加工しているため お分かり難いかと存じます。
 
 -----------------------------------------------------------------------------------------
 @ Private Sub UserForm_Initialize() の中で TextBox を 変数 BoxDim に代入して
   BoxDim(1)〜(3) に Null値を代入する場合。
 A Private Sub CommandButton1_Click() で BoxDim(1)と BoxDim(3) に Null値を代入する場合。
  上記の@とAの場合は エラーは発生しません。
 
 -----------------------------------------------------------------------------------------
 B Private Sub Box_DblClick で、選択したオブジェクトの名前と値を BoxDim(4)とBoxDim(5) に
   代入する下記のコードではエラーが発生してしまいます。
      BoxDim(4).Text = Box.Name
      BoxDim(5).Text = Box.Text
  実行時エラー '91'
  オブジェクト変数または With ブロック変数が設定されていません。
 
 ------------------------------------------------------------------------------------------
 C 下記のコードで対応しています。
   With UserForm1
       .TextBox4.Text = Box.Name
       .TextBox5.Text = Box.Text
   End With
 ------------------------------------------------------------------------------------------
 TextBox が多い場合に変数に代入したいのです。仕方なくCで対応しています。
 是非、良いお知恵をお願いいたします。
 
 
 

回答
投稿日時: 19/11/11 20:27:47
投稿者: WinArrow
投稿者のウェブサイトに移動

 
同類のコントロールのイベントを共通化する手法として
 
コントロール配列という手法を紹介します。
モジュールとしては、ユーザーフォームとクラスモジュールを使います。
事前作業:
アクティブシートの列Aのセルに、データを入力しておく(テキストぼクスの個数分)
 
<Userform1>
テキストボックス 個数:任意
ラベル:1個・・・テキストボックスをダブルクリックしたとき、テキストボックス名と「値」を表示する
 
 
Option Explicit
 
Dim myTXT() As New Class1
Dim i As Long
 
Private Sub UserForm_Initialize()
Dim ctrl As Control
    ReDim myTXT(0): i = 0
    For Each ctrl In Me.Controls
        If LCase(TypeName(ctrl)) = "textbox" Then
            ReDim Preserve myTXT(0 To i)
            myTXT(i).txt_set ctrl, Cells(i + 1, "A").Value
            i = i + 1
        End If
    Next
End Sub
 
Friend Sub txtDblClick(ByRef txtctrl As MSForms.TextBox)
    Me.Label1.Caption = "「" & txtctrl.Name & "」の値は「" & txtctrl.TEXT & "」です"
End Sub
 
<Class1>・・・クラスモジュール
Option Explicit
 
Private WithEvents txt As MSForms.TextBox
 
 
Public Sub txt_set(ByRef fmTXT As MSForms.TextBox, ByVal DATA As String)
    Set txt = fmTXT
    txt.TEXT = DATA
End Sub
 
Private Sub txt_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Call UserForm1.txtDblClick(txt)
    Cancel = True
End Sub
 

回答
投稿日時: 19/11/11 20:58:04
投稿者: WinArrow
投稿者のウェブサイトに移動

後付けになりますが、
 
質問者さんのオードの中の
問題は、前レスで書きました通り、
> Set Form = New UserForm1
です。
このコードが実行されるたびに、Useform_Initializeが再実行され
変数が初期化されてしまいます。
従って、
>Box_DblClick
プロシジャでは、Nothing(初期化)した変数を空く背うすることになり、
>オブジェクト変数または With ブロック変数が設定されていません。
という意味不明なエラーになります。
 
ステップ実行すれば、動きと変数の内容(ローカルウインドウ)が確認できます。
 
 
そこで、Form変数を外し(FormをMeに変更)
実行すると、エラーは出なくなりますが、
原因未確認ですが、TextBox5をダブルクリックしたときだけしか、反応しません。
 
以上がコードに対する検証です。
 
コントロール名は、Excelが勝手につけた名前より、自分で分かりやすい名前をつけた方が
わかりやすいコードとなることもあり
コントロール配列を使う方法を提案しました。
 
 
 

回答
投稿日時: 19/11/12 08:19:24
投稿者: 半平太

> @ Private Sub UserForm_Initialize() の中で TextBox を 変数 BoxDim に代入して
>   BoxDim(1)〜(3) に Null値を代入する場合。
> A Private Sub CommandButton1_Click() で BoxDim(1)と BoxDim(3) に Null値を代入する場合。
>  上記の@とAの場合は エラーは発生しません。
  
Userform1型のインスタンスは全部で6つありますよね。
これが頭に入ってないと始まらないです。
  
直接的な操作の対象になっているのは、いわば「原UF」です。
その他はいわば「ナンバー UF」です。5つあります。
  
(1)TextBox1のダブルクリックで起動するBox_DblClickは、「No1 UF」内のイベントです。
 :          
(5)TextBox5のダブルクリックで起動するBox_DblClickは、「No5 UF」内のイベントです。
  
ところが、「ナンバーUF」のBoxDim()は、Nothingなので
BoxDim(4).Text = Box.Name ←は当然の如くエラーになります。
  
※エラーにならないのは、「原UF」のインスタンス内で処理している時だけです。
  
では、どうすればいいのかですが、
一案としては、「原UF」にアクセス可能なプロパティ・プロシージャを追加すればいい。
  
 ※当然「ナンバーUF」にも同じプロパティが追加されますが、
   それらのインスタンス内で使われる機会はないです。
 
具体的には
> Private Sub Box_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
> BoxDim(4).Text = Box.Name
> BoxDim(5).Text = Box.Text
> End Sub
 
の代わりに、こうする
  

 Private Sub Box_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
      UserForm1.BxDm(4) = Box.Name
      UserForm1.BxDm(5) = Box.Text
  End Sub
  
  Friend Property Let BxDm(TBnum As Long, TBtx As String)
    BoxDim(TBnum).Text = TBtx
  End Property

まぁ、それで動くというだけなので、構造はすごく分かりにくい。
 
クラスモジュールを使うのが妥当だと思います。

回答
投稿日時: 19/11/12 18:32:16
投稿者: WinArrow
投稿者のウェブサイトに移動

文章が間違っていましたので、修正します。
 
1>プロシジャでは、Nothing(初期化)した変数を空く背うすることになり、

プロシジャでは、Nothing(初期化)した変数をアクセスすることになり、
 
 
問題に対することではないが、正しい理解をしていただくために
 
> BoxDim(4).Text = ""
> BoxDim(5).Text = ""
 
このコードを、NULLをセットしていると表現したいますが、
NULLではなく、空白文字列です。

投稿日時: 19/11/13 23:13:46
投稿者: ななつぼし

 WinArrow 様
 半平太 様
 
 お忙しい中、いろいろとお知恵、ご提案等をいただきまして誠にありがとうございました。
 無事、解決できました。