Could someone please review my code?

[hansgrandia]

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:

• 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

 

[Andrew Poulsom]

Welcome to MrExcel.

For a start you should avoid Selecting. For example this:

Range("B201").Select
ActiveCell.FormulaR1C1 = "ANWB"

can be rewritten as:

Range("B201").FormulaR1C1 = "ANWB"

Also you should really be using Value rather than FormulaR1C1 there, because you are entering a constant not a formula.

 

[hansgrandia]

Thank you for helping this beginner find his way in VBA! Changed the code where possible. Regards,
Hans

 

[Jerry Sullivan]

Hans,

In addition to Andrew's suggestion to avoid Selecting, your code could also be improved by writing multiple values to a range of cells like this...

 Dim vCategories As Variant
 Dim lItemCount As Long
   
 vCategories = Array( _
   "ANWB", _
   "Autoverzekering", _
   "Bankkosten", _
   "Ziektenkosten")
 
 '--get count of items in array
 lItemCount = UBound(vCategories) - LBound(vCategories) + 1
 
 '--write array values to 1-column wide range beginning at B201
 Range("B201").Resize(lItemCount).Value = _
   Application.Transpose(vCategories)

This is a little faster; however the primary benefit is to simplify maintenance.

For example, if you decide move the Expense Categories to C205:C225, you can just modify B201 to C205 instead of making 20 edits to your list of cell addresses.

You can also add, delete, or reorder items more easily.

 

[hansgrandia]

Hello Jerry,
Thank you for this piece of vba language which I did not know until now. I have implementeted sucessfully it and hopefully I can use this coding for other project too! Highly appreciated your input. After reading some book about VBA, for me it's learning by doing.
Regards from the Netherlands,
Hans