Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting code to function
Thanks that worked great!
"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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting formulas to code | New Users to Excel | |||
converting a simple formula into VBA code | Excel Programming | |||
Needs help converting some pseudo code into VB code | Excel Programming | |||
UDF problem in converting data into code 128 B | Excel Worksheet Functions | |||
Converting code to R1C1 format | Excel Programming |