如何在 Excel VBA 中创建字符串数组并将其传递给子？

How do I create and pass string array to a sub in Excel VBA?

提问by Barrett Kuethen

VBA arrays are new to me and it seems like there are multiple ways to create string arrays.

VBA 数组对我来说是新的，似乎有多种方法可以创建字符串数组。

• I believe I need to create a dynamic array
• But I can't find any examples how to pass dynamic arraysto a subroutine

• 我相信我需要创建一个动态数组
• 但我找不到任何如何将动态数组传递给子程序的示例

I know how many items there need to be in the array by the count of the User range (so maybe I don't need a dynamic array??). I'm having trouble passing the array through to another Subroutine.

我通过用户范围的计数知道数组中需要有多少个项目（所以也许我不需要动态数组？？）。我在将数组传递给另一个子程序时遇到问题。

The thought process is as follows:

思考过程如下：

1. Iterate through a list of user names
2. Create a sheet for each
3. Save each user name in an array as I iterate through
4. In another Subroutine, select all the sheets I created and save as a PDF

1. 遍历用户名列表
2. 为每个人创建一个工作表
3. 在我迭代时将每个用户名保存在一个数组中
4. 在另一个子程序中，选择我创建的所有工作表并另存为 PDF

Below is my code. I'm getting Run-time error 9 - Subscript out of range (Referring to the array object)

下面是我的代码。我收到运行时错误 9 - 下标超出范围（指数组对象）

I appreciate any help! Thank you!

我感谢任何帮助！谢谢！

Sub CreateAllDashboards(StartDate As Date, EndDate As Date)
'Used to iterate through the list of users and call the Sub to create Dashboards

Dim UserNameRangeStart As Range
Set UserNameRangeStart = Range("UserName")
Dim SheetNames() As String

'Cyle through users
For i = 1 To GetUserNameRange().CounT
    'Some code
    ReDim Preserve SheetNames(i)
    SheetNames(i) = UserNameRangeStart.Offset(i, 0).Value
Next i

Call CreatePDF(EndDate, SheetNames) 'Also tried SheetNames()

End Sub

---

---

Sub CreatePDF(FileDate As Date, ByRef SheetNames As Variant)

Dim FilePath As String, FileName As String

FilePath = Application.ActiveWorkbook.Path
FileName = "Production Dashboards - " & Format(FileDate, "mmddyy") & ".pdf"


ThisWorkbook.Sheets(SheetNames).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
    FileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
     IgnorePrintAreas:=False, OpenAfterPublish:=True

End Sub

采纳答案by dee

The array parameter is not a problem it is passed correctly to method CreatePDF(...). The parameter type can be changed to SheetNames() As Stringbut SheetNames As Variantworks as well.

数组参数不是问题，它被正确传递给 method CreatePDF(...)。参数类型可以更改为SheetNames() As String但SheetNames As Variant同样有效。

Then the Run-time error 9 - Subscript out of rangeis raised here ThisWorkbook.Sheets(SheetNames).Selectbecause the array SheetNamescontains invalid sheet name, which is the very first item. This item is an empty string and empty string is not valid as a sheet name.

然后在Run-time error 9 - Subscript out of range此处引发，ThisWorkbook.Sheets(SheetNames).Select因为数组SheetNames包含无效的工作表名称，这是第一个项目。此项为空字符串，空字符串作为工作表名称无效。

In the For Nextloop index starts with value 1but the array starts with 0. So the very first item of the array SheetNamesremains untouched and is finally an empty string. To solve it set the lower bound in ReDimexplicitly to 1. HTH

在For Next循环中索引以 value 开头，1但数组以0. 所以数组的第一项SheetNames保持不变，最后是一个空字符串。为了解决它，将下限ReDim显式设置为1。HTH

(Note: if you omit lower bound then Option Baseis used and if no Option Baseis specified then 0is used.)

（注意：如果省略下限则Option Base使用，如果没有Option Base指定则0使用。）

'Cyle through users
For i = 1 To GetUserNameRange().Count
    'Some code
    ReDim Preserve SheetNames(1 To i)
    SheetNames(i) = UserNameRangeStart.Offset(i, 0).value
Next i

回答by simpLE MAn

I would change this:
Sub CreatePDF(FileDate As Date, ByRef SheetNames As Variant)

我会改变这个：
Sub CreatePDF(FileDate As Date, ByRef SheetNames As Variant)

To this:
Sub CreatePDF(FileDate As Date, SheetNames() As String)

对此：
Sub CreatePDF(FileDate As Date, SheetNames() As String)

But your problem is at this line:
ThisWorkbook.Sheets(SheetNames).Select

但是您的问题出在这一行：
ThisWorkbook.Sheets(SheetNames).Select

Edited from dee's comment: You can put an array of sheet names in .Sheets()but withoutempty rows. So in your sub "CreateAllDashboards" do this:

从 dee 的评论中编辑：您可以将工作表名称数组放入.Sheets()但没有空行。因此，在您的子“CreateAllDashboards”中执行以下操作：

ReDim Preserve SheetNames(i - 1)
SheetNames(i - 1) = UserNameRangeStart.Offset(i, 0).Value

and you could read thatabout arrays in VBA.

你能读的是关于VBA数组。

回答by Mark Fitzgerald

I've tested the following using a single sheet workbook with a range named Users and another named FileDate. It does what you asked.

我已经使用具有名为 Users 的范围和另一个名为 FileDate 的范围的单表工作簿测试了以下内容。它做你问的。

The reason for the Run-time error 9 - Subscript out of rangeerror is that you have to reference the array element. ThisWorkbook.Sheets(SheetNames).Selectwill throw an error but ThisWorkbook.Sheets(SheetNames(x)).Selectwon't (as long as x is initialised and within the bounds of the array)

Run-time error 9 - Subscript out of range错误的原因是你必须引用数组元素。ThisWorkbook.Sheets(SheetNames).Select会抛出错误但ThisWorkbook.Sheets(SheetNames(x)).Select不会（只要 x 被初始化并且在数组的范围内）

Sub PopulateArray()
Dim user As Range
Dim SheetNames As Variant

    ReDim SheetNames(1 To 1) 'Initialise the array
    For Each user In [Users]
        ThisWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
        With Worksheets(Worksheets.Count)
            .Name = user.Value2
            .[A1] = user.Value2 'you can't print a blank sheet!
        End With
        SheetNames(UBound(SheetNames)) = user.Value2
        ReDim Preserve SheetNames(1 To UBound(SheetNames) + 1)
    Next user
    ReDim Preserve SheetNames(1 To UBound(SheetNames) - 1) 'Delete the last element
    Call CreatePDF([FileDate], SheetNames)
End Sub

Sub CreatePDF(FileDate As Date, ByRef SheetNames As Variant)

Dim FilePath As String, FileName As String
Dim x As Long

    FilePath = Application.ActiveWorkbook.Path & "\" 'Note backslash added to path.
    FileName = "Amtec Production Dashboards - " & Format(FileDate, "mmddyy")

    For x = 1 To UBound(SheetNames)
        ThisWorkbook.Sheets(SheetNames(x)).ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
            FileName & SheetNames(x) & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
             IgnorePrintAreas:=False, OpenAfterPublish:=True
    Next x
End Sub

The above demonstrates how to parse an array to another sub as requested but you could integrate the CreatePDF code into the calling sub fairly easily too.

上面演示了如何根据请求将数组解析为另一个子程序，但您也可以很容易地将 CreatePDF 代码集成到调用子程序中。