Visual Basic Lesson 1
ITF - Mr. Shultz

In this lesson, you will be learning how to use Microsoft Visual Basic, sometimes referred to as Visual Basic for Applications.

Visual Basic is a programming language built into Microsoft Office Products.

We'll be using Visual Basic to learn how to write program code.

For the rest of this lesson, VB may be used as an abbreviation for Visual Basic.

We will use Microsoft Office Excel 2010 to create some small programs.

Before we can use Visual Basic in Excel, we have to complete some steps to set it up.

First, let's go ahead and open Excel.

Click on the Start button at the bottom left of Windows, then click on All Programs.

You may have to scroll down, then click on the Microsoft Office folder, then Microsoft Office Excel 2010:

vba201.jpg

You should now be looking at a blank worksheet in Microsoft Excel.

Excel has named this worksheet Book1, but we're going to re-name it.

To do that, click on the File menu on the top left corner of the Excel window, then click on Save As:

vba202.png

 

In the Save As dialog box that comes up, complete the four steps shown below:

vba203.png

Now, you will need to do a little more to to use Visual Basic - Complete the four steps shown below:

 vba204.png

 

You should now see the "Developer" tab on the Ribbon.

If you don't see it, go back and repeat the steps on the previous two pages.

If you do see it, click it!

vba107.png

On the Developer tab, click on Visual Basic:

vba108.png

In the Visual Basic window, DOUBLE-click on "This Workbook":

vba109.png

Now you will see that you have a new window on the right side of the screen.

vba110.png

This is where you will enter the code for your program!

OK, Excel is set up, what do we do now?

The first thing you need to do is to create a Subroutine.

A Subroutine is a small portion of code that performs a specific task in a program.

To create your Subroutine you will type in:

vba1code01.JPG

NOTE - there is a space between "Sub" and "ConvertTemp".

The "Sub" part tells Visual Basic that you are creating a Subroutine.

The "ConvertTemp" is the name of the Subroutine.

