Best way to capture digits to left of decimal place?

[Jennifer]

The Int() and fix() functions look to be equally good at isolating the digits to the left of the decimal place. Is that correct?

R/C	C	D
4	x	function
5	1	int(abs(x)) = '1', fix(abs(x)) = '1'
6	-1	int(abs(x)) = '1', fix(abs(x)) = '1'
7	123.456	int(abs(x)) = '123', fix(abs(x)) = '123'
8	-123.456	int(abs(x)) = '123', fix(abs(x)) = '123'
9	123.99999	int(abs(x)) = '123', fix(abs(x)) = '123'
10	-123.99999	int(abs(x)) = '123', fix(abs(x)) = '123'
11	123.999999999999000	int(abs(x)) = '123', fix(abs(x)) = '123'
12	-123.999999999999000	int(abs(x)) = '123', fix(abs(x)) = '123'
13	12.999999999999900	int(abs(x)) = '12', fix(abs(x)) = '12'
14	1.999999999999990	int(abs(x)) = '1', fix(abs(x)) = '1'
15	0.999999999999990	int(abs(x)) = '0', fix(abs(x)) = '0'

Is there a better way?

[mikerickson]

Excel does not have FIX function. But it does have the function FIXED, which rounds values.


FIXED(1.6,0) = 2
INT(1.6)= 1


VBA does have a Fix function, that rounds towards 0, as opposed to Int with returns the integer that is lower than the given value


Int(-77.2) = -78
Fix(-77.2) = -77

[Jennifer]

Excel does not have FIX function. But it does have the function FIXED, which rounds values.

FIXED(1.6,0) = 2
INT(1.6)= 1

VBA does have a Fix function, that rounds towards 0, as opposed to Int with returns the integer that is lower than the given value

Int(-77.2) = -78
Fix(-77.2) = -77

I'm doing this in VBA. I suppose I should have said that, but since this is VBA Express, I guess I assumed it would be about VBA. ;-)

I also used the Abs() function, which, I think, makes Int() and Fix() work the same, no? I think int(abs(x)) = fix(x), correct?

But my question was whether this is the best way to capture just the digits to the left of the decimal place. Is there a better way or are there any problems with this way?

I know that an alternative is to use logs, but VBA stupidly only offers natural logs, so I have to either use worksheet functions or perform a log conversion and then there are different rounding errors.

What I really want to do is find out how many significant digits there are to the left of the decimal place.

PS: I find it a little ironic that in a VBA forum, the word "VBA" is flagged as a spelling error. ;-)

[p45cal]

deleted, not relevant.

[mikerickson]

Converting natural logs to base 10 is easy. Try this

Function CountLeftNumber(aNumber As Double) As Double
    CountLeftNumber = 1 + Int(Log(Abs(aNumber)) / Log(10))
End Function

CountLeftNumber(-123.456) = 3

[snb]

In VBA no better methods than Int or fix.

Sub M_snb()
    MsgBox Int(99999.99)
    MsgBox Fix(99999.09)
End Sub