I have always wanted to start unit testing my code, and ending last week I downloaded Rubberduck again (first time there was just too little information how to do it to get me going) but after watching this video unit testing, I decided that it looks easy enough.
So the whole weekend I read up more and watched more videos and got more excited - because Monday I am going to go through my latest project and start incorporating unit tests!!
Or maybe not... I got to this first class of mine, and I really do not see a lot of places that I can do unit testing - meaning not a lot of return functions with a value to test.
The long and short of this class is that I inherited a worksheet with a lot of data all over the place, so much so that I can not even add or delete a line without breaking a lot of formulas (true story). Anyway, I made a new sheet, loaded information from a DB into that sheet, and from there on I am doing a data transfer to the infected patient (as mentioned above) every time I need to update the information.
My class with methods is below and I am wondering if there is anyway to run a test to check if data are transferred correctly, or to re-write the code to make it less brittle. Please feel free to tear the code apart and give any other advice you deem necessary.
Private firstTruckColm As Long
Private truckRow As Long
Private numberOfShops As Long
Private numberOfProducts As Long
Private wb As Workbook
Private laailys As Worksheet
Private bloem As Worksheet
Private bloemShopNamesStartRow As Long
Private bloemShopNamesStartColm As Long
Public Sub loadTrucksInit()
Private Sub initValues() 'Loads values from a "settings" module. (Any thoughts on this)'
firstTruckColm = mdl_settings.returnLaailysFirstTruckColm
truckRow = mdl_settings.returnLaailysTruckRow
numberOfShops = mdl_settings.returnNumberOfBloemShops
numberOfProducts = mdl_settings.returnNumberOfBloemProducts
Set wb = ThisWorkbook
Set laailys = wb.Sheets("Laailys")
Set bloem = wb.Sheets("Bloem")
bloemShopNamesStartRow = mdl_settings.returnBloemShopNamesStartRow
bloemShopNamesStartColm = mdl_settings.returnBloemShopNamesStartColm
Private Sub insertShopNamesAndProd()
Dim a As Long
For a = 1 To numberOfShops
.Cells(truckRow, firstTruckColm + a - 1).Value = bloem.Cells(bloemShopNamesStartRow, bloemShopNamesStartColm + a).Value
Call goThroughProducts(bloemShopNamesStartColm + a, firstTruckColm + a - 1)
Private Sub goThroughProducts(colmToUseBloem As Long, colmToUseLaailys As Long)
Dim a As Long
For a = bloemShopNamesStartRow + 1 To numberOfProducts + bloemShopNamesStartRow
If bloem.Cells(a, colmToUseBloem).Value <> vbNullString Then
Call insertProducts(bloem.Cells(a, 1).Value, bloem.Cells(a, colmToUseBloem).Value, colmToUseLaailys)
Private Sub insertProducts(productCode, prodAmount, colmToUseLaailys)
Dim a As Long
Dim checker As Boolean
checker = False
For a = 1 To 200
If laailys.Cells(a, 4).Value = productCode Then
laailys.Cells(a, colmToUseLaailys).Value = prodAmount
checker = True
If checker = False Then
MsgBox productCode & " did not read in correctly, make sure the product code in 'Laailys' is the same as in 'Bloem'"
A few other questions that I also want to know if possible:
I like to have a
initValuesmethod that loads all the properties that I am going to use in my class all at once in one neat place if I need to change any. Is this an acceptable practice?
I point to colm values like
firstTruckColm + a - 1, should I first declare another variable and give this value to that variable, just so that my arguments for other procedures look better?
Should I rather change my Subs to Functions with a return value as most I can?