Log10 Error using Office 2016

[captbluefin]

We have the need to round the contents of various different cells to 3 significant figures. For the sake of this discussion lets use a value of 0.7567 that is in range A1

If we use the following to set the value of a cell to 3 decimal places in a sheet, the line does just what we want (rounds any number to 3 sig figs.):

= ROUND(A1, 3 - (1 + INT(LOG10(ABS(A1))))) -----> This works great in a sheet.

If we use the next line of code:

Please Login or Register  to view this content.

The numeral 3 in the code above is for significant figures.

Using this line of code in VBA we get the following error: Compile error: Sub or function not defined and Log10 is highlighted as the cause of the error. It appears Log10 doesn't work in VBA.
If anyone has an idea how we could get this line of code to work in VBA, it would be greatly appreciated.

This table shows an example of how we deal with significant figures.

Starting Value Value need from starting value
0.7567 0.757
1.7567 1.76
10.7567 10.8
100.7567 101
1075.7567 1080
10757.7567 10800
107576.7567 108000


Thanks for looking, and we appreciate any help!!!!

[shg]

Two ways:

	A	B	C	D
1	0.7567	0.757	0.757	B1: =--TEXT(A1, "0.00E+0")
2	1.7567	1.76	1.76	C1: =SigDig(A1, 3)
3	10.7567	10.8	10.8
4	100.7567	101	101
5	1075.7567	1080	1080
6	10757.7567	10800	10800
7	107576.7567	108000	108000

Please Login or Register  to view this content.

[MrShorty]

There is no VBA function log10() -- that is an Excel function.

You can use Excel worksheet functions in VBA through the WorksheetFunction object: https://msdn.microsoft.com/en-us/vba...n-visual-basic
VBA has the natural log built into its Log() function. If you recall your "change of base" rules for logarithms (http://www.purplemath.com/modules/logrules.htm ), then it should be easy to develop the formula to get log base 10 using the natural log function.

If you are interested in a different approach, I stumbled upon a "text" approach that looks something like this:
Excel functions: =VALUE(TEXT(A1,"0.00E+0")). If one wanted to tackle a variable number of sig figs, it would be easy to build the number format code using the REPT() function. If Excel decides to default to a scientific format, format the cell with a suitable number format.
VBA newvalue=CDbl(Format(oldvalue,"0.00E+0")). Again, if you want it to be variable, you just figure out the text function that builds the number format code.

[xladept]

Hi Captain,

The log is negative so this rounds to four places:

Please Login or Register  to view this content.

*so .7567 is unaltered

[mikerickson]

Note Log10(x) = Log(x)/Log(10)

[captbluefin]

We used shg's function and the following code to make all numbers display properly.
Thanks to all for you help and ideas!!!!!!!!!!!!!!!!!!!!

Please Login or Register  to view this content.