hansgrandia
Board Regular
- Joined
- Jan 10, 2015
- Messages
- 53
Hi,
Just started discovering VBA by reading books and attempting to build some codes into my monthly administration in order to make my life a bit more efficient (learning by doing). For me it's fun too! I took me some hours to build below standing code and I'm sure there are people who are much more experienced and might give me some tips to improve myself.
There is no need to rebuild the complete code, just a view bullits (with example would be very appreciated!)
Some concrete questions:
Thank you,
Hans Grandia (Netherlands)
........................................................................................................................
Sub INGtransacties()
' load data
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\Hans\Documents\PRIVE\BOEKHOUDING\KASBOEKEN\DUMPS\transacties.txt" _
, Destination:=Range("$A$1"))
.Name = "transacties"
.TextFileCommaDelimiter = True
.Refresh BackgroundQuery:=False
End With
' Delete unnecessary columns and add a column expense category
Columns(3).EntireColumn.Delete
Columns(7).EntireColumn.Delete
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Range("D1").Select
Selection.FormulaR1C1 = "expense category"
' range of expense categories
Range("B201").Select
ActiveCell.FormulaR1C1 = "ANWB"
Range("B202").Select
ActiveCell.FormulaR1C1 = "Autoverzekering"
Range("B203").Select
ActiveCell.FormulaR1C1 = "Bankkosten"
Range("B204").Select
ActiveCell.FormulaR1C1 = "Eten & drinken / Persoonlijke verzorging"
Range("B205").Select
ActiveCell.FormulaR1C1 = "Kleding"
Range("B206").Select
ActiveCell.FormulaR1C1 = "Kranten / weekbladen / kerkblad / boeken"
Range("B207").Select
ActiveCell.FormulaR1C1 = "Lasten woning"
Range("B208").Select
ActiveCell.FormulaR1C1 = "Lidmaatschap kerk en goede doelen"
Range("B209").Select
ActiveCell.FormulaR1C1 = "Onderhoud auto"
Range("B210").Select
ActiveCell.FormulaR1C1 = "Opleiding en persoonlijke ontwikkeling"
Range("B211").Select
ActiveCell.FormulaR1C1 = "Overig"
Range("B212").Select
ActiveCell.FormulaR1C1 = "Reisverzekering"
Range("B213").Select
ActiveCell.FormulaR1C1 = "Sport"
Range("B214").Select
ActiveCell.FormulaR1C1 = "Uitvaartverzekering"
Range("B215").Select
ActiveCell.FormulaR1C1 = "Vakantie en ontspanning"
Range("B216").Select
ActiveCell.FormulaR1C1 = "Vakbond"
Range("B217").Select
ActiveCell.FormulaR1C1 = "Vervoer"
Range("B218").Select
ActiveCell.FormulaR1C1 = "Wegenbelasting"
Range("B219").Select
ActiveCell.FormulaR1C1 = "Zakgeld / cadeaus / boetes"
Range("B220").Select
ActiveCell.FormulaR1C1 = "Ziektenkosten"
' create drill down for expense category
Range("E1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$B$201:$B$220"
End With
' limit the number of characters in remarkfield
Range("I2").Select
Selection.FormulaR1C1 = "=LEFT(RC[-1],31)"
Selection.Copy
ActiveCell.Offset(0, -2).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 2).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft
Columns("H:H").EntireColumn.AutoFit
Range("H1").Select
ActiveCell.FormulaR1C1 = "Opmerking"
'layout
ActiveSheet.PageSetup.Orientation = xlLandscape
ActiveSheet.Cells.HorizontalAlignment = xlLeft
ActiveSheet.Cells.Font.Name = "Calibri"
ActiveSheet.Cells.Font.Size = "9"
Cells(2, 1).Select
ActiveWindow.FreezePanes = True
Columns("D:D").ColumnWidth = 34.57
Range("A1:H1").Select
Selection.Font.Bold = True
Range("G2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "$ #,##0.00"
ActiveCell.CurrentRegion.Select
Selection.Sort Key1:=Range("F1"), Order1:=xlDescending, Key2:=Range("A1") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
With Selection.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub
Just started discovering VBA by reading books and attempting to build some codes into my monthly administration in order to make my life a bit more efficient (learning by doing). For me it's fun too! I took me some hours to build below standing code and I'm sure there are people who are much more experienced and might give me some tips to improve myself.
There is no need to rebuild the complete code, just a view bullits (with example would be very appreciated!)
Some concrete questions:
- Would builing an array usefull to store data in an other module? If yes: should I use a public / private declaraton of should I use the option of a sub function? How should I refer to this in my procedure (since this is stored in an other module)
- I do not use variables. Would this help me in this code to make the code go faster? If yes, what would be helpfull to declare?
Thank you,
Hans Grandia (Netherlands)
........................................................................................................................
Sub INGtransacties()
' load data
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\Hans\Documents\PRIVE\BOEKHOUDING\KASBOEKEN\DUMPS\transacties.txt" _
, Destination:=Range("$A$1"))
.Name = "transacties"
.TextFileCommaDelimiter = True
.Refresh BackgroundQuery:=False
End With
' Delete unnecessary columns and add a column expense category
Columns(3).EntireColumn.Delete
Columns(7).EntireColumn.Delete
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Range("D1").Select
Selection.FormulaR1C1 = "expense category"
' range of expense categories
Range("B201").Select
ActiveCell.FormulaR1C1 = "ANWB"
Range("B202").Select
ActiveCell.FormulaR1C1 = "Autoverzekering"
Range("B203").Select
ActiveCell.FormulaR1C1 = "Bankkosten"
Range("B204").Select
ActiveCell.FormulaR1C1 = "Eten & drinken / Persoonlijke verzorging"
Range("B205").Select
ActiveCell.FormulaR1C1 = "Kleding"
Range("B206").Select
ActiveCell.FormulaR1C1 = "Kranten / weekbladen / kerkblad / boeken"
Range("B207").Select
ActiveCell.FormulaR1C1 = "Lasten woning"
Range("B208").Select
ActiveCell.FormulaR1C1 = "Lidmaatschap kerk en goede doelen"
Range("B209").Select
ActiveCell.FormulaR1C1 = "Onderhoud auto"
Range("B210").Select
ActiveCell.FormulaR1C1 = "Opleiding en persoonlijke ontwikkeling"
Range("B211").Select
ActiveCell.FormulaR1C1 = "Overig"
Range("B212").Select
ActiveCell.FormulaR1C1 = "Reisverzekering"
Range("B213").Select
ActiveCell.FormulaR1C1 = "Sport"
Range("B214").Select
ActiveCell.FormulaR1C1 = "Uitvaartverzekering"
Range("B215").Select
ActiveCell.FormulaR1C1 = "Vakantie en ontspanning"
Range("B216").Select
ActiveCell.FormulaR1C1 = "Vakbond"
Range("B217").Select
ActiveCell.FormulaR1C1 = "Vervoer"
Range("B218").Select
ActiveCell.FormulaR1C1 = "Wegenbelasting"
Range("B219").Select
ActiveCell.FormulaR1C1 = "Zakgeld / cadeaus / boetes"
Range("B220").Select
ActiveCell.FormulaR1C1 = "Ziektenkosten"
' create drill down for expense category
Range("E1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$B$201:$B$220"
End With
' limit the number of characters in remarkfield
Range("I2").Select
Selection.FormulaR1C1 = "=LEFT(RC[-1],31)"
Selection.Copy
ActiveCell.Offset(0, -2).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 2).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft
Columns("H:H").EntireColumn.AutoFit
Range("H1").Select
ActiveCell.FormulaR1C1 = "Opmerking"
'layout
ActiveSheet.PageSetup.Orientation = xlLandscape
ActiveSheet.Cells.HorizontalAlignment = xlLeft
ActiveSheet.Cells.Font.Name = "Calibri"
ActiveSheet.Cells.Font.Size = "9"
Cells(2, 1).Select
ActiveWindow.FreezePanes = True
Columns("D:D").ColumnWidth = 34.57
Range("A1:H1").Select
Selection.Font.Bold = True
Range("G2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "$ #,##0.00"
ActiveCell.CurrentRegion.Select
Selection.Sort Key1:=Range("F1"), Order1:=xlDescending, Key2:=Range("A1") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
With Selection.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub