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/mic...2b0ee70b4a2af6

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