見出し画像

【Excel VBA】開発を効率化！表データを扱うクラスモジュール

おくやん

こんにちは、おくやんです。
Excel VBAの開発効率を大幅に向上させる、自作のライブラリクラスをご紹介しています。

今回はWorksheetオブジェクトを拡張した、表形式のデータを扱うために特化したライブラリクラスのご紹介です。

実際の開発現場でも20以上のツールに利用した実績のあるライブラリとなりますので、皆さんもぜひ活用してみて下さい。

サンプルプログラムを下記で紹介しています。
本記事と合わせて読んでみて下さい。



名称

LibWorkSheetクラス

概要

  • Worksheetオブジェクトを拡張し、表形式のデータを扱うために特化したクラス

  • 1つの表に対して、1つのインスタンスを生成する

  • 対象のシート名やヘッダーの開始行／開始列（省略可能）を指定し、表データの読み込みや書き込みを行う

関連クラス

  • LibWorkBookクラスのインストールが必要

簡単な使い方

データを読み込む

表形式のデータを読み込み、値を取得する。

読込対象の表データ
Dim libWs As LibWorkSheet: Set libWs = New LibWorkSheet
libWs.Init "table1"

' 読み込み
Debug.Print libWs.Val("1列目") '=> あ
Debug.Print libWs.Val("2列目") '=> い
Debug.Print libWs.Val("3列目") '=> う

' 次の行を読み込み
libWs.ReadNext
Debug.Print libWs.Val(1) '=> a
Debug.Print libWs.Val(2) '=> b
Debug.Print libWs.Val(3) '=> c

' 指定行を読み込み
libWs.ReadRow = 4
Debug.Print libWs.Val("A") '=> 1
Debug.Print libWs.Val("B") '=> 2
Debug.Print libWs.Val("C") '=> 3

データを書き込む

表形式のデータに値を書き込む。

Dim libWs As LibWorkSheet: Set libWs = libWb.Sheet("table1")

' 読み込み
Debug.Print libWs.Val("1列目") '=> あ
Debug.Print libWs.Val("2列目") '=> い
Debug.Print libWs.Val("3列目") '=> う

' 書き込み
libWs.Val("1列目") = "か"
libWs.Val("2列目") = "き"
libWs.Val("3列目") = "く"

' 読み込み
Debug.Print libWs.Val("1列目") '=> か
Debug.Print libWs.Val("2列目") '=> き
Debug.Print libWs.Val("3列目") '=> く
書き込み結果

プロパティ定義

Ws() As Worksheet

  • 対象シートのWorksheetオブジェクトを返す

SheetName = sheet_name As String

  • 対象シートのシート名をsheet_nameにする

SheetName() As String

  • 対象シートのシート名を返す

HeaderRow = header_row As Long

  • 表のヘッダー行数をheader_rowに設定する

  • 初期値は1行目

  • ０以下の値を設定するとヘッダーなしとして動作する

HeaderRow() As Long

  • 表のヘッダー行数を返す

StartCol = start_column As Long

  • 表の開始列数をstart_columnに設定する

  • 初期値は1列目

StartCol() As Long

  • 表の開始列数を返す

ColNames() As Variant

  • 表の列名を配列形式で返す

  • ヘッダーなしの場合は「X列目（X: 列番号）」という文字列配列が返る

KeyCol = key_column As Variant

  • 表の主キーをkey_columnに設定する

  • 列名、列番号、アルファベット指定が可能

KeyCol() As Variant

  • 表の主キーとなるカラム名を返す

ReadRow = read_row As Long

  • 読み込み行数をread_rowに設定する

ReadRow() As Long

  • 現在の読み込み行数を返す

WriteRow = write_row As Long

  • 書き込み行数をwrite_rowに設定する

WriteRow() As Long

  • 現在の書き込み行数を返す

ColNum(column_name) As Long

  • 列名から列番号を取得する

  • [PARAM] column_name As Variant

    • 列を指定する

    • 列名、列番号、アルファベット指定が可

ColName(column_number) As String

  • 列番号から列名を取得する

  • [PARAM] column_number As Long

    • 列番号を指定する

MaxRow(target_column) As Long

  • 表の最大行を返す

  • [PARAM] Optional target_column As Variant = ""

    • 最大行を確かめるため、基準とする列を指定する（列名、列番号、アルファベット指定が可能）

    • 未指定の場合は主キー（KeyCol）に設定された列を基準列とする

    • 主キー未設定の場合は表の開始列数（StartCol）を基準列とする

MaxCol(target_row) As Long

  • 表の最大列を返す

  • [PARAM] Optional target_row As Long = 0

    • 最大列を確かめるため、基準とする行を指定する

    • 未指定の場合はヘッダー行数（HeaderRow）に設定された行を基準行とする

    • ヘッダーがない場合は1行目を基準行とする

