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.

Lily

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

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

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

Klaus

So why use integers, if there are longs?

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.

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.

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

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

​

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:

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.

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)

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.

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.

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.

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

Exercises

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.