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




Here is the code to test it –

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




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




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:




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:




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




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>