How to specify a Tab Character in VBA in Excel 2010

 

A tab character may be necessary to improve the appearance of text displayed through vba coding.
To specify a tab character in VBA we will use the constant vbTab or [Chr(9)]

We will take two examples to show the Tab character in between a string.

Example 1

See the coding & the output as MSGBOX in the below screenshot

 

img1

 

Here is the code to test it –

Sub example1()
Msgbox “RIO” & VBTAB & “DE” & VBTAB & “JANEIRO”
End Sub

 

img2

 

In the above screenshot, you can see the gap in between RIO DE JANEIRO
These are actually Tabs.

Example 2

In this example, we are storing the value in cell A1

 

img3

 

Here is the code for testing –

Sub example2()
Dim i as Integer
Dim MyOutput as String
Range(“A1”).Value = “RIO” & VBTAB & “DE” & VBTAB & “JANEIRO”
For i = 1 To Len(Range(“A1”).Value)
MyOutput = MyOutput & Asc(Mid(Range(“A1”).Value, i, 1)) & “ “ & _
Mid(Range(“A1”).Value, i, 1) & vbNewLine
Next i

Msgbox MyOutput

End Sub

 

The output is in cell A1, see the below screenshot:

 

img4

 

To check where exactly the value of Tab is getting stored enter the formulas shown above in cell B1.

You can see cells B4 & B7 are showing as empty because these are actually the Tab spaces.
The same can be further validated if we enclosed the above MID formula with the CODE Function, see screenshot below:

 

img5

 

The code function is giving the code for the respective character in Excel. The tab has its code as 9, A = 65, B = 66, C = 67, etc.

Finally, we can run the macro in example 2 & see the result

 

img6



Example:


Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>