Converting code to function


D

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!
 
Ad

Advertisements

J

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

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
 
D

Dino

Thanks that worked great!


Don Guillett said:
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)
Dino said:
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!
 
D

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?


Don Guillett said:
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)
Dino said:
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!
 
Ad

Advertisements

G

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?


Don Guillett said:
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)
Dino said:
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!
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top