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.
Option Explicit 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() Call initValues Call insertShopNamesAndProd End Sub 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 End Sub Private Sub insertShopNamesAndProd() Dim a As Long For a = 1 To numberOfShops With laailys .Cells(truckRow, firstTruckColm + a - 1).Value = bloem.Cells(bloemShopNamesStartRow, bloemShopNamesStartColm + a).Value End With Call goThroughProducts(bloemShopNamesStartColm + a, firstTruckColm + a - 1) Next a End Sub 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) End If Next a End Sub 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 Exit Sub End If Next a If checker = False Then MsgBox productCode & " did not read in correctly, make sure the product code in 'Laailys' is the same as in 'Bloem'" End If End Sub
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?