Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Character formating for User defined function problem
Hi everyone,
I have some user defined function, but i need the output formated at the character level. For example see below A B 1 3 Three 2 4 Four The column B contains user defined function which converts numbers in Column A to Figures in column B. I need the First character (T in Three, F in Four) to be in bold and the rest normal. Note that the result is programmatically created. How shall i incorporate it into my user defined function. Any Ideas? Thanks in advance. Bye |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Character formating for User defined function problem
Hi RosH, Well if you add this to your procedure that would be best. Tr something like Sub BoldFirstChar() Dim test$, cell As Range test = "One" 'This value would be the output of your function Set cell = Range("B1") cell.Value = test With cell.Characters(Start:=1, Length:=1).Font .FontStyle = "Bold" End With End Sub Hope this helps, Jaso -- jt ----------------------------------------------------------------------- jtp's Profile: http://www.excelforum.com/member.php...fo&userid=2113 View this thread: http://www.excelforum.com/showthread.php?threadid=49601 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Character formating for User defined function problem
On 26 Dec 2005 00:12:44 -0800, "RosH" wrote:
Hi everyone, I have some user defined function, but i need the output formated at the character level. For example see below A B 1 3 Three 2 4 Four The column B contains user defined function which converts numbers in Column A to Figures in column B. I need the First character (T in Three, F in Four) to be in bold and the rest normal. Note that the result is programmatically created. How shall i incorporate it into my user defined function. Any Ideas? Thanks in advance. Bye I don't believe you can incorporate this into your function. A function can only return a result, and does not affect the formatting of a cell. In addition, in order to format certain characters within a cell differentially, the contents of a cell, as far as I know, must be a string. A formula that returns a string cannot be formatted in that way. One method to return a result in the manner you are discussing would be to use a Sub to return a text string to the cell in question; and use the Characters method to format the characters appropriately. For example: ======================= Sub BoldFirst() Dim c As Range For Each c In Selection c.Offset(0, 1).Value = Run([NBTEXT], c.Value, 2, 0) c.Offset(0, 1).Characters(1, 1).Font.Bold = True Next c End Sub ======================= In the above, substitute your number-to-text function and arguments for the Run([NBTEXT], c.Value, 2, 0) phrase that I used. (NBTEXT is a function from Longre's free morefunc.xll add-in that I use to do number to text conversions). Note that the result is written into the cell in the adjacent column to the values, and the first character is then BOLD'd. You could put this in a regular module and call it manually; or, if you prefer, you could put it in a worksheet module and have it triggered by a sheet_change or calculate event. --ron |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Character formating for User defined function problem
Thanx ron for that detailed explanation. I understand that i cant do
it directly from the function. Thats a new thing to learn for me. But, about the number to text function, tell me from where i can download it. If i had known it earlier, it would have saved me lot of programming time, because i made one myself. Thank you once again. Bye |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Character formating for User defined function problem
http://xcell05.free.fr/
-- Regars, Tom Ogilvy "RosH" wrote in message oups.com... Thanx ron for that detailed explanation. I understand that i cant do it directly from the function. Thats a new thing to learn for me. But, about the number to text function, tell me from where i can download it. If i had known it earlier, it would have saved me lot of programming time, because i made one myself. Thank you once again. Bye |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Character formating for User defined function problem
On 26 Dec 2005 07:02:38 -0800, "RosH" wrote:
Thanx ron for that detailed explanation. I understand that i cant do it directly from the function. Thats a new thing to learn for me. But, about the number to text function, tell me from where i can download it. If i had known it earlier, it would have saved me lot of programming time, because i made one myself. Thank you once again. Bye Laurent Longre's free morefunc.xll add-in can be downloaded from http://xcell05.free.fr/ There are *many* useful functions in that add-in. Note in the HELP file with that add-in the explanation of use of the VBA RUN method to call the XLL functions, as I did in my example. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problem with user defined function | Excel Discussion (Misc queries) | |||
User Defined Function Problem | Excel Programming | |||
Labels in User-Defined Function Not Working Right (First Problem) | Excel Programming | |||
User-defined data type; Error: Only User-defined types... | Excel Programming | |||
User defined type problem | Excel Programming |