ExcelBanter - Converting code to function

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Converting code to function (https://www.excelbanter.com/excel-programming/418567-converting-code-function.html)

Converting code to function

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!

Converting code to function

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...
--
HTH...

Jim Thomlinson

"Dino" wrote:

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!

Converting code to function

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?

"Don Guillett" wrote:

You did say function. So here is a UDF. Place in a REGULAR moduleon 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

"Dino" wrote in message
...
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!

Converting code to function

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

On Wed, 15 Oct 2008 16:34:01 -0700, Dino
wrote:

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?

"Don Guillett" wrote:

You did say function. So here is a UDF. Place in a REGULAR moduleon 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

"Dino" wrote in message
...
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!