Friday, March 23, 2012

Excel VBA Class Module | Final Column

Public Function FinalCol()
    FinalCol = pFinalColumn
End Function

Public Property Get Method1()
    Method1 = pMethod1
End Property
Public Property Get Method2()
    Method2 = pMethod2
End Property
Public Property Get Method3()
    Method3 = pMethod3
End Property
Public Property Get Method4()
    Method4 = pMethod4
End Property
Public Property Get Method5()
    Method5 = pMethod5
End Property
Private Function pMethod1()
    pMethod1 = Cells(1, 256).End(xlToLeft).Column
End Function
Private Function pMethod2()
    pMethod2 = Cells(1, Columns.Count).End(xlToLeft).Column
End Function
Private Function pMethod3()
    pMethod3 = ActiveSheet.UsedRange.Columns.Count
End Function
Private Function pMethod4()
    pMethod4 = Cells.Find("*", SearchOrder:=xlByColumns, _
    LookIn:=xlValues, SearchDirection:=xlPrevious).Column
End Function
Private Function pMethod5()
    pMethod5 = Range("A1").SpecialCells(xlCellTypeLastCell).Column
End Function
Private Function pFinalColumn()
    Dim FinalCol As Long
    Dim FCL As New FinalColLocator
    
    FinalCol = FCL.Method1
    If FCL.Method2 > FinalCol Then
        FinalCol = FCL.Method2
    End If
    If FCL.Method3 > FinalCol Then
        FinalCol = FCL.Method3
    End If
    If FCL.Method4 > FinalCol Then
        FinalCol = FCL.Method4
    End If
    If FCL.Method5 > FinalCol Then
        FinalCol = FCL.Method5
    End If
    
    pFinalColumn = FinalCol
End Function

No comments:

Post a Comment