This is a follow up to this question
Code incorporates Mathieu's comments and it works. As some parts of the review left some code to my knowledge, I ask for another review to see if I implemented them correctly.
Objectives:
Load the current Styles list (name and type=builtin or custom) in an Excel Structured Table (ListObject)
Allow users to:
Delete
Duplicate (create a new style based on another)
Replace (one style with another)
Main suggestions from previous review:
Apply naming conventions
Add factory method
Add Actions by composition
Note: My current level couldn't understand well how to apply the dependency injection concept
GUI:
Module: Macros
'@Folder("Styles")
Option Explicit
Public Sub LoadStyles()
Dim myStyleProcessor As StyleProcessor
Set myStyleProcessor = New StyleProcessor
myStyleProcessor.LoadToTable
End Sub
Public Sub ProcessStyles()
Dim myStyleProcessor As StyleProcessor
Set myStyleProcessor = New StyleProcessor
myStyleProcessor.LoadFromTable
myStyleProcessor.Process
myStyleProcessor.LoadToTable
End Sub
Class: StyleInfo
'@Folder("Styles")
'@PredeclaredID
Option Explicit
Public Enum Action
DeleteStyle
DuplicateStyle
ReeplaceStyle
RenameStyle
End Enum
Private Type TStyleInfo
Style As Style
Name As String
Action As String
Target As String
Exists As Boolean
End Type
Private this As TStyleInfo
Public Property Let Name(ByVal value As String)
this.Name = value
End Property
Public Property Get Name() As String
Name = this.Name
End Property
Public Property Let Action(ByVal value As String)
this.Action = value
End Property
Public Property Get Action() As String
Action = this.Action
End Property
Public Property Let Target(ByVal value As String)
this.Target = value
End Property
Public Property Get Target() As String
Target = this.Target
End Property
Public Property Set Style(ByVal Style As Style)
Set this.Style = Style
End Property
Public Property Get Style() As Style
Set Style = this.Style
End Property
Public Property Get Self() As StyleInfo
Set Self = Me
End Property
Public Function Create(ByVal Name As String, ByVal Action As String, ByVal Target As String) As StyleInfo
With New StyleInfo
.Name = Name
.Action = Action
.Target = Target
If Exists(Name) Then
Set .Style = ThisWorkbook.Styles(Name)
End If
Set Create = .Self
End With
End Function
Public Function Exists(ByVal Name As String) As Boolean
' Returns TRUE if the named style exists in the target workbook.
On Error Resume Next
Exists = Len(ThisWorkbook.Styles(Name).Name) > 0
On Error GoTo 0
End Function
Class: StyleProcessor
'@Folder("Styles")
Option Explicit
Private infos As Collection
Private StyleActions As Collection
Private Sub Class_Initialize()
Set infos = New Collection
Set StyleActions = New Collection
StyleActions.Add New StyleActionDelete, "Delete"
StyleActions.Add New StyleActionDuplicate, "Duplicate"
StyleActions.Add New StyleActionReplace, "Replace"
End Sub
Private Sub Class_Terminate()
Set infos = Nothing
End Sub
'TODO Public Sub Add(obj As StyleInfo) : infos.Add obj : End Sub
'TODO Public Sub Remove(Index As Variant) : infos.Remove Index : End Sub
'@DefaultMember
Public Property Get Item(ByVal Index As Variant) As StyleInfo
Set Item = infos.Item(Index)
End Property
Public Property Get Count() As Long
Count = infos.Count
End Property
Public Sub LoadToTable()
Dim stylesTable As ListObject
Dim currentStyle As Style
Dim tempStyleInfo() As Variant
Dim counter As Long
Dim counterStyles As Long
counter = 0
counterStyles = ThisWorkbook.Styles.Count
ReDim tempStyleInfo(counterStyles + 1, 3)
Set stylesTable = MStyles.ListObjects("TableStyles")
If Not stylesTable.DataBodyRange Is Nothing Then stylesTable.DataBodyRange.Delete
For Each currentStyle In ThisWorkbook.Styles
tempStyleInfo(counter, 0) = currentStyle.Name
tempStyleInfo(counter, 1) = IIf(currentStyle.BuiltIn, "BuiltIn", "Custom")
counter = counter + 1
Next currentStyle
stylesTable.Resize stylesTable.Range.Resize(RowSize:=UBound(tempStyleInfo, 1))
stylesTable.DataBodyRange = tempStyleInfo
End Sub
Public Sub LoadFromTable()
Dim stylesTable As ListObject
Dim styleCell As Range
Set stylesTable = MStyles.ListObjects("TableStyles")
For Each styleCell In stylesTable.DataBodyRange.Columns(1).Cells
If styleCell.Offset(ColumnOffset:=2) <> vbNullString Then
infos.Add StyleInfo.Create(styleCell.Value2, styleCell.Offset(ColumnOffset:=2).Value2, styleCell.Offset(ColumnOffset:=3).Value2)
End If
Next styleCell
End Sub
Public Sub Process()
Dim info As StyleInfo
For Each info In infos
Dim strategy As IStyleInfoAction
Set strategy = StyleActions(info.Action)
strategy.Run info
Next
End Sub
Class (Interface): IStyleInfoAction
'@Folder("Styles")
Option Explicit
Public Sub Run(ByVal newStyleInfo As StyleInfo)
End Sub
Class: StyleActionDelete
'@Folder("Styles.Action")
Option Explicit
Implements IStyleInfoAction
Private Sub IStyleInfoAction_Run(ByVal newStyleInfo As StyleInfo)
If Not newStyleInfo.Style Is Nothing Then newStyleInfo.Style.Delete
End Sub
Class: StyleActionDuplicate
'@Folder("Styles.Action")
Option Explicit
Implements IStyleInfoAction
Private Sub IStyleInfoAction_Run(ByVal newStyleInfo As StyleInfo)
Dim styleCell As Range
Dim newName As String
Set styleCell = MStyles.Range("E1")
styleCell.Style = newStyleInfo.Name
newName = newStyleInfo.Target
ThisWorkbook.Styles.Add newName, styleCell
styleCell.Clear
End Sub
Class: StyleActionReplace
'@Folder("Styles.Action")
Option Explicit
Implements IStyleInfoAction
Private Sub IStyleInfoAction_Run(ByVal newStyleInfo As StyleInfo)
Dim evalCell As Range
Dim newStyle As Style
Dim replaceSheet As Worksheet
Set newStyle = ThisWorkbook.Styles(newStyleInfo.Target)
For Each replaceSheet In ThisWorkbook.Worksheets
For Each evalCell In replaceSheet.UsedRange.Cells
If evalCell.Style = newStyleInfo.Style And evalCell.MergeCells = False Then evalCell.Style = newStyle
Next evalCell
Next replaceSheet
End Sub
IStyleInfoAction
objects as a dependency, and providing them to the processor class via aProperty Set
member, a factory method, or as an argument to theProcess
method. That way adding new actions doesn't require modifying the processor class. If you inject all the dependencies of your class, you're able to write code that tests its functionality (and injects test-controlled dependencies) - unit tests. \$\endgroup\$