Skip to content

Instantly share code, notes, and snippets.

@amyunus
Last active August 29, 2015 14:15
Show Gist options
  • Save amyunus/911f865b0de491fe8e15 to your computer and use it in GitHub Desktop.
Save amyunus/911f865b0de491fe8e15 to your computer and use it in GitHub Desktop.
Reverse Engineering: Toshl
'----- ----- ----- ----- ----- ----- ----- -----
'
' 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
@amyunus
Copy link
Author

amyunus commented Feb 21, 2015

Let's make it more efficient and more modular

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment