VBA/Excel - how do I create a user-defined function out of a makro?

Hi everybody!

I've made a macro in Excel/VBA, which I use to format numbers in Excel 2003 in such a way that the number is formatted with the number of decimals corresponding to a defined number of significant digits. For example: for 2 significant digits, the numbers 1234.56 and 0.56789 are formatted to 1200 and 0.57, respectively. It's important to stress that only the number format is changed, and not the values themselves, i.e. the values are not rounded down.

(And yes: I know that Excel 2007 can do this, but my workplace is stuck with 2003 for at least another couple of years... )

So far, so good... But what I want is a user-defined Excel function that does the same trick. For example, if my value is in cell A1, then I'd like to write in cell B1:

=USERFUNCTION(A1,SIGDIG)

Where SIGDIG is the chosen number of significant digits. The value of B1 will be the same as A1, but with the right number format.

I've enclosed my macro code - if there's anyone out there that can help turn this macro into a user-defined Excel programme, I'd be eternally grateful.

Regards, Matthew A. Rasmussen

'==============================================================
Sub FormatSigDig()

Dim value As Double
Dim decimals As Integer

value = Selection.value
significantdigits = 2

If value >= 9.95 Then
decimals = 0

Else: decimals = significantdigits - 1 - Int(Math.Log(Abs(Round(value, significantdigits - 1 - Int(Math.Log(Abs(value)) / Math.Log(10))))) / Math.Log(10))

End If

Select Case decimals
Case 0
Selection.NumberFormat = "0"
Case 1
Selection.NumberFormat = "0.0"
Case 2
Selection.NumberFormat = "0.00"
Case 3
Selection.NumberFormat = "0.000"
Case 4
Selection.NumberFormat = "0.0000"
Case 5
Selection.NumberFormat = "0.00000"
Case 6
Selection.NumberFormat = "0.000000"
Case 7
Selection.NumberFormat = "0.0000000"
Case Is > 7
Selection.NumberFormat = "0.0E+00"
End Select

End Sub
'==============================================================

Nope. A function won't do it. A function returns a value. If you wanted the value to be 12000 rather than 1234.56, a function would do the trick. But you want to DO something (change the format of a cell). A UDF doesn't DO things, it returns a value.

Thanks a bunch for your reply mikerickson, even though it wasn't exactly the answer I wanted to hear!

How about the following:

1. As soon as I enter a value in cell A1 (or e.g. put a x in cell D1), then the macro starts automatically...

2. If the value in cell A1 is changed, the macro starts again automatically...

Is it possible to do something like this, and if so what would the code look like?

Regards, Matthew

Originally Posted by mikerickson

Nope. A function won't do it. A function returns a value. If you wanted the value to be 12000 rather than 1234.56, a function would do the trick. But you want to DO something (change the format of a cell). A UDF doesn't DO things, it returns a value.

Hi mikerickson,

Thanks a lot for your help!