Displaying the infinity symbol in an excel spreadsheet

  • Thread starter Thread starter Bob
  • Start date Start date
B

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
 
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
 
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
 
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
 
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
 
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.
 
How do you do the same thing with code in VBA as a function (not a sub)?

Bob

Rookie 1st class said:
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.

Peter T said:
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
Click to expand...
Click to expand...
 
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
 
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
 
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
 
Back
Top