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?
This question is related to
function
vba
return-value
ms-word
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.
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
.
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
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
You could try returning a VBA Collection.
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.
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?
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:
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
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.
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
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
Ideas :
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
Source: Stackoverflow.com