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

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

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

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

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

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