20240412 エクセルVBAクラス備忘録 シートからコレクション
エクセルVBA オブジェクト指向備忘録クラス シートからコレクション(名前付きセルの名前と値の取得)
＜前書き＞
エクセルVBAオブジェクト指向、初心者です。
VBA自体は、以前から使用していました。
オブジェクト指向というものがあることは、知っていましたが、ネットなどを見てもよく分からず、挫折した過去あり。
＜以上前書き＞
前回の備忘録に、「エクセルは、オブジェクト指向と相性がいいのかも」と書いたことなどを書きます。
自分用のメモです。間違っていたらすみません。汗
今回、シートからのデータの取得について、書きます。
「手続き型」でもいろんな方法が考えられます。
（配列を使ったり、別シートに転記したり。いろいろ。）
前提：
シート名「入力シート」
B4セルに「入力欄1」、
E3セルに「入力欄2」を用意します。
中のセルには、（何でもいいのですが、）
B4セルに「犬」、
E3セルに「猫」と入力しておきます。
これを取り出すためのコードを書く前に、今回、これらのセルに名前を付けて、その名前をfor each文で回して取得することを考えました。
まず、注意ですが、セルに名前を付ける場合、「数式タブ」「名前の管理」から「新規作成」で行って下さい。
この場合、範囲を「入力シート」に限定して下さい。範囲「ブック」全体にすると、うまくfor each文で取得できませんでした。
（ちなみに、名前ボックスから、セルに名前を付けると、範囲が「ブック」全体になってしまいます。）
で、最初に、作ったコードが下記です。（今回、「Bing様」に聞いたりしているので、変数名がおかしくすみません。）
------------------------------
Sub GetAllNamedCells1() 'nameオブジェクトを取っている
Dim ws As Worksheet
Dim namedCell As Name '名前付きセル操作
' アクティブなシートを取得
Set ws = ActiveSheet
' シート内の名前付きセルをループ
For Each namedCell In ws.Names
' 名前を表示
MsgBox namedCell.Name '入力シート!入力欄1
MsgBox namedCell.value 'これだと、=入力シート!$E$3
Stop
Next namedCell
Stop
End Sub
---------------------------
コメントに書きましたが、
MsgBox namedCell.Name '入力シート!入力欄1
MsgBox namedCell.value 'これだと、=入力シート!$E$3
という感じで、セルの値である「犬」が取れません。
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
で、次に作ったコードがこれ。
--------------------------------
Sub GetNamedCellValues1_2()
Dim ws As Worksheet
Dim namedCell As Name
Dim cellValue As Variant
' アクティブなシートを取得
Set ws = ActiveSheet
' シート内の名前付きセルをループ
For Each namedCell In ws.Names
' 名前付きセルの値を取得
cellValue = namedCell.RefersToRange.value
' 名前と値を表示
MsgBox "名前: " & namedCell.Name & vbNewLine & "値: " & cellValue
Next namedCell
End Sub
--------------------------------
これだと、
名前：入力シート!入力欄1
値：「犬」
が取れます。
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
で、上記をコレクションに入れたいと思って作ったコードがこれ。
--------------------------------
Sub GetAllNamedCells2() 'nameオブジェクトを取っている
Dim ws As Worksheet
Dim namedCell As Name
Dim namedCellValues As New Collection ' 新しいコレクションを作成
' アクティブなシートを取得
Set ws = ActiveSheet
' シート内の名前付きセルをループ
For Each namedCell In ws.Names
' 名前付きセルの値をコレクションに追加
namedCellValues.Add namedCell.RefersToRange.value, Key:=namedCell.RefersToRange.Name
Next namedCell
Stop
End Sub
--------------------------------
上記のstopの時点で、ローカルウィンドウを使い、namedCellValuesを見てみると、
: namedCellValues : : Collection/Collection
: Item 1 : "犬" : Variant/String
: Item 2 : "猫" : Variant/String
となっています。
コレクションではありますが、
name名前：入力シート!入力欄1
value値：「犬」
ではありません。
こうなっているのは、Dim namedCell As Nameで、nameオブジェクトを取っているからなのでしょう。
（stringなど）
で、For Each文にebug.Printを入れてみます。
Debug.Print namedCell.Name '入力シート!入力欄1①
Debug.Print namedCell.RefersToRange.value '犬②
Debug.Print namedCell.RefersToRange.Name '=入力シート!$B$4③
と出ます。
上記のコレクションでは、①と②が欲しいのに②しか取れていないようです。
（厳密には③も持っています。）
Debug.Print namedCellValues.Item("=入力シート!$B$4") '犬②
'addでnameオブジェクトを取得してる。(当該セルの情報)
'nameオブジェクトには、名前付きセルのプロパティはない。(①は持てない)
という感じかなと思いました。
＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋
で、いろいろした挙句、下記のコードにしました。（日本語の変数名も混ざってすみません。）
名前付きセルの名前と値をコレクションに取れるようにクラス「セルクラス」を作成しました。
-----------------------
クラス「セルクラス」
Option Explicit
Public 値 As String
Public セル名 As String
----------------------
Sub GetAllNamedCells3()
Dim ws As Worksheet
Dim namedCell As Name
Dim namedCellValues As Collection
Set namedCellValues = New Collection ' 新しいコレクションを作成
' アクティブなシートを取得
Set ws = ActiveSheet
' シート内の名前付きセルをループ
For Each namedCell In ws.Names
'ここで、クラスを作る
Dim vセルクラス As セルクラス
Set vセルクラス = New セルクラス
vセルクラス.セル名 = namedCell.Name
vセルクラス.値 = namedCell.RefersToRange.value
' 名前付きセルの値をコレクションに追加
namedCellValues.Add vセルクラス, vセルクラス.セル名
Next namedCell
Stop
End Sub
-----------------------------
コレクションに、namedCellを直接addせずに、クラスを使って、必要な情報（string）を入れました。
: namedCellValues : : Collection/Collection
: Item 1 : : Variant/Object/セルクラス
: セル名 : "入力シート!入力欄1" : String
: 値 : "犬" : String
: Item 2 : : Variant/Object/セルクラス
: セル名 : "入力シート!入力欄2" : String
: 値 : "猫" : String
こんな感じに入っています。
コード自体は、過去に書いたように、もっとクラス側に寄せた方がいいと思います。
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
で、思ったことは、「エクセルはオブジェクトの集合」で、それを使わない手はないということ。
ただ、オブジェクトでも何でもかんでもプロパティを持っているわけではない。
（オブジェクトブラウザとかを活用する。）
必要なものはクラスを作って、なんとかする。汗
この記事に間違いがたくさんあると思いますが、今はこんな感じ（認識）です。
コードには、
Dim namedCell As 「Name」
For Each namedCell In ws.「Names」
しれっと、「Name」「Names」とか微妙なものも出ています。（理解が足りない。）
name は、ワークブック内の名前付き範囲や名前付きセルを指します。
names は、ワークブック内のすべての名前付き範囲や名前付きセルのコレクションを表します。
ということだったり、
cell は、単一のセルを指します。たとえば、Range("A1") はシート上のセル A1 を表します。
cells は、複数のセルを指定するためのメソッドです。たとえば、Range("A1:B5") はセル範囲 A1 から B5 を表します。
というようなことをbing様は教えてくれました。（合っているのか？汗）
今後は、もっと、既存の「オブジェクト」を理解することが必要なのかなと思っています。
