セル関連処理

セル位置情報初期化

セル位置情報を初期化する。
セル範囲指定時はセル範囲値を反映する。

'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
' セル関連処理
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
' 位置情報初期化
'------------------------------------------------------------------------------
' 行位置初期化
Public Property Get G_Excel_InitPosRowInf( _
        Optional ByVal aRg As Range = Nothing) As T_EXCEL_POS_ROW_INF
    Dim wkRtn As T_EXCEL_POS_ROW_INF
    
    If aRg Is Nothing Then
        wkRtn.Stt = D_EXCEL_ROW_NONE
        wkRtn.End = D_EXCEL_ROW_NONE
        wkRtn.Cnt = 0
    Else
        With aRg
            wkRtn.Stt = .Row
            wkRtn.Cnt = .Rows.Count
            wkRtn.End = .Row + wkRtn.Cnt - 1
        End With
    End If
    
    G_Excel_InitPosRowInf = wkRtn
End Property

' 列位置初期化
Public Property Get G_Excel_InitPosClmInf( _
        Optional ByVal aRg As Range = Nothing) As T_EXCEL_POS_CLM_INF
    Dim wkRtn As T_EXCEL_POS_CLM_INF
    
    If aRg Is Nothing Then
        wkRtn.Stt = D_EXCEL_CLM_NONE
        wkRtn.End = D_EXCEL_CLM_NONE
        wkRtn.Cnt = 0
    Else
        With aRg
            wkRtn.Stt = .Column
            wkRtn.Cnt = .Columns.Count
            wkRtn.End = .Column + wkRtn.Cnt - 1
        End With
    End If
    
    G_Excel_InitPosClmInf = wkRtn
End Property

Public Property Get G_Excel_InitPosInf( _
        Optional ByVal aRg As Range = Nothing) As T_EXCEL_POS_INF
    Dim wkRtn As T_EXCEL_POS_INF
    
    With wkRtn
        .Row = G_Excel_InitPosRowInf(aRg)
        .Clm = G_Excel_InitPosClmInf(aRg)
    End With
    
    G_Excel_InitPosInf = wkRtn
End Property

セル範囲値取得

指定したセル範囲値の値を配列で返却する

'------------------------------------------------------------------------------
' セル範囲値取得
'------------------------------------------------------------------------------
Public Function F_Excel_ReturnRangeValueArray( _
        ByVal aRg As Range) As Variant
    Dim wkRtnAry As Variant
    
    If aRg Is Nothing Then
        Exit Function
    End If
    
    wkRtnAry = aRg.Value
    '配列でない場合
    If IsArray(wkRtnAry) <> True Then
        ReDim wkRtnAry(D_EXCEL_ROW_START To D_EXCEL_CLM_START)
        wkRtnAry(D_EXCEL_ROW_START, D_EXCEL_CLM_START) = aRg.Value
    End If
    
    F_Excel_ReturnRangeValueArray = wkRtnAry
End Function

オートフィルタ設定

オートフィルタを設定する

'------------------------------------------------------------------------------
' オートフィルタ設定
'------------------------------------------------------------------------------
Public Sub S_Excel_SetAutoFilter( _
        Optional ByVal aRg As Range = Nothing, _
        Optional ByVal aSh As Worksheet = Nothing)
    Dim wkSh As Worksheet: Set wkSh = aSh
    Dim wkRg As Range: Set wkRg = aRg
    
    If Not wkRg Is Nothing Then
        Set wkSh = wkRg.Worksheet
    Else
        If wkSh Is Nothing Then
            Set wkSh = ActiveSheet
        End If
        wkRg = wkSh.UsedRange
    End If
    
    'オートフィルタが設定されている場合は一旦解除
    If wkSh.AutoFilterMode = True Then
        wkSh.Cells.AutoFilter
    End If
    
    'オートフィルタ設定
    wkRg.AutoFilter
End Sub