PDA

View Full Version : Get Workbook Index from VBA



Benzadeus
12-05-2011, 12:25 PM
Hello,

I know that you can use
Workbooks.Item(l)
'or
Workbooks(l)
to set reference to a specific Workbook. But the Workbook object hasn't the Item (neither Index) property. Is there a way to get directly a Workbook's index instead of using my workaround below?
Sub Exemplo()

Debug.Print ÍndiceWB(ThisWorkbook)

End Sub

Private Function ÍndiceWB(wb As Workbook) As Long
Dim l As Long

For l = 1 To Workbooks.Count
If Workbooks(l).FullName = wb.FullName Then
ÍndiceWB = l
Exit Function
End If
Next l
End Function

mdmackillop
12-05-2011, 12:38 PM
I've never used Index with workbooks. I'd rather use variables set to the named book to avoid possible confusion.
For the current book you can simply use
Set wb = ThisWorkbook

Benzadeus
12-05-2011, 12:43 PM
It is hard to explain, by I need this property on my project. I think I'll just keep using my custom function.