Excel VBA - Sheets(Arrary("Sheet2","Sheet3")) 使用变量作为工作表名称
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9089796/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA - Sheets(Arrary("Sheet2","Sheet3")) using a variable for sheet names
提问by HL8
I want to code the following
我想编写以下代码
Sheets(Arrary("Sheet2","Sheet3")).Select, by creating a variable for the sheet names to replace the "Sheet2","Sheet3".
Sheets(Arrary("Sheet2","Sheet3")).Select，通过为工作表名称创建一个变量来替换“Sheet2”、“Sheet3”。
Sub SelectMultipleSheets()
Dim sSheets as String
Dim intSheet As Integer
sSheets = ""
For intSheet = 1 To Sheets.count
If Sheets(intSheet).Name <> "Sheet1" And intSheet <= Sheets.count Then
Sheets(intSheet).Cells.Hyperlinks.Delete 'deleting hyperlinks
sSheets = sSheets & Chr(34) & Sheets(intSheet).Name & Chr(34) & ","
End If
Next
sSheets = Left(sSheets, Len(sSheets) - 1)
Sheets(Array(sSheets)).Select
End Sub
I get an error message "Subscript not in range. How do I fix this? Thanks
我收到一条错误消息“下标不在范围内。我该如何解决这个问题？谢谢
回答by Steve Homer
When you build up the array list parameter in this line
当您在此行中构建数组列表参数时
sSheets = sSheets & Chr(34) & Sheets(intSheet).Name & Chr(34) & ","
you're actually creating a single comma delimited string variable and Excel has no way of know that you actually mean a list of comma delimited strings.
您实际上是在创建一个逗号分隔的字符串变量，而 Excel 无法知道您实际上是指一个逗号分隔的字符串列表。
You can get around it by creating the array directly, like this.
您可以通过直接创建数组来解决它，就像这样。
Option Explicit
Sub SelectMultipleSheets()
Dim intSheet As Integer
Dim arSheets() As String
Dim intArrayIndex As Integer
intArrayIndex = 0
For intSheet = 1 To Sheets.Count
If Sheets(intSheet).Name <> "Sheet1" Then
Sheets(intSheet).Cells.Hyperlinks.Delete 'deleting hyperlinks
ReDim Preserve arSheets(intArrayIndex)
arSheets(intArrayIndex) = Sheets(intSheet).Name
intArrayIndex = intArrayIndex + 1
End If
Next
Sheets(arSheets).Select
End Sub
回答by Doug Glancy
It can't be done that way, even though it looks like it should work. Instead, the code below takes advantage of Select'sReplaceargument to add to the selection in the loop. The boolNoSelectionYet variable ensures that it doesn't add to the selection that existed before the loop starts, e.g., if Sheet1 was selected when the routine starts, you don't want it to stay selected.
不能那样做，即使它看起来应该可行。相反，下面的代码利用Select'sReplace参数添加到循环中的选择。boolNoSelectionYet 变量确保它不会添加到循环开始之前存在的选择中，例如，如果在例程开始时选择了 Sheet1，您不希望它保持选中状态。
Sub SelectMultipleSheets()
Dim intSheet As Integer
Dim boolNoSelectionYet As Boolean
boolNoSelectionYet = True
For intSheet = 1 To Sheets.Count
If Sheets(intSheet).Name <> "Sheet1" Then
Sheets(intSheet).Cells.Hyperlinks.Delete
Sheets(intSheet).Select (boolNoSelectionYet)
boolNoSelectionYet = False
End If
Next
End Sub
Note that I removed the second part of your Ifstatement as your For Nextloop ensures that intSheet will never be more than the count of sheets.
请注意，我删除了If语句的第二部分，因为您的For Next循环确保 intSheet 永远不会超过张数。
