Numeric expressions

A numeric expression is one that…, er, gives you a number.

Surprise

Here's a numeric expression:

3 - ( aVariable / anotherVariable ^ 2 + 4) * Sqr( yetAnother + 13 )

It has variables (aVariable, anotherVariable, and 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.

Integers

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.

Lily
Lily
What if you want to round? So that 3.3 is 3, and 3.7 is 4?

Tara
Tara
Use the 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.

Longs

There's a problem with integers, though. They only go up to 32,767. Check out this code:

  1. Dim i As Integer
  2. i = 32767
  3. i = i + 1
When the program tries to run line 3:

Error

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. Long is short for "long integer." This code works just fine:

  1. Dim i As Long
  2. i = 32767
  3. i = i + 1
Longs can go up to 2,147,483,647.

Klaus
Klaus
So why use integers, if there are longs?
Tara
Tara
You could do that. Longs take more memory, but these days, it doesn't matter much. If you want to use longs instead of integers in all your programs, that's OK.

Singles

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.

Operators

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

Examples:

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.

Priority Operator(s)
1 ^
2 * and /
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

+ and - are equal priority. They're done from left to right.

41 - 1

40

Use () 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

243

Fill in the blank

Expression's value 1

What does this work out to?

11 + 3 - 2

Your answer:

Fill in the blank

Variable's value

What will c be after this calculation?

Dim a As Single
Dim b As Single
Dim c As Single
a = 4
b = a * 1.5
c = a * (b - 1.5 * 2 + 1) / 2 - 1

Work it out on paper first, before you try it in Excel.

Your answer:

Functions

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:

Sqr() Square root Sqr(9) is 3
Abs() Absolute value Abs(-4) is 4
Int() Integer part of a single Int(3.88) is 3
Round() Rounds a single Round(3.88) is 4.
Round(3.88, 1) is 3.9

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…

Sqr(9)

… you might think that you can only send constants into functions. Not so. You can send variables:

Sqr(aVariable)

In fact, this is the formal syntax of Sqr():

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.

Fill in the blank

Hyp's value

What will hyp be after this calculation? Cell A2 is 3, and cell B2 is 4.

Dim width As Single
Dim height As Single
Dim hyp As Single
width = Cells(1, 2)
height = Cells(2, 2)
hyp = Sqr(width ^ 2 + height ^ 2)

Work it out on paper first, before you try it in Excel.

Your answer:

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:

Type error

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.

Input validation

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.

Put some text into A2, and then run the program. What happens?

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.

The End statement ends the program.

Here's the pattern:

Pattern

Input validation

Situation:
You get input from the user. S/he might have made a mistake.

Actions:
Add an If statement to check the data before processing it.

If you click More, you'll see that it's a relatively long pattern. It's worth reading through it.

Fill in the blank

Animal type

What will animal be after this calculation?

Dim a As Single
Dim b As Integer
Dim c As Single
Dim animal As String
a = 3
b = a * 1.5 - Sqr(a + 1)
If b <= 2 Then
    animal = "dog"
Else
    animal = "cat"
End If
Your answer:

Exercises

Exercise: Seesaw weight difference
Seesaws are fun! Unless the difference between the weights of the people is too great. Then…

Seesaw, 1902

If the difference between the weights of the people is more than 20% of their total weight, maybe they should skip the seesaw.

Here is a workbook with a program that warns the user if the difference between the weights is too high. Download the file, and save it on your computer.

In this situation…

Low difference

… 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…

High difference

High difference

… 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 difference.

Attachments

seesaw-weight-difference-buggy.xlsm

(If you were logged in as a student, you could submit an exercise solution, and get some feedback.)

Summary

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.