Simple SSN UDF

[Guest]

Hello. I am struggling with my VBA knowledge. I would like to convert the
following formula into a UDF (=ssn(cell)), but I do not know what the code
will look like. Can anyone help?

Formula:
=text(cell, "000-00-0000")

Thanks!

 

[Guest]

Function ssn(myrange As Range)
ssn = Format(myrange.Value, "000-00-0000")
End Function

 

[Guest]

works perfect. Thank you kindly.

Function ssn(myrange As Range)
ssn = Format(myrange.Value, "000-00-0000")
End Function

 

[Rick Rothstein \(MVP - VB\)]

Hello. I am struggling with my VBA knowledge. I would like to convert

the
following formula into a UDF (=ssn(cell)), but I do not know what the code
will look like. Can anyone help?

Formula:
=text(cell, "000-00-0000")

Place this code in a Module (Insert/Module from the VBE menu)...

Function SSN(ByVal ValueIn As Variant) As String
Dim Temp As String
If TypeOf ValueIn Is Range Then
Temp = ValueIn.Value
Else
Temp = ValueIn
End If
Temp = Replace(Temp, "-", "")
Application.EnableEvents = False
If Temp Like "#########" Then
SSN = Format$(Temp, "@@@-@@-@@@@")
Else
' Not sure what you want to do
' if the value passed in is not
' a valid as a SSN "shape", so
' I am doing nothing here which
' means the original value will
' be returned unaltered
SSN = Temp
End If
Application.EnableEvents = True
End Function


Rick

 

[Rick Rothstein \(MVP - VB\)]

What if you make a mistake and only enter 7 or 8 digits... do you want the
function to automatically add 2 or 1 leading zero automatically? Or enter
12e3? Or if you want to feed it a numeric constant like =SSN(123456789)?

Rick