Working with Numbers in VBScript
By The Microsoft Scripting Guys
Welcome to Sesame Script, the column for beginning script writers. The goal of this column is to teach the very basics of Windows scripting for system administration automation. We’ll provide you with the information you’ll need to begin reading and understanding scripts and to start modifying those scripts to suit your own needs. If there’s anything in particular about scripting you’re finding confusing, let us know; you’re probably not alone.
Check the Sesame Script Archives to see past articles.
On This Page
Working with Numbers
Operators
VBScript Functions
IsNumeric
FormatNumber
Math Functions
Conversion Functions
More Information
Wrap-Up
Working with Numbers
When you were in school, was there ever that one person in math class who used to whine to the teacher, “When am I ever going to use this in real life?” Was that you? Well, we’re glad you grew out of all that whininess; the rest of us were getting tired of it. And we’re sure that by now you’ve probably discovered why you needed that math class. Okay, so maybe you don’t spend your days writing out proofs or trying to figure out what y is in the equation 4a + 7b = 27y. But you still need to know something about how numbers work. Even in scripting, there can be a little more to numbers than simply 2 + 2 = 4.
You want us to prove it? No, sorry, we don’t do proofs anymore, either.
Well, okay, just this once. Aren’t you glad that we paid attention in math class?
Operators
When you work with VBScript you use the standard arithmetic operators: +, -, *, and /. So, yes, it’s true, with this script you can still simply add 2 + 2 and get an output of 4:
Wscript.Echo 2 + 2
But say we want to get just a little bit more complex. Say, for example, we want to add 10 to 20 (for a total of 30), then divide the answer by 2 (to get 15). We could try this:
Wscript.Echo 10 + 20 / 2
Unfortunately, though, that won’t work. If we run that script our output will be 20. Why? Because VBScript and Windows Script Host (WSH) follow the basic rules of arithmetic when it comes to precedence. That means that equations are evaluated in this order:
*, / (multiplication, division)
+, - (addition, subtraction)
What this means is that in our equation, 10 + 20 / 2, the first part of the equation to be evaluated is the division: 20 / 2. The result of that calculation is 10, so when 10 is added to 10 we get output of 20.
So how do we get the value of 15 that we were looking for? We simply use parentheses:
Wscript.Echo (10 + 20) / 2
Remember the precedence order we just showed you? Well, parentheses come before any of that. Therefore, the first thing that happens is that we calculate the part of the equation that’s inside the parentheses, 10 + 20, to get our value of 30. Then the rest of the equation is calculated: 30 is divided by 2 for a final result of 15.
VBScript Functions
VBScript provides quite a few functions to help us with our math and with numbers in general; it’s actually a little surprising how many functions VBScript devotes to working with numbers. But that’s okay; we’re sure someone has found a use for all of them at some point. Let’s take a look at a few of the more frequently-used functions.
IsNumeric
One thing that might be useful to know is whether you’re actually working with a number. Suppose you have a script that reads from a database or a spreadsheet, some place where the data entered might not be completely reliable. If you’re expecting a number and you get a letter unpleasant things can happen:
a = 5
b = "A"
c = a + b
Wscript.Echo c
If you run this script here’s the unpleasant thing that will happen:
C:\Scripts\begin\test.vbs(3, 1) Microsoft VBScript runtime error: Type mismatch: 'b'
This error message tells us that there’s a problem on line 3, and that the problem is a type mismatch with the variable b. That’s a somewhat obscure way of telling you that you can’t use b in the equation because b contains a string, and you can’t add a string to a number. (For more on data types, see What Type of Data Is That?) An easy way around this is to check ahead of time to make sure that a and b both contain numbers before we try to add them. We do that with the IsNumeric function:
a = 5
b = "A"
If IsNumeric(a) And IsNumeric(b) Then
c = a + b
Wscript.Echo c
Else
Wscript.Echo "Both values must be numeric. a = " & a & " and b = " & b
End If
In this script we set the values of a and b, then we call the IsNumeric method, passing it the variable a. IsNumeric returns either True or False, which is why we can put the method right in the middle of the If statement; we’re simply saying, “If IsNumeric returns True Then…. (For more on If statements see the Sesame Script article Make a Decision.) Because a and b both need to be numbers in order for our equation to work, we also call IsNumeric and pass the variable b to make sure b is a number. If a and b are both numeric (that is, if IsNumeric returns True for both a and b) then we add the two numbers and echo the result. If they’re not both numeric we echo a message and display the values of a and b.
This time our output looks like this:
Both values must be numeric. a = 5 and b = A
Much better than crashing with a “Type Mismatch” error.
FormatNumber
Another helpful function having to do with numbers is FormatNumber. This function allows you to, well, format a number. FormatNumber can take as many as five parameters:
Number |
The number you want to format. |
NumberOfDigits |
The number of digits you want displayed after the decimal point. For example, if you pass the number 1.5 but specify 2 as the number of digits, FormatNumber will return 1.50. |
LeadingZeroes |
This can be one of three values. -1 indicates that you want to display a leading zero when you’re working with fractions. For example, .75 would be displayed as 0.75. A 0 indicates that you don’t want to display a leading zero. -2 indicates that you want to use the regional settings on the computer to determine whether leading zeroes are displayed. |
NegativesInParens |
This can be one of three values. If you pass a value of -1 negative numbers will be displayed in parentheses rather than with a minus sign. For example, if you pass the number -5 your output will be (5). A value of 0 indicates you don’t want to use parentheses to represent negative numbers, and a value of -2 indicates that the regional settings should be used. |
GroupDigits |
This parameter can also be one of three values. A -1 indicates that you want to use the delimiter listed in the regional settings to group thousands. For example, if you pass the number 5000 and your system settings use a comma as a delimiter, your output will be 5,000. A 0 indicates you don’t want to do any grouping, and -2 also means use the regional settings. |
All parameters but the first are optional. If you don’t specify any of the others, the regional settings for the computer will be used.
Here’s an example of using FormatNumber:
a = 2.5793
b = "400"
c = FormatNumber(a, 2)
d = FormatNumber(b, 4)
Wscript.Echo c
Wscript.Echo d
And here’s our output:
2.58
400.0000
In the first instance we passed two parameters to FormatNumber: the variable a and the number 2. As our output shows, we displayed the number stored in a, 2.5793, showing two digits past the decimal point. As you can see, FormatNumber rounded up for us.
In the second instance, we passed a string” “400”. If we had passed a string containing non-numeric characters VBScript would have returned a “Type Mismatch” error. But because VBScript recognized that the string contained only numbers, it went ahead and did the conversion. This time we wanted to display four decimal places after the number, which is exactly what happened.
Now let’s suppose we want to use the default settings (in other words, use the regional settings) to format the number, with the exception of negative numbers; in that case we want negative numbers to be displayed in parentheses. Let’s take a look at how we’d go about doing that:
Const NegativesInParens = -1
a = -2.5793
b = -30.7
c = FormatNumber(a)
d = FormatNumber(b,,,NegativesInParens)
Wscript.Echo c
Wscript.Echo d
The first thing we do in this script is declare a constant, NegativesInParens, and set the value to -1. We’ll use this constant in our call to FormatNumber to specify that we want negative numbers to be displayed in parentheses. We then assign values to the variables a and b. As you can see, we’ve assigned negative values to both variables.
After that we call FormatNumber on a and then on b, assigning the output to the variables c and d, respectively. But let’s take a closer look at those calls to FormatNumber:
c = FormatNumber(a)
d = FormatNumber(b,,,NegativesInParens)
In the first call we haven’t specified any parameters other than the number we’re formatting. That means the number will be formatted according to the regional settings on the computer.
In the second call, the first parameter we pass to FormatNumber is once again the number we want to format. After that we put a comma, then…wait a minute, now we have another comma…and another, and another. What are all the commas for? Well, remember our list of parameters we showed earlier? That list is in order. What that means is that when you pass parameters to FormatNumber the number you’re formatting has to come first, then the number of digits setting, then the leading zeroes setting, and so on. In this case, we don’t want to set the number of digits, we want to default to the regional settings. However, we still have to leave a blank space where that parameter is supposed to go. (It doesn’t literally have to be a space, we just don’t put anything there at all.) So we skip that parameter, put in a comma, and move on to the next. The next parameter sets leading zeroes, but we don’t want to set that either. So once again we leave that one out, put in another comma, and move on.
We’ve now reached the fourth parameter, the one that contains the negative numbers setting. We do want to set that one, so we put in our constant NegativesInParens. We don’t want to specify any more parameters following that one, so we can stop putting in commas and leaving placeholders for them; VBScript will assume you don’t want to use anything following NegativesInParens. Here’s the output:
-2.58
(30.70)
One thing you’ll notice is that the regional settings on this computer default to showing only two decimal places. The next thing you’ll notice is that the first number shows the negative as a minus sign, the second as parentheses. This is because in the first call to FormatNumber we didn’t set the NegativesInParens parameter so VBScript used the regional settings, which on this computer defaulted to the minus sign. In the second call we specifically set the NegativesInParens parameter to show parentheses, and that’s exactly what happened.
Math Functions
If you take a look in the VBScript Language Reference you’ll see a list of all the functions dealing with math. Some of them, such as Sin (sine), Cos (cosine), and Tan (tangent) probably don’t get used much in system administration scripting. Others could come into play on occasion. Here’s an example of a script that uses the Int function, which returns the integer portion of a number, leaving off any decimal places:
a = Int(5.7)
b = Int(5.2)
Wscript.Echo a
Wscript.Echo b
Here’s the output from this script:
5
5
As you can see, Int doesn’t do any rounding, it simply strips off everything following the decimal point.
Another math function that comes in very handy sometimes is the Rnd function. This function returns a random number. However, there are some tricks to using the Rnd function. Take a look at this example:
a = Rnd
Wscript.Echo a
This is a legitimate script; it will run without any problem. However, it will also return the same number every time you run it. You can pass a value to Rnd to give it a seed value to work from:
a = Rnd(10)
Wscript.Echo a
Unfortunately this doesn’t help; you’re still going to get that same number. Let’s see what happens if we put it in a loop:
For i = 1 to 5
a = Rnd
Wscript.Echo a
Next
This time we initially get back the same number, but the number does change each time through the loop. The reason for this is that the Rnd function uses the results of the previous call as a seed for the next call. However, the results probably aren’t what you were looking for:
0.7055475
0.533424
0.5795186
0.2895625
0.301948
We have a series of random numbers, but they’re all less than 1. Also, you’ll get back this exact same sequence of numbers the next time you run the script. And the next, and the next….
All right, so how does this thing work anyway? Well, the trick is, you have to call the Randomize function first:
Randomize
For i = 1 to 5
a = Rnd
Wscript.Echo a
Next
Randomize tells VBScript to use the system time as the “seed,” which means your results will be different each time you run the script. We are, however, still looking at values less than 1:
0.7001459
0.5183789
0.7184041
0.591656
0.2394175
What if we want a random number between 1 and 100? Can we do that? Of course we can:
upper = 100
lower = 1
Randomize
For i = 1 to 5
a = Int((upper - lower + 1) * Rnd + lower)
Wscript.Echo a
Next
This one looks a little complicated, but trust us, it works. Okay, don’t trust us - try it out and you’ll see it works. What we’re doing here is assigning the highest value we want to return (100) to the variable upper and the lowest value (1) to the variable lower. Now, remember our parentheses: we subtract lower from upper and add 1; in our example this gives us a value of 100. (Why not just use upper? Well, what if you wanted your random number to be between 10 and 50? This equation works no matter what your upper and lower bounds are.) This value is then multiplied by the results of Rnd, which, because of our call to Randomize, returns a random number that is randomized based on the system time. Then that entire value is added to the lower value. Because this value will most likely be a fraction we use our old friend the Int function to remove any decimal places and leave us with a whole number between 1 and 100. Something like this:
82
98
97
3
44
Conversion Functions
There is another set of functions dealing with numbers that can also be useful at times. If you look in the VBScript Language Reference again, this time under Conversion Functions, you’ll see a list of functions that allow you to convert values to other data types.
One of the more useful conversion functions when it comes to numbers is CInt. This function converts some sort of expression into an integer. CInt is similar to Int, except that instead of just removing the decimal places CInt will round to the nearest number. We saw some examples of using CInt in the Sesame Script article What Type of Data Is That?, but here’s one more for good measure:
a = 1.3
b = 2.6
Wscript.Echo CInt(a)
Wscript.Echo CInt(b)
And here’s the output:
1
3
While CInt will interpret some sort of number as an integer, other conversion functions will convert between different types of numbers. As you know, our standard way of working with numbers is to work with decimal numbers, meaning we use a combination of 10 numerals, 0 through 9, to create all possible numbers. In binary arithmetic (the basis of all computing), only two numbers are used: 0 and 1. That means that, in binary. the decimal number 3 is represented as 11 (one 2 plus one 1), because we only have the numbers 0 and 1 to work with to make up a 3. We can also go to the other extreme and use the hexadecimal system, which uses 16 characters: 0 through 9 and A through F, meaning that the decimal number 10 is the hexadecimal number A.
Why do we care about that? Believe it or not, there are occasions when you need to know the hexadecimal (or hex) equivalent of a decimal number. Let’s first take a look at how to retrieve the hex value, then we’ll show a more real-world example where it might come in handy. We return the hex equivalent of a decimal number like this:
a = 25
Wscript.Echo Hex(a)
The output from this script is 19, which just happens to be the hex equivalent of 25. And how did we get this number? By using the Hex method of course.
One really good example of when Hex comes in handy has to do with error-handling. We’re not going to go into error-handling right now; you’ll find articles for that in the More Information section below. For this example you only need to know that VBScript uses an object called the Err object to return error messages. You can use this object to retrieve error messages from WMI scripts, but the trick is that the Err object returns a decimal number. However, if you look up an error number for WMI, those numbers are always in hex. So displaying a hex number rather than a decimal number will be more useful to you in your troubleshooting. Here’s an example:
On Error Resume Next
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv3")
If Err.Number <> 0 Then
WScript.Echo "Error: " & Err.Number
WScript.Echo "Error (Hex): " & Hex(Err.Number)
Err.Clear
End If
When you run this script, you’ll get output that looks like this:
Error: -2147217394
Error (Hex): 8004100E
You can now go into the WMI documentation and look up the hex number 8004100E. When you do that you’ll see that’s the code for a WBEM_E_INVALID_NAMESPACE error, meaning the namespace couldn’t be found. If you look closely at the script you’ll see we tried to connect to the root\cimv3 namespace. As it turns out, WMI doesn’t have a root\cimv3 namespace, only a root\cimv2 namespace. Problem solved.
But how do you go the other way? After all, if you put the number 19 into CInt you’ll get 19 back, you won’t get 25. Well, that’s pretty easy:
a = &H19
Wscript.Echo CInt(a)
Any number preceded by the characters &H is identified as a hexadecimal number. The output from this script is 25.
Let’s look at one last conversion function before we go: the Asc function. This function simply takes a letter and returns the ASCII equivalent:
letter = "B"
Wscript.Echo Asc(letter)
When you run this script, here’s what your output will be:
66
If you look up the character B in an ASCII conversion table, you’ll see that the decimal value is 66. Take a look at this Hey, Scripting Guy! article for a practical example of using the Asc function.
More Information
For more on working with numbers, see the following:
Working with Numbers - Windows 2000 Scripting Guide
VBScript Functions - VBScript Language Reference
For more on error handling, check out the following articles:
Introduction to Error Handling - Sesame Script
To Err is VBScript - Doctor Scripto’s Script Shop
Wrap-Up
If two trains 60 miles apart are traveling towards each other…. Okay, we won’t give you a story problem. We’re at the end of our lesson on numbers and math and all that stuff. But you might want to consider tracking down your former math teachers and apologizing.