从VBA中的工作表名称获取工作表 [英] Getting sheet indice from Sheet Name in VBA
问题描述
我需要在工作簿中拉一张工作表,只知道它的名字 - 例如:
I need to pull a sheet's position in a workbook knowing only it's name -- so for instance:
如果我们有一张表,说
Workbook.Sheets(Sheet2)
if we have a sheet, say
Workbook.Sheets("Sheet2")
如何找到相应的整数,以便我可以参考作为:
说我是一个整数
Workbook.Sheets(i)
how would I find the corresponding integer so that I could refer to it as:
say i is an integer
Workbook.Sheets(i)
我需要做这个反向指示,所以我可以参考我参考的表格旁边的工作表。
I need to be able to do this reverse indicing so I can refer to sheets next to the sheet I'm referencing.
谢谢你的帮助。
推荐答案
Workbook.Sheets("sheet name").Index
编辑: brettdj的答案启发了我,所以我写了这个函数,可能会被清理掉考虑到这一点,如果我真的要使用和支持这个,我可能会找到一个查找工作表函数,而不是如果你为第四个参数表示true,那么子做什么:
brettdj's answer inspired me, so I wrote this function which could probably be cleaned up infact thinking about it, if I were actually going to use and support this I would probably make a find sheet function instead of what the sub does if you say true for the 4th parameter:
Function adjacentsheet(Optional ws As Worksheet, Optional wsName As String, Optional nextSheet As Boolean = True, Optional search As Boolean = False) As Worksheet
'Expects worksheet or worksheet.name if blank, uses activesheet.
'Third parameter indicates if the next sheet or previous sheet is wanted, default is next = true
'Indicates adjacent sheet based on worksheet provided.
'If worksheet is not provided, uses worksheet.name.
'If no worksheet matches corresponding name, checks other workbooks if 4th parameter is true
'If no worksheet can be found, alerts the user.
'Returns found worksheet based upon criteria.
If (ws Is Nothing) Then
If wsName = "" Then
Set adjacentsheet = adjacentsheet(ActiveSheet, , nextSheet)
Else
'Check all workbooks for the wsName, starting with activeWorkbook
On Error Resume Next
Set ws = Sheets(wsName)
On Error GoTo 0
If (ws Is Nothing) Then
If search = True Then
If Workbooks.Count = 1 Then
GoTo notFound
Else
Dim wb As Workbook
For Each wb In Application.Workbooks
On Error Resume Next
Set ws = wb.Sheets(wsName)
On Error GoTo 0
If Not (ws Is Nothing) Then
Set adjacentsheet = adjacentsheet(ws, , nextSheet)
Exit For
End If
Next
If (ws Is Nothing) Then GoTo notFound
End If
Else
GoTo notFound
End If
Else
Set adjacentsheet = adjacentsheet(ws, , nextSheet, search)
End If
End If
Else
With ws.Parent
If nextSheet Then
If ws.Index = .Sheets.Count Then
Set adjacentsheet = .Sheets(1)
Else
Set adjacentsheet = .Sheets(ws.Index + 1)
End If
Else
If ws.Index = 1 Then
Set adjacentsheet = .Sheets(.Sheets.Count)
Else
Set adjacentsheet = .Sheets(ws.Index - 1)
End If
End If
End With
End If
Exit Function
notFound:
MsgBox "Worksheet name could not be found!", vbCritical, "Invalid worksheet name."
End Function
以下是一些使用示例:
'使用示例
Here are some usage examples: 'Usage Examples
Dim nextws As Worksheet
'returns sheet before the active sheet
Set nextws = adjacentsheet(, , False)
'returns sheet after the active sehet
Set nextws = adjacentsheet()
'returns sheet after sheet named "Test" in current workbook
Set nextws = adjacentsheet(, "Test")
'returns sheet after sheet named "Test" in any open workbook checking current workbook first
Set nextws = adjacentsheet(, "Test", , True)
这篇关于从VBA中的工作表名称获取工作表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!