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

Incomplete. At least I have started my first program on Visual Basic. Just for curiosity purpose.

Completing some functions. You are able to use it already. But requires some enhancements on formatting and efficiency. More update coming later.

Anyway I have learned how to create string index on array in Visual Basic. Need extra library to implement, called Dictionary. Wow, I was surprised it has a bit different treatment to PHP.

Let's make it more efficient and more modular