Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable for percentage
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable for percentage
dim it as double
-- Gary Keramidas Excel 2003 "laavista" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Found the answer...
I found the answer. I need to dim as "single"...
"laavista" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable for percentage
THANKS! I will.
"laavista" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable for percentage
"laavista" wrote:
I found the answer. I need to dim as "single" 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" wrote previously: 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+ 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 ----- "laavista" wrote in message ... I found the answer. I need to dim as "single"... "laavista" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable Labor percentage based off of fixed sales +/- | Excel Worksheet Functions | |||
Nothing Keyword Destories Objects rather than just resetting the variable to an empty variable | Excel Programming | |||
Calculating a percentage with the end percentage in mind | Excel Discussion (Misc queries) | |||
Bar Chart depicting the "percentage of another percentage(less than 100)" | Charts and Charting in Excel | |||
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions |