"Excel/VBA for Creative Problem Solving, Part 2" builds off of knowledge and skills obtained in "Excel/VBA for Creative Problem Solving, Part 1" and is aimed at learners who are seeking to augment, expand, optimize, and increase the efficiency of their Excel spreadsheet skills by tapping into the powerful programming, automation, and customization capabilities available with Visual Basic for Applications (VBA).
In Part 2 of the course, learners will: 1) learn how to work with arrays and import/export arrays from/to Excel using VBA code; 2) learn how to work with text strings and write data to .txt files and import information from .txt files; 3) automate the import, modification, and consolidation of information from multiple worksheets into a central worksheet as well as the import of information from multiple workbooks to a central workbook; and 4) gain experience with creating professional user forms to interface with the user, perform advanced calculations, and manipulate data on the spreadsheet.
Learners who have a foundational understanding of VBA code and programming structures can jump right into Part 2 of the course without taking Part 1 and use the screencasts in Part 1 as reference.
Each module will introduce foundational and broad problems inspired by situations that you might encounter in the real world. To pass each module, you'll need to pass a mastery quiz and complete a problem solving assignment. This course is unique in that the weekly assignments are completed in-application (i.e., on your own computer in Excel), providing you with valuable hands-on training.

RR

Not only am i extremely happy about the course material both in quality but in presentation , but also about the support provided by the profesor. Congrats. A very recommendable course.

SJ

Jun 03, 2019

Filled StarFilled StarFilled StarFilled StarFilled Star

The course provides one the opportunity to work with a lot of concepts/objects in Vba.Be it arrays,working with diffreent workbooks/worksheets/user forms,you have it all in the course.

From the lesson

User forms and advanced user input/output

In Week 4, you will learn about advanced input and message boxes and event handlers. You will learn how to interface with the user in a professional manner using user forms that validate input and prevent the "debug" window and VBE from appearing. You will also learn how to place combo boxes on user forms and populate those combo boxes with information on the spreadsheet. Finally, you will learn how to put it all together and implement a solving technique (bisection method) to solve a real world problem in a user form. Week 4 is concluded with a quiz, which unlocks Assignment 4.

Taught By

Charlie Nuttelman

Instructor

Transcript

All right, I'm going to show you how to do basic input validation for user forms in this screen cast. Input validation for user forms is quite a bit different than just regular subroutines where you're asking the user in an InputBox for something. Recall that when we did, in the first part of the course, we did input validation using do loops. And once the input was valid we exited the do loop using a exit do statement. However, for user forms, it's going to be quite different. We're going to do a couple different types of input validation. The example I'm working with is the tank example in the preceding screen cast. So we've got a couple problems with how it is right now. First of all, we want to make it such that the user cannot enter any negative numbers, so all fields should be positive. We're going to display an error message if at least one of the four input fields is negative. What I mean by this, is right now we don't have anything to protect the user from entering negative five and it's going to give us actually a negative mass and we obviously don't want that. There are also some other errors that can occur that once you give this to a client or co-worker you want it to ever open up. The editor and so we're going to try to protect against all possibilities such as that editor will never pop up and there will never be debug window. So, what I'm going to do first is I'm going to add in some statements into the code to verify that H, R row and the depth are all positive. All these input validation is going to go at the very beginning of the sub, right after we define what pi is. So, first thing I've said here is if any of those variables height, radius, rho, or depth. So I have or separating them, if any of those statements this conditional statements, is true then we're going to display a message box at least one of the inputs is negative. Please try again, and we're going to exit sub. In general, you shouldn't use exit sub unless it's for something like input validation in user forms. So let's go ahead and see if this is working, if I put in something like a negative five then it tells me at least one of the inputs is negative. Please try again. So it protected against the first type of input validation. The second thing we want to do, there should be an error message if at least one of the first four fields is left blank. It doesn't make any sense if we try to run this calculate button if we leave one or more of these fields blank. So what I've done here is I've just put If any of those variables is equal to just empty quotations, that means they left it blank. If any of those are true, then we're going to trigger a MsgBox, at least one of the inputs is missing, please try again and then we exit the sub. So when we try to do this, let's just put some numbers in here. I'm going to leave that blank. Then it says at least one of the inputs is missing, please try again. The third thing we want to look at is if the depth exceeds the tank height, then obviously that doesn't make any sense. So your sub should detect this, and you should let the user know that the depth can exceed the tank height. So I've put these lines here, if depth is greater than height, then that obviously doesn't work, so we display a message box and we exit the sub. So if we run it with something that doesn't make sense, the height of the tank is 5, yet the depth is 6, it's going to notify the user of that. Fluid depth cannot exceed the height of the tank! Please try again, and it's going to exit the sub. So that's how we can protect against the user inputting something that just doesn't make sense when the depth is greater than the height of the tank. By the way, I should warn you, sometimes when you're doing comparisons like this and you're adding numbers and multiplying, these depth and height, they're actually all strings. So if for some reason and I'm not exactly sure why it happens or when it happens? But if sometimes you're not getting what's expected, always troubleshoot, always stepthrough to make sure you're getting what you expect. But if it's not working, remember these are strings and most of the time it can do a comparison of the numbers that are in the strings. But if it doesn't work you can always put like a 1 times and a 1 times which is sort of a trick to convert depth as a string into a number. That's just kind of a trick that I've learned over the years. The last thing that we're going to do is we're only going to accept numbers that are entered into the text fields and we're going to enter an error if text is entered. And I'm actually going to put this a little bit higher in the code. I've placed it up here, and I'm using this isNumeric. IsNumeric of a number will be true, so if any of these height, radius, rho or depth are not numbers, then what we're going to have for example if the radius is a string isNumeric of a string is false. But we're taking not of that, so not false is true, so if any of these is not a number then we'll have a true and we'll trigger this error message and then we're going to exit this up. So this is a good way to check, to make sure that the inputs are numeric. So I've got a string here. And when we do that, it says at least one of the inputs is not a number, please try again. The idea here is we've validated the input. If we leave it blank, we're detecting that. If we put in a negative number, we are protecting against that. And in no way do we end up bringing up the Visual Basic editor or that debug window and so this is very professional. You don't ever want to scare the user with bringing up that debug window because they don't know what what's going on, and it's not very professional. So, hopefully this gives you a good idea of how to validate input in VBA User Forms.

Explore our Catalog

Join for free and get personalized recommendations, updates and offers.

Coursera provides universal access to the world’s best education, partnering with top universities and organizations to offer courses online.