VBA：从函数返回工作表对象引用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16856588/
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
VBA: Returning A Worksheets Object Reference From A Function
提问by user1275094
How do I return a Worksheets Object Reference? I've been perusing through various Google searches with nada results.
如何返回工作表对象引用？我一直在仔细阅读各种带有 nada 结果的 Google 搜索。
For example, I have a functioning code like so. wSheet already dim'ed:
例如，我有一个这样的功能代码。wSheet 已经变暗：
Public wSheet As Worksheet
...
Set wSheet = ActiveWorkbook.Worksheets("ExampleSheet")
wSheet.Range("A1").Value = "Hello"
However, I want wSheet to now call a module that supplies it to the correct reference. Something like this:
但是，我希望 wSheet 现在调用一个模块，将其提供给正确的引用。像这样的东西：
Public wSheet As Worksheet
...
Set wSheet = myModule.get_ExampleSheet
wSheet.Range("A1").Value = "Hello"
And then have a function in module myModule
然后在模块 myModule 中有一个函数
Function get_ExampleSheet() As Worksheets
get_ExampleSheet = ActiveWorkbook.Worksheets("ExampleSheet")
End Function
Everything I try gives me various runtime errors. Is there anyway to get this to work?
我尝试的一切都给了我各种运行时错误。有没有办法让这个工作？
Thanks and advance!
感谢并提前！
回答by enderland
You are returning the wrong type of object in your function.
您在函数中返回了错误类型的对象。
Function get_ExampleSheet() As Worksheets
get_ExampleSheet = ActiveWorkbook.Worksheets("ExampleSheet")
End Function
This currently has several errors.
这目前有几个错误。
Function get_ExampleSheet() As Worksheet
Set get_ExampleSheet = ActiveWorkbook.Sheets("Sheet1")
End Function
Note I changed:
注意我改变了：
- Return type to
Worksheet(you are trying to set a variable, wSheet, which is of typeWorksheetto aWorksheetstype variable) - Added
setkeyword - Changed to
.Worksheetsto.Sheetsto return the specific sheet you are interested in
- 返回类型
Worksheet（您正在尝试设置一个变量 wSheet，它的类型Worksheet为Worksheets类型变量） - 添加
set关键字 - 更改为
.Worksheets以.Sheets返回您感兴趣的特定工作表
回答by Basvaraj
Sub Lookup()
Dim state As Variant
Dim PodName As Variant
Set state = ThisWorkbook.Worksheets("Sheet1").Range("E:E")
Sheets("Sheet1").Activate
PodName = WorksheetFunction.VLookup(state, Range("A1:C55"), 2, False)
ThisWorkbook.Worksheets("Sheet1").Range("F:F") = PodName
End Sub
Macro should stop once the target cell is blank
一旦目标单元格为空，宏应该停止
回答by pelos
http://excelmacromastery.com/Blog/index.php/the-complete-guide-to-worksheets-in-excel-vba/
http://excelmacromastery.com/Blog/index.php/the-complete-guide-to-worksheets-in-excel-vba/
in your regular part of the code
在代码的常规部分
Dim issues_sheet As Worksheet
Set issues_sheet = create_working_sheet("Issues")
issues_sheet.Range("A1").Value = "bssssbb"
the function can be something like
该功能可以是这样的
Function create_working_sheet(sheet_name As String) As Worksheet
For i = 1 To Worksheets.Count
If Worksheets(i).Name = sheet_name Then
exists = True
End If
Next i
If Not exists Then
Worksheets.Add.Name = sheet_name
'if we want it at the end
'Sheets.Add(After:=Sheets(Sheets.Count)).Name = sheet_name
End If
Set create_working_sheet = ThisWorkbook.Sheets(sheet_name)
End Function
回答by Peter Albert
In order to return an Object from a function, you need to provide the Setkeyword:
为了从函数返回一个对象，您需要提供Set关键字：
Function get_ExampleSheet() As Worksheet
Set get_ExampleSheet = ActiveWorkbook.Worksheets("ExampleSheet")
End Function
回答by user3680395
A complete overview of the ways you can set a worksheet using VBA can be found here: http://codevba.com/excel/set_worksheet.htm
可以在此处找到使用 VBA 设置工作表的方法的完整概述：http: //codevba.com/excel/set_worksheet.htm
