メソッドの概要

　他の記事（「EXCEL VBA　シート操作オブジェクト　作成計画」）で掲載した、「シート操作オブジェクト」の1個目のメソッド「列番号取得メソッド」の紹介記事です。

　列名のほか、セル名、名前として定義されたセルの名前（列につけられたものも当然含む）でも列番号を取得できるほか、数値か数字（文字列の数字）にかかわらず、列番号が指定されてもエラーにならずそのまま列番号を返します。

メソッドを作成する理由

　列名から列番号を取得するには、WorksheetオブジェクトのColumnsプロパティを列名を指定して取得し、得られたオブジェクト（Range型）のColumnプロパティで取得できます。

	    Dim ws As Worksheet
	    Set ws = ThisWorkbook.Worksheets("Sheet1")
	    
	    MsgBox ws.Columns("D").Column  '結果 4

　Columnsプロパティは列番号を指定してもエラーにならずにそのまま列番号を取得できます。
ただし、これは数値や数値型の変数で指定した場合です。
文字列の「数字」または、String型で列番号を指定するとエラーになります。

	    Dim int1 As Integer
	    Dim lng1 As Long
	    
	    int1 = 10
	    lng1 = 10
	    
	    MsgBox ws.Columns(10).Column  '結果 10
	    MsgBox ws.Columns(int1).Column  '結果 10
	    MsgBox ws.Columns(lng1).Column  '結果 10
	
	    Dim str1 As String
	    
	    str1 = "10"
	    MsgBox ws.Columns("10").Column  '結果 実行時エラー
	    MsgBox ws.Columns(str1).Column  '結果 実行時エラー

　また、WorksheetオブジェクトのColumnsプロパティは、列名のみの指定なら取得できますが、A1形式のセル名で指定するとエラーになります。

　逆に、WorksheetオブジェクトのRangeプロパティは、A1形式のセル名で指定で取得でき、得られたオブジェクト（Range型）のColumnプロパティで列番号を取得できますが、列名のみの指定はできません。

　今回作成したメソッドでは、

列名（単一列名）での指定（"A"）
複数セル指定方法での列名指定（"A:A"）
数値での列番号指定
数字（文字列）での列番号指定
列ではなく、セル名を指定
定義した「セルの名前」での指定
の、どの指定方法でも、列番号を取得でき、複数列や複数セルの場合も、開始列（もっとも左側の列）の列番号が取得されます。

　つまり、このメソッドを使用すれば、列名だろうがセル名だろうが気にせず列番号を取得でき、
「列名を指定してもらうつもりが列番号を指定されてしまったら？」
といったことも気にせずに済むということです。

　このようなメソッドなどを追加していき、VBAでワークシートを操作する際によくあるんだけど構文があやふやだったりするところを、このシート操作オブジェクトを取り込んでおけば、なるべくつまづかずにコーディングできるようにしていきたいと考えています。

ソースと使用方法

　以下、「EXCEL VBA　シート操作オブジェクト　作成計画」の記事にも記載していますが、ソースと使用方法です。

ソース

　クラスモジュール
　　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

使用方法

　このメソッドを使用するには、シート操作オブジェクトのインスタンスを生成し、必ず対象となるワークシートを設定してください。
対象となるワークシートの設定は、Setを使用した構文でも、Setを使用しない「=」だけの代入構文でもできます。

使用例

Module1



Sub Example1()

    Dim WsCtrl As ttWorkSheetController
    
    Set WsCtrl = New ttWorkSheetController
    
    '必ず対象シートを設定して使用する。
    WsCtrl.Sheet = ThisWorkbook.Worksheets("Sheet1")
'    Set WsCtrl.Sheet = ThisWorkbook.Worksheets("Sheet1") 'これでも設定できる
    
    
    ' "A" 列名単数で指定できる
    Debug.Print WsCtrl.getColumnNumber("A")  '結果  1
    Debug.Print WsCtrl.getColumnNumber("D")  '結果  4
    
     ' "a" 小文字で指定できる
    Debug.Print WsCtrl.getColumnNumber("a")  '結果 1
    Debug.Print WsCtrl.getColumnNumber("b")  '結果  2
    
    ' "A:A" 単数列を複数列形式の列名指定で指定できる
    Debug.Print WsCtrl.getColumnNumber("A:A")  '結果  1
    Debug.Print WsCtrl.getColumnNumber("E:E")  '結果  5
    
    ' 「1」  数値や、Integer型の変数で列番号を指定できる。
    Debug.Print WsCtrl.getColumnNumber(1)  '結果  1
    Debug.Print WsCtrl.getColumnNumber(5)  '結果  5
    
    '  "1" 文字列やString型の変数で列番号を指定できる
    Debug.Print WsCtrl.getColumnNumber("1")  '結果  1
    Debug.Print WsCtrl.getColumnNumber("10")  '結果  10
    
    '  セルを指定できる
    Debug.Print WsCtrl.getColumnNumber("A1")  '結果  1
    Debug.Print WsCtrl.getColumnNumber("C4")  '結果  3
    
    '  セルの名前で指定できる （名前を定義しておく）
    Debug.Print WsCtrl.getColumnNumber("列_L")  '結果 12  「名前の定義」で「Sheet1!$L:$L」に「列_L」と設定
    Debug.Print WsCtrl.getColumnNumber("領域1")  '結果 4 「名前の定義」で「Sheet1!$D$4:$F$8」に「領域1」と設定
    
    '  複数の列を指定できる   （左端の列の列番号を返す）
    Debug.Print WsCtrl.getColumnNumber("A:C")  '結果  1
    Debug.Print WsCtrl.getColumnNumber("F:J")  '結果  6
    
    '  複数のセルを指定できる （左端の列の列番号を返す）
    Debug.Print WsCtrl.getColumnNumber("A1:C3")  '結果  1
    Debug.Print WsCtrl.getColumnNumber("b4:E100")  '結果  2
    
    
    '  存在しない列、存在しないセル、などを指定した場合、エラーとしてマイナスの値を返す
    '　（エラーにはならないので、処理は続行される）
    Debug.Print WsCtrl.getColumnNumber("XFF")  '結果 -3
    Debug.Print WsCtrl.getColumnNumber("XFF1")  '結果 -3
    Debug.Print WsCtrl.getColumnNumber(20000)  '結果 -1
    Debug.Print WsCtrl.getColumnNumber(0)  '結果 -1
    Debug.Print WsCtrl.getColumnNumber(-10)  '結果 -1
    
    
End Sub