So I was wondering, how can I return multiple values from a function, sub or type in VBA? I've got this main sub which is supposed to collect data from several functions, but a function can only return one value it seems. So how can I return multiple ones to a sub?
所以我想知道,如何从函数、子类型和VBA中返回多个值?我有一个主子,它应该从几个函数中收集数据,但是一个函数只能返回一个值。那么我怎样才能将多个变量返回给子呢?
9 个解决方案
#1
43
You might want want to rethink the structure of you application, if you really, really want one method to return multiple values.
您可能需要重新考虑应用程序的结构,如果您真的想要一个方法来返回多个值。
Either break things apart, so distinct methods return distinct values, or figure out a logical grouping and build an object to hold that data that can in turn be returned.
要么将其分解,这样截然不同的方法返回不同的值,要么计算出一个逻辑分组,并构建一个对象来保存可以返回的数据。
' 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
#2
15
You could try returning a VBA Collection.
您可以尝试返回一个VBA集合。
As long as you dealing with pair values, like "Version=1.31", you could store the identifier as a key ("Version") and the actual value (1.31) as the item itself.
只要处理一对值,比如“Version=1.31”,就可以将标识符作为键(“版本”)和实际值(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
Accessing the values after that it's a breeze:
在这之后,获取这些值是一件轻而易举的事情:
c.Item("Version") 'Returns 1.31
or
c("Version") '.Item is the default member
Does it make sense?
它有意义吗?
#3
12
Ideas :
想法:
- Use pass by reference (ByRef)
- 通过引用传递(ByRef)
- Build a User Defined Type to hold the stuff you want to return, and return that.
- 构建一个用户定义的类型来保存您想要返回的内容,并返回它。
- Similar to 2 - build a class to represent the information returned, and return objects of that class...
- 类似于2 -构建一个类来表示返回的信息,并返回该类的返回对象。
#4
8
You can also use a variant array as the return result to return a sequence of arbitrary values:
您还可以使用一个变体数组作为返回结果来返回任意值的序列:
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
Ugly and bug prone because your caller needs to know what's being returned to use the result, but occasionally useful nonetheless.
丑陋和bug很容易发生,因为您的调用者需要知道返回的结果是什么,但是偶尔有用。
#5
3
A function returns one value, but it can "output" any number of values. A sample code:
函数返回一个值,但它可以“输出”任意数量的值。一个示例代码:
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
#6
2
Not elegant, but if you don't use your method overlappingly you can also use global variables, defined by the Public statement at the beginning of your code, before the Subs. You have to be cautious though, once you change a public value, it will be held throughout your code in all Subs and Functions.
不优雅,但是如果您不使用您的方法,您也可以使用全局变量,在代码的开头,在Subs之前,由公共声明定义。但是,您必须谨慎,一旦您更改了公共值,它将在您的所有代码和函数中被保留。
#7
1
you can return 2 or more values to a function in VBA or any other visual basic stuff but you need to use the pointer method called Byref. See my example below. I will make a function to add and subtract 2 values say 5,6
您可以将两个或更多的值返回到VBA中的函数或任何其他可视的基本内容,但您需要使用名为Byref的指针方法。看我下面的例子。我要做一个函数来加减2个值,比如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
#8
1
I always approach returning more than one result from a function by always returning an ArrayList
. By using an ArrayList
I can return only one item, consisting of many multiple values, mixing between Strings
and Integers
.
我总是返回一个函数的多个结果,总是返回一个ArrayList。通过使用ArrayList,我只能返回一个项目,由多个多个值组成,在字符串和整数之间进行混合。
Once I have the ArrayList
returned in my main sub, I simply use ArrayList.Item(i).ToString
where i
is the index of the value I want to return from the ArrayList
一旦我的主子返回了ArrayList,我就使用ArrayList. item (I)。ToString where i是我要从ArrayList返回的值的索引。
An example:
一个例子:
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
And then call the Function like this:
然后调用这个函数:
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
#9
0
you could connect all the data you need from the file to a single string, and in the excel sheet seperate it with text to column. here is an example i did for same issue, enjoy:
您可以将所需的所有数据从文件连接到单个字符串,并在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
#1
43
You might want want to rethink the structure of you application, if you really, really want one method to return multiple values.
您可能需要重新考虑应用程序的结构,如果您真的想要一个方法来返回多个值。
Either break things apart, so distinct methods return distinct values, or figure out a logical grouping and build an object to hold that data that can in turn be returned.
要么将其分解,这样截然不同的方法返回不同的值,要么计算出一个逻辑分组,并构建一个对象来保存可以返回的数据。
' 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
#2
15
You could try returning a VBA Collection.
您可以尝试返回一个VBA集合。
As long as you dealing with pair values, like "Version=1.31", you could store the identifier as a key ("Version") and the actual value (1.31) as the item itself.
只要处理一对值,比如“Version=1.31”,就可以将标识符作为键(“版本”)和实际值(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
Accessing the values after that it's a breeze:
在这之后,获取这些值是一件轻而易举的事情:
c.Item("Version") 'Returns 1.31
or
c("Version") '.Item is the default member
Does it make sense?
它有意义吗?
#3
12
Ideas :
想法:
- Use pass by reference (ByRef)
- 通过引用传递(ByRef)
- Build a User Defined Type to hold the stuff you want to return, and return that.
- 构建一个用户定义的类型来保存您想要返回的内容,并返回它。
- Similar to 2 - build a class to represent the information returned, and return objects of that class...
- 类似于2 -构建一个类来表示返回的信息,并返回该类的返回对象。
#4
8
You can also use a variant array as the return result to return a sequence of arbitrary values:
您还可以使用一个变体数组作为返回结果来返回任意值的序列:
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
Ugly and bug prone because your caller needs to know what's being returned to use the result, but occasionally useful nonetheless.
丑陋和bug很容易发生,因为您的调用者需要知道返回的结果是什么,但是偶尔有用。
#5
3
A function returns one value, but it can "output" any number of values. A sample code:
函数返回一个值,但它可以“输出”任意数量的值。一个示例代码:
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
#6
2
Not elegant, but if you don't use your method overlappingly you can also use global variables, defined by the Public statement at the beginning of your code, before the Subs. You have to be cautious though, once you change a public value, it will be held throughout your code in all Subs and Functions.
不优雅,但是如果您不使用您的方法,您也可以使用全局变量,在代码的开头,在Subs之前,由公共声明定义。但是,您必须谨慎,一旦您更改了公共值,它将在您的所有代码和函数中被保留。
#7
1
you can return 2 or more values to a function in VBA or any other visual basic stuff but you need to use the pointer method called Byref. See my example below. I will make a function to add and subtract 2 values say 5,6
您可以将两个或更多的值返回到VBA中的函数或任何其他可视的基本内容,但您需要使用名为Byref的指针方法。看我下面的例子。我要做一个函数来加减2个值,比如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
#8
1
I always approach returning more than one result from a function by always returning an ArrayList
. By using an ArrayList
I can return only one item, consisting of many multiple values, mixing between Strings
and Integers
.
我总是返回一个函数的多个结果,总是返回一个ArrayList。通过使用ArrayList,我只能返回一个项目,由多个多个值组成,在字符串和整数之间进行混合。
Once I have the ArrayList
returned in my main sub, I simply use ArrayList.Item(i).ToString
where i
is the index of the value I want to return from the ArrayList
一旦我的主子返回了ArrayList,我就使用ArrayList. item (I)。ToString where i是我要从ArrayList返回的值的索引。
An example:
一个例子:
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
And then call the Function like this:
然后调用这个函数:
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
#9
0
you could connect all the data you need from the file to a single string, and in the excel sheet seperate it with text to column. here is an example i did for same issue, enjoy:
您可以将所需的所有数据从文件连接到单个字符串,并在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