EXCEL macro : String manipulation (how to display character in your worksheet)

In this post, I'll show how to get character on your EXCEL worksheets.

In VB, there are some characters that do not appear on the keyboard. Also there are characters that are not visible, but rather actions, like TAB, ENTER, BACKSPACE, etc.

Why it is useful to know about character? For example ,vb syntax already have this " " syntax to represent a string. If you want the string to have this " character inside it, then it will be a problems.

Ex

text ="My name is "Nidzam""

VB compiler will detect this as error. So we can fix this in 2 ways. One using double "" and the other one using chr.

 ====================================================

 Sub try1()  
  Text = "My name is ""Nidzam"""  
  MsgBox Text  
  End Sub  

=====================================================

=====================================================

 Sub try2()  
  Text = "My name is " & Chr(34) & "Nidzam" & Chr(34)  
  MsgBox Text  
  End Sub  

=====================================================

So in EXCEL we can show char  in two ways. One using the worksheets function and the other one

using VB code.

OK. To do this on worksheets, we just click on the specify cells an write this below functions

=CHAR(number of character that you want to display).

Oh! I forgot to mention that you need to know the character number you want to display.

You can found about it here

gtwiki

In vb code, the char code is as below

CHR(number of character that you want to display)

In the example, I created a macro to display chr in sheet1.

VB example

=====================================================

 Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)  
 Sub Char()  
 'Dim Chr As Characters  
 Dim i As Integer  
 For i = 4 To 67  
 Sheet1.Cells(i, 1).Value = Chr(i - 3)  
 Sheet1.Cells(i, 2).Value = "Chr(" & i - 3 & ")"  
 'Sleep 50  
 Next i  
 For i = 4 To 67  
 Sheet1.Cells(i, 3).Value = Chr(i + 61)  
 Sheet1.Cells(i, 4).Value = "Chr(" & i + 61 & ")"  
 'Sleep 50  
 Next i  
 For i = 4 To 67  
 Sheet1.Cells(i, 5).Value = Chr(i + 125)  
 Sheet1.Cells(i, 6).Value = "Chr(" & i + 125 & ")"  
 'Sleep 50  
 Next i  
 For i = 4 To 67   
 Sheet1.Cells(i, 7).Value = Chr(i + 188)  
 Sheet1.Cells(i, 8).Value = "Chr(" & i + 188 & ")"  
 'Sleep 50  
 Next i  
 End Sub  
 Sub reset() 'this to reset back sheet1  
   Range("A4").Select  
   ActiveWindow.SmallScroll Down:=63  
   Range("A4:H67").Select  
   Selection.ClearContents  
   Range("A4").Select  
 End Sub  

=====================================================

Try the code to learn it.

Download example