"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

Week four is all about advanced user input. You've already learned a little bit about the InputBox function. We've done just the simplified version where we just said x= InputBox and then we have some sort of prompt. So we've typically used this bottom format here but the input box function has a couple of other arguments optional arguments. I want to kind of go through these optional arguments to show you how we can do more things with the InputBox function. So in this example, I've got my prompt "Please enter something:" I've got the title which is I've named Charlie's input and they've got a default value here of seven. And what we're going to do is we're going to obtain that from a user. I'm just dimming this as a variant and then it's going to message this box that. So here's the typical use of the InputBox function that we've used. I can step through this and we can obtain something like a seven and then the message boxes that. I've included here have dimmed x as a variant so that we could enter other things like strings. The nice thing about dimming x as a variant, if you just press cancel or okay as it is, it returns an empty quotations and is going to be a variant or a string. You can use this in input validation. So here I've included in a do loop. This is input validation. Refer to Part 1 of the course for how we can validate input. The nice thing about dimming x as a variant is if they press the cancel button here, it returns that as just an empty string. And we can use that then, we can check to see if it's not equal to an empty string. If that's the case, then we actually do, if that's not the case, then we message box and let them know that they didn't enter anything and then we can move along as planned, and we can exit the do and display it with the Enter. If this was dimmed as an integer, then if they press cancel or okay, then there's a type mismatch because the return of empty is not an integer, and so we have an error. So that's nice thing about dimming x as a variant. So now I have included those optional arguments. We have Charlie's Input, that's the title, and we have seven, that's the default value. And when we go through this, you see that the title on this input box is Charlie's Input. We still have the prompt but now we have a default value. So that default value of seven is in there for example if the user will most of the time be entering a certain value or a string or so on, then you can make that a default value to save them some time. Similarly, if I delete this and I press OK or cancel, I'll press OK this time, there's a type mismatch because x was dimmed as an integer and not a string. If x were dimmed as a string, then x would be just empty quotations which is an acceptable string, but then that would be caught using our input validation. Now I want to introduce the InputBox method we can use Application.InputBox. We have a prompt title we have all these optional arguments, we could change the default, we could change the x and y positions and there's all sorts of other things. The most common are the prompt, title, default and type. The type there are different codes. If you want the type that they can input into the input box to only be a formula, the type would be zero, one for a number, two for a string and so on. And the nice thing about this numbering method is that you can combine codes. So if you wanted the InputBox to accept numbers and strings, then you can add those two and you could use a three for a number or a string. So let me show you a couple of examples related to this. So I'm dimming x as a variant. That's the most common using this Application.InputBox, especially, if you're specifying different types at the end here. And explain a little bit about that in a minute. But here we've got in a do loop so we can do input validation. We obtain x in this InputBox method Application.InputBox, we have our prompt, we have our title, we have a default value and the type. The type here if I go back to the codes, is a string. So this InputBox is only going to accept strings. So if I run through this, then we bring up this input box and if I just leave it as a seven, a seven can be interpreted as a string, or I could write in something like "Hello" and that's interpreted as a string. So when we run this, then we see down here in the Locals window that x is "Hello" and we have our input validation it's not false, and I'll explain why I put false there in a minute, and then we message box that. One of the really nice things about these Application.InputBox is sort of got a built-in input validation. What I mean is if I change this type to one, then that means the user the input box is only going to accept numbers. The type code for number is one. So if I run through this and I put in a string, a string is not type it's not a number, so when I press enter, it's already got this input validation that's sort of built in which is nice. So then it asks it sort of leaves this box up and the user has to input something again. So maybe you put in an eight there and since eight is a number, then we go through and do everything as expected. I put a false here because the Application.InputBox method when you go through this, if I press cancel, then you see that x has been given a value of false and I can use that in my input validation. If you don't have the input validation, it'll give you kind of an error. And now I can trap that error so that cancel we have a false, we're going to only get a false when that cancel button is pressed. So if x is not equal to false then we exit the do and we continue. Otherwise, if it is false, then we let the user know they didn't enter anything. Please try again. We can also deal with the problem of them pressing okay when nothing has been input into the input box in that case I like to put a Type 3 here. We can use numbers and strings. So if we run through this and for whatever reason they just delete it, everything in that input box and they press okay, the result is an empty quotation. What I can do is add in an "And x not equal to empty quotations" then we exit do. So if one or more of these are true, then we stay inside the loop and we say you didn't enter anything. So let's go ahead and run through this. If for whatever reason the user just inputs a blank input box, then we are trapped inside that loop. We don't exit and it tells them they didn't enter anything and it asks them for this again. This is just sort of some advanced input validation using the InputBox method.

Explore our Catalog

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