A numeric expression is one that…, er, gives you a number.
Here's a numeric expression:
3 - ( aVariable / anotherVariable ^ 2 + 4) * Sqr( yetAnother + 13 )
It has variables (
yetAnother), constants (3, 2, 4, and 13), operators (-, (), /, ^, +, and *), and a function (
Sqr(), that calculates square root).
Numeric data types
Each variable has a data type. The data type limits what data you can put into the variable. VBA has about half-a-dozen numeric data types. We only use a few in this course.
Integer variables contain whole numbers, positive or negative. -3 is an integer. So is 122.
5.828 is not an integer, so you can't put it into an integer variable. What happens if you try? Suppose this is your code:
Dim foo as Integer foo = 5.828 Cells(1, 1) = foo
You'll see 5 in cell A1 in the worksheet. The CPU truncates numbers (cuts off the decimal part) before putting them into integers.
Round()function for that. So…
Cells(1,1) = Round(3.3)
… will put 3 in the cell. This…
Cells(1,1) = Round(3.7)
… will put 4 in the cell.
Integers are often used for counts of things. Like how many spleens you have, or how many llamas.
There's a problem with integers, though. They only go up to 32,767. Check out this code:
Remember that a variable is a piece of memory. "Overflow" means the program tried to put a value into a piece of memory that's too small to hold it. The data overflows the memory space.
There's an easy fix: the data type
Long is short for "long integer." This code works just fine:
Longs can go up to 2,147,483,647.
Singles are numbers that can have fractional parts. Like 3.4, -2.3, and 15. Yes, 15 can be a single. Think of it as 15.0.
BTW, single is short for single precision floating point. Let's just call them singles.
Here are the most common arithmetic operators.
|+||Addition||3 + 1 is 4|
|–||Subtraction||6 – 10 is -4|
||Multiplication||7 * 2 is 14|
|/||Division||10 / 2 is 5|
|^||Power||3 ^ 2 is 9|
circum = 2 * 3.14159 * radius area = 3.1419 * radius ^ 2 score = goals * 6 + behinds
As in normal arithmetic, some operators have a higher priority than others.
|3||+ and –|
Operators with the same priority are done left to right.
Checkout this expression
5 + 4 * 3 ^ 2 - 1
^ as the highest priority, so it's done first.
5 + 4 * 9 - 1
* has the next highest priority, so it's done next:
5 + 36 - 1
- are equal priority. They're done from left to right.
41 - 1
() to change the order. For example:
(5 + 4) * 3 ^ (2 + 1)
That's done in this order:
9 * 3 ^ (2 + 1)
9 * 3 ^ 3
9 * 27
A function is a piece of code with a name. You send some data into a function, it does something with the data, and sends out a result. Here are some common functions that are built-in to VBA:
||Integer part of a single||
||Rounds a single||
The expressions in the
() are called parameters. Most VBA functions have one parameter.
Round() can take two parameters. The second one is the number of decimal places to round to.
When you look at…
… you might think that you can only send constants into functions. Not so. You can send variables:
In fact, this is the formal syntax of
Sqr( [expression] )
So each of the parameters of a function is an expression. Examples:
hyp = Sqr( width ^ 2 + height ^ 2 ) root = (-b + Sqr( b ^ 2 - 4 * a * c )) / 2 * a somethingStrange = Round( (0.97 * 1.133 + 5) * 4, 2)
Everywhere you can put a constant or a variable, you can put an expression.
Trouble with types
This code won't work so well:
Dim i As Integer i = "Cow"
i is an integer, and integers can't hold text. You'll get:
Here's another one:
Dim i As Integer i = Cells(1, 2)
Will this be a type mismatch? It depends what's in cell B1. If it's a number, like 45, no trouble. But what if B1 has text, like "llama"? Type mismatch. Ack.
Instead of letting the program crash, how about we check what's in the cell before we put it into the integer variable? This is called input validation.
Here's Eva for the tip calculation again.
Whether the CPU is running a program or not. Next line
Next line of the program to be run.
You can change it. Evaluator
Where the CPU does calculations. What just happened
Here's a version with input validation.
IsNumeric() is a function that returns true if the data you send it is a number. So the expression…
Not IsNumeric(Cells(1, 2))
… is true if the content of cell A2 is not numeric. If so, the program shows a message to the user, telling him/her what went wrong. That's what
MsgBox does. Send it some text, and it will show it on the screen.
End statement ends the program.
Here's the pattern:
If you click More, you'll see that it's a relatively long pattern. It's worth reading through it.
If the difference between the weights of the people is more than 20% of their total weight, maybe they should skip the seesaw.
In this situation…
… the difference in weights is small. Click the Run button, and the program should tell you that seesawing is no problem. But in either of these situations…
… the program should warn you that there could be a problem.
The program doesn't work right. Fix it, and submit the fixed workbook.
Hint: Remember that you can use the debugger to look inside the code. Check out the value of variables, like
(If you were logged in as a student, you could submit an exercise solution, and get some feedback.)
When the CPU's evaluator computes a number, you have a numeric expression. There are two numeric data types in this course: integers and singles. Integers are whole numbers. Singles are not.
Use operators and functions in expressions, to do calculations. Some operators have a higher priority than others.
Try to put text into a numeric variable, and Bad Things happen. You can use input validation to check data, before you put it into a numeric variable.