Created
July 20, 2016 18:53
-
-
Save exSnake/18e84c5134109f9f84561d7efe1f0548 to your computer and use it in GitHub Desktop.
Controller e Implementazione per Tabelle/ListObject di Excel
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Attribute VB_Name = "Tab_Controller" | |
Option Explicit | |
'Tab Controller che serve ad implementare tutte le funzioni non presenti | |
'nella listobject. Possiamo definirla come un interfaccia per le tabelle | |
'Ritorna la ListObject di una tabella presenta all'interno del foglio, accetta in ingresso | |
'il nome della ListObject | |
Public Function GetLobj(name As String) As ListObject | |
Set GetLobj = Range(name).ListObject | |
End Function | |
'Ritorna una singola cella che e' l'intersezione di una data intestazione di colonna e una riga | |
Public Function GetCell(lobj As ListObject, riga As Range, col As String) As Range | |
Set GetCell = Intersect(riga.EntireRow, lobj.ListColumns(col).DataBodyRange) | |
End Function | |
'Come sopra a differenza che invece della riga come range, accetta in ingresso il numero di riga della listobject (non assoluto) | |
Public Function GetCellByRow(lobj As ListObject, riga As Long, col As String) As Range | |
Set GetCellByRow = Intersect(lobj.ListRows(riga).Range.EntireRow, lobj.ListColumns(col).DataBodyRange) | |
End Function | |
'Prende in ingresso la tabella e il nome di intestazione di una colonna e ne ritorna il range dei dati | |
Public Function GetColumnData(lobj As ListObject, colName As String) | |
Set GetColumnData = lobj.ListColumns(colName).DataBodyRange | |
End Function | |
'Cancella tutti i dati presenti all'interno della tabella e ne crea una riga vuota | |
Public Sub Reset(lobj As ListObject) | |
lobj.DataBodyRange.Delete | |
lobj.ListRows.Add | |
End Sub | |
'Accetta in ingresso la listobject, il numero di righe e il numero di colonne, | |
'ridimensiona la tabella eliminando i dati superflui che dopo il ridimensionamento | |
'finiranno all'esterno della tabella | |
Public Sub Resize(lobj As ListObject, Row As Long, colNumber As Long) | |
Dim rng As Range, val As Variant, rngs As Range | |
Set rng = Range(lobj.name & "[#All]").Resize(Row + 1, colNumber) | |
Set rngs = Nothing | |
For Each val In lobj.ListRows | |
If Intersect(val.Range, rng) Is Nothing Then | |
If Not rngs Is Nothing Then | |
Set rngs = Union(rngs, val.Range) | |
Else | |
Set rngs = val.Range | |
End If | |
End If | |
Next val | |
lobj.Resize rng | |
rng.Interior.Pattern = xlNone | |
If rngs Is Nothing Then Exit Sub | |
With rngs | |
.ClearContents | |
.Interior.Pattern = xlSolid: .Interior.PatternColorIndex = xlAutomatic | |
.Interior.ThemeColor = xlThemeColorDark2 | |
End With | |
End Sub | |
'Ordina la tabella in ordine ascendente in base alla colonna scelta | |
Public Sub Sort(lobj As ListObject, col As String, Optional ascending As Boolean) | |
Dim field As SortField | |
lobj.Sort.SortFields.Clear | |
Set field = lobj.Sort.SortFields.Add(Range(lobj.name & "[[#All],[" & col & "]]")) | |
With field | |
.SortOn = xlSortOnValues | |
.order = IIf(ascending, xlAscending, xlDescending) | |
.DataOption = xlSortNormal | |
End With | |
lobj.Sort.Apply | |
End Sub | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Attribute VB_Name = "insertName" | |
Option Explicit | |
Private Const name As String = "tableName" 'Rinominare con il nome della tabella | |
Private Const col As Integer = 19 'Inserire il numero di colonne della tabella | |
Private lobj As ListObject | |
Public Sub DoubleClick(Target As Range, cancel As Boolean) | |
'' TODO | |
End Sub | |
Public Function GetLobj() As ListObject | |
On Error Resume Next | |
If lobj Is Nothing Then Set lobj = Tab_Controller.GetLobj(name) | |
Set GetLobj = lobj | |
On Error GoTo 0 | |
End Function | |
Public Function GetCell(riga As Range, col As String) As Range | |
Set GetCell = Tab_Controller.GetCell(GetLobj, riga, col) | |
End Function | |
Public Function GetCellByRow(riga As Long, col As String) As Range | |
Set GetCellByRow = Tab_Controller.GetCellByRow(GetLobj, riga, col) | |
End Function | |
Public Function GetColRng(col As String) As Range | |
Set GetColRng = Tab_Controller.GetColumnData(GetLobj, col) | |
End Function | |
Public Sub Reset() | |
Tab_Controller.Reset GetLobj | |
End Sub | |
Public Sub Resize(Row As Long) | |
Tab_Controller.Resize GetLobj, Row, col | |
End Sub | |
Public Sub SortByColHeader(col As String) | |
Tab_Controller.Sort GetLobj, col | |
End Sub | |
Public Sub Update() | |
'Metodo che serve ad aggiornare la tabella | |
End Sub |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment