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

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 arrays to 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

在另一个子例程中,选择我创建的所有工作表并另存为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

3 个解决方案

#1

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

数组参数不是正确传递给方法CreatePDF(...)的问题。参数类型可以更改为SheetNames()As String,但SheetNames As Variant也可以。

Then the Run-time error 9 - Subscript out of range is raised here ThisWorkbook.Sheets(SheetNames).Select because the array SheetNames contains 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.

然后在这里引发运行时错误9 - 下标超出范围ThisWorkbook.Sheets(SheetNames)。选择因为数组SheetNames包含无效的工作表名称,这是第一个项目。此项为空字符串,空字符串无效作为工作表名称。

In the For Next loop index starts with value 1 but the array starts with 0. So the very first item of the array SheetNames remains untouched and is finally an empty string. To solve it set the lower bound in ReDim explicitly to 1. HTH

在For Next循环中,索引以值1开始,但数组以0开头。因此,数组SheetNames的第一项保持不变,最后是一个空字符串。为了解决它,将ReDim的下限明确设置为1. HTH

(Note: if you omit lower bound then Option Base is used and if no Option Base is specified then 0 is 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

#2

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)。选择

Edited from dee's comment: You can put an array of sheet names in .Sheets() but without empty 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 that about arrays in VBA.

你可以在VBA中读到有关数组的内容。

#3

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 range error is that you have to reference the array element. ThisWorkbook.Sheets(SheetNames).Select will throw an error but ThisWorkbook.Sheets(SheetNames(x)).Select won't (as long as x is initialised and within the bounds of the array)

运行时错误9 - 下标超出范围错误的原因是您必须引用数组元素。 ThisWorkbook.Sheets(SheetNames).Select将抛出一个错误但是ThisWorkbook.Sheets(SheetNames(x))。选择不会(只要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代码集成到调用子中。