# finding how much decimal places numbers have

#### dikken20

##### Board Regular
Hey all,

in short, my purpose is to find the maximum decimal places among 6 prices.
meaning,
if I have the next prices (I'll list only 3 prices in the example, don't need more):
stock A: 100
stock B: 100.1
stock C: 100.11
then I would like to get the answer 2 as stock C has 2 decimal places which are more than 1 or 0 of stocks B and A.

so I wrote a function that does that, and for some reason when I play with the prices to check if it works it has some issues (in particular if all prices has no decimal and I add one decimal to a price it's ok, but as soon as I add another decimal to another stock [two stocks with one decimal each] then suddenly I get the answer 2 instead of 1)

can someone give me a hand on this please ? thanks

Function DecimalPlaces(ByVal bp1!, ByVal bp2!, ByVal bp3!, ByVal ap1!, ByVal ap2!, ByVal ap3!) As Byte 'bp = Bid Price
Dim nodp As Byte 'nodp = number of decimal places
nodp = 0
If (bp1 * 10 - CInt(bp1 * 10) <> 0) Or (bp2 * 10 - CInt(bp2 * 10) <> 0) Or (bp3 * 10 - CInt(bp3 * 10) <> 0) Or _
(ap1 * 10 - CInt(ap1 * 10) <> 0) Or (ap2 * 10 - CInt(ap2 * 10) <> 0) Or (ap3 * 10 - CInt(ap3 * 10) <> 0) Then
nodp = 2
ElseIf (bp1 - CInt(bp1) <> 0) Or (bp2 - CInt(bp2) <> 0) Or (bp3 - CInt(bp3) <> 0) Or _
(ap1 - CInt(ap1) <> 0) Or (ap2 - CInt(ap2) <> 0) Or (ap3 - CInt(ap3) <> 0) Then
nodp = 1
Else
nodp = 0

End If

DecimalPlaces = nodp
End Function

Last edited:

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
<b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J6</th><td style="text-align:left">{=MAX(<font color="Blue">LEN(<font color="Red">MID(<font color="Green">I8:I10,1+SEARCH(<font color="Purple">".",I8:I10&"."</font>),255</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.</td></tr></table><br />

Excel 2013
IJ
65
7
8100.6666
9987.5575
10100.11
Sheet2

Thanks VBA Geek, but I need this to be as a VBA function since I'm using it in some other functions and not partically in a range of cells..

Code:
``````Function MaxDecimals(ParamArray Prices() As Variant) As Long
Dim cntr As Long, tempMax As Long, lDecimals As Long
For cntr = LBound(Prices) To UBound(Prices)
lDecimals = Len(Mid\$(Prices(cntr), InStr(1, Prices(cntr) & ".", ".") + 1))
If lDecimals > tempMax Then tempMax = lDecimals
Next cntr
MaxDecimals = tempMax
End Function``````

sweet, much appreciated!

by the way, why did you choose to use the array as Variant ? isn't that a waste of memory and might be problematic (in the meaning of miss interpertation of VBA to recognize the right type of variable) ?

your can only user ParamArray as a variant

you were using variants as well in your function arguments:

Code:
``[COLOR=#333333]Function DecimalPlaces(ByVal bp1!, ByVal bp2!, ByVal bp3!, ByVal ap1!, ByVal ap2!, ByVal ap3!) As Byte[/COLOR]``

sweet, much appreciated!

by the way, why did you choose to use the array as Variant ? isn't that a waste of memory and might be problematic (in the meaning of miss interpertation of VBA to recognize the right type of variable) ?

as far as I know ! refers to single, not as variant, correct me if I'm wrong..

By the way,
I would be greatful if you could tell me what is wrong with my original code as I need to learn from my mistake, if you've got a spare moment please.

thanks

you are omitting the "As Single" in your function arguments, so you're using variants as well since you do not specify any data type.

what i see wrong in your function is that it is not dynamic, meaning that you have to code in all the arguments of the function, whereas in mine you can pass as many as you like, second it won't catch numbers with more than 2 decimals the way it is constructed

you are omitting the "As Single" in your function arguments, so you're using variants as well since you do not specify any data type.

That is incorrect. If you print TypeName(bp1) in dikken20's original implementation, you will see it is type Single.

That is because dikken20 wrote bp1! in the original implementation. Note the exclamation point; it causes the variable to be type Single.

(I prefer to write As Single explicitly.)

Replies
5
Views
478
Replies
14
Views
250
Replies
0
Views
507
Replies
0
Views
892
Replies
15
Views
970

1,221,241
Messages
6,158,736
Members
451,513
Latest member
EbenAgya

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back