What is Excel VBA Indentation?
Indentation represents some spaces at the beginning of a new row of sentence. Indentation is not a word reserved for VBA, it is a word that also appears in Microsoft Word, Excel spreadsheet and even Powerpoint. Indentation is also used for all kinds of computer programming.
In VBA coding, indentation is not mandatory, which means it does not cause compile error if you miss it, but it is definitely a good practice for you or for others to follow your code easily. There is no black and white rule for what code needs to be indented, but there are generally acceptable rules.
Why do you need Excel VBA Indentation?
I will show an example below for you to understand more easily. The below code has no indentation.
Now with indentation
Indent using Ribbon Options If you prefer using the mouse instead, Excel has the options in the ribbon where you can indent with a click on a button. Below are the steps to indent using the ribbon options: Select the cells where you want to apply the indentation. Indent text in a cell. In Microsoft Excel, the Tab key does not indent text in a cell like it does, say, in Microsoft Word; it just moves the pointer to the next cell. To change the indentation of the cell contents, use the Indent icons that reside right underneath the Orientation button. To move text further to the right, click the Increase Indent icon. Excel:set indent level based on value of a cell. Excel:set indent level based on value of a cell. Stick1 (Mechanical) (OP) 5 Sep 08 13:23. I am able to extract an indented BOM from our PLM software for placement into Excel 2003. The tab-delimited BOM file has the indents shown via a column of values. I would like to indent certain columns in Excel based on the value in the indent level column that has been imported. Select the entire column which has the indentation (in this example, it’s column A). To do this, click on the top of the column label (where it says A) Right-click and select ‘Insert 1 left’. This will insert one column to the left of the selected column.
The first code is difficult to read because it looks like a mess, all codes combine together.
The second code is easier because codes are in different layers.
Rules of Excel VBA Indentation
There are some generally acceptable rules for indentation
1) For each set of code that has an starting and ending keyword such as IF…End If, indent the lines of code between. Use “Tab” key for indentation, Shift+Tab to undo the Tab effect.
2) Do not need to indent code that has starting keyword but no ending keyword in separate line, such as Dim xxx As Integer
2) Double line spacing is optional, just use it if it makes you easier to read
3) Do not worry about spacing within each line of code, it will be adjusted automatically
Example 1
Indent the codes between Sub and End Sub
Example 2
Indent the codes between If and End If
Example 3
Indent the codes between For /Next and nested IF / Else / End IF
Just ignore what the code means, focus on the indentation.
VBE Option – Auto Indent
VBE (Visual Basic Editor) has an option called Auto Indent, but it does not automatically indent all lines of code. Auto Indent functions when you press Enter key to start a new line, if the code before you press Enter has indentation, the new line will begin at the position of the previous indentation. If you don’t turn on the Auto Indent function, the next line will start on the left.
In VBE, navigate to Tools > Options >Editor Tab
Check the box Auto Indent to enable the option, you can also specify Tab width, the default value is 4, you can leave it as is.
Outbound References
By default, Excel aligns numbers to the right and text rings to the left in a cell.
And if you don’t like these numbers/texts are too close to the cell border, you also have an option to indent the content of the cell. You can indent to the left or to the right in a cell.
In this short Excel tutorial, I will show you how to indent In Excel.
Indent In Excel Vba
There is a button to do that right there in the ribbon, and you can also use a keyboard shortcut (if you are more comfortable with that).
So let’s get started!
Indent using a Keyboard Shortcut
Suppose you have a data set as shown below and you want to intend the numbers a little to the right.
Below is the keyboard shortcut you can do to that:
To use this shortcut, select the cells where you want to apply the indent and then press these keys in succession (one after the other).
Indent using Ribbon Options
If you prefer using the mouse instead, Excel has the options in the ribbon where you can indent with a click on a button.
Below are the steps to indent using the ribbon options:
- Select the cells where you want to apply the indentation
- Click the Home tab
- In the Alignment group, click on the indent icons (based on whether you want to indent left or right)
Note that the indentation is applied to the entire cell content. So, if you have multiple lines in the same cell, all the lines would be indented.
If you need to change the indentation quite often, you can also add the indent icons to the Quick Access Toolbar. Just right-click on the indentation icon and then click on the ‘Add to Quick Access Toolbar’‘
Fun fact: When you indent using these options, Excel indents equivalent to three space characters.
Indenting Individual Lines in the Same Cell
In case you have multiple lines in the same cell, you may want to indent only some of the cells and not the others.
Unfortunately, you can not do that using the indenting options covered above. As soon as you get into the edit mode in a cell, the indent options are grayed out.
This, however, can be done with a work-around that is not very elegant. You can do this by manually adding the space characters before the lines in which you want to add the indent.
Suppose you have a dataset as shown below and you want to indent all the lines after the first line.
Below are the steps to add an indent to multiple lines in the same cell:
- Select the cell
- Double-click on the cell (or press F2 to get into the edit mode)
- Place the cursor where you want to insert in the indentation
- Enter the space character to get the desired indentation
In case you want to have the indentation in multiple lines, you need to place the cursor at each line and add the space characters.
So these are some of the ways you can use to indent in Excel.
I hope you found this tutorial useful!
Other Excel tutorials you may like: