VBA function return array

In VBA, a function can return a value. If you want to allow a function to return a collection of values (array), You need to place parentheses after the return type of the array. Then you can let a variant variable to take the returned array when the function is called. The variant variable can be accessed in the same way as a normal array.

Example:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim strp As Variant 'variant variable to capture the returned array from function
strp = GetDateParts(Now)
MsgBox "Day: " & strp(0) & vbCrLf & "Month: " & strp(1) & vbCrLf & "Year: " & strp(2)
End Sub

Function GetDateParts(dat As Date) As String()
   Dim adate As Variant
   adate = Format(Date, "dd-mm-yyyy") ' format date value
   GetDateParts = Split(adate, "-") 'split date string in to an array that stores day, month, and year and return it
End Function

Posted by: Dara | post date: 02-28-2013 | Subject: VBA for MS Excel




This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.