SheetBuffer

Option Explicit

Private BufferSheet As Excel.Worksheet
Private Buffer() As Variant
Private BufferRows As Long
Private BufferColumns As Long
Private BufferBeginRow As Long
Private BufferBeginColumn As Long
Private BufferEndRow As Long
Private BufferEndColumn As Long
Private BufferBeginRowS1 As Long
Private BufferBeginColumnS1 As Long
Private EditRows As Long
Private EditColumns As Long

'=======================================
'   Initialize / Terminate
'=======================================
Private Sub Class_Initialize()
    
    Set BufferSheet = Nothing
    
End Sub

Private Sub Class_Terminate()
    
    Call Commit
    
    Set BufferSheet = Nothing
    
End Sub

'=======================================
'   Public Method
'=======================================
Public Sub Commit()
    
    If (BufferSheet Is Nothing) Then
        Exit Sub
    Else
        BufferSheet.Cells(BufferBeginRow, BufferBeginColumn).Resize(96, 1) = Buffer
        BufferSheet.Cells(BufferBeginRow, BufferBeginColumn).Resize(EditRows, EditColumns) = Buffer
    End If
    
End Sub

Public Function Get_(ByVal Row As Long, ByVal Column As Long) As Variant
    
    If (Row < BufferBeginRow Or Row > BufferEndRow Or _
        Column < BufferBeginColumn Or Column > BufferEndColumn) Then
        Call Commit
        Call ResetBuffer(Row, Column)
    End If
    Get_ = Buffer(Row - BufferBeginRowS1, Column - BufferBeginColumnS1)
    
End Function

Public Sub Set_(ByVal Row As Long, ByVal Column As Long, ByVal Value As Variant)
    
    If (Row < BufferBeginRow Or Row > BufferEndRow Or _
        Column < BufferBeginColumn Or Column > BufferEndColumn) Then
        Call Commit
        Call ResetBuffer(Row, Column)
    End If
    Buffer(Row - BufferBeginRowS1, Column - BufferBeginColumnS1) = Value
    
    If (Row - BufferBeginRowS1 > EditRows) Then EditRows = Row - BufferBeginRowS1
    If (Column - BufferBeginColumnS1 > EditColumns) Then EditColumns = Column - BufferBeginColumnS1
    
End Sub

Public Sub Reset(ByRef XSheet As Excel.Worksheet, ByVal Rows As Long, ByVal Columns As Long)
    
    If Not (BufferSheet Is Nothing) Then
        Call Commit
    End If
    
    Set BufferSheet = XSheet
    BufferRows = Rows
    BufferColumns = Columns
    
    Call ResetBuffer(1, 1)
    
End Sub

'=======================================
'   Private Method
'=======================================
Public Sub ResetBuffer(ByVal Row As Long, ByVal Column As Long)
    
    If BufferRows = 1 Then
        BufferBeginRowS1 = Row - 1
    Else
        BufferBeginRowS1 = (Row \ BufferRows) * BufferRows
    End If
    If BufferColumns = 1 Then
        BufferBeginColumnS1 = Column - 1
    Else
        BufferBeginColumnS1 = (Column \ BufferColumns) * BufferColumns
    End If
    BufferBeginRow = BufferBeginRowS1 + 1
    BufferBeginColumn = BufferBeginColumnS1 + 1
    BufferEndRow = BufferBeginRowS1 + BufferRows
    BufferEndColumn = BufferBeginColumnS1 + BufferColumns
    EditRows = 1
    EditColumns = 1
    Buffer = BufferSheet.Range(BufferSheet.Cells(BufferBeginRow, BufferBeginColumn), _
                               BufferSheet.Cells(BufferEndRow, BufferEndColumn))
    
End Sub

  • 最終更新:2012-10-11 14:05:50

このWIKIを編集するにはパスワード入力が必要です

認証パスワード