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

"Bob" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>
>

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

"Bob" wrote:

> 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
>
>
>

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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>
>

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

"Doug Glancy" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> "Bob" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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
>>
>>
>
>

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" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > 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
> >
> >
>
>
>

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

Bob

"Rookie 1st class" <Rookie1stClass@SpamThis> wrote in message
news:793592A0-00CB-4988-ABA9-(E-Mail Removed)...
>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" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>> > 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 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

"Bob" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> "Doug Glancy" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>> "Bob" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> 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
>>>
>>>
>>
>>
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> "Doug Glancy" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>> "Bob" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> 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
>>>
>>>
>>
>>
>
>

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


"Peter T" <peter_t@discussions> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>> "Doug Glancy" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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
>>>
>>> "Bob" <(E-Mail Removed)> wrote in message
>>> news:%(E-Mail Removed)...
>>>> 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
>>>>
>>>>
>>>
>>>
>>
>>
>
>