Converting code to function

[Dino]

Hello,
I have a procedure that perform often that I would like to convert into a
function called "DASHES". All it does is take a 10-digit number in a cell and
insert dashes into the appropriate places, and save the result. I did a macro
to get the code, but I don't know how to turn this into a function that will
be available every time I use my copy of Excel. Here is the code:

Sub DASHES()
'
' DASHES Macro
' Macro recorded 10/15/2008 by Dino
'
ActiveCell.FormulaR1C1 = _
"=LEFT(RC[-1],4)&""-""&MID(RC[-1],5,3)&""-""&RIGHT(RC[-1],3)"
Range("C3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub


Any help would be appreciated, thanks!

 

[Jim Thomlinson]

What you are asking for can be done but there are some drawbacks... You need
to put the code in an addin. The addin will only exist on your mahine so if
you send the file to anyone else the function will bomb... A better option in
my opinioin would be to use a custom format...

Format -> Format Cell -> Number -> Custom 000-000-0000

The format will be embeded in the cell and it is very easy to do...

 

[Don Guillett]

You did say function. So here is a UDF. Place in a REGULAR module>on the ws
type =dashes(1111111111) to see what you get.

Function dashes(x)
dashes = Left(x, 4) & "-" & Mid(x, 5, 3) & "-" & Right(x, 3)
End Function

 

[Dino]

Thanks that worked great!

You did say function. So here is a UDF. Place in a REGULAR module>on the ws
type =dashes(1111111111) to see what you get.

Function dashes(x)
dashes = Left(x, 4) & "-" & Mid(x, 5, 3) & "-" & Right(x, 3)
End Function

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Hello,
I have a procedure that perform often that I would like to convert into a
function called "DASHES". All it does is take a 10-digit number in a cell
and
insert dashes into the appropriate places, and save the result. I did a
macro
to get the code, but I don't know how to turn this into a function that
will
be available every time I use my copy of Excel. Here is the code:

Sub DASHES()
'
' DASHES Macro
' Macro recorded 10/15/2008 by Dino
'
ActiveCell.FormulaR1C1 = _
"=LEFT(RC[-1],4)&""-""&MID(RC[-1],5,3)&""-""&RIGHT(RC[-1],3)"
Range("C3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
End Sub


Any help would be appreciated, thanks!

 

[Dino]

Thanks! Is there a way to save this function so that it'll work every time I
use Excel regardless of what spreadsheet I'm opening? Or do I have to copy it
into every spreadsheet that I intend to use it?

You did say function. So here is a UDF. Place in a REGULAR module>on the ws
type =dashes(1111111111) to see what you get.

Function dashes(x)
dashes = Left(x, 4) & "-" & Mid(x, 5, 3) & "-" & Right(x, 3)
End Function

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Hello,
I have a procedure that perform often that I would like to convert into a
function called "DASHES". All it does is take a 10-digit number in a cell
and
insert dashes into the appropriate places, and save the result. I did a
macro
to get the code, but I don't know how to turn this into a function that
will
be available every time I use my copy of Excel. Here is the code:

Sub DASHES()
'
' DASHES Macro
' Macro recorded 10/15/2008 by Dino
'
ActiveCell.FormulaR1C1 = _
"=LEFT(RC[-1],4)&""-""&MID(RC[-1],5,3)&""-""&RIGHT(RC[-1],3)"
Range("C3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
End Sub


Any help would be appreciated, thanks!

 

[Gord Dibben]

If you save a workbook as an add-in with that function in a module.

Or if you store it in your Personal.xls.

In that case you would enter it as =Personal.xls!dashes(cellref) or
dashes(10-digit number)

If stored in an add-in you don't need the filename!


Gord Dibben MS Excel MVP

Thanks! Is there a way to save this function so that it'll work every time I
use Excel regardless of what spreadsheet I'm opening? Or do I have to copy it
into every spreadsheet that I intend to use it?

You did say function. So here is a UDF. Place in a REGULAR module>on the ws
type =dashes(1111111111) to see what you get.

Function dashes(x)
dashes = Left(x, 4) & "-" & Mid(x, 5, 3) & "-" & Right(x, 3)
End Function

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Hello,
I have a procedure that perform often that I would like to convert into a
function called "DASHES". All it does is take a 10-digit number in a cell
and
insert dashes into the appropriate places, and save the result. I did a
macro
to get the code, but I don't know how to turn this into a function that
will
be available every time I use my copy of Excel. Here is the code:

Sub DASHES()
'
' DASHES Macro
' Macro recorded 10/15/2008 by Dino
'
ActiveCell.FormulaR1C1 = _
"=LEFT(RC[-1],4)&""-""&MID(RC[-1],5,3)&""-""&RIGHT(RC[-1],3)"
Range("C3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
End Sub


Any help would be appreciated, thanks!