Reverse Engineering: Toshl

main.vba

'----- ----- ----- ----- ----- ----- ----- -----
'
' Main function
'
'----- ----- ----- ----- ----- ----- ----- -----
Sub main()
   
    ' Set range
    Set planDate = Sheets("Plan").Range("A2", "A32768")
    Set planCategory = Sheets("Plan").Range("B2", "B32768")
    Set planAmount = Sheets("Plan").Range("D2", "D32768")
    Set dispSumTagsArea = Sheets("Summary").Range("A2:B32768")
    Set dispSumStart = Sheets("Summary").Range("A2")
   
    ' Get date parameters
    Set startDate = Sheets("Params").Range("B2")
    Set endDate = Sheets("Params").Range("C2")
   
    ' Get category parameters
    Sheets("Params").Select
    Range("A2").Select
    Dim categories
    Do Until Selection.Value = ""
        categories = categories & CStr(Selection.Value)
        Selection.Offset(1, 0).Select
        If Not Selection.Value = "" Then
            categories = categories & ","
        End If
    Loop
   
    ' Set and format
    xCategory planCategory, categories
    xDate planDate, startDate, endDate
    xAmount planAmount
    xCalcSumTags dispSumTagsArea, dispSumTagsStart
 
End Sub
 
'----- ----- ----- ----- ----- ----- ----- -----
' Set date validation into cells based on params
'----- ----- ----- ----- ----- ----- ----- -----
Sub xDate(planDate, startDate, endDate)
      
    ' Set validation date
    With planDate.Validation
        .Delete
        .Add xlValidateDate, xlValidAlertStop, xlBetween, startDate, endDate
    End With
   
    ' Set number format e.g. 1 Jan 2000
    planDate.NumberFormat = "[$-409]d mmm yyyy;@"
   
End Sub
 
'----- ----- ----- ----- ----- ----- ----- -----
' Set number validation into cells
'----- ----- ----- ----- ----- ----- ----- -----
Sub xAmount(planAmount)
 
    ' Set validation number
    With planAmount.Validation
        .Delete
        .Add xlValidateNumber, xlValidAlertStop, xlGreaterEqual, 0
    End With
   
    ' Set number format as Rp Indonesia
    planAmount.NumberFormat = "_([$Rp-421]* #,##0.00_);_([$Rp-421]* (#,##0.00);_([$Rp-421]* ""-""??_);_(@_)"
 
End Sub
 
'----- ----- ----- ----- ----- ----- ----- -----
' Build dynamic dropdown value based on parameters
' - User will be able to input category
' - User is required to define category in params before use
'----- ----- ----- ----- ----- ----- ----- -----
Sub xCategory(planCategory, categories)
     
    ' Set validation list
    With planCategory.Validation
        .Delete
        .Add xlValidateList, xlValidAlertStop, xlBetween, categories
    End With
   
End Sub
 
'----- ----- ----- ----- ----- ----- ----- -----
' Calculate summary of tags
' - User will be able to input any tags whenever they want
' - It means user not required to define them first before use
' - Tags are different with category
'----- ----- ----- ----- ----- ----- ----- -----
Sub xCalcSumTags(dispSumTagsArea, dispSumTagsStart)
   
    Sheets("Plan").Select
    Range("E2").Select
 
    Dim xSumTags As New Dictionary
    Do Until Selection.Value = ""
       
        xTagList = Replace(Selection.Value, ", ", ",")
        xTagList = Replace(xTagList, " ,", ",")
 
        xTagArray = Split(xTagList, ",")
        For Each xTag In xTagArray
            If Not xTag = "" Then
                If Not xSumTags.Exists(xTag) Then
                    xSumTags.Add LCase(xTag), Selection.Offset(0, -1).Value
                Else
                    xSumTags(LCase(xTag)) = xSumTags(LCase(xTag)) + Selection.Offset(0, -1).Value
                End If
            End If
        Next
 
        Selection.Offset(1, 0).Select
    Loop
   
    xDispSummary xSumTags, dispSumTagsArea, dispSumTagsStart
 
End Sub
 
'----- ----- ----- ----- ----- ----- ----- -----
' Display summary of categories or tags
'----- ----- ----- ----- ----- ----- ----- -----
Sub xDispSummary(xSum, dispSumArea, dispSumStart)
 
    dispSumArea.Value = ""
    'dispSumStart.Select
    Sheets("Summary").Select
    Range("A2").Select
   
    For i = 1 To xSum.Count
        Selection.Value = LCase(xSum.Keys()(i - 1))
        Selection.Offset(0, 1).Value = xSum.Items()(i - 1)
        Selection.Offset(1, 0).Select
    Next i
   
    Set xSum = Nothing  'Someone said to do this after using Dictionary
 
End Sub

Let's make it more efficient and more modular