arrays - 您知道在 Excel VBA 中选择数组中已有的多个工作表的任何直接方法吗？

Question

我有一段代码可以从工作表创建可变数量的副本。
每个新副本都设置为数组中的下一个 Worksheet 类型变量:

Dim wsv() as Worksheet
Dim ddf as Integer, i as Integer

'After some processing, ddf will define the number of copies required,
'therefore the size of the array.

'ws is the original worksheet from which the copies will be made

If ddf > 0 then

    ReDim wsv(0 to ddf) as Worksheet
    
    For i = 0 to ddf
        ws.Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        Set wsv(i) = ThisWorkbook.ActiveSheet
    Next i

End if

最后我需要选择所有这些新工作表，以便将它们导出为单个 PDF。
我用过 ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select在要选择的工作表的数量/名称已知的情况下，我希望 ThisWorkbook.Sheets(wsv).Select或 ThisWorkbook.Sheets(Array(wsv)).Select可以在这个特定的地方工作，但他们没有。
你知道我该怎么做，利用所有必需的工作表来选择已经在一个数组中吗？
更新:
我需要使用一个字符串数组，因为这就是 ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select预计。
感谢@BigBen 和@VBasic2008 的帮助!

Answer 1

工作表数组

函数createWorksheetsArray是一种混合体。它会创建新的工作表，例如 Sub可以，它返回新创建的工作表的工作表名称数组，如 Function会做。

程序testCreateWorksheetsArray将三个工作表添加到包含此代码的工作簿( ThisWorkbook )，返回数组中的三个名称，其内容打印到 Immediate window CTRL+G 在所有三个工作表被“一次性”删除之前。

而不是删除，你宁愿做这样的事情:

wb.Worksheets(wsv).Select
ActiveSheet.ExportAsFixedFormat...

新版本

Option Explicit

Sub addWorksheetCopies(Sheet As Worksheet, _
                       Optional ByVal NumberOfCopies As Long = 1)
    
    If Sheet Is Nothing Then
        GoTo ProcExit
    End If
    
    If NumberOfCopies < 1 Then
        GoTo ProcExit
    End If
    
    With Sheet
        Dim n As Long
        For n = 1 To NumberOfCopies
            .Copy After:=.Parent.Sheets(.Parent.Sheets.Count)
        Next n
    End With

ProcExit:

End Sub

Function getLastWorkSheetNames(Book As Workbook, _
                               Optional ByVal NumberOfWorksheets As Long) _
         As Variant
    
    Dim wsCounter As Long
    wsCounter = NumberOfWorksheets
    Dim Data As Variant
    ReDim Data(1 To wsCounter)
    Dim shCounter As Long
    shCounter = Book.Sheets.Count
    
    Dim sh As Object
    Do
        Set sh = Book.Sheets(shCounter)
        If sh.Type = xlWorksheet Then
            Data(wsCounter) = sh.Name
            wsCounter = wsCounter - 1
            shCounter = shCounter - 1
        End If
    Loop Until wsCounter = 0
    getLastWorkSheetNames = Data

ProcExit:

End Function

Sub testBoth()
    
    Const NumberOfCopies As Long = 3
    
    ' Define workbook.
    Dim wb As Workbook
    Set wb = ThisWorkbook
    
    ' Define Source Worksheet ('ws').
    Dim ws As Worksheet
    Set ws = wb.Worksheets("Sheet1")
    
    ' Create copies of Source Worksheet.
    addWorksheetCopies ws, NumberOfCopies
    
    ' Write the names of the copies to Worksheets Array ('wsv').
    Dim wsv As Variant
    wsv = getLastWorkSheetNames(wb, NumberOfCopies)
    If IsEmpty(wsv) Then
        GoTo ProcExit
    End If
    
    ' Status:
    ' You have created your copies of worksheet ws,
    ' and the array wsv contains the names of the copied worksheets.
    ' Continue...
    
    ' e.g.
    Dim n As Long
    For n = LBound(wsv) To UBound(wsv)
        Debug.Print wsv(n)
    Next n
    
    Application.DisplayAlerts = False
    wb.Worksheets(wsv).Delete
    Application.DisplayAlerts = True
    
ProcExit:
    
End Sub

代码气味版本(不推荐)

Function createWorksheetsArray(SourceWorksheet As Worksheet, _
                               Optional ByVal NumberOfCopies As Long = 1) _
         As Variant
    
    If SourceWorksheet Is Nothing Then
        GoTo ProcExit
    End If
    
    If NumberOfCopies < 1 Then
        GoTo ProcExit
    End If
    
    Dim Data As Variant
    ReDim Data(1 To NumberOfCopies)
    With SourceWorksheet
        Dim n As Long
        For n = 1 To NumberOfCopies
            .Copy After:=.Parent.Sheets(.Parent.Sheets.Count)
            Data(n) = .Parent.ActiveSheet.Name
        Next n
    End With

    createWorksheetsArray = Data

ProcExit:

End Function

Sub testCreateWorksheetsArray()
    
    Dim wb As Workbook
    Set wb = ThisWorkbook
    
    Dim ws As Worksheet
    Set ws = wb.Worksheets("Sheet1")
    
    Dim wsv As Variant
    wsv = createWorksheetsArray(ws, 3)
    If IsEmpty(wsv) Then
        GoTo ProcExit
    End If
    
    Dim n As Long
    For n = LBound(wsv) To UBound(wsv)
        Debug.Print wsv(n)
    Next n
    
    Application.DisplayAlerts = False
    wb.Worksheets(wsv).Delete
    Application.DisplayAlerts = True
    
ProcExit:
    
End Sub