如何将工作表变量传递给另一个子任务

Question

我有一个主子(sub Macro5(，它正在调用一个私有子(NamedRanges(。(仅以下添加的部分代码(

我已经在main sub中声明并设置了工作簿和工作表为wb和wSh，因为名称会不断更改，所以我将它们用作变量。

我在被调用的私有子中使用这些变量时遇到了问题。错误出现在With myWorksheet.Cells的专用子中错误："对象变量或未设置变量"我想问题出在设置工作表名称上吧？

'identify worksheet containing cell range
Set myWorksheet = wSh

这是我第一次尝试在不同的子中使用变量。我在网上看过，但不知道如何解决这个问题。下面是私有子代码和主要子代码的一部分。

Private Sub NamedRanges(wb As Workbook, wSh As Worksheet)
'declare object variable to hold reference to worksheet containing cell range
Dim myWorksheet As Worksheet
'declare variables to hold row and column numbers that define named cell range (dynamic)
Dim myFirstRow As Long
Dim myLastRow As Long
'declare object variable to hold reference to cell range
Dim myNamedRangeDynamicVendor As Range
Dim myNamedRangeDynamicVendorCode As Range
'declare variable to hold defined name
Dim myRangeNameVendor As String
Dim myRangeNameVendorCode As String
'identify worksheet containing cell range
Set myWorksheet = wSh
'identify first row of cell range
myFirstRow = 2
'specify defined name
myRangeNameVendor = "namedRangeDynamicVendor"
myRangeNameVendorCode = "namedRangeDynamicVendorCode"
'Vendor Name range
With myWorksheet.Cells
'find last row of source data cell range
myLastRow = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'specify cell range
Set myNamedRangeDynamicVendor = .Range(.Cells(myFirstRow, "A:A"), .Cells(myLastRow, "A:A"))
End With
End Sub


Sub Macro5
Dim wb As Workbook
Dim ws As Worksheet
Dim path As String
Dim MainWB As Workbook
Dim MasterFile As String
Dim MasterFileF As String
Set ws = Application.ActiveSheet
Set MainWB = Application.ActiveWorkbook
Application.ScreenUpdating = False
'Get folder path
path = GetFolder()
'Get visible worksheet on Master data File
MasterFile = Dir(path & "*Master data*.xls*")
MasterFileF = path & "" & MasterFile
Set wb = Workbooks.Open(MasterFileF)
'Count visible worksheets
Dim i As Integer
Dim wSh As Worksheet
i = 0
For Each ws In wb.Worksheets
If ws.Visible = True Then
i = i + 1
End If
Next ws
'if more then 1 sheet visible then prompt to choose one
If i > 1 Then
MsgBox "More than one worksheet visible, please edit 'Master data' File to have only the 1 worksheet visible that it needs to use, and rerun macro"
Exit Sub
Else
'If only 1 sheet visible use sheet name
Set wSh = ws
End If
'Set Vendor Name and Code Range names
Call NamedRanges(wb, wSh)

我为主要子部分包含了很多代码，以显示我如何获得wb和wSh变量(如果有帮助的话(。

Answer 1

根据您的代码，您似乎希望wSh设置为wb中第一个也是唯一可见的工作表

因此将Macro5的最后一部分更改为：

For Each ws In wb.Worksheets
If ws.Visible = True Then
Set wSh = ws
i = i + 1
End If
Next ws
'if more then 1 sheet visible then prompt to choose one
If i > 1 Then
MsgBox "More than one worksheet visible, please edit 'Master data' File to have only the 1 worksheet visible that it needs to use, and rerun macro"
Exit Sub
End If
'Set Vendor Name and Code Range names
Call NamedRanges(wb, wSh)

Answer 2

我认为对于SubNamedRanges，您已经声明了wSh As Worksheet，不需要使用'identify worksheet containing cell range Set myWorksheet = wSh

你最好直接使用'Vendor Name range With wSh.Cells

对不起，由于我的特权，我还不能发表评论：(