メソッドの概要
「EXCEL VBA シート操作オブジェクト 作成計画」で掲載した「シート操作オブジェクト」の2個目のメソッドとなる「列名取得メソッド」です。列番号を指定してメソッドを呼び出すと、戻り値として列名を返します。
メソッドを作成する理由
第１弾で「列番号取得メソッド」を作成したので、第２弾は列名取得メソッドを作成しました。
列名から列番号は、EXCEL VBA既存のプロパティで取得できますが、列番号から列名は既存のプロパティで簡単には取得できなかったような覚えがあるので、いくらか需要があるのではないかと思います。
ソースと使用方法
本来は、「シート操作オブジェクト」のクラスモジュールである「ttWorkSheetController」に追記するメソッドですが、「列名取得メソッド」を確認しやすくするために、先に、「Class1」モジュールに「列名取得メソッド：getColumnName」のみを書いたソースを掲載します。
「getColumnName」メソッドを単体で動作確認するために、「m_WorkSheet」をPublic変数として定義していますが、本来の「ttWorkSheetController」クラスでは、すでに対象ワークシートをプロパティとして設定できるようになっており、追記する必要はないです。
今回の「getColumnName」メソッドを追加した、本来の「シート操作オブジェクト」のクラスモジュール「ttWorkSheetController」は、一番下に記載します。
ソース
Class1
Class1
Option Explicit
'単体として動かすための一時的なプロパティ
Public m_WorkSheet As Worksheet
Public Function getColumnName(ColNumber As Integer, Optional relative_or_absolute As Integer = 1 _
, Optional nameOnly_or_rangeAddr As Integer = 1)
'引数に指定された列番号の列の、列名を返す
' 引数 ColNumber:対象列の列番号
' relative_or_absolute:戻り値が相対参照形式か絶対参照形式か
' 1:相対参照、2;絶対参照 それ以外は相対参照
' nameOnly_or_rangeAddr:戻り値が列名単独か、範囲型か
' 1:列名単独、2:範囲型 それ以外は相対参照
Dim wkColStr As String
getColumnName = ""
If (m_WorkSheet Is Nothing) Then Err.Raise Number:=10011, Description:="対象シートが設定されていません。"
wkColStr = m_WorkSheet.Columns(ColNumber).Address
'相対参照（"$"なし）、か絶対参照（"$"つき）か
If relative_or_absolute = 1 Then
'相対参照（"$"なし）
wkColStr = Replace(wkColStr, "$", "")
ElseIf relative_or_absolute = 2 Then
'絶対参照（"$"つき）
wkColStr = wkColStr
Else
wkColStr = Replace(wkColStr, "$", "")
End If
'列名一文字(A)か、列範囲のアドレス(A:A)か
If nameOnly_or_rangeAddr = 1 Then
'列名一文字(A)
wkColStr = Split(wkColStr, ":")(0)
ElseIf nameOnly_or_rangeAddr = 2 Then
'列を範囲とした形式のアドレス(A:A)
wkColStr = wkColStr
Else
wkColStr = Split(wkColStr, ":")(0)
End If
getColumnName = wkColStr
End Function
使用方法
引数は3つで、必須は1つ、省略可能が2つです。
| 引数 | 説明 |
|---|---|
| ColNumber | 対象となる列番号を指定します。 |
| relative_or_absolute | 相対参照形式で取得するか絶対参照形式するかを指定します。 「1」:相対参照形式です。「A」や「A:A」の形式です。 「2」:絶対参照形式です。「\$A」や「\$A:\$A」の形式です。 省略可能です。省略すると「1:相対参照形式」です。 |
| nameOnly_or_rangeAddr | 列名単独の形式か、範囲としての形式かです。 「1」:列名単独として返します。「A」や「\$A」の形式です。 「2」:範囲の形式として返します（内容としては単独列です）。「A:A」や「\$A:\$A」の形式です。 省略可能です。省略すると「1:列名単独」です。 |
使用例
Module1
Sub Example1()
'統合された本来のttWorkSheetControllerの場合はこの宣言
' Dim WsCtrl As ttWorkSheetController
'統合された本来のttWorkSheetControllerの場合のインスタンス生成
' Set WsCtrl = New ttWorkSheetController
'メソッド単独の動作確認のため、テスト用「Class1」を使用する
Dim WsCtrl As Class1
Set WsCtrl = New Class1
'統合された本来のttWorkSheetControllerは、この書き方で必ず対象シートを設定して使用する。
' WsCtrl.Sheet = ThisWorkbook.Worksheets("Sheet1")
'' Set WsCtrl.Sheet = ThisWorkbook.Worksheets("Sheet1") 'これでも設定できる
'今回は、メソッド単独の動作確認のため、この書き方になる
Set WsCtrl.m_WorkSheet = ThisWorkbook.Worksheets("Sheet1")
'列名単独 相対参照形式 ("A"の形式 もっとも基本的と思われる使い方)
MsgBox WsCtrl.getColumnName(5) '結果 「E」が表示される
MsgBox WsCtrl.getColumnName(5, 1) '結果 「E」が表示される
'列範囲型 相対参照形式 ("A:A"の形式)
MsgBox WsCtrl.getColumnName(5, 2) '結果 「$E」が表示される
'列名単独 絶対参照形式 ("$A"の形式)
MsgBox WsCtrl.getColumnName(5, , 2) '結果 「E:E」が表示される
MsgBox WsCtrl.getColumnName(5, 1, 2) '結果 「E:E」が表示される
'列範囲型 絶対参照形式 ("$A:$A"の形式)
MsgBox WsCtrl.getColumnName(5, 2, 2) '結果 「$E:$E」が表示される
End Sub
本来の「シート操作オブジェクト」クラスモジュールに追加
今回の「getColumnName」メソッドを追加したクラスモジュール「ttWorkSheetController」を掲載します。コードが長いため、折りたたみます。
コードを表示する
ttWorkSheetController
Option Explicit
Private m_WorkSheet As Worksheet
Public Property Get Sheet() As Worksheet
Set Sheet = m_WorkSheet
End Property
Public Property Let Sheet(sh As Worksheet)
Set m_WorkSheet = sh
End Property
Public Property Set Sheet(sh As Worksheet)
Set m_WorkSheet = sh
End Property
Public Function getColumnNumber(ColStr As String) As Integer
'列名などの文字列から列番号を取得
' 列名だけでなく、列番号（数値型、文字列型を問わない）でも可能。
' 列ではなくセルでも可能（列番号のみ返す）。
' 定義されたセルの名前でも可能。
' 複数列、複数セルの場合は、開始列（左端の列）の列番号を返す。
' 存在しない列、セルは、エラーとする。
' エラーは、負の整数（マイナスの値）を返す
'
Dim wkColNum As Integer
Dim wknum As Integer
Dim errNo As Integer
getColumnNumber = -99
wknum = -98: errNo = 0
If (m_WorkSheet Is Nothing) Then Err.Raise Number:=10011, Description:="対象シートが設定されていません。"
If IsNumeric(ColStr) = True Then
'数値の場合
'Columnsプロパティにより列番号の取得を試みる
On Error Resume Next
wknum = CInt(ColStr)
wkColNum = m_WorkSheet.Columns(wknum).Column
errNo = Err.Number
On Error GoTo 0
If errNo <> 0 Then
'エラーとする
Select Case errNo
Case 1004
wkColNum = -1
Case Else
wkColNum = -2
End Select
End If
Else
'数値以外の場合
'Columnsプロパティにより列番号の取得を試みる
On Error Resume Next
wkColNum = m_WorkSheet.Columns(ColStr).Column
errNo = Err.Number
On Error GoTo 0
If errNo <> 0 Then
'Rangeプロパティで列番号の取得を試みる
errNo = 0
On Error Resume Next
wkColNum = m_WorkSheet.Range(ColStr).Column
errNo = Err.Number
On Error GoTo 0
If errNo <> 0 Then
wkColNum = -3
End If
End If
End If
getColumnNumber = wkColNum
End Function
Public Function getColumnName(ColNumber As Integer, Optional relative_or_absolute As Integer = 1 _
, Optional nameOnly_or_rangeAddr As Integer = 1)
'引数に指定された列番号の列の、列名を返す
' 引数 ColNumber:対象列の列番号
' relative_or_absolute:戻り値が相対参照形式か絶対参照形式か
' 1:相対参照、2;絶対参照 それ以外は相対参照
' nameOnly_or_rangeAddr:戻り値が列名単独か、範囲型か
' 1:列名単独、2:範囲型 それ以外は相対参照
Dim wkColStr As String
getColumnName = ""
If (m_WorkSheet Is Nothing) Then Err.Raise Number:=10011, Description:="対象シートが設定されていません。"
wkColStr = m_WorkSheet.Columns(ColNumber).Address
'相対参照（"$"なし）、か絶対参照（"$"つき）か
If relative_or_absolute = 1 Then
'相対参照（"$"なし）
wkColStr = Replace(wkColStr, "$", "")
ElseIf relative_or_absolute = 2 Then
'絶対参照（"$"つき）
wkColStr = wkColStr
Else
wkColStr = Replace(wkColStr, "$", "")
End If
'列名一文字(A)か、列範囲のアドレス(A:A)か
If nameOnly_or_rangeAddr = 1 Then
'列名一文字(A)
wkColStr = Split(wkColStr, ":")(0)
ElseIf nameOnly_or_rangeAddr = 2 Then
'列を範囲とした形式のアドレス(A:A)
wkColStr = wkColStr
Else
wkColStr = Split(wkColStr, ":")(0)
End If
getColumnName = wkColStr
End Function