Example: Fuel efficiency user form

Loading...

Del curso dictado por University of Colorado Boulder

Excel/VBA for Creative Problem Solving, Part 2

151 calificaciones

University of Colorado Boulder

Excel/VBA for Creative Problem Solving, Part 2

151 calificaciones

Curso 2 de 3 en SpecializationExcel/VBA for Creative Problem Solving

"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.

De la lección

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.

Advanced input boxes7:32

Advanced message boxes6:53

Event handlers4:33

Introduction to user forms3:07

Creating your first user form10:35

Example: Fuel efficiency user form7:40

Example: Tank volume user form6:08

Dim'ming (or not Dim'ming) variables in user forms1:59

Input validation in user forms6:16

Introduction to combo boxes, Part 15:41

Introduction to combo boxes, Part 26:07

Example: Periodic table user form8:05

(OPTIONAL) Putting it all together: Conversion Solver user form10:38

Conoce a los instructores

Charlie Nuttelman
Instructor
Chemical and Biological Engineering

All right. I've got

a user form starter file on the course website it's called FuelForm STARTER.xlsm.

So you can go ahead and download that if you want to work along with me.

I've already created this user form just to save time.

You should definitely feel comfortable and gain

practice with making these from scratch and naming everything.

So everything has a name especially these text boxes.

So, I've put a frame here,

another frame and I have miles and kilometers.

And so, again, you should know how to put

all these buttons on here and so on, and name them.

So let's just go through what everything is named.

This first one is named distance, dist.

This second one is volume,

vol. Then the outputs.

So when we press GO,

we're going to use the inputs here for either mileage or kilometers,

and gallons or liters.

And we're going to output miles per gallon and kilometers per liter for both of these.

So this thing is named mpg,

that's going to be one of the output fields.

This is named kpl.

We've got a GO button and we've got a QUIT button.

I've also got these option buttons.

So we have Miles,

Kilometers that's just km,

gallons, gal and liters.

So, let's go ahead and put the code in here.

The easiest button to code is the QUIT button,

so I'm going to double click on QUIT button.

The name of our form is FuelCalculator.

So there's two ways to quit and I'm going to use unload FuelCalculator.

And that will then delete anything that the user has

put into the text fields of that user form.

One thing I didn't put on here is a reset button.

So if you like you can put a reset button.

The reset button will just unload the FuelCalculator form and then you can show it again.

So, let's go ahead and code the GO button.

So I'm going to double click on the GO button.

Now we're going to start everything in a two-way If Then,

because what we've got is that the distance can either be miles or kilometers.

So this radio button,

remember radio buttons are booleans they're either true or false.

So this is called Miles.

So I'm going to say If miles Then,

and then we're going to do whatever assuming that distance is in miles,

Else we're going to assume that it's in kilometers.

So, inside the Miles we're also going to have another If Then,

and that is If gallons Then,

and then we're going to have an Else.

So, if we haven't selected gallons, remember,

gallons corresponds to this radio button here.

So if gallons isn't selected then we're going to assume that we're in liters.

So I've got that. We have the End If and I'm just going to go ahead and copy this.

Because essentially what we've got is four different options,

we can have miles and gallons,

miles and liters, we can have kilometers and gallons,

or we can have kilometers and liters.

So we're going to have four different cases here.

For each of the four cases,

we're going to have two outputs.

We want to output the miles per gallon that's mpg,

and we also want to output kilometers per liter which is kpl.

The easiest two are Miles per gallon and Kilometers per liter,

so mpg corresponds to this text field here, this text box.

Mpg then is equal to distance divided by volume.

The other easy one to do is Kilometers per liter.

So If we're not Miles,

Then we're down here in Kilometers.

But we're here Kilometers per liter.

And Kilometers per liter Then,

assuming we've checked the right things which sort of directs us to this last one here.

Then Kilometers per liter is going to be distance per

volume because distance will be in kilometers,

volume will be in liters.

Now we can put in the other,

so we're going to have Kilometers per liter.

For each of these four options we're going to have mpg and kpl.

So we just use typical conversion factors.

If you're not quite comfortable with the math here,

these are just conversion factors because for example,

here in this first one,

we have distance is in miles volume is in gallons.

So what I'm doing in the numerator here,

is converting distance in miles to kilometers,

and then I'm taking the volume in gallons and converting that to liters.

So we're just going to do this,

for the other six cases,

we're going to put in these conversion factors.

And so, I've spent some time doing this.

I've also added the format number which will

round the answer to the nearest to the 10th place.

So we've got all of our results here.

And now, I think we're ready to go.

So now I can go to my Fuel Calculator.

Let's just press run here.

Let's do a couple of things so we can kind of test.

So if I go 100 miles in five gallons,

I should get a gas mileage of 20 miles per gallon, which seems right.

Let's do 100 kilometers in five liters,

I don't know if that's on the right order of magnitude,

but we can calculate,

we get 20 kilometers per liter.

And let me just make sure some of these other options work,

miles, and liters, and so on.

And then the QUIT button works.

Now the last things we need to do,

are we need to make it so that we can run this from the Spreadsheet.

In order to run it from the Spreadsheet,

we need to assign a button.

So I need to have a button here.

I'm going to go insert a button.

But before we can insert a button,

the button can only run a normal module.

So I'm going to go ahead and insert a module in which we show the Fuel Calculator form,

and I'm calling this run form.

So now on the spreadsheet,

I can insert a button.

So I'll just put it here. And I can assign that to a run form module.

And then I can rename that something like Calculate.

So when I press the Calculate button,

it's running the run form module.

The run form module opens up our user form,

and we get the user form to open.

And now, I can quit.

The last thing I'm going to do,

is if we're sending this to a client or co-worker,

we want it to automatically open upon when they open up the workbook.

So I can put event handler behind this workbook.

I can go up here to workbook and we enter in this,

and I'm just going to run.

There's two things you can do, you can either do FuelCalculator.Show,

or just type in the name of the run form module,

so when we open up,

we're going to run that and we're going to open up the user form.

Either way, you're going to get the same result.

But you definitely need a module with

FuelCalculator.Show because the button here is assigned to that,

and you can't just open a user form using a button.

So, that's it for just making a basic user form in VBA. Thanks for watching.

Explora nuestro catálogo

Inscríbete de manera gratuita y obtén recomendaciones personalizadas, actualizaciones y ofertas.

Coursera brinda acceso universal a la mejor educación del mundo, al asociarse con las mejores universidades y organizaciones, para ofrecer cursos en línea.

© 2018 Coursera Inc. Todos los derechos reservados.

Descargar en la App Store

Consíguelo en Google Play