Excel VBA: Getting the Index Number of an Active Sheet

Sometimes we have to get the index number of an active sheet when we make a macro.

Macro Examples

The following Excel VBA macro displays the active sheet index number in a message box.

Sub GetActiveSheetIndex()

  MsgBox ActiveSheet.Index

End Sub

Description

[Application.]ActiveSheet

  ActiveSheet...

The Application.ActiveSheet property returns an active Worksheet object or a Chart object. So the Object Browser shows us that returned value is “Object”.

obj-Excel-Application-ActiveSheet

The ActiveSheet property is a member of the Excel.Global class, so we can omit the Application property.

obj-Excel-Global-ActiveSheet

ActiveSheet.Index [Worksheet.Index or Chart.Index]

  ActiveSheet.Index

The ActiveSheet.Index (Worksheet.Index or Chart.Index property) returns a Long value that represents the index number of the Worksheet object or the Chart object.

obj-Excel-Worksheet-Index

obj-Excel-Chart-Index

MsgBox function

The code: ActiveSheet.Index is set to the parameter of a MsgBox function.

Sub GetActiveSheetIndex()

  MsgBox ActiveSheet.Index

End Sub

So, a message box shows the index number of an active sheet.

Properties

http://www.relief.jp/itnote/archives/excel-vba-activesheet-index.php

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007