×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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
'==============================================================

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

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources