Initializing the combo boxes and today's date

Loading...

En provenance du cours de University of Colorado Boulder

Excel/VBA for Creative Problem Solving, Part 3 (Projects)

11 notes

University of Colorado Boulder

Excel/VBA for Creative Problem Solving, Part 3 (Projects)

11 notes

Cours 3 sur 3 dans Specialization Excel/VBA for Creative Problem Solving

In this course, learners will complete several VBA projects. It is highly recommended that learners first take "Excel/VBA for Creative Problem Solving, Part 1" and "Excel/VBA for Creative Problem Solving, Part 2". This course builds off of skills learned in those two courses. This is a project-based course. Therefore, the projects are quite open-ended and there are multiple ways to solve the problems. Through the use of Peer Review, other learners will grade learners' projects based on a grading rubric.

À partir de la leçon

Easy Projects

Choose one of two potential easy projects to complete!

Introduction to the currency converter project4:58

How to run a data query8:33

Initializing the combo boxes and today's date8:09

Importing data from the currency website using proper date format10:33

Plotting the last 30 days10:03

Rencontrer les enseignants

Charlie Nuttelman
Instructor
Chemical and Biological Engineering

One of the first things you're going to need to do for

the currency converter project is to initialize the initial user form.

The user form that I've created as shown here,

you're welcome to make it however you would like.

So, I've got a couple combo boxes and I've got this date here,

and in this screencast,

I'm going to go through how to initialize

the combo boxes and also to initialize the date.

The combo boxes, what you populate them with, the dropdown items,

and the date, all have to be populated before you show the user form.

What you're going to want to do is to start with a sheet here,

I've named this currencies,

that just has all the currencies found on the imported website.

So, I've just got a column of all the three letter codes plus the actual currencies,

and those are in columns B.

But when you bring this thing up,

it needs to concatenate or join

the three letter code with the more descriptive phrase for that unit of currency.

Also, you notice that it, by default,

has populated the date box with today's date.

So, let me just show you this with a real easy example.

I've just got three things here,

I've got three letters and I've got three phrases.

We're going to concatenate or join the letters in column A with

the phrase in column B and then we're going to add that to a user form.

So, I've just created a real basic user form,

I've got a combo box one and I've got a box here,

which I'm going to name DateBox.

The way we do this is you have to create a module.

So, I'm going to create a module and this module is going to populate that combo box and

it's also going to populate the date box with today's date when we open up the user form.

So I've just created a subroutine here, called sub OpenForm.

On my example, this combo box is named ComboBox1.

So, I'm going to use that here in my subroutine.

I want to populate that combo box with the items that are found in column A here.

So, what I'm going to do is I'm going to activate sheet one

and I'm going to select Range A1 as our starting point.

So after running sheets Sheet1 Select and Range A1 Select,

I will have selected Range A1 of sheet one and we want to make sure we do sheet

one because we might have

some other sheets in there and we don't want to make an ambiguous reference.

So, now we're going to put in the code to populate

the combo boxes with those items in column A.

And at the same time we're going to concatenate those with the items in column B.

Because I've got multiple items,

I'm going to iterate through.

So, I'm going to Dim i as an integer,

I'm going to iterate through a couple of items.

I'm iterating from one to,

I'm using the count function here to count the number of items in column A,

so that'll be three,

and then I'm using inside the iteration here,

inside the for loop UserForm1.ComboBox1.AddItem.

So that's going to add the item to the combo box into

the dropdown list and I'm active cell offsetting by i - 1 rows and 0 columns.

So when i equals 1,

we're going to active cell offset by zero rows and zero columns.

So that's just Range A1 which makes sense.

We are going to go through all three of those items and at the end,

we're going to just make the default value equal to the first value.

So I'm just saying, UserForm1.ComboBox1.Text equals Range A1.

That just makes the default value when it pops up.

Right now, we can just make sure that this is working.

I can press F8.

I forgot to put worksheet function here.

So now I've corrected that to worksheet function and I step through this using F8.

We're going to count the number of items so that should be equal to three,

and for each of those three items,

we're going to add to the combo box.

We're going to add an item and then we're going to make the default equal to Range A1.

Now, at this point, I don't have,

I'm not showing the UserForm,

so let's just put UserForm1.Show and now if I do that,

it'll pop up with UserForm1 and you see in our dropdown list our combo box,

we have those three items that were found in column A.

The default value, that is the text that we put in there,

that line for the text,

and that's equal to just Range A1.

We also wanted to concatenate the items

that were in column A with the items that are in column B,

so let's go ahead and do that right now.

We can use the ampersand to concatenate two things.

So I've just added on this second part here where we're

concatenating the active cell offset in column A with

the hyphen with spaces on either side and concatenating

that with the one row overactive cell offset.

So what we're doing there is we're taking

the item that is in column A and concatenating with

a hyphen with spaces on either side of the hyphen and

joining it with whatever's in the same row in column B.

And I've done the same thing for our default text in the combo box.

Now when we run this, I'm just going to press F5,

it pops up and you see now,

I've concatenated whatever is in column A

of each row with whatever is in column B of that row.

So you're going to want to do something similar for your currency converter project.

The last thing I'm going to show you is how we can

display in the user form into this field,

I've named that DateBox.

I'm going to show you how we can put today's date in there.

There is a Now function built into VBA,

so I've just got a test sub here and if I run this,

it gives you a message box with today's date,

the time, and if it's AM or PM.

And each of these items is separated by a space.

So we can take advantage of the split function of

VBA to split the output into the message box,

the output of the Now function into three different components,

and then we're going to use the first component.

And actually, that would be the zero element because the split function creates

a vector from these three different items

and the zero of item is the first item of that vector.

So I've Dim DateArray as a variant,

I've Dim TodayDate As String,

and then I've set DateArray equal to Split of Now.

So let's go ahead and I'm just going to run to this line and if we look at this,

I go down here to the Locals window and let me just run this line.

You see that in the DateArray,

it split the Now function into three components.

So we have a vector.

The zeroth element of that is what we want to use.

So, we want to actually use today's date which 2/25/2018.

Now, what we want to do is we want to display,

we want to say DateBox on

our UserForm recall that DateBox corresponds to this text input field.

And then what I'm going to do is I'm going to say,

UserForm1.DateBox equals DateArray zero.

So that again, DateArray zero is just today's date.

So, I'm going to go ahead and run this, going to press F5.

And when we do that, we populate the ComboBox here

and we also set that DateBox field equal to today's date.

So that's how you can then populate the combo boxes and you can

set the default date to today with your currency converter project.

Thanks for watching and good luck.

Explorer notre catalogue

Rejoignez-nous gratuitement et obtenez des recommendations, des mises à jour et des offres personnalisées.

Coursera propose un accès universel à la meilleure formation au monde, en partenariat avec des universités et des organisations du plus haut niveau, pour proposer des cours en ligne.

© 2018 Coursera Inc. Tous droits réservés.

Télécharger dans l'App Store

Disponible sur Google Play