VBA/Excel: How do I create a user-defined programme from a macro?

[matthewarasmussen]

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.

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
'==============================================================

 

[Denial]

As I understand "the rules", you cannot achieve your aim. User-defined functions (UDF) cannot change anything about a cell other than its contents: not its colour, not its formatting, not its font size, not its…

You can get the correct appearance from a UDF by converting the number to a text string, then putting that text string into the cell. But then your spreadsheet cannot perform further numerical operations on the result.

 

[joerd]

Denial is right - for 99%. Of course Excel has a few exceptions to the rule: read

http://j-walk.com/ss/excel/odd/odd06.htm

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.