Executing a Value in a cell

I need to "execute" a formula in a cell.

eg
Sheets("Sheet1").Select
ActiveWorkbook.Names.Add Name:="chr_string",
RefersToR1C1:="=Sheet1!R1C1" ' create a range name known as chr_string
ActiveCell.FormulaR1C1 = "'=chr(65)&chr(66)" 'put chr(65)&chr(66)
into range name
MsgBox (Range("chr_string"))

What is msgbox displayed is "=chr(65)&chr(66)".
I need the msgbox to display "AB" instead (chr(65)="A", chr(66)="B").

Changing the
ActiveCell.FormulaR1C1 = "'=chr(65)&chr(66)"
to
ActiveCell.FormulaR1C1 = "AB"
is not an option as I need to put in some non-printable control chars in
there as well (not for display, but for comparison).

How can I do that make the msgbox display "AB" instead of "=chr(65)&chr(66)"?

"Chr" is is VBA's equivalent of Excel's worksheet function "Char". Chr is not
recognized by Excel. Try:
ActiveCell.FormulaR1C1 = "=CHAR(65) & CHAR(66)"

Regards,
Greg

"Tan" wrote:

> I need to "execute" a formula in a cell.
>
> eg
> Sheets("Sheet1").Select
> ActiveWorkbook.Names.Add Name:="chr_string",
> RefersToR1C1:="=Sheet1!R1C1" ' create a range name known as chr_string
> ActiveCell.FormulaR1C1 = "'=chr(65)&chr(66)" 'put chr(65)&chr(66)
> into range name
> MsgBox (Range("chr_string"))
>
> What is msgbox displayed is "=chr(65)&chr(66)".
> I need the msgbox to display "AB" instead (chr(65)="A", chr(66)="B").
>
> Changing the
> ActiveCell.FormulaR1C1 = "'=chr(65)&chr(66)"
> to
> ActiveCell.FormulaR1C1 = "AB"
> is not an option as I need to put in some non-printable control chars in
> there as well (not for display, but for comparison).
>
> How can I do that make the msgbox display "AB" instead of "=chr(65)&chr(66)"?
>

Greg

Thanks for the response. By putting "=char(65)&char(66)", I got "AB"
displayed in the cell. However, what I want is for it to be displayed in the
message box. The excel cell is only as a STORE - to store the value I want
to be "execute" so that it can be displayed (or compared) in the macro - no
intention for the cell to display the actual value I want as the value in the
chr might contains non-printable characters.

Any idea?

"Greg Wilson" wrote:

> "Chr" is is VBA's equivalent of Excel's worksheet function "Char". Chr is not
> recognized by Excel. Try:
> ActiveCell.FormulaR1C1 = "=CHAR(65) & CHAR(66)"
>
> Regards,
> Greg
>
> "Tan" wrote:
>
> > I need to "execute" a formula in a cell.
> >
> > eg
> > Sheets("Sheet1").Select
> > ActiveWorkbook.Names.Add Name:="chr_string",
> > RefersToR1C1:="=Sheet1!R1C1" ' create a range name known as chr_string
> > ActiveCell.FormulaR1C1 = "'=chr(65)&chr(66)" 'put chr(65)&chr(66)
> > into range name
> > MsgBox (Range("chr_string"))
> >
> > What is msgbox displayed is "=chr(65)&chr(66)".
> > I need the msgbox to display "AB" instead (chr(65)="A", chr(66)="B").
> >
> > Changing the
> > ActiveCell.FormulaR1C1 = "'=chr(65)&chr(66)"
> > to
> > ActiveCell.FormulaR1C1 = "AB"
> > is not an option as I need to put in some non-printable control chars in
> > there as well (not for display, but for comparison).
> >
> > How can I do that make the msgbox display "AB" instead of "=chr(65)&chr(66)"?
> >

I'm not quite sure what you're looking for, but you can likely derive what
you need from the appended code. The code will:
1. Create a new named range
2. Set a range variable to the cell refered to by the name
3. Add a formula to the cell which returns the desired result (AB)
4. Display a message box that returns the cell value (calculated by Excel)
5. Display a message box that idependantly returns the same value (AB) as
calculated by VBA using Chr instead of Char.

Sub xyz()
Dim Nm As Name
Dim c As Range
Set Nm = Names.Add("chr_string", RefersTo:="=Sheet1!A1")
Set c = Range(Nm.Name)
c.Formula = "=CHAR(65) & CHAR(66)"
MsgBox c.Value 'cell value calculated by Excel
MsgBox Chr(65) & Chr(66) 'this result calculated by VBA
End Sub

Regards,
Greg



"Tan" wrote:

> Greg
>
> Thanks for the response. By putting "=char(65)&char(66)", I got "AB"
> displayed in the cell. However, what I want is for it to be displayed in the
> message box. The excel cell is only as a STORE - to store the value I want
> to be "execute" so that it can be displayed (or compared) in the macro - no
> intention for the cell to display the actual value I want as the value in the
> chr might contains non-printable characters.
>
> Any idea?
>
> "Greg Wilson" wrote:
>
> > "Chr" is is VBA's equivalent of Excel's worksheet function "Char". Chr is not
> > recognized by Excel. Try:
> > ActiveCell.FormulaR1C1 = "=CHAR(65) & CHAR(66)"
> >
> > Regards,
> > Greg
> >
> > "Tan" wrote:
> >
> > > I need to "execute" a formula in a cell.
> > >
> > > eg
> > > Sheets("Sheet1").Select
> > > ActiveWorkbook.Names.Add Name:="chr_string",
> > > RefersToR1C1:="=Sheet1!R1C1" ' create a range name known as chr_string
> > > ActiveCell.FormulaR1C1 = "'=chr(65)&chr(66)" 'put chr(65)&chr(66)
> > > into range name
> > > MsgBox (Range("chr_string"))
> > >
> > > What is msgbox displayed is "=chr(65)&chr(66)".
> > > I need the msgbox to display "AB" instead (chr(65)="A", chr(66)="B").
> > >
> > > Changing the
> > > ActiveCell.FormulaR1C1 = "'=chr(65)&chr(66)"
> > > to
> > > ActiveCell.FormulaR1C1 = "AB"
> > > is not an option as I need to put in some non-printable control chars in
> > > there as well (not for display, but for comparison).
> > >
> > > How can I do that make the msgbox display "AB" instead of "=chr(65)&chr(66)"?
> > >