Solved: Way too many decimal places, anyone help?

[GregE]

Hi, I'm a first time user of this forum and a real newbee to the world of VBA.
I've done some programming with Basic many moons ago, but haven't been able to find good examples on the net for VBA programming.

I'm currently trying to create a Word document that will do some tedious calculations for me, but the output contains 13 numbers after the decimal when only 2 or three are required.

Anyone know how I can truncate this to only include a few decimal places?

Greg

[fumei]

Yes. Look up the Round function in Help.

Round(22 / 7, 4) = 3.1429
Round(22 / 7, 10) = 3.1428571429

If you use Round with no numdecimalplaces, just the integer is returned:

Round(22 / 7) = 3

[GregE]

Thanks Gerry...

I tried the Round(X,2) to reduce my calculated variable X to two decimal places, but I just keep getting a syntax error.

It looks like it wants me to define the variable as "Long"

Is that required? How do I do that?

Greg

[jasoncw]

Another option is to use the Format function. The difference is that Format will maintain the decimals, as opposed to Round, which truncates them.
[VBA]
Format(22 / 7, "0.00") 'for 2 decimal places
Format(22 / 7, "0.000") 'for 3 decimal places
[/VBA]

[fumei]

1. are you using Option Explicit???

2. when posting here, please try and be VERY explicit. I have no idea whatsoever what your X is. Did you declare it? If so, what did you declare it as?

3. when mentioning error messages you get, ALWAYS give us what that error message actually states.

4. it helps to actually post code. You can't yet, as I think it is still a minimum of 5 posts before you can. So post a few more, then post your actual code.

The input variable for Round can be any expression that can be evaluated as a number. It does not require a Long. You say: "It looks like it wants me to define the variable as "Long""

But how can I know that is correct if you do not state the actual error message? I have been playing around, trying to get some error message that implies that...but have not yet.

[fumei]

Jason is correct. Round means just that, it rounds things.

Notice the difference.

Round(22 / 7, 4) = 3.1429
Round(22 / 7, 10) = 3.1428571429

the 8571429 is ROUNDed up to 9.

Format will not, as the number of decimal places is explicitly set. In other words, Format is a...formatting function. Round is a....mathematical function.

Which to use is a matter of requirements. Which do you need?

[GregE]

Gerry:

Sorry about being vague, again I'm really new at this forum thing.

I defined a varaible "Dim X As Variant" then let X = an mathematical expression.

After X has been calculated,the next line of code I type in is the line "Round (X,2)" it gives me a "Compile error:" "Expected: =" error message.

Do you need any other info?

Greg

[jasoncw]

Greg, will X always be a number? If so, declare it as double, rather than a variant. It is always best to declare variables how they will be used. Variant should be used when it is unclear as to what data will be assigned to the variable, or if the variable will have more than one use. So use:
[vba]
Dim X as Double
X = a mathematical expression
[/vba]
Notice you do not need to use the Let statement.

Jason

[GregE]

Thanks Jason

X will always be a number so I'll try declaring the variable X as "Double" rather than "Variant".

Should the Round and Format functions work then?

Greg

[jasoncw]

Definitely. Post back if you're still having issues.

[GregE]

Thanks Jason, That Worked!

Greg

[fumei]

I just want to point out that:

Round (X,2)

WILL indeed return an error "Expected: ="

Round (X,2)

by iteself does not mean anything. Round is a function and therefore must return something...i.e. something must EQUAL the value of the returned function.

Y = Round(X, 2)

or

X = Round(X, 2)

[GregE]

Thanks Steve...

I did end up figuring that out after I clued into the "Expected: =" error message.


Thanks again...Greg

[fumei]

I just love it when people call me Steve. Always wanted to be a Steve.

[GregE]

Sorry Gerry

I'm a little language-challenged

Greg

[fumei]

Not a problem. Kind of get a kick out of it.