Memorandum

Excel5.0ダイアログExcel5.0ダイアログシートを用いたセル参照

「Excel5.0ダイアログ」は、古いバージョンのExcelで、現在のユーザーフォーム代わりに使用されていたものです。OptionButtonやTextBox(5.0ではEditBoxと呼ばれる)等が配置でき、マクロで制御します。
ユーザーフォームの方が拡張性が高く、イベントドリブン型のプログラムを使用できるため、私は基本的にフォームを使用していますが、セル参照のための「RefEdit」コントロールはエラーが多いため使用していません。
そのため、プログラムでユーザーにセルを参照してもらう場合、「Excel5.0ダイアログ」使用しています。これはExcel5.0ダイアログで、セル参照するためのメモとサンプルコードです。

ポイント

・Excel5.0ダイアログシート
・選択中のセルアドレスをEditBoxに表示
・EditBoxが空白時の対応例
・セル参照の文字列からブック名・シート名、セルアドレスを取得
・セル参照範囲とデータで使用しているセル範囲の共通セルを取得
・参照先が複数の場合の対応

[サンプルファイル] 42kb
Excel5.0ダイアログシートが挿入されており、サンプルコードが記述されているExcelファイル


Excel5.0ダイアログシートの挿入

1.シートタブを右クリック
2.挿入を選択→「挿入」ダイアログが表示される
3.「MS Excel5.0 ダイアログ」を選択し「OK」

EditBoxの挿入

1.「開発」タブを選択
2.挿入をクリック
3.「フォームコントロール」の「テキストフィールド」を選択
4.ダイアログシートの任意の場所をクリック→EditBoxが挿入される

EditBoxの属性選択

EditBoxを右クリックし「コントロールの書式設定」をクリック
「コントロールの書式設定」ダイアログが表示される


ダイアログの「コントロール」タブを選択し、「入力できるデータ」を「参照先」に設定し「OK」


Excel5.0ダイアログの表示

表示にはマクロを使用。以下はサンプルコード。 ※"Dialog1"はシート名

Sub E5_Dialog_1()
    If ThisWorkbook.Sheets("Dialog1").Show = False Then Exit Sub
End Sub

上記のマクロの実行結果

・ダイアログの「キャンセル」ボタンをクリックすると、「False」が返る。
・セル範囲とは関係のない文字列を入力し「OK」をクリックすると、エラーが表示。

EditBox右端のボタンを押すと、下図のようにダイアログが折りたたむ。


EditBoxに選択中のセルアドレスを表示

ダイアログ表示時に、EditBoxに選択中のセルアドレスを表示するサンプルコード

Sub E5_Dialog_2()
    Dim s As String
    If TypeName(Selection) = "Range" Then
        s = Selection.Areas(1).Address
    End If
    With ThisWorkbook.Sheets("Dialog1")
        .EditBoxes(1).Text = s
        If .Show = False Then Exit Sub
        MsgBox .EditBoxes(1).Text
    End With
End Sub

EditBoxは、上記のEditBoxes(1)のようにIndexで指定。
EditBoxの文字列の取得・設定にはTextプロパティを使用。


EditBoxが空白で「OK」を押した場合の対応

EditBoxが空白だと、再度ダイアログを表示するサンプルコード

Sub E5_Dialog_3()
    With ThisWorkbook.Sheets("Dialog1")
        Do
            If .Show = False Then Exit Sub
            If Len(.EditBoxes(1).Text) <> 0 Then Exit Do
        Loop
        MsgBox .EditBoxes(1).Text '参照元セルアドレス
    End With
End Sub

ダイアログを閉じた時に、EditBoxの値を調べ、空白の場合はDo~Loopで再度表示。


参照したセルアドレスから該当セルのプロパティを取得・設定

参照文字列から、該当セル範囲のセルの個数を取得するサンプルコード

Sub E5_Dialog_4()
    With ThisWorkbook.Sheets("Dialog1")
        Do
            If .Show = False Then Exit Sub
            If Len(.EditBoxes(1).Text) <> 0 Then Exit Do
        Loop
        '参照したセル範囲のセル個数を取得
        MsgBox Application.Range(.EditBoxes(1).Text).Cells.Count
    End With
End Sub

他ブック・他シートのセル参照も可能。


参照したセルアドレスからブック名・シート名・セルアドレスを取得

参照文字列から、ブック名、シート名、セルアドレスを取得

Sub E5_Dialog_5()
    Dim r As Range
    With ThisWorkbook.Sheets("Dialog1")
        Do
            If .Show = False Then Exit Sub
            If Len(.EditBoxes(1).Text) <> 0 Then Exit Do
        Loop
        Set r = Application.Range(.EditBoxes(1).Text)
    End With
    '///ブック名の取得
    MsgBox r.Parent.Parent.Name
    '///シート名の取得
    MsgBox r.Parent.Name
    '///セルアドレスの取得
    MsgBox r.Address
End Sub

ブック名の取得には、セル参照の書式を知る必要がある。

セルの参照例



●ブック名
他ブックを参照した場合は、ブック名の前後に [ ] が付く。

●シート名
他シートを参照した場合、シート名の後に「!」が付く。

●セルアドレス
A1形式で、行列ともに絶対参照の「$」が付く。
複数範囲を選択の場合は「,」で区切られる。

●その他
ブック名、シート名に「'」(シングルクオンテーション)が入っている場合、
ブック名、「!」、「'」それぞれの前に、「'」が追加される。


ワンポイント

セル参照範囲を取得するケースでは、多くの場合データを取得することが目的であると思います。そんな時には「セル参照範囲」と「データで使用しているセル範囲」の共通セルを取得することで、処理の効率化を図れることがあります。

Sub E5_Dialog_6()
    Dim r As Range
    With ThisWorkbook.Sheets("Dialog1")
        Do
            If .Show = False Then Exit Sub
            If Len(.EditBoxes(1).Text) <> 0 Then Exit Do
        Loop
        Set r = Application.Range(.EditBoxes(1).Text)
    End With
    '///セル参照範囲
    MsgBox r.Address
    '///共通セル範囲の取得
    Set r = Intersect(r, r.Parent.UsedRange)
    If r Is Nothing Then MsgBox "共通セルはありません" Else MsgBox r.Address
End Sub

参照元のセル範囲が複数の場合の対応

参照元のセル範囲が、複数の場合の対応処理サンプルコード。セル参照が複数の場合「,」で区切られる。

Sub E5_Dialog_7()
    Dim r As Range, s As String, v As Variant, i As Long
    With ThisWorkbook.Sheets("Dialog1")
        Do
            If .Show = False Then Exit Sub
            If Len(.EditBoxes(1).Text) <> 0 Then Exit Do
        Loop
        s = .EditBoxes(1).Text
    End With
    If 0 = InStr(1, s, ",") Then v = s Else v = Split(s, ",")
    If IsArray(v) Then
        For i = LBound(v) To UBound(v)
            Set r = Application.Range(v(i))
            MsgBox r.Parent.Parent.Name & " " & r.Parent.Name & " " & r.Address
        Next
    Else
        Set r = Application.Range(v)
        MsgBox r.Parent.Parent.Name & " " & r.Parent.Name & " " & r.Address
    End If
End Sub
ページトップへ戻る
Copyright(C) 2009- 坂江保 All Rights Reserved.