NOTE - the end of the line is actually the open and close parentheses symbols (it's not a zero).

The parentheses symbols tell Visual Basic that the name is done.

Remember, you must type this in EXACTLY as you see it - the computer isn't smart, it's just fast!

If you haven't already done it, type in Sub ConvertTemp() and press the Enter key:

vba111.png

Hey, what happened, you just typed in "Sub ConvertTemp()", you didn't type in "End Sub"!

Actually, Excel did that for you.

The Subroutine you started with "Sub" has to end with "End Sub".

Excel knows that and puts it in for you. Thanks, Excel!

Now that you've created a Subroutine, the next step is to create Variables.

Variables are dedicated blocks of memory in a computer that hold data for your program.

In a program, Variables are kind of like individual sheets of paper in a notebook.

The computer can use the Variables to store information just like you would write information in a notebook.

But just like the name says, Variables are NOT constant!

Change is an important and necessary aspect of Variables.

Just like you can erase information from a sheet of notebook paper and replace it with new information, you can change the information held in a Variable.

When you create Variables in Visual Basic, you must tell Visual Basic how much space to reserve.

This is like saying how large of a sheet of paper you need to hold your information.

The way you tell Visual Basic how much space to reserve is to declare a Variable Type.

Visual Basic has five possible Variable Types:

Now that you know about Variable Types, we need to create some Variables for our program.

Be sure to type these lines BEFORE the "End Sub" that Excel made for you. Anything typed AFTER the "End Sub" will NOT be part of the subroutine.

To create your first Variable, you will type in:

vba1code03.JPG

The "Dim" part tells Visual Basic that you are creating a Variable. Dim is short for Dimension, which means "to create" in Visual Basic.

The "F_Temp" part is the name of the Variable. It is short for "Fahrenheit Temperature".

It is usually a good idea to abbreviate names in programming, since you will have to type in the variable name later in the code.

After the name, the "As Single" tells Visual Basic to reserve enough space for whole numbers up to 32,768.

That means that the program user will only be able to put in temperatures that are whole numbers.

That's good, but we'll need two more Variables for our program.

The second variable you'll type in will be:

vba2code04.JPG

This creates "C_Temp" (short for "Celsius Temperature") with the Variable Type of Single.

The third variable you'll type in will be:

vba1code05.JPG

This creates "Message" with the Variable Type of String. We will use this Variable to provide output to the user.

So, if you haven't already typed in your variables, go ahead and type them in now. It should look like this:

vba112.png

OK, now we have Variables, let's get the rest of the code to make this program do something.

Before we do that, you have to know about Syntax.

Syntax is the rules that govern how statements in language (human and computer) must be constructed.

Every programming language has its own syntax that must be followed for a program to work.

This is just like every human language that has grammar rules that must be followed (or else you wouldn't make any sense!)

So, when we want to code the input, process, and output, we must to use the correct syntax.

To input data with Visual Basic, the syntax looks like this:

Variable = Application.InputBox("Request Prompt")

Notice that a Variable is being set equal to the Input Request.

Also, notice that there are spaces before and after the = sign.

And, very important, there is NOT a space between Application.InputBox and ("Request Prompt").

In programming, the "=" actually means assign the value of the process on the right to the variable on the left of the sign.

So, for our Celsius to Fahrenheit program, we will type in this code to get a Celsius temperature from the user:

vba1code06.JPG

Notice that we are using the Variable "C_Temp" that was created earlier.

The "Application.InputBox" part is a function built into Visual Basic. It will make a nice input box we can use to ask the user for input.

The "("What is the temperature in Celsius?")" part tells the "Application.InputBox" part what words to show to the user. In this case, we're asking a question.

Go ahead and type in this code. Your program should look like this:

vba113.png

Note that there are some blank lines.

You don't have to do this, but it can help you identify the different parts of the program.

So far, our program has only gotten the temperature in Celsius from the user.

Now we need to process that data and convert it to Fahrenheit.

Remember when we wrote the algorithm, processing the data looked like this:

Let's see what this looks like in Visual Basic syntax.

First, we will multiply the user's input by 9.

Remember, we used the Variable "C_Temp" to store the information.

To multiply by 9, the code will be:

vba1code07.JPG

Don't forget, the "=" means assign the value of the process on the right to the variable on the left of the sign.

So, the C_Temp that the user input is multipled by 9 (on the right side of the "=").

Then, the new value is assigned to C_Temp.

Using the notebook paper example, we looked at the number written on the notebook paper, multiplied it by 9, then erased it and wrote down the new number in the same place.

The next line of code will be very similar, it will divide the new C_Temp value by 5 and then change C_Temp again. This code is:

vba1code08.JPG

The third line of processing code will add 32 to C_Temp.

But, since this will complete the conversion from Celsius to Fahrenheit, we will assign the result to F_Temp. This code is:

vba1code09.JPG

Go ahead and type in the three lines of processing code. Your program should look like this:

vba114.png

OK, you've got the code typed in, but let's review what that code is doing.

To do that, let's say that the Celsius temperature the user input was 100.

So, the "C_Temp = Application.InputBox("What is the temperature in Celsius?") writes the number 100 in the Variable C_Temp.

The "C_Temp = C_Temp * 9" multiplies 100 times 9, erases the 100 from C_Temp, and writes 900 in C_Temp.

The "C_Temp = C_Temp / 5" divides 900 by 5, erases the 900 from C_Temp, and writes 180 in C_Temp.

The "F_Temp = C_Temp + 32" adds 32 to 180, and stores the result, 212, in F_Temp.

Now, let's show the user the result.

All of this work to get user input and process it would be useless if we couldn't provide output to the user.

And just like the other code we have created, you must follow the correct syntax to do this.

To output data with Visual Basic, the syntax looks like this:

Variable = MsgBox("Text Message" & Variable)

Notice that the output of your code can contain two types of information:

To add the two types of information together, you use the Ampersand (&).

Now, let's take that syntax and apply it to our program.

In our temperature conversion program, the line of code for output will look like this:

vba1code10.JPG

Notice that we are using the Variable "Message" that was created earlier.

The "MsgBox" part is a function built into Visual Basic. It will make a nice output box to show the user the output.

The "The temperature in Fahrenheit is " part tells the "Application.InputBox" part what words to show to the user.

In this case, we're telling the user what the temperature is. Notice that there is a space after the word "is" and the ending double quote mark. If you do not add this space, the temperature number will come right after the word "is" with no space - that just wouldn't look right.

The & F_Temp part adds the value stored in the F_Temp Variable (the temperature number) after the text.

Go ahead and type in this code. Your program should look like this:

 vba115.png

OK, now you have all the program code done.

Now it's time to run the program and see if it works. 

To run the program you three options:

Click on the Run menu and click "Run Macro" (It may say "Run Subroutine")

Press the F5 key

Click on the toolbar button that looks like a play button

vba116.png

If you see this dialog box, click on "Run":

vba117.png

NOTE - If your program runs at this point, go ahead to Page 22.

 

You only have to go through the rest of the steps on this page if your program does NOT run.

If you see this dialog box, click on "OK":

vba118.png

After clicking on OK, look for the Excel button on the taskbar (at the bottom of the screen):

vba119.png

Click on it to bring up Excel. In Excel, make sure you're in the Developer tab, and click on "Macro Security" in the Code group:

vba120.png

On the Trust Center dialog box, click in the circle next to "Enable all macros (not recommended; potentially dangerous code can run), then click on OK.

vba121.png

After clicking on OK, close Excel.

Then, go to your Z drive and double-click on your VBA1 Excel file. Go to the Developer tab, click on Visual

Basic, and try again to run your program.

If you have trouble running the program, go back and compare your code to the code in the instructions.

Remember, the computer is fast, it's not smart, so the code must match EXACTLY.

That means that you must use capitals and lower case exactly like the instructions.

Also, make sure you didn't put in any extra spaces, and you put in quotation marks where they are shown.

 

If your program runs, you need to test the code to make sure it is calculating correctly. To do this:

If either answer is wrong, check your code to make sure it matches exactly. Make changes as needed until you get the correct answers.

Great, you've completed your first program!

Don't forget to save it now!

Then, go ahead and CLOSE the Visual Basic window and CLOSE Excel.

Go to the next page to complete the assignment.

Now, start Excel again.

Just like before, use "Save As" and be sure to click on "Excel Macro-Enabled Workbook".

Also, be sure to save in your Z drive.

The one thing to change this time, is that you will save this workbook using the name "VBA2".

Now, check to see if Excel remembered to show the Developer tab in the Ribbon.

If you see the Developer tab in the Ribbon, you don't have to do anything to add it.

If you do not see the Developer tab in the Ribbon, go into Excel Options like you did earlier and check the

"Show Developer tab in the Ribbon" box.

Now, using the code from the Celsius to Fahrenheit program as an example, enter the code to:

If your program runs, you need to test the code to make sure it is calculating correctly. To do this:

If either answer is wrong, check your code to make sure it matches exactly. Make changes as needed until you get the correct answers.

 

When you're done, go to the Schoology Algorithm/Flowcharting/Visual Basic folder, and click on the Visual Basic Lesson 1 Excel Files assignment. Read the instructions, and submit BOTH the VBA1 and VBA2 Excel files.