variable for percentage

  • Thread starter Thread starter laavista
  • Start date Start date
L

laavista

I need to store the value of a percent in a variable.

The value in the cell is 48.4%.
If I change the format to a number it's
0.484320557491289

I've tried
dim ThePrecentage as long (I get a value of 0)
dim ThePercentage as integer (I get a value of 0)
dim ThePercentage as text (just to see what I got-- I got
"0.484320557491289"

Later in the procedure I will be checking to see in what range the
percentage falls, e.g., 0-39.9; 40.0 to 49.9 or 50.0+

Any help you can provide would be VERY appreciaed
 
laavista said:
I found the answer. I need to dim as "single"
Click to expand...

I think your later posting suggests that you will take Gary's suggestion and
use Double, not Single.

But just to reinforce the point, try this simple experiment:

Function mySingle() As Double
Dim f as Single
f = 1 / 3
mySingle = f
End Function

In Excel:

A1: =mySingle()
A2: =(A1 = 1/3)

You will see that A2 returns FALSE(!).

The reason is: since a Double has more bits of precision, most Single and
Double values will not match, even if they are derived from the same
expression (e.g. 1/3). Since Excel uses Double for all numbers, it is
prudent to use Double in VBA if you will compare with Excel values, store
values into Excel cells, or return values to Excel.

Moreover, nowadays, there really is no point in using Single instead of
Double. In "the old days", Single was more efficient to use; it no longer
makes any difference in modern computers, notably Intel CPUs. Single still
requires less storage space; but storage space is rarely an issue for
applications nowadays.


laavista said:
The value in the cell is 48.4%.
If I change the format to a number it's 0.484320557491289 [....]
Later in the procedure I will be checking to see in what range
the percentage falls, e.g., 0-39.9; 40.0 to 49.9 or 50.0+
Click to expand...

You should not use such discrete ranges when working with numbers with
decimal fractions.

As you can see from your own example, a number that Excel displays as 39.9%
can really be any value between 0.3985 and less than 0.3995. Since 0.3985
to 0.3995 are between 0.399 and 0.400, they will not fit into any of your
ranges. (Aside.... Also note that the constant 40% does not mean the same
thing in VBA and Excel. You should write 0.40 in VBA.)

Ostensibly, you should test for <0.400, <0.500 and otherwise (>=0.500). If
you truly want to exclude negative numbers, you should add an initial test
for <0.

However, even that might result in some surprises.

As I noted above, if Excel displays 40.0%, you might expect it fall into the
second range. But the value might actually be as small as 39.5%, which
falls into the first range.

There are several ways to deal with that. The following are two:

Dim d as Double
d = WorksheetFunction.Round(Range("A1"),1)
If d < 0.40 Then
...first range...

or

d = Range("A1")
If d < 40.005 Then
...first range...

I prefer the first (rounding) because it avoids surprises that might arise
due to the fact that 40.005 cannot be represented exactly as a Double.

Note that I use WorksheetFunction.Round instead of VBA Round(). The latter
rounds differently, namely "banker's rounding". Although it might not make
a difference for your ranges, compare Round(39.85,1) and
WorksheetFunction.Round(39.85,1). Notwithstanding philosophical pros and
cons, the point is: WorksheetFunction.Round should round like Excel, for
better or worse.


----- original message -----
 
Back
Top