Day6 – if We Could Meet Again

VBA CHR Function

VBA CHR function is categorized under Text/String function.

Each & every character in Excel has a specific assigned number code (ASCII code) to itself. VBA Chr function returns or results in Cord containing the character associated with the specified graphic symbol lawmaking (also referred to as ASCII value). ASCII stands for American Standard Code for Information Interchange

ASCII_Value: It is a standard for the relationship between a number of value and a character blazon.

ASCII value should exist in the range of or between 0 and 255 which are categorized into 3 types.

• Between 0 to 31 range is an ASCII control character OR Non-Printing Control Codes.
• 32 to 127 range is an ASCII printable characters.
• 128 to 255 range is an Extended ASCII codes.

Syntax of CHR in Excel VBA

After typing Chr, click on spacebar the below-mentioned syntax appears.

Chr( ascii_value ) or Chr(charcode)

It contains one input parameter or statement (Compulsory & required), Charcode argument is a Long which identifies a character. It will result in both printable and unprintable characters.

Mapping of Characters with their ASCII Values(Each code explained in Excel file _ CHARACTER CODE Clarification SHEET)

1 – 9		58	:	87	W	116	t	145	'	174	®	203	Ë	232	è
10		59	;	88	Ten	117	u	146	'	175	¯	204	Ì	233	é
11 – 31		60	<	89	Y	118	v	147	"	176	°	205	Í	234	ê
32		61	=	ninety	Z	119	westward	148	"	177	±	206	Î	235	ë
33	!	62	>	91	[	120	x	149	•	178	²	207	Ï	236	ì
34	"	63	?	92	\	121	y	150	–	179	³	208	Ð	237	í
35	#	64	@	93	]	122	z	151	—	180	´	209	Ñ	238	î
36	$	65	A	94	^	123	{	152	˜	181	µ	210	Ò	239	ï
37	%	66	B	95	_	124	|	153	™	182	¶	211	Ó	240	ð
38	&	67	C	96	`	125	}	154	š	183	·	212	Ô	241	ñ
39	'	68	D	97	a	126	~	155	›	184	¸	213	Õ	242	ò
40	(	69	E	98	b	127		156	œ	185	¹	214	Ö	243	ó
41	)	70	F	99	c	128	€	157		186	º	215	×	244	ô
42	*	71	1000	100	d	129		158	ž	187	»	216	Ø	245	õ
43	+	72	H	101	eastward	130	‚	159	Ÿ	188	¼	217	Ù	246	ö
44	,	73	I	102	f	131	ƒ	160		189	½	218	Ú	247	÷
45	–	74	J	103	1000	132	„	161	¡	190	¾	219	Û	248	ø
46	.	75	G	104	h	133	…	162	¢	191	¿	220	Ü	249	ù
47	/	76	L	105	i	134	†	163	£	192	À	221	Ý	250	ú
48	0	77	M	106	j	135	‡	164	¤	193	Á	222	Þ	251	û
49	ane	78	N	107	chiliad	136	ˆ	165	¥	194	Â	223	ß	252	ü
50	2	79	O	108	l	137	‰	166	¦	195	Ã	224	à	253	ý
51	3	fourscore	P	109	m	138	Š	167	§	196	Ä	225	á	254	þ
52	4	81	Q	110	n	139	‹	168	¨	197	Å	226	â	255	ÿ
53	v	82	R	111	o	140	Œ	169	©	198	Æ	227	ã
54	6	83	Due south	112	p	141		170	ª	199	Ç	228	ä
55	7	84	T	113	q	142	Ž	171	«	200	È	229	å
56	viii	85	U	114	r	143		172	¬	201	É	230	æ
57	nine	86	V	115	s	144		173		202	Ê	231	ç

How to Use CHR Function in Excel VBA?

Below are the different examples to employ CHR Function in Excel using VBA code.

You can download this VBA CHR Excel Template hither – VBA CHR Excel Template

Now let us test this CHR Office through some examples and learn how they work.

VBA CHR Part – Example #1

Step 1: Select or click on Visual Basic in the Code group on the Programmer tab or yous can directly click on Alt + F11 shortcut central.

Step 2: Now, you can see VB Editor window, under the project window, in VBA projection, you can see work file listed (i.e. Sheet1 (VB_CHR)

To create a blank module, under the Microsoft excel objects, right-click on canvass 1(VB_CHR) click on Insert and select Module and so that a new bare module gets created.

VBA CHR Function – Example #two

Step 1: Now the blank module is created, it is likewise called a code window, where you tin showtime writing VBA CHR role statement codes.

Code:

              Sub              CHRAC()  Sheet1.Range("D6") = CHR(77)              End Sub            

Footstep ii: The above-mentioned VBA code returns or results in an Upper-case alphabet 1000 in "D6" cell, i.e. for the ASCII value 77.

VBA CHR Function – Example #3

Stride i: Offset another subprocedure as follows,

Code:

              Sub              CHRAC1()              End Sub            

Step 2: Similarly, multiple character codes can be obtained in the cell with the assistance of below-mentioned code

Code:

              Sub              CHRAC1()  Sheet1.Range("D9") = CHR(37) Sheet1.Range("D11") = CHR(47) Sheet1.Range("D13") = CHR(57) Sheet1.Range("D15") = CHR(128)              Stop Sub            

Step iii: Allow us execute the above code. One time you lot run the above-mentioned code, y'all can notice a graphic symbol code in the excel file.

• CHR(37) volition return a % (Percent symbol) in a cell "D9" whereas,
• CHR(47) returns / (Slash/Divide symbol) in the cell "D11"
• CHR(57) returns a numeric value nine in the prison cell  "D13" &
• CHR(128) returns a Euro Sign in the cell "D11"

VBA CHR Part – Example #4

In the below-mentioned case, suppose I want to insert double quotation for the eye name i.e. MANI

Here, for this, I tin use VBA CHR office.

Pace ane: Once, I run the below-mentioned lawmaking with the CHR code 34, it will insert a double quotation mark in the middle proper name.

Code:

              Sub              CHRAC2()  Sheet3.Range("C4") = "MANI" & CHR(34) & "RAM" & CHR(34) & "KUMAR"              Terminate Sub                          

Stride 2: Permit us execute the above code past clicking on the play button and the result in the beneath-mentioned output in the prison cell "E19"

VBA CHR Function – Example #v

In the below-mentioned example, suppose I desire to add a trademark symbol for company i.e. GOOGLE

Here, for this, I can use VBA CHR function with ASCII code "153"

Step ane: Once, I run the below-mentioned code with the CHR code 153, it will show ^TM symbol for the discussion GOOGLE.

Lawmaking:

              Sub              CHRAC3()  Sheet4.Range("C3") = "GOOGLE" & CHR(153)              End Sub            

Pace ii: The in a higher place code, result in the below-mentioned output in the cell "K18"

Save your workbook as "Excel macro-enabled workbook". By clicking on save as at the left corner of the worksheet. Once more if you open a file, you tin click on shortcut key i.eastward. Fn + Alt +F8, "Macro" dialog box appears, where you tin can run a saved macro lawmaking of your choice or you lot tin click on Fn + Alt + F11 for a full macro window.

Things to Remember

If you enter an invalid number to the Chr part i.e. Number in a range of lesser than 0 or more than 255, then you will get the error i.e. Run-time mistake.

Recommended Manufactures

This is a guide to VBA CHR. Hither nosotros discuss how to use Excel VBA CHR function along with applied examples and downloadable excel template. You lot can also get through our other suggested manufactures –

1. VBA Copy Paste
2. VBA RGB
3. VBA Subscript out of Range
4. VBA XML

jonesmomall.blogspot.com

Source: https://www.educba.com/vba-chr/

Share this post