Val(target_column) As Variant

  • 現在の読み込み行数（ReadRow）から指定された列の値を返す

  • [PARAM] target_column As Variant

    • 読み込み対象の列を指定する

    • 列名、列番号、アルファベット指定が可能

Val(target_column) = write_val As Variant

  • 現在の書き込み行数（WriteRow）の指定された列にwrite_valを書込む

  • [PARAM] target_column As Variant

    • 書き込み対象の列を指定する

    • 列名、列番号、アルファベット指定が可能

Line() As Dictionary

  • 現在の読み込み行（ReadRow）を1行分読み込み、連想配列形式で返す

Eof() As Boolean

  • 現在の読み込み行数（ReadRow）が表の最大行を超えているかを返す

関数定義

Init(sheet_name, start_header_row, start_header_col, key_column, wb_instance)

  • 対象の表がある対象のシート名やヘッダーの開始行／開始列などを指定する初期化関数

  • インスタンス生成後必ず実行する必要がある

  • [PARAM] sheet_name As String

    • 対象の表があるシート名を指定する

  • [PARAM] Optional start_header_row As Long = 1

    • 対象の表のヘッダー行数を指定する

    • 省略した場合は1が設定される

  • [PARAM] Optional start_header_col As Long = 1

    • 対象の表の開始列を指定する

    • 省略した場合は1が設定される

  • [PARAM] Optional key_column As Variant = Empty

    • 対象の表の主キーを指定する

    • 省略した場合は主キー設定は行われない

  • [PARAM] Optional wb_instance As Workbook

    • 対象の表があるWorkbookオブジェクトを指定する

    • 省略された場合はThisWorkbookが設定される

ReadNext()

  • 読み込み行数を次の行に移動する

ReadFirst()

  • 読み込み行数を先頭行に移動する

WriteNext()

  • 書き込み行数を次の行に移動する

WriteFirst()

  • 書き込み行数を先頭行に移動する

ソースコード

Visual Basic Editorからクラスモジュール LibWorkSheetを追加して、下記コードを張り付けてください。

Option Explicit

' メンバー定義
Private ws_ As Worksheet
Private headerRow_ As Long
Private startCol_ As Long
Private keyCol_ As Variant
Private rRow_ As Long
Private wRow_ As Long

' Property定義
Private Property Set Ws(ws_instance As Worksheet)
  Set ws_ = ws_instance
End Property

Public Property Get Ws() As Worksheet
  Set Ws = ws_
End Property

Public Property Let SheetName(sheet_name As String)
  Ws.name = sheet_name
End Property

Public Property Get SheetName() As String
  SheetName = Ws.name
End Property

Public Property Let HeaderRow(header_row As Long)
  headerRow_ = IIf(header_row <= 0, 0, header_row)
  ReadRow = HeaderRow + 1
  WriteRow = HeaderRow + 1
End Property

Public Property Get HeaderRow() As Long
  HeaderRow = headerRow_
End Property

Public Property Let StartCol(start_column As Long)
  startCol_ = IIf(start_column <= 1, 1, start_column)
End Property

Public Property Get StartCol() As Long
  StartCol = startCol_
End Property

Public Property Get ColNames() As Variant
  Dim tmpNames As Variant: ReDim tmpNames(MaxCol - StartCol)
  Dim i As Long
  For i = LBound(tmpNames) To UBound(tmpNames)
    If HeaderRow > 0 Then
      tmpNames(i) = Ws.Cells(HeaderRow, StartCol + i).value
    Else
      tmpNames(i) = "列" & str(i + 1)
    End If
  Next
  ColNames = tmpNames
End Property

Public Property Let KeyCol(key_column As Variant)
  keyCol_ = key_column
End Property

Public Property Get KeyCol() As Variant
  KeyCol = keyCol_
End Property

Public Property Let ReadRow(read_row As Long)
  rRow_ = read_row
End Property

Public Property Get ReadRow() As Long
  ReadRow = rRow_
End Property

Public Property Let WriteRow(write_row As Long)
  wRow_ = write_row
End Property

Public Property Get WriteRow() As Long
  WriteRow = wRow_
End Property

' 列名から列番号を取得
Public Property Get ColNum(column_name As Variant) As Long
  ColNum = GetColNum(column_name)
End Property

' カラム名取得
Public Property Get ColName(column_number As Long) As String
  ColName = ColNames(column_number - StartCol)
End Property

' 対象シートの最大行を取得
Public Property Get MaxRow(Optional target_column As Variant = "") As Long
  Dim tmpTargetCol As Long
  If target_column <> "" Then
    tmpTargetCol = GetColNum(target_column)
  ElseIf Not IsEmpty(KeyCol) Then
    tmpTargetCol = GetColNum(KeyCol)
  Else
    tmpTargetCol = StartCol
  End If

  MaxRow = Ws.Cells(Ws.Rows.Count, tmpTargetCol).End(xlUp).Row
End Property

