Let's Code - VBA - 99 Bottles of Beer Excercise

hi i'm philip from codecabinet.com and this is going to be another video in the let's code series where i do not explain much at least not the whole context where i mainly just code and show you how i do things and this one's a particular weird video coming up but before we go into details this is certainly not a video for beginners so if you are in that audience sorry there will be other videos for beginners coming in the next couple of weeks and months but this is not one of those it is rather for advanced vba programmers who would like to broaden their horizon so it's a bit of a weird one coming up [Music] i recently bought a book it's called 99 bottles of oop and it's a practical guide to object object oriented design and particularly to write good code code that is easy to maintain and easy to read and easy to change and i'm very curious i did not read this book yet because there is a slight challenge at the beginning the whole book is centered around the song 99 bottles of beer not sure if you know it let's look at the lyrics here for a second this is basically the song and you will see in a minute why it's in a comment here um it's just 99 bottles of beer on the wall 99 bottles of beer take one down and pass it around 98 bottles of beer on the wall well sorry i'm not really a good singer anyways this was the first verse and that is repeated with one bottle less over and over again until you reach the end of the song which is then here and um with with no more bottles of beer yeah you go to the store and buy some more yeah then it starts all over again if you really like to and the book is centered around that song and the coding exercise encoding challenge is to write code to output the whole text of the song and as i said the challenge um is to to deal with that problem before reading the book and that's what i'm going to do right now i'm trying to work on this challenge and uh it is suggested you just work 30 minutes on it and that's about what i will do and well i don't know let's see how far we get um so i think um before we get started just just one more thing from the book there where test cases to test that your code is correctly working they were originally in ruby because that is a ruby book mostly um but i converted these test cases to vba using um my own unit testing framework for vba that is publicly available i'm going to put a link somewhere in the description of the video okay um let's look at the test that is the first test there are many more these are commented out because they will not compile yet because we don't have the the code that um is required for them to compile but the first one is this and you should go about this challenge enabling one test after the other only after the previous tests all pass so it's all test driven development and incremental development and that's what we are going to do here i already created a class bottles that that is basically the the core class that is going to do that and and there is a slight trick in that to to use that if we go from here that the actual method call is this it goes bottles new instance and verse and then it passes on the number of bottles for the verse and to make that work i created the new instance class and i did some dodgy stuff to make that work and i'm not going to go into that detail now you can simply put that method into any normal standard module and create the class without it um and then it will work all the same so i'm not going into this detail but um let's finally get started i run a timer here for 30 minutes so that that is the deadline well no more words getting started with coding now okay this is the first test and if we debug compile we get an error because the method burst is not there yet so we should first write that and it should be a function um verse and it should get the number of bottles as an integer and it should return a string so now we compile and it works and now i'm going to run my access unit test runner and you see one test that is the test we just saw and i started and of course the test failed it says y expected 99 bottles of beer as return value of that function and that is obviously not what the function returns now um let's start really on the cheap side we see here what is the expected value so go quickly and make the very first test pass here we are that was an easy one i guess but we need to run it that is mandatory so start and we see success the test passed that is easy so let's go to our test class and uncomment the next test and now it's where it's getting interesting we compile it does work that was expected um i just closed that and reopened it to pick up the new test started again and now of course the first test has passed but the second failed because it does not work and now the real work begins oh and i'm going to cut out uh any sections where i'm just thinking because i knew of the challenge in advance of course but i didn't prepare in any way so i'm as unprepared as you are and i probably need a lot of thinking to get get this going and then i'll cut that out and continue the video when when i'm ready to do something actually or talk about something actually okay okay i've got an idea how to do that um how to make the second um second test pass [Music] so i'm going to to use a slide helper tool that that is um i i had that in mind already before that is the print function and i'll show you right quickly how it works um it's just getting a string argument and the important bit is this placeholder there can be any placeholders in there and they will be replaced with the arguments um following that and i can of course use several placeholders and then they will all get replaced and i'm going to use that because that seems to be very helpful to not repeat the the string concatenation stuff all the time but uh that is going to be the only helper here i'm going to use so back to our bottles class i'm going to put the two lines here as constants so this is going to be the first line and i'm going to get another con second line also as string that's going to be this one um con second line what's the problem with underscore missing so and i'm going to use my placeholder here another placeholder here so what we are going to do is um saying in the red violet short for return value that's my return buffer for this function and now say the return value oops right what is going on here right [Music] it's going to be printf that's my uh function and it's first line and then i pass in the number of bottles just copy that here that is return value and print second line number of bottles minus one so finally the verse function should return the return value and i think that might do it already so let's see i fire up the test runner and start and it passes so i have two tests covered so let's uncomment another test and that's going to fail because it's expecting that um that the code can detect that there's only one and you need to singular for uh the bottle term okay but uh make sure it actually fails so sure the first two pass and this failed okay back to our class i'm going to get rid of that and maybe switch to the procedure view so we need to handle the the singular and plural of bottles [Music] this is a string and it is bottle if number is not one then oh we need a return value the return value is the container name [Music] um and we are going to append an s at the end if the number is exactly zero and then we return that value so this should do it um let's focus on on our output of the verses now this is going to be the container term so we need to remove and we need to put the placeholder in here as well so that are all occurrences of the container now the first argument is the number of bottles and the second one is get container term for number of bottles that's a closing parenthesis missing so um now i would extract that here and just say number of bottles equals number of bottles minus one and put it here and once again get containered for number of bottles so a little bit of refactoring and i can compile this stuff and now i'm going to run my test again now all three of those um of those methods of those tests pass that is going pretty well now um continue with one more test i uncomment that and you see it's getting um more difficult than before just for the sake of it we run the test it fails so now i'm going to to copy that to have it in plain view in the class here that is the expected output now okay well the the main difficulty here is that the no more is now used instead of a numeric zero so we need to find an text representation of our number of bottles that shouldn't be too difficult oh bloody bloody stuff um no no it's it's going it's going to be easy yeah private function number representation number text representation so number as integer and that return value is going to be a string and if number equals zero then or we need a return value as string so then this is going to be no more else it's going to be simply the number and we could do an explicit conversion to to convert it to a string so [Music] now if we use the number text representation then it should work already if we put this here number text representation here and also here so what did i do wrong parenthesis missing so i compile that works fine and now let's run our test oh so um what is the problem that that is going to be a bit of a problem because i s obviously it failed so there must be something wrong but actually i don't really see what's wrong here but that is something i also like about having unit tests here i just put a breakpoint in here and run that again and we are immediately here at the location where the problem is and we can easily put in our variables and then compare what is different oh i see it it is addressing the the bottle with it and one explicitly oh take it down and pass it around yeah i didn't notice that at all so this is an example how useful good unit tests are they they help you to find bugs instantly and that would have been a bug that i would never have spotted if i would have manually tested that so okay but let's focus on the problem um take one down take it down um oh well am i my english slightly failing me here in finding the proper the proper naming oh that is lovely well i i've cut a bit out because axis just totally crashed and i had to restart and luckily we i saved the stuff and we are exactly where we left off but uh if something in in the video seems different because windows moved and bits then it's because i had to restart access but you didn't miss anything in between okay now let's focus on the on the bit of the the bloody i don't know the english grammar to address that term correctly private function we need another function to take one take it is that a particle so sorry english people if that is grammatically incorrect i will name that get particle if i had more time i would look that up but for now it should be good enough third and fall as string if number equals zero then our return value will be it and else our return value will be one and get particle is the return value and yeah the locals window should go and and here's another gotcha um i i reduced the number of bottles already so i can't use it here because that refers to the previous line but it's it's not too bad but uh it's getting a bit tired somehow i'm reading the arguments so i'm going to put them on separate lines here a number of bottles so let's particle and this is number of bottles plus one because we are referring to the original number of bottles before it had been reduced so maybe that's doing it already let's run it yes success okay and we we go ahead or just another test here um that is the final verse yeah just just copy and paste that over to have it side by side here just for for me to see that no more bottles there no more is covered and then we have a completely new line yeah i'm afraid i we need an if construct here if number of bottles is less than zero no no no no if greater equals zero i mean the the technical result would be the same but i rather have the more common clause first in an if-then-else statement usually um i don't don't do that religiously so um this is going to be the final line and and we can just there's nothing then there's nothing changing then so that that is just the final line we appended here no worries okay let's run the test failed yeah okay um once again to to get to the root of the problem i put a break point in there and let's look at expected and let's look at what our bottles class is generating so what is the problem the the casing at the beginning not entirely sure if this is the problem so we need to use proper case oh my you see that that is the whole thing here is something that looked like a trivial task to do and the 30 minutes are almost up less than a minute left and we are not done yet that the question is how to detect that some uh that that is the bell that the thing is over the problem here is how to detect that my placeholder is going up at the beginning of the line and should be uppercase and that is non-trivial i'd say well anyways uh the time is up so i completed my challenge because it was not really required to complete the whole stuff um but um yeah well um [Music] it was required to write down how far we got and i i put down a note on the side um then i got to the case problem because that will be something that will will kind of discussed in the book later how far you got and stuff so i'm allowed to continue in in the challenge but we i should kind of put down where the 30 minutes time limit ended so i hope you enjoyed this slightly weird video i certainly enjoyed writing the code and and speaking about it and doing it and i i would really love it if you would if you watched the the video to the end if you would put in your thoughts and ideas into the comments and i would really love to to read that and i will read every comment i cannot always um answer to every comment uh and reply um but i definitely read every comment so okay thank you very much for watching bye bye until next time [Music]

Channel: codekabinett.com/en

Views: 821

Rating: 5 out of 5

Keywords: VBA, Test Driven Development, TDD, Unit Test, Unit Testing, 99 Bottles, Sandi Metz, Incremental Development, Coding Exercise

Id: c_-3vCMqR3A

Length: 29min 19sec (1759 seconds)

Published: Sun Aug 09 2020