return 2 or more variables from a function

[David Adamson]

I just can remember how to do the following.

I need to return 2 variables from a function so that I can use them within a
sub.
In this case 2 column numbers so that I can use them to find something.

Any help would be appreciated

regards
David

-----------
Sub Find_Columns
Dim Col1, col2 as integer

Col1 = YColumn(check)

End sub


Function YColumn(check As String)

Select Case check

Case "Vegetables"
Col1 = 2
Col2 =4

Case "Mellons"
Col1 =5
Col2 = 6

End Select

End function

 

[agarwaldvk]

David

You cannot return more than one variable from a function.

What you can do though is return a variant containing an array. Thi
array can contain any number of values that you might want to retun.

Remember, a variant can contain any data type and that includes a
array of variants as well.

Hope that helps!


Best regards


Deepak Agarwa

 

[David Adamson]

thanks

 

[Jon Peltier]

Another approach is to dimension your function as a Boolean, but pass a
bunch of arguments by ref. These might be empty placeholders passed in, but
the function gives them values. If the function does its calculations
correctly, without error, it returns a value of True, so the calling sub
knows it can rely on the arguments passed back. These can only be used by
other VBA procedures, not in UDFs.

An overly simple example might go like this:

Sub DumbSub()
Dim i1 As Integer, i2 As Integer
Dim iMin As Integer, iMax As Integer
Dim bTest As Boolean

Set rng = ActiveSheet.Range("A1:A10")
i1 = 1
i2 = 5

bTest = GetMinMax(i1, i2, iMin, iMax)

If bTest Then
MsgBox iMax & " > " & iMin
Else
MsgBox i1 & " = " & i2
End If

End Sub

Function GetMinMax(iOne As Integer, iTwo As Integer, _
iLow As Integer, iHigh As Integer) As Boolean

If iOne = iTwo Then
GetMinMax = False
Exit Function
End If

If iOne > iTwo then
iHigh = iOne
iLow = iTwo
Else
iHigh = iTwo
iLow = iOne
End If

GetMinMax = True

End Function

The calling sub passes in two values to find out which is greater. It checks
for an error (i.e., the two values are equal). If there's an error, it
displays one message, but if there's no error, it displays a different
message.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______