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 (`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.

`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:

- Dim i As Integer
- i = 32767
- i = i + 1

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:

- Dim i As Long
- i = 32767
- i = i + 1

`Long`

s can go up to 2,147,483,647.
## 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`

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

# 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 A2. If it’s a number, like 45, no trouble. But what if A2 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.

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:

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

# Exercises

\[a x^2 + bx + c=0\]

The roots of a quadratic are where it crosses the x axis, that is, where y is zero. Quadratics have two roots. For example:

This formula can compute the two roots:

\[x = {-b \pm \sqrt{b^2-4ac} \over 2a}\]

To get the two roots, use the formula twice. Once with -b + …, and once with -b – …

Write a program that will compute the two roots. For example:

Use VBA for all calculations, of course.

Upload your Excel file.

Show an error message if a is zero.

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

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…

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

.

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