Video instructions and help with filling out and completing Can Form 2220 Periods

Instructions and Help about Can Form 2220 Periods

If we're running code in a spreadsheet and that code is making fundamental changes to the spreadsheet then the user needs to know that something has happened it's not always evident if you're running vba code that something is actually happening the user can't see that the user needs feedback so we need to tell the user that a report has run and ideally we need to tell the user some helpful summary information this is a really nice touch in spreadsheet some helpful summary information about the report if we can use Excel VBA message boxes to provide user feedback and we can do those things it's got to further improve the user information in a spreadsheet the users gonna love it so let's get into the spreadsheets and we've got our routine here remember we've got a routine that just lists the entries in this database by gender just gonna run this routine quickly there we go got a confirmation box from the previous video and we can see that we've got this list by gender so with one run the routine but how do we know the routine had finished you know there's no indication the routine and finished obviously we're programmers so we kind of get a feel for these things but the user doesn't know and I see this in lots of spreadsheets there's a real problem with the transparency if you like of what's going on the user can't quite understand this really really decreases user confidence in the spreadsheet so ideally once the code is run we'd like something to flush up and we want some helpful summary information how are we going to go about doing this you might want to stop the video now and just looking at this routine we've got where are we gonna put the code how might the code work so firstly we're going to use our principle of recycling got a read recycle some code that works this message box code and I'm just going to pop it down here for now and I'm gonna make it an annotation so Excel will ignore that for the time being so we're going to have some kind of message box at the end to flush up and let's just do something simple actually let's do something to begin with and let's just say Reports completes and there we go and this is just a simple message box so I can use some code that I know is working mate mate that's weeks change the message box type tou0 the simplest message books and then let's get rid of the brackets here as well there we go so we've got a nice simple message box here so how is this going to improve the code well it should mean that now when we run the code we're going to have requests for confirmation to begin with excellent and then at the end we've got this just very simple message box flashing up saying that the reports complete I guarantee this is going to add value for the user getting info timely information from the spreadsheet it's going to build user confidence but I think we can do better than this we can do better than just having a message box to say the report is complete if you want to go to the next level we want to provide some helpful summary information what might that be well it depends on the context depends on what you're trying to do so let's make some assumptions about this situation let's suppose that the the customer if you like is interested in how many people said no answer to this question so that's the critical information they're looking for weird like that information to flush up so there's various ways to do this I'm going to look at I'm going to look at two ways in this video so what are we looking at well if the entry in the database is no answer we can see one here then we want to count those up so how might we do that again you might want to stop the video but let's think about the existing mechanism here well we've got a loop here loops super-powerful you'll see them in the videos on this channel all the time so we're looping through we're going through each line of code so let's just think conceptually if when we're processing this line of code we could note down if you like that it's a no answer and then tally up add up those no answers as we go along that's conceptually we'd like to do now what tools do we need to do that well we're going to need accounting very at one possible approach one possible approach is to use accounting variable and I'm going to say no no answer very confusingly here we go as integer so this means number of no answers and then we're going to take this down here we've got to look through the code and think where are we looking at what is in the gender column room we've got the gender column here and we can see here we're actually assigning the value in the gender column to a variable here I'm not going to go too deep into this code but this code this line of code is interesting to us so if we say here if gender equals no answer just going to check that in the database yep no answer so if gender equals no answer then no no answer which is our variable name remember we've declared a variable up here there no no answer equals no no answer plus one so what are we done here well remember on this line of code I've helpfully left an annotation here which helps me understand on this line of code we're assigning the value in this column of the database the