vba 从函数、子或类型返回多个值？

Return multiple values from a function, sub or type?

提问by Kenny Bones

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 中的函数、子或类型返回多个值？我有这个主要的子程序，它应该从几个函数收集数据，但一个函数似乎只能返回一个值。那么如何将多个返回给一个子？

回答by Michael Paulukonis

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

回答by Oneide

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”，您就可以将标识符存储为键（“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

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?

是否有意义？

回答by Martin Milan

Ideas :

想法：

1. Use pass by reference (ByRef)
2. Build a User Defined Type to hold the stuff you want to return, and return that.
3. Similar to 2 - build a class to represent the information returned, and return objects of that class...

1. 使用按引用传递 (ByRef)
2. 构建一个用户定义的类型来保存你想要返回的东西，然后返回。
3. 类似于 2 - 构建一个类来表示返回的信息，并返回该类的对象......

回答by dlm

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.

丑陋且容易出错，因为您的调用者需要知道返回什么才能使用结果，但偶尔还是有用的。

回答by Givi

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

回答by Drl Sherif Omran

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 或任何其他可视化基本内容中向函数返回 2 个或更多值，但您需要使用名为 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

回答by robotik

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.

不优雅，但如果您不重叠使用您的方法，您还可以使用全局变量，由代码开头的 Public 语句定义，在 Subs 之前。但是您必须小心，一旦您更改了公共值，它将在您的所有 Subs 和 Functions 中的代码中保持不变。

回答by SilverShotBee

I always approach returning more than one result from a function by always returning an ArrayList. By using an ArrayListI can return only one item, consisting of many multiple values, mixing between Stringsand Integers.

我总是通过总是返回一个ArrayList. 通过使用ArrayListI 只能返回一个项目，该项目由许多多个值组成，在Strings和之间混合Integers。

Once I have the ArrayListreturned in my main sub, I simply use ArrayList.Item(i).ToStringwhere iis the index of the value I want to return from the ArrayList

有一次，我已经在ArrayList我的主子回来，我只是用ArrayList.Item(i).ToString在那里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

回答by Nirr

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