This Excel VBA Code gets the outline level of each row. This can be useful when determining the level of each row within the asset structure, FMECA, organisation, etc.. It can also be used to conduct advanced conditional formatting for each row (and is more stable than the inbuilt automatic styling for outline rows). To read about using outline levels in MS Excel to easily view and manage structured data, see Excel Tip: Collapsing rows with structured data.
This VBA code simply returns the Outline Level of each row. If the user changes the row outline level, the formula will not automatically update. Instead the user will need to press Ctrl-Alt-F9 to recalculate the workbook.
Use and Example
Simply type =Level() into the desired cells. To update values after any changes press Ctrl-Alt-F9.
The following articles are related to this function:
- Function Level(Optional cCell As Range)
- ' LEVEL returns the outline level of the current row. It will not automatically update and therefore
- ' a recalculation Ctrl-Alt-F9 is required.
- ' =level()
- ' Let the outline level of the row be 1, =level() returns 1.
- ' Author: Andrew O'Connor <firstname.lastname@example.org>
- ' Date: 23 Apr 2013
- ' Copyright: 2014 Relken Engineering
- If cCell Is Nothing Then
- Set cCell = Application.Caller
- End If
- Level = cCell.Rows.OutlineLevel
- End Function