Modern Software Quality with VBA and Access - Live at AccessDevCon 2019

[Music] I'm Philip diva and I work with X's and VBA for about 20 years now and for almost as long I think about how I can improve my work how I can optimize processes how I can use new methodologies new techniques to improve the code I write to improve my efficiency and to improve the software I create so with this background I'm here today to talk about software quality modern software quality with access and VBA originally I wanted to start my presentation with a couple of anecdotes from customers IT administrators other developers people in 90 industry speaking derogative Lee about excessive VBA but I changed my mind because the last month something was published that gives you a much broader view about the problem and excuse me first second I forgot to get my thingy here vba has been voted the most dreaded language of them all that is the Stack Overflow program a survey for 2019 and well that is an achievement it impressively shows a lot of people know VBA but they're not particularly fond of it so I can only guess what the reasons for that result are pretty obvious is the VBA environment and VBA language which has not been updated for more than a decade now and it's not significantly that is certainly affecting this result significantly and there's not much we can do about it another factor is truly that VBA is an entry level language that is used by our users who are not really programmers and by beginners in programming and that many people just recall macros and the output is VB am and some are you upset it and that's that's horrible stuff program there but it's just a recorded macro and that is a good thing audiences yeah you can't do that in excess but VBA in general yeah yeah so that is actually good thing that there is a low power of entry to access that's good and we can't do much about those people having a little knowledge when they start but I think there's also third factor in that have seen many huge projects were created by professional developers and they had poor code quality and we're hard to understand how to maintain and that is basically the area here I am going to focus on how to improve on that so before we get really sad just a short idea what software quality is what what it's in the industry is an term that has been used by the international standards organization to create the standard ISO nine one to six in 1991 and the monks other areas it covers the area of maintainability and they focus on analyze ability is it possible to understand how program works and what it does change ability can I change bits of a program easily and it's the progress program stable they're like if I do a little change the rest should not fall apart and testability can I verify that the program works as intended so what effects does that have for us or for any developer working with our projects immediate effects of high end good code quality are clear and unambiguous code you know what you really don't do don't need to think about what is the person talking about writing about in the code it's easy to understand and it's easy to test you can verify does it work or does it not and then it's obviously easy to change and the end result in in the developer sphere is you have a product that has fewer errors and the indirect effects that needs to are less development time long term yes if you have a very small project that you're working on for two or three days pushing it out to the customer and they're using that in a very limited area with one or two users and that is superseded by another application in a couple of months and stuff yeah maybe you get away with lower quality that's just a quick fix but on the other hand these Pro projects tend to come back they live longer than you expected they kind of spread throughout the company and the year later that temporary solution for a single user has become a critical application for a whole small or mid-sized company so even then you should not ignore code quality and if you have less development time your customer has to spend less money and that is an obvious advantage for your customer but it does not mean that you are getting less money because if the client is happy with the project he will kind of give you more work and you will extend the project and that will be in the end more work for you and you will be able to charge more money for your work so you benefit as well from good code quality so just to preemptively answer question I frequently get is this whole topic relevant for the single developer well yes code you have not touched in a while it could have F as well been written by somebody else there is a quote I unfortunately was not able to find out the source of that quote but I like it very much because I can absolutely confirm it is true and the other aspect is if you set yourself standards and write them down or whatever kind of fix them you are holding yourself accountable it's not as easy to just do a quick thing and nobody sees it and I don't care if you know you are violating your own standards then be a bit harder to do sloppy work there so how translates all that into our work by coding conventions I'm not really going to talk about the basics like option explicit and typed variables and indented code that's basics I hope all you do that but what I'm going to dig in a little bit are naming conventions clean code and commenting code excuse me excuse me for a second I just gotten it fixed I I need to check in that here's a bug lurking and I need to change that and I need to check that in there too to production and not to production but I thought I need to check that in because it's really an urgent face so and yeah I'm sorry you have to see the code and I promise I'll fix that later yes okay we're very yes naming conventions that is the first that comes to mind when talking about coding conventions and I guess all of you who does not know the Hungarian notation and the listen ski retic adaptation of it for VBA who actually uses this Oh quite a lot more than half like this okay so this seems to be a common standard that many of you use but how much does this really help let's do a quick excursion into the history of Hungarian notation it originally started when Chancellor mone and Hungarian hence the name invented a naming scheme for variables and he thought yeah well it would help encode if we incorporate the logical type of a variable into its name and if you are working with Windows VBA you see this quite a lot there are abbreviations like hWnd for window handle or CB for a count of bytes or retinal specification levels us for unsafe string in the API will see a lot of LPS gr stuff for pointers to areas of class which are in our understanding strings and this leads to this being obviously a little bit fishy I've gotta actually need something a Breno handle and it's a sign a CV something else' count of bytes that looks fishy that is probably not going to end well if I do that in code so this helps to spot errors when looking at code but if we look at the VBA declaration both types are long so considering Lozinski and Redick we would have to do this we are kind of watering down the Hungarian notation and there's actually a term for that it's system endurance notation and it takes the technical type of the variable as a prefix so it's LNG for hour long and if we look at energy something if I let them give you something else that doesn't look too bad does it okay you might say that is my fault because I did use very ambiguous name with something and something else so let's change that energy main window handle is assigned LNG buffer size now that looks bad again you see this is probably not right okay problem fixed but let's take the Hungarian notation out of the equation and we've got this and it's still looking 50 you still see the problem immediately and we got rid of a bit of craft with our variable name prefixes there so Hungarian notation is in my opinion not a real call benefit of naming conventions if you use it I'm not telling you to stop also but it's not that helpful as as many people say because well it does not make such a difference what makes more difference in my opinion is language and I'm not talking about the programming language but about human language you should think about which languages do you use in your project and I originally thought yeah everybody should only use English but I was at a user group meeting where we discussed call quality and this particular thing and many people were from Germany and metal mania that is that is going to be a bit of a problem because we are not so familiar and proficient with English and we have a hard time figuring out the proper English names and we might make mistakes there and need to look things up in a dictionary might wrong term so maybe just making English compulsory is not the best idea but if you use your local language or other languages you should say yeah you can use English terms and German but nothing else because otherwise if you have to French developer then he will use French terms and maybe there's someone from China and he is using Chinese terms and then that is going to be problem so you should say yeah we are allowing this language and maybe this else but you know other human languages another point is terminology in a project I work in for a finance services company and they deal with loans and some people in the company called the loans accounts so depending on who you talk to you will say yeah I need the to enter the ID or the number of the loan and magnitude pull off the loan details and the developer listening to that will say the loan ID has long and start a quote but other people to talk to other people in they say yeah it's all about an accountant and someone else is saying in account ID and yeah essentially it's both meaning the same thing so you need to go through the terminology and say yeah we are using the term account and not alone and there are lots of other areas where you need to kind of define the project language inside your code the terms you are using but not one day you use this term and the other that and you essentially mean the same thing because that is going to be confusing work separators just a tiny bit you can use camera case like putting an uppercase letter at the beginning of words and no spacing in between or you could use underscores and I'm not telling you one of better than the other but you should define a standard what you do and kind of use that throughout the project and be careful with abbreviations you might think yeah the the ABC thing everybody in the project knows what ABC is and you write get ABC and then you moon programmer comes along and you know what is ABC I've never heard that term so if you use acronyms and everything only use them if you can assume anyone who is familiar with VBA programming does know the term or anyone who may have has had a brief introduction in the problem domain does know the term as well and beyond that do not use abbreviations because they will create questions and in the end it all boils down to creating and using meaningful and pronounceable names that is the essence iya meaningful yes yeah okay yeah that is indeed an interesting aspect and I cut that out because I'm going to short on time I would say if it is the bowel which is a customer ID you shouldn't write ID dot customer because you will probably have like 20 different ID thingies and they all start with ID so I would say customer ID account ID order ID whatever the more meaningful term first but now that we have got a naming convention which would use it use it to name things in our code an operation like two or three lines of code that are belonging together and do something together should become a method and the method is the name the method name is the name for that operation and if they produce a result that result is stored in a variable and thus the result is named and if we have constants in our application that we use anywhere then we should name them as a constant and not just write down the value in into the code and if we have a defined set of options we should create an eminent for these options and I brought a little sample here please don't don't look at the spaces in the name it's taken from North web that is not what this is about but beyond that a lot of code and it's working it's compiling it's doing what it's supposed to do but it is not easy to figure out what exactly is there it is supposed to do but now I named things the functionality is identical like as it was before but I removed operations and put them into their own methods I replaced magic numbers with constants or an options and now you can actually read it if not order is invoice then message box okay only cannot ship not invoiced as if ownership then message box okay only cannot ship order shipped already is it not valid at shipping in full then message box okay oh you get you get what I mean you can read it it's like a text and I did not change the code I just put in names they're the things that are actually referenced there and that is a transition to clean code clean code is simple and direct clean code reads like well written prose clean code never excuse the designers intent but Rama is full of crisp of strategies and straightforward lines of control that is a quote by Brady booth and it is kind of the the first time the term clean code was used with this intent and that is what Robert C Martin built upon with this clean code book and he kind of created a bit of philosophy so I think clean code is something that every developer should at least have heard about and I highly recommend reading the book the basics of clean our follow the standard so in the first place you need to have a standard so we are back at the coding convention then simpler is always better get rid of unnecessary complexity and the Boy Scout rule leave the playground cleaner than you found it and our playground is the code of course that is these rules apply generally everywhere and a little bit more directed to cold there are couple of more co-directed rules dry don't repeat yourself avoid redundancy don't copy and paste things all over your application create methods that couple that that wrap that and call these methods limit dependencies and couplings in exes we have a tendency to do the stuff with forms bang some form name dot controls subform whatever don't controls control name dot value and creating such a chain of dependencies and that is something you should try to avoid it because you not only depend on one form you depend on a form of control another form in that and and another control in them try to to reduce these dependencies rather depend on abstractions instead of concretization that is a little bit difficult in excess because it requires sometimes requires interfaces or later bound references and stuff but even though it's not easy and not so straightforward in excess it's still better than having there huge chain of dependencies that will break once you change any bit in that chain so regarding two methods our procedures and functions there are couple of suggestions in the clean code tools they should be small and there is not an exact measurement but if the methods longer then you can see on a stream without scrolling it is most likely too long it should do one thing only and that implies it has no side effects if you have a method that that says update order it should not kind of send an email to to whatever general update order do one thing it should have as few arguments as possible and it should have no flag arguments changing the behavior of stunts and there is something I had a hard time understanding at first because I thought you have well my arguments to a function of company pass arguments to a function to change its behavior that is the purpose of the argument isn't it and that's not what it's meant here and just to visualize this point I created a bit of sample code and something interesting to happen what I'm talking about that later I've got a method calculate it takes an art one and a half to of a double and a curve type of calculation time and when just seeing the declaration you have no idea what Methodist's actually calculated only when you look at the code you see yes the calculation type is addition subtraction multiplication that is basic math and depending what the calculation type is that is passed into that function it will do an addition and subtraction whatever we'll do totally different things so this is clearly an argument that is changing the functions behavior and now I will rewrote the stuff according to the clean code metrology is to remove this document that is changing a functions behavior and now I've got four methods it's just subtract multiply and divide and I've got parameter names like minuend dividend multiplier that are meaningful instead of r1 and r2 and now it is clear what isn't what is meant and I created that to explain the additional argument but it just happened this illustrates all the items before have here names I've got methods that do one thing only I got rid of a parameter in each method and actually the four methods combined together are shorter in lines of code than the long method before so less that saddl already illustrates lots of the aspects of clean code although I had not intended that in this case so now let's look at classes always keep in mind forms are classes and reports are classes it's not only if create an own class manually of guts quite a few classes in excess they should be smaller and they should do or be one thing only once again well that is a little bit harder to drag here but if you if you see yeah this this form is displaying this and that and our class is doing different things than it's something that that should be reconsidered maybe most importantly they should hide their internals unless you are designing a public API like the access object model you should prevent users from doing forms been named a lalalalalalala as I explained earlier that should not even be possible when you design the class so that you hide all these internals away and just make the stuff available that should be used by clients of that class there are quite a few more suggestions for classes in the clean code stuff in the clean code book but I don't talk about that because in excess we are rather limited with classes so does not really apply to us but if you work in another language like vb.net or C sharp or Baba read all about the classes there's lots of interesting and useful stuff comments Commons important to my experience if you use underscore in function for property names we need to discuss they not work when you started the whole tenure on the drop-down list and India we will not come back oh great I did not know that that is an important thing okay but now I'm kind of focusing on comments in code comments are go up I explained exactly well comments are a bit of a pet peeve of mine and I think comments are statements waiting to become lies I gather just a few examples I have through most of them out because the time does not allow to show many but he is just one end and these comments these are from real applications they are written by real developers by experienced developers some of them have more experience than I do so this is not any fake stuff well and an actually four code area private methods whether the next one is published stuck in it for me filter form well not so private anymore and this one that's a pet peeve of mine there's a tiny procedure head I'm not really one get command that is supposed to execute a stored procedure so the name is not very good then the method names get command and there is an argument SP s string yeah well and the developer got the idea yeah SP is not very understand what should that be and then he added a comment SP : start procedure name and I do not get it why people do that but I see it a lot but why don't they simply change the name of the argument induced or procedure name then you would see it in any sense when you're trying to call that method you would see it in the object catalog but this comment you only see it if you actually look at the method because you have no idea what it does or not that hmm I don't understand this but anyway the procedure ahead that's something I see often - we got a procedure here in the the procedure header repeats its name why the name is already in the procedure then there is the author and the date it was written and I am kind of blur the name - to protect the guilty why yeah well I'm not sure what how does it help understand the code to see when it was written and by whom not sure about that and it does not really get updated because I can assure you that Apache or our method was written on August 1st 2016 that it was changed a lot after well then the purpose explaining the purpose is good isn't it well let's look at it this is about the combobox entity contact and it's the after update an event handler so the purposes updates are total company date and a score after update of entity contact it does help a lot doesn't it we'll never thought that yeah well but what is updates up will true company data it's not really an explanation what exactly that is but it's just repeating the lines of code and then and base code I don't see anything about the nays code in in the actual code and that is because it's not there anymore so the the purpose is totally useless or wrong hmm so what about commenting code module and procedure headers no I don't think so they're quickly out of date and contain lots of irrelevant information that you have to look closely to find the bits that are relevant and hope they are still correct if you want to track this information who changed what use source control I'm going to say word on that later what about explaining code that is hard to read and understand that certainly is a good thing isn't it maybe maybe most of the time it is not rather make the code easy to read and easy to understand instead of writing a comment that's much better isn't it so when we are writing comments we should add comments that explain the intent we have when writing the code if it's not possible to put that in the code itself and to clarify hold and as the rule of thumb I would say comment on why you are doing something not what the what is in the code only in the really really rare circumstances that what is not clear for whatever reason it you absolutely cannot fix it then maybe comment on water otherwise always focus on the why and I tried to find an example where some comment was good in my opinion and I had a hard time finding one word I did found one that's very quick it's the name this is two lines of code reading data from an excel sheet and the first line is including the header in the data and the second line is using a sequel command filtering out the header that you just included deliberately so that this seems ridiculous at first and thama someone might look at it and say yeah we can simplify that and get rid of that first column had a name which is an additional argument through the method we can simplify that but know we are doing this intentionally to force all columns to to be of type text otherwise automatic data type detection will cause errors with miss detected types that is a useful comment because you cannot see that in the code and you cannot really make it obvious from the college so all I was talking until now we're rules where you you need apply some sense of own human justice to verify our de mettre they not met what would be the rules you can also define a couple of technical rules like for the declaration you need to include option explicit obviously you need to have access modifiers at public and private you have variable types and stuff your code should probably not contain any stop or end statement and as far as I'm concerned I do not want any of my projects come to contain stuff like doing menu item or sendkeys de stuff and usually not run command because there are better options to do that so these are kind of prohibited terms in my projects which I work on and there are a couple of tools that will help you finding this type of issues there are the rubber duck inspections and sexuals has a varied review quality feature who of you knows review quality in an sexuals two three four five six not too many and there's total access analyzer which does similar stuff okay quick demo here gotta be be a project it's derived from north wind so there is a bit of stuff in here I have installed the rubber duck vba ed in that is open-source totally free and it has a feature home inspections and I need to click the refresh button you'll go see bunnies or puppy is going in a circle and now it completed and it found stuff and I rather group that by severity here are arrows return home return value for mendicant orders never signed there are a lot of these arrows in here and other stuff let's look at it we can do double click on that and then we'll jump to it and right here's a function that's not doing anything and it has no return value and I wrote that too to show something else so it was intentionally here but the code inspections of Robert AK pointed that out that is something that is probably not going going according to plan if you run that code so we know that interesting stuff option explicit not specified so that is interesting stuff as well and I'm not going into too much detail there other areas thing here and you see it has a bit of a delay and stop and off when it's a very small project Roberta has really good ideas but it's having a bit of a performing issues still I reported a couple of bucks regarding that and the rubber duck team a they promised they're going to look into it but it's not yeah that really there yet but keep this name in your mind raccattack it's really a great project but I'm going to show you review quality in amplitudes because that is what I use myself and that is really good now will only mark two of these thingies there are lots of different reviews here I checked the dead cult review and the programming rules review because there are the most useful in my personal opinion and now I run this and I can already zoom in and on your streaming online yeah some of the hints are in German función was Wakaba Vietnam that is the same as funky needs to have a return value what rubber duck pointed out the parameter ID is not used then it's basically the same of an urn in here yeah I got a parameter idea I'm not using it so probably is obsolete or can be removed and there are lots of these reviews and I can kind of expand these on and off collapse all now I see all the forms here and then can expand this bit by pitch on on method level and then it is kind of drilling down and you see stuff that is kind of firm yet marked by Miami tools and these rules you define them themselves he has a large configuration area I will zoom in in a second here are programming rules so and this is the the method needs to have a function needs to have a return value and this is this check is implemented with a regular expression I can enter regular expressions and have predefined variables like the code element name the cold element name yes the method name in this case but it could also on other level be the class name or the module name or whatever and I can write regular expressions to define really complex rules but it's much easier to define like simple rules this one is a very simple rule classes must contain option explicit unison is a code that is a predefined property in and that with the code of a module must contain all in explicit very very basic we will very useful if you for whatever reason miss to add the option explicit yourself so there are lots and lots of rules that are predefined with them set rules and you can add your own rules and really tweak it all to your liking and there are other things in here that the indentation review that code review bit of code is not used in intentionally obvious some of the other stuff some of the other stuff in here is not really relevant to access because it's focused on VBA user forms which you don't have like that access Keys review the keyboard shortcuts and controls that's not working in Access but most of it is and most of the his arm useful if you want to use them and the best other programming rules as I just shown with all those configuration options here so testing we need to test our code you all do test your code don't you yeah and of course you can test any application manually by running the UI and clicking in there doing nasty stuff and it should not fall apart them but that is time-consuming and tedious and we should have some sort of automatic process to test our code because that runs in seconds worthless minutes or maybe even hours to test all the stuff manually so this leads us to unit testing because that is the most common form of automatic testing Paul Pawhuska did a presentation on unit testing year two years ago so I'm not going too deep yet just do very quick summary unit tests verify functionality functionality automatically they should run fast and isolated and they need to have a binary outcome either the test past or it fail it there's nothing in between good or not good that should be the result because otherwise you need to process the results in your brain that's not good you need to see good not good and all that within seconds running automatic tests and isolated means you should not depend on external dependencies that is why it's called unit tests you're testing a unit in isolation and driving that a little bit further we are a test-driven development and that implies you not only have writing automatic tests for your code but you're writing the tests first before you write the code and then you implement the method so you write a small test you run it it fails because you test something that does not exist if that succeeds already there is going to be a problem then you add the functionality to make the test pass run the test again it should succeed now and then you probably need to add more detail to the functionality but you write the test first that requires that detail implementation run the test once again it fails to know better to the code at the functionality run the test again it succeeds and it creates a very very quick feedback loop that you always see yeah this tiny bit of feature is missing the test fails I added test completed the feature there works and you always know your code doesn't want to expect it to do and another aspect of test-first development as it's sometimes called is you will end up with a better design because if you focus on the test first and see your method you are going to implement soon as the client of that method using it you get a much better idea how this should work with how the method should be implemented which arguments and information ever have to way live let this one which inner pass in which run it back and and stop Annette leads quite often to much better design so how can we do unit tests in Isis and VBA hmm testing is hard with access because the database is hard to test it depends on some data being there already or you are writing to the database and you verify the data there than to change the database so the test is not repeatable you need to add additional codes to set up your tests to create the data on your tests requires or afterwards delete the data are your tests as written so that is a bit hard and it's not really so isolated because you already have called and database combined another thing that is hard to test is the user interface it's nearly impossible to to simulate the user clicking in the UI and entering data in there that's really really hard to do and excess is a tool that is very powerful and a very rapid rapid tool when you just glue data to the user interface and then there is nothing in between that allows you to test it so that is a difficult situation and I haven't got a real solution for it but there are still lots of things in VBA and in the OBPA projects that are easy to test like methods that are not immediately reading and writing data or are only reading and writing very limited data well already as I said creates more effort but it's still possible and if you have classes that implement logic which are independent of the UI or exists then you can test that but at least keep test code you written anyway now explain what what that means I frequently have write complicated methods make that even bigger because the court buff does not really matter you remember that small ATS obstructs methods if I write complicated methods I usually do things like that in the immediate pain to test the code and then I will do different tests for like different situations so I would subtract our use ad with a negative number or with two negative numbers and then I see here looks good I've tested all the different use cases I had in mind looks good and I close the immediate pain and drove that away of course for add function mania we don't need too much of a test but just let's assume that is a more complicated function and then we should keep this test code we wrote here anyway and sometimes if it's more complicated thing I need to write a short method to actually test the code and that is something that we should keep in our code for future reference and now I'm using the test Explorer feature of robotic VBA and I've got a test module in here already but it's very easy to create your own test module here is a menu item you just click test module and it will create a new module in the application and then you can add a test method and then you end up with with something like this here which is essentially what I had in in the immediate pain down here I check for our equal and the hash mark here is to define and code this is a double that is something I haven't yet figured out rubber duck complains about different types of I would just write four it would be a long time and the the add method returns a double now I can't just run my code from the test explorer and I run all tests and now I've got the green check mark here and it is and the tests passed so and I am that is one way to test it and just that you see there are multiple different tests or testing tools from testing frameworks that was rubber duck and it has a bit of tendency to hang in some way during that process most of the time it works sometimes it ends therefore I did not do that really light but prepared it and here is another test I prepared for another test framework and that is something I can easily do from scratch I create a new class module I name that oh yeah something's hanging here and my guess that is robotic vba okay let's let's kill that off that is a shame because I like the ideas behind a rubber duck and I like what they do their approach the open source stuff and and all about it but it does not really work so well yet so as I said keep an eye on rubber duck but don't invest too much time to get started with it because it has lots of troubles and I'm going to kind of unload it to prevent further problems so what I wanted to do is create a new class module name that just test just has Def Con and then I can add the public sub test something append an X because I think that's something similar before it's not completely intuitive because for reasons I will explain in a second I need to pass in an object that is what I need to know and then I can just do stuff like a search like assert a test condition that is the the core essence in unit test you usually usually do like a range you prepare the test act do something and finally assert evaluate the results but I'm going to cut the first bits out and just do a quick test here that is just a comment describing what I'm doing and now I can call my subtract method and say yeah I have 10 and I to subtract 5 and that should equal 5 so that is a boolean expression and that is basically the essence of the test if that is 5 equals subtract 10 minus 5 it passes otherwise it doesn't and now I've got a tiny bolus let's improve the name just name that tests of dragons I compiled my code and now I run a tiny Eddy that is a unit testing framework originally developed by and Raymond Horst it's called XS unit and that is actually developed in axis and VBA the the code is open source the links will be in my presentation and what you are seeing here is the bit of an extension to their projects what I did and it can run as an add-in and now I've got my tests and it there is a bit of the issues sometimes complains about compiling as I haven't figured out how to fix that but you need to compile and now I've got this big red bar because here is a test that failed test basics I see something's amiss so I go to where was it test something test basics here we are and this test is not working and I'm too busy at the moment to really fix it so I just delete it and run my test again and now you see I've got a green bar and all the the tests run and I can add some more information and it will include the Commons and that that way I have a easy possibility to run tests code here and you see the results the the orange stuff is warnings the setup and teardown methods that are methods in the most used unit test frameworks to set up stuff before a test and teardown to clean up after them and they are by definition called before and after each test not going into too much detail has just why the important thing is the bar is green that is what I'm after here that the big green bar everything and then our about fifty not schewe ten to fifteen tests in here they run in a split second and that gives me feedback my code works as intended sold okay and I named my my thing excess unit four because it is derived from the original excess unit the source code will be available and it will be you can all download it it will be open source as well so there's a quick question right in videos I killed it by Joseph / - lizards does it have problem 3 because it's a well-known framework yes it is there there should be information in the slides but I kind of missed it each unit is probably the most mature unit testing framework it's written by user but it's open source as well the links are in the the slides as well but I don't use it because it depends on the testing framework called simply BB unit and that is I'm not sure if it's maintained what access unit uses an old version of that framework as a requirement so you need to install that old version to install that unit and I discarded that completely because I needed to run these tests at a client and I'm not allowed to install anything there and it is a pain to to get them installed so I use the XS based solution by andrey meanest and build an extended upon that so sometimes if you read about automatic testing will see unit tests on one hand and integration tests on yoga that is basically the same thing but with a difference gold the unit test is isolated without dependencies and the integration test is implementing the same concept with multiple you it's combined so if you have a database function you explicit explicitly include the database in the test or if you have multiple dependent objects you will test them but together and then it is an integration test to test if their individual units integrate well with each other the basic concept is essentially the same so I'm not going to show that in more detail oh yes and that is the slide I was waiting for these are a couple of unit testing tools or frameworks for access and I already named them so we can probably skip over that very quickly UI automation tests there are lots of tools that claim they can help you testing your software and verifying functionality by UI automation yeah they don't work with access access is a special kind of Beast because you don't have real Windows your controls are not real windows that are painted on the screen only the ActiveX control is the window and most UI automation tools depend on controls being Windows to kind of deal with them and even if you can work with UI automation it's really really time consuming to create and maintain those tests there break easily and I've done a couple of coded UI test on my product oversee and I need to sit in front of the screen and what the automatic testing process good thing because it the form might show up too late and the UI automation already click there where the form was supposed to be but wasn't and well let's cut this short you I oughta Meishan with exes it does not work unfortunately another important topic I've spoken about this quite a bit source code control if you ask me you have to use source code control but I'll keep this short an important aspect regarding code quality and source code control is it separates the metadata out of the code that is what we try to include in the procedure headers who wrote this when did he do it when was a change to changed it why was it changed and stuff and if you really record that in the procedure ahead it's going to be a long procedure header and I guarantee you there will be changes that get missed so if you're really are interested in that kind of information you need to have source code control and it will not just tell you what has changed in the opinion of the developer but you can really easily do this and see every single character that was changed in a source file at any given time of which any Kevin thank you but we can't reuse the source code control of it who use the source code control in this project you showed a tie counter to many but it's not entirely new area and if you work in a team you absolutely have have to have source code control because it is the single source of truth in a team project if developers tell yeah I fixed that part I have the the code on my machine it's working it's fabulous it will solve all the problems in the world but I have not react checked it in as far as I'm concerned this does not exist once it's in the repository and it can be executed by everyone yeah we can talk about it but before that it does not exist and that is really important to keep the code in one central location and one thing particularly interesting today is those code control can be used as the base or automated processes if you're a single developer you could do that on your local disk but if you are distributed or working on different locations and stuff you need to have a central location that is the base for automated processes what kind of automated processes am I talking about here continuous integration or continuous delivery yes please I created an edit for that that is available as a commercial product thank you for asking guy I thought everybody was aware about that because I talked about that a couple of times in that did not want to kind of promote my stuff it's called Ivor C and Google that come to me later we can talk about them but there was a source code control editing from Microsoft before that was working until access 2010 so yes the source code control repository as basis for continuous integration and delivery continuous integrations you automatically build the new version of your product not for deployment but just to verify all the source files can be merged together into an really executable application that does actually compile that is the idea behind continuous integration then you can of course verify the functionality of that file but it's not yet intended to be deployed to users that's just for internal quality control that you can integrate continuously each checked in that any developer makes any change that is made to the product would still allow the product to compile and all quality tests that you may have implemented should still work that is continuous integration and if you put that one step further then we are at continuous delivery then this automatically created version of your application of your software is automatically delivered to at least a test area where a real humans test with it and maybe even with some sort of quality control process integrated maybe even automatically delivered to production after certain testing and quality control measurements we're taking care of the core stuff for that or the core tools for that are build servers and their copy of build servers available like Jenkins and I forgot there are lots of them and they are standard tools they chrome include standard processes and stuff to build most type of software's unfortunately Microsoft Access is not most types of software in the continuous integration context so if you want to automatically build an access application you need to write your own build scripts that kind of implement that build process like pull the the most recent stuff from the source code control report repository create a compiled a de file if you want the at all just make sure the code compiles in an AC CDP or whatever and then run your quality control and deployments on on that file so quality control I showed you the review quality feature of M sexuals and the developer of impetuous commerce 2000 he is a great guy and he just buy his stuff I mean I can't explain enough but buy it and he even implemented after enacting a little bit an automation feature for ins actuals that's officially documented you can automate the review quality feature I showed you earlier in M sexuals and let's look into my email and I thought there is something I am I want to show you but just in case it does not properly update here oh here we are so this has been generated today at 10 o'clock that was why I was standing here this email was sent and the subject is called quality Auto review and if you paid attention earlier to to my urgent fix I had to check in that I was promising to fix later just to make sure I don't forget to really fix it I created a process that pulls every change from our source control repository and runs and actuals review quality against this check in and if there are any results coming up they will be merged into an email and email to the person who checked in that code and this is kind of the summary of the problems that were ultimately detected with my code that I checked in earlier and I get this by email so that I can say I totally forgot about that so I am at least have a reminder of I still can't ignore it but I can't really say yeah I did not know because that is actually a real problem I tell people I work with and and myself included please do run the review quality feature of NSAIDs before checkmate stuff but it's as it so happens like I'm in a real hurry I need to check in I forgot to to review quality so I created this automatic process to make it impossible to forget it and I'm quite I love it because I used to be the bad guy in the projects telling the developers oh you did this and that and that and I don't like it please fix it and a week after that I need to repeat the same stuff again and I hate it I don't want to tell people that all the time so I created this automatic process that the people are negative out these issues yeah automatically and I don't need to do that so unfortunately the only real tool that is available that does this kind of automation excuse me that was and what if the only tool I'm aware of that can be used for such code quality review and that can be automated is em set words if you want to put in a lot of effort you could take the rubber duck source code and build your own validation process and that is actually probably even better approach because they are really parsing the VBA code independently but it will be a lot of work and I'm not really suggesting better any of you unless you want to really create a common tool for that it's too much work to do on product on project work so what about automated unit tests I originally intended to show you the same bit like review quality here for automatic unit tests that are also run after each checking but I was not able yet to implement it it definitely should be possible and should not be too hard I just did not find the time to do it the ACC unit unit testing framework that can the the slight read it should be possible it can the developer confirmed it can be automated but the bit of a problem remains it's only really sparsely documented I think there is a sample he posted a sound lifetime asked him it's three lines yeah you did this that and that and that you can do all that amazing stuff you want to do yeah it works I believe me that is not a question but it's you need to do a bit of research and try and error and stuff to work out how it all works rubber duck cannot automated that's just in UI tool but the source code is available as I said you could do some stuff yourself access unit cannot be automated as well but my for quark the the the thing why I especially what I especially try to enhance and improve is automation features that unit tests can be run in an automated automated process and create automatic output so everything I spoke about here until now was about VBA and access and maybe a bit of similar languages but mostly BBA we have another language that we deal with regular a sequel or T sequel the sequel server programming language so what about that the situation is different there and most of the stuff as that only applies to VBA code because it's sequel it's much harder to use functions in a nexus not too bad in sequel server if you use too much functions in in your views in your select statements and stuff that will degrade performance even if you are careful not to kind of prevent the Sarge ability that the index index usage of the queries and stuff even if you pay attention to that and only kind of move simple calculations or or stuff from the sequel like in a case statement columnist or wherever nuclear to function and all the funky instead it will significantly degrade performance however that is something that Microsoft is particularly addressing with sequel server 2019 and people who had seen earlier releases and previews and say they are that hugely improved so then the the situation is changing so that rules out that we create named methods that we isolate functionalities on sequel will still have huge blocks of code that makes for ma formatting the code in a readable way even more important and Commons here are more useful because you have less options to make your code more readable of course you still can use functions and use whenever there is a legitimate reasons from from the functionalities might still use them don't misunderstand me that I say you can't use functions but they actually might slow things down so don't use it just to name things as I showed it in Access and well yes that comments may be more useful unfortunately in excess sequel view or excess period yeah you don't have any comments but well there's nothing we can do about yet okay that is basically the end of my presentation so I just want to to address you and say thank you and I have got one big thing to ask you and I think you are all professionals professional developers please take care of the quality of your source code [Applause] [Music]

Channel: codekabinett.com/en

Views: 1,183

Rating: 4.8888888 out of 5

Keywords: VBA, Access, Code Quality, clean code, software development, naming conventions, coding standards, hungarian notation, comments, source code control, unit test, TDD, test driven development, MZ-Tools, RubberduckVBA, accessUnit, automatic tests

Id: 5ZcPOELBu08

Length: 78min 34sec (4714 seconds)

Published: Mon May 06 2019