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

Question

所以我想知道，如何从 VBA 中的函数、子函数或类型返回多个值？我有这个主子程序，它应该从多个函数收集数据，但一个函数似乎只能返回一个值。那么如何将多个返回给子程序呢？

Answer 1

如果您真的非常希望一个方法返回多个值，您可能需要重新考虑应用程序的结构。

要么将事物分解，以便不同的方法返回不同的值，要么找出逻辑分组并构建一个对象来保存可以依次返回的数据。

' 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