我在想,如何在 VBA 的函数、子过程或类型中返回多个值? 我有一个主过程,需要从几个函数中收集数据,但是似乎一个函数只能返回一个值。那么,我该如何将多个值返回给主过程呢?
我在想,如何在 VBA 的函数、子过程或类型中返回多个值? 我有一个主过程,需要从几个函数中收集数据,但是似乎一个函数只能返回一个值。那么,我该如何将多个值返回给主过程呢?
如果你真的非常希望一个方法返回多个值,那么你可能需要重新考虑应用程序的结构。
要么将事物分开,使不同的方法返回不同的值,要么找出逻辑分组并构建一个可容纳该数据的对象,以便可以返回该对象。
' this is the VB6/VBA equivalent of a struct
' data, no methods
Private Type settings
root As String
path As String
name_first As String
name_last As String
overwrite_prompt As Boolean
End Type
Public Sub Main()
Dim mySettings As settings
mySettings = getSettings()
End Sub
' if you want this to be public, you're better off with a class instead of a User-Defined-Type (UDT)
Private Function getSettings() As settings
Dim sets As settings
With sets ' retrieve values here
.root = "foo"
.path = "bar"
.name_first = "Don"
.name_last = "Knuth"
.overwrite_prompt = False
End With
' return a single struct, vb6/vba-style
getSettings = sets
End Function
你可以尝试返回一个 VBA 集合。
只要处理成对的值,比如 "Version=1.31",你就可以将标识符作为键("Version"),实际值(1.31)作为集合项本身存储。
Dim c As New Collection
Dim item as Variant
Dim key as String
key = "Version"
item = 1.31
c.Add item, key
'Then return c
访问这些值随后就轻而易举了:
c.Item("Version") 'Returns 1.31
or
c("Version") '.Item is the default member
这有意义吗?
想法:
您还可以使用变体数组作为返回结果,以返回任意值的序列:
Function f(i As Integer, s As String) As Variant()
f = Array(i + 1, "ate my " + s, Array(1#, 2#, 3#))
End Function
Sub test()
result = f(2, "hat")
i1 = result(0)
s1 = result(1)
a1 = result(2)
End Sub
这种方法常常会出现问题,因为使用它的调用者需要知道返回值的内容才能使用它,但有时仍然很有用。
一个函数返回一个值,但它可以“输出”任意数量的值。以下是示例代码:
Function Test (ByVal Input1 As Integer, ByVal Input2 As Integer, _
ByRef Output1 As Integer, ByRef Output2 As Integer) As Integer
Output1 = Input1 + Input2
Output2 = Input1 - Input2
Test = Output1 + Output2
End Function
Sub Test2()
Dim Ret As Integer, Input1 As Integer, Input2 As Integer, _
Output1 As integer, Output2 As Integer
Input1 = 1
Input2 = 2
Ret = Test(Input1, Input2, Output1, Output2)
Sheet1.Range("A1") = Ret ' 2
Sheet1.Range("A2") = Output1 ' 3
Sheet1.Range("A3") = Output2 '-1
End Sub
在VBA或任何其他Visual Basic相关的环境中,您可以通过使用指针方法Byref来向函数返回两个或更多值。请看下面的示例。我将创建一个函数来添加和减去两个值,例如5、6。
sub Macro1
' now you call the function this way
dim o1 as integer, o2 as integer
AddSubtract 5, 6, o1, o2
msgbox o2
msgbox o1
end sub
function AddSubtract(a as integer, b as integer, ByRef sum as integer, ByRef dif as integer)
sum = a + b
dif = b - 1
end function
ArrayList
来处理从函数中返回多个结果的情况。使用 ArrayList
,我可以返回仅包含多个值(包括 Strings
和 Integers
)的单个项目。ArrayList
,我只需使用 ArrayList.Item(i).ToString
,其中 i
是我想要从 ArrayList
返回的值的索引。 Public Function Set_Database_Path()
Dim Result As ArrayList = New ArrayList
Dim fd As OpenFileDialog = New OpenFileDialog()
fd.Title = "Open File Dialog"
fd.InitialDirectory = "C:\"
fd.RestoreDirectory = True
fd.Filter = "All files (*.*)|*.*|All files (*.*)|*.*"
fd.FilterIndex = 2
fd.Multiselect = False
If fd.ShowDialog() = DialogResult.OK Then
Dim Database_Location = Path.GetFullPath(fd.FileName)
Dim Database_Connection_Var = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""" & Database_Location & """"
Result.Add(Database_Connection_Var)
Result.Add(Database_Location)
Return (Result)
Else
Return (Nothing)
End If
End Function
然后像这样调用函数:
Private Sub Main_Load()
Dim PathArray As ArrayList
PathArray = Set_Database_Path()
My.Settings.Database_Connection_String = PathArray.Item(0).ToString
My.Settings.FilePath = PathArray.Item(1).ToString
My.Settings.Save()
End Sub
虽然不够优雅,但如果您不重叠使用方法,也可以使用由代码开头的Public语句定义的全局变量在Subs之前。 不过,您必须小心,一旦更改公共值,它将在所有Subs和Functions中保留。
你可以将需要的所有数据从文件连接到单个字符串中,然后在 Excel 表格中使用“文本分列”功能进行分隔。
这里是我为同样的问题所做的一个示例,希望能帮到你:
Sub CP()
Dim ToolFile As String
Cells(3, 2).Select
For i = 0 To 5
r = ActiveCell.Row
ToolFile = Cells(r, 7).Value
On Error Resume Next
ActiveCell.Value = CP_getdatta(ToolFile)
'seperate data by "-"
Selection.TextToColumns Destination:=Range("C3"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Cells(r + 1, 2).Select
Next
End Sub
Function CP_getdatta(ToolFile As String) As String
Workbooks.Open Filename:=ToolFile, UpdateLinks:=False, ReadOnly:=True
Range("A56000").Select
Selection.End(xlUp).Select
x = CStr(ActiveCell.Value)
ActiveCell.Offset(0, 20).Select
Selection.End(xlToLeft).Select
While IsNumeric(ActiveCell.Value) = False
ActiveCell.Offset(0, -1).Select
Wend
' combine data to 1 string
CP_getdatta = CStr(x & "-" & ActiveCell.Value)
ActiveWindow.Close False
End Function