finding how much decimal places numbers have

[dikken20]

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

 

[VBA Geek]

<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
	I	J
6		5
7
8	100.6666
9	987.5575
10	100.11
Sheet2

 

[dikken20]

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..

 

[VBA Geek]

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

 

[dikken20]

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) ?

 

[VBA Geek]

your can only user ParamArray as a variant

you were using variants as well in your function arguments:

[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) ?

 

[dikken20]

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

 

[dikken20]

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

 

[VBA Geek]

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

 

[joeu2004]

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.)