' 対象シートの最大列を取得
Public Property Get MaxCol(Optional target_row As Long = 0) As Long
  Dim tmpTargetRow As Long
  If target_row > 0 Then
    tmpTargetRow = target_row
  ElseIf HeaderRow > 0 Then
    tmpTargetRow = HeaderRow
  Else
    tmpTargetRow = 1
  End If

  MaxCol = Ws.Cells(tmpTargetRow, Ws.columns.Count).End(xlToLeft).Column
End Property

' 読込み処理
Public Property Get Val(target_column As Variant) As Variant
  Val = Ws.Cells(ReadRow, GetColNum(target_column)).value
End Property

Public Property Get Line() As Object
  Dim tmpLine As Object: Set tmpLine = CreateObject("Scripting.Dictionary")
  Dim readColumns As Variant: readColumns = ColNames
  Dim readColumn As Variant
  For Each readColumn In readColumns
    tmpLine.Add readColumn, Val(readColumn)
  Next
  
  Set Line = tmpLine
End Property

' 書込み処理
Public Property Let Val(target_column As Variant, write_val As Variant)
  Ws.Cells(WriteRow, GetColNum(target_column)) = write_val
End Property

' 読込み終了確認処理
Public Property Get Eof() As Boolean
  Eof = (MaxRow < ReadRow)
End Property


' Initialize定義
Sub class_initialize()
  StartCol = 1
End Sub

' Public Function定義
' 初期化処理
Public Function Init( _
  sheet_name As String, _
  Optional start_header_row As Long = 1, _
  Optional start_header_col As Long = 1, _
  Optional key_column As Variant = Empty, _
  Optional wb_instance As Workbook _
)
  Dim libWb As LibWorkBook: Set libWb = CreatWb(wb_instance)
  If Not libWb.HasSheet(sheet_name) Then Err.Raise _
                                           number:=10101, _
                                           description:="指定されたシート名が存在しません[列名：" & sheet_name & "]"
  Set Ws = libWb.Wb.Sheets(sheet_name)
  HeaderRow = start_header_row
  StartCol = start_header_col
  KeyCol = key_column
End Function

' 読込み行移動処理
Public Function ReadNext()
  ReadRow = ReadRow + 1
End Function

' 読込み行初期化処理
Public Function ReadFirst()
  ReadRow = HeaderRow + 1
End Function

' 書込み行移動処理
Public Function WriteNext()
  WriteRow = WriteRow + 1
End Function

' 書込み行初期化処理
Public Function WriteFirst()
  WriteRow = HeaderRow + 1
End Function

' Private Function定義
' 列名または引数から列番号を取得する処理
Private Function GetColNum(col_name As Variant) As Long
  Dim tmpCol As Long
  If HasColName(col_name) Then
    ' 列名指定
    tmpCol = ColNumByName(col_name)
  ElseIf IsNumeric(col_name) Then
    ' 数値指定
    tmpCol = Int(col_name)
  ElseIf CheckAlphabetCol(col_name) Then
    ' アルファベット指定
    tmpCol = Ws.Range(col_name & "1").Column
  End If
  
  If tmpCol <= 0 Or 16384 < tmpCol Then Err.Raise number:=10100, description:="指定された列が不正です[列：" & col_name & "]"
  GetColNum = tmpCol + (StartCol - 1)
End Function

' 列名取得
Private Function ColNumByName(col_name) As Long
  Dim tmpColNames As Variant: tmpColNames = ColNames
  Dim i As Long
  For i = LBound(tmpColNames) To UBound(tmpColNames)
    If tmpColNames(i) = col_name Then Exit For
  Next
  
  ColNumByName = i + 1
End Function

' 列名指定判定
Private Function HasColName(col_name) As Boolean
  Dim tmpColNames As Variant: tmpColNames = ColNames
  HasColName = (ColNumByName(col_name) <= UBound(tmpColNames) + 1)
End Function

' アルファベット列指定判定
Private Function CheckAlphabetCol(check_val As Variant) As Boolean
  If Len(check_val) <= 0 Or 3 < Len(check_val) Then Exit Function
  
  Dim i As Long
  For i = 1 To Len(check_val)
    If Not (Mid(check_val, i, 1) Like "[A-Z]") Then Exit Function
  Next
  
  If Len(check_val) = 3 And Left(check_val, 1) Like "[X-Z]" Then Exit Function
  
  CheckAlphabetCol = True
End Function

' Excel設定作成処理
Private Function CreatWb(base_wb As Workbook) As LibWorkBook
  Dim wbInstance As Workbook
  If base_wb Is Nothing Then
    Set wbInstance = ThisWorkbook
  Else
    Set wbInstance = base_wb
  End If

  Dim libWb As LibWorkBook: Set libWb = New LibWorkBook
  Call libWb.Init(wbInstance)
  Set CreatWb = libWb
End Function

この記事が気に入ったらサポートをしてみませんか？