Executing a Value in a cell

[Guest]

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)"?

 

[Guest]

"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

 

[Guest]

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?

 

[Guest]

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