O
obliteratu
Hi
In a previous thread* I have found the formula to round a number to a
set degree of significant figures (not decimal places... woot!),
however, I would like the equivalent code for VBA so that I can write
my own function, rather than having to remmeber the formula every time.
*
http://groups.google.co.uk/group/mi..._frm/thread/1c9c2dcda3a48283/182b0ee70b4a2af6
Formula: (Thanks to J.E. McGimpsey for this stroke of genius)
For N significant figures, use:
=ROUND(A1, N - 1 - INT(LOG10(ABS(A1))))
The code I have written (note that Function statements have been
commented out so I can debug and a msgbox function added to check the
result):
Sub breakitdown()
'Function SigFig(x, SigFigs)
'chuck in a number for debugging
x = 654321
'set defult sigfigs if none provided
If SigFigs = "" Then SigFigs = 3
SigFig = Round(x, ((SigFigs - 1) - Int(Log10(Abs(x)))))
'check result
MsgBox SigFig
'End Function
End Sub
This works fine for small numbers (rounding decimal places), but,
unlike the worksheet function, VBA will not accept a negative integer
for the number of decimal places in the Round function. (I found this
out by using the altered step-by-step formula below to find the exact
point the code was falling over)
temp1 = Abs(mydata)
temp2 = Log10(temp1)
temp3 = Int(temp2)
temp4 = SigFigs - 1
temp5 = temp4 - temp3
SigFig = Round(mydata, temp5)
Does anyone know of a function in VBA that will round large numbers to
a set number of significant figures? I.e. 654321 becomes 654000
Thanks in advance
Rob
In a previous thread* I have found the formula to round a number to a
set degree of significant figures (not decimal places... woot!),
however, I would like the equivalent code for VBA so that I can write
my own function, rather than having to remmeber the formula every time.
*
http://groups.google.co.uk/group/mi..._frm/thread/1c9c2dcda3a48283/182b0ee70b4a2af6
Formula: (Thanks to J.E. McGimpsey for this stroke of genius)
For N significant figures, use:
=ROUND(A1, N - 1 - INT(LOG10(ABS(A1))))
The code I have written (note that Function statements have been
commented out so I can debug and a msgbox function added to check the
result):
Sub breakitdown()
'Function SigFig(x, SigFigs)
'chuck in a number for debugging
x = 654321
'set defult sigfigs if none provided
If SigFigs = "" Then SigFigs = 3
SigFig = Round(x, ((SigFigs - 1) - Int(Log10(Abs(x)))))
'check result
MsgBox SigFig
'End Function
End Sub
This works fine for small numbers (rounding decimal places), but,
unlike the worksheet function, VBA will not accept a negative integer
for the number of decimal places in the Round function. (I found this
out by using the altered step-by-step formula below to find the exact
point the code was falling over)
temp1 = Abs(mydata)
temp2 = Log10(temp1)
temp3 = Int(temp2)
temp4 = SigFigs - 1
temp5 = temp4 - temp3
SigFig = Round(mydata, temp5)
Does anyone know of a function in VBA that will round large numbers to
a set number of significant figures? I.e. 654321 becomes 654000
Thanks in advance
Rob