Displaying the infinity symbol in an excel spreadsheet

[Bob]

Hi Everyone:

I posted a similar message on the VB newsgroup, and got my answer for VB.
Now, I was wondering does anyone know how to display the infinity symbol in
the excel spreadsheet using VBA? I tried this code, but it did not work on
excel 2003.

Range("A1").Font.Name = "Arial"
w = "This is the infinity symbol: " & Chr(165)
Range("A1").Value = w
x = Len(Range("A1").Value)
Range("A1").Characters(Start:=x, Length:=1).Font.Name = "Symbol"

Thanks for your help.

Bob

 

[Doug Glancy]

Bob,

You don't say what error you get. It works for me. I did change the code
slightly, to declare your variables:

Sub test()
Dim w As String
Dim x As Long

With Range("A1")
.Font.Name = "Arial"
w = "This is the infinity symbol: " & Chr(165)
.Value = w
x = Len(Range("A1").Value)
.Characters(Start:=x, Length:=1).Font.Name = "Symbol"
End With
End Sub

hth,

Doug

 

[FSt1]

hi
code worked for me in 2003. not sure why it don't work for you.
you do realize the author of the code is putting 2 fonts in the cell.
Arial for the words. and symbols for the infinity sign.
Chr(165) in arial is the yen sign or ¥ but formated to symbols it becomes
the infinity sign. not sure if that is your problems or not. shouldn't be
i shorted the code. if you just want the infinity sign only, use this......

Range("A2").Font.Name = "Symbol"
Range("A2").Value = Chr(165)

another way......cheap and dirty.... enter the number eight and set the
orientation to 90 degrees........

Range("A3").Value = 8
Range("A3").Orientation = 90

while this "Looks" ok on the sheet but it can cause problems.

on the keyboard, you can produce the infinity sign with Alt+236 on th keypad.

regards
FSt1

 

[Peter T]

Your code works fine for me in xl2003. Try the following for two different
ways

Sub test2()
Dim w As String
Range("A1:A2").Font.Name = "Arial"

w = "This is the infinity symbol: "

' mixed font
With Range("A1")
.Value = w & Chr$(165)
.Characters(Len(.Value), 1).Font.Name = "Symbol"
End With

' all Arial font
Range("A2") = w & ChrW$(8734)

End Sub

Regards,
Peter T

 

[Bob]

Thanks Doug. I am not using a sub, but a function, that I call from inside
the spreadsheet. I call the function from cell A1. I know a sub works
fine.

Bob

 

[Rookie 1st class]

I don't use symbols. Hold down the [Alt] key, on the number pad type 236,
release the [Alt] key. TADA. It works for me regardfless of the font selected.

 

[Bob]

How do you do the same thing with code in VBA as a function (not a sub)?

Bob

I don't use symbols. Hold down the [Alt] key, on the number pad type 236,
release the [Alt] key. TADA. It works for me regardfless of the font
selected.

Your code works fine for me in xl2003. Try the following for two
different
ways

Sub test2()
Dim w As String
Range("A1:A2").Font.Name = "Arial"

w = "This is the infinity symbol: "

' mixed font
With Range("A1")
.Value = w & Chr$(165)
.Characters(Len(.Value), 1).Font.Name = "Symbol"
End With

' all Arial font
Range("A2") = w & ChrW$(8734)

End Sub

Regards,
Peter T

 

[Doug Glancy]

Bob,

You generally can't change a cell in a function. So all the formatting you
do in the sub is not available. And I can't figure out how to insert the
infinity symbol into a VBA string, which could then be returned by the
function.

Doug

 

[Peter T]

As Doug has explained a UDF cannot change the interface, which includes
reformatting font name.

Curiosity, why didn't you try the second example I posted which does not
require reformat of the font and so will work fine in a UDF (subject to the
existing font being a "typical" font)

Regards,
Peter T

 

[Bob]

Thanks Peter. It works great. I did not even see the second portion of
your previous post. Sorry about that. Have a great weekend.

Bob

 

[Bob]

Thanks Doug. Peter's post helped and works great. Have a nice weekend.

Bob