計画の背景

　VBAでワークシートを操作する時に、結構よく使用する構文なのだけど、そのたびに調べなくてはならないことがよくありました。
　また、構文は覚えてるのだけどそのたびに書かなくてはならなかったり、関数としてまとめてはいるのだけど関数を利用するのにちょっと手間がかかる、ということもよくありました。

計画の内容

　そこで、ワークシートの操作でよく使用する構文をオブジェクトにまとめ、このオブジェクトのクラスモジュールを丸ごと取り込んむことで、これらの構文を簡単に利用できるようにしようと計画しています。
　このオブジェクトを「シート操作オブジェクト」と名付けて、オブジェクトのクラスを「ttWorkSheetController」として、クラスモジュールにまとめていこうと計画しています。

オブジェクト1個目のメソッド

　第一弾として、列番号の取得関数を作成し、このオブジェクトのメソッドにしてみます。列番号の取得のメソッドについては、ほかの記事で詳しく書こうと思ってます。説明とソースのみ記載しておきます。

列番号取得メソッドの説明

列番号の取得メソッドは、

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

ソース

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