VBA/Excel/Access/Word/Language Basics/Function Return

Functions That Return an Array

   <source lang="vb">

 Function MonthNames()
     MonthNames = Array("January", "February", "March", _
       "April", "May", "June", "July", "August", _
       "September", "October", "November", "December")
 End Function
 =TRANSPOSE(MonthNames())
</source>
   
  

Passing Parameters and Returning Values

   <source lang="vb">

Sub cmdPassParameters_Click()

 Call Initials("First", "LastName")

End Sub Sub Initials(strFirst As String, strLast As String)

 MsgBox "Your Initials Are: " & Left$(strFirst, 1) _
  & Left$(strLast, 1)

End Sub

</source>
   
  

Return a string from funtion

   <source lang="vb">

Sub cmdReturnValues_Click()

   Dim strInitials As String
   strInitials = ReturnInit("First", "Last")
   MsgBox "Your initials are: " & strInitials

End Sub Function ReturnInit(strFName As String, strLName As String) As String

   ReturnInit = Left$(strFName, 1) & Left(strLName, 1)

End Function

</source>
   
  

Returning a sorted list

   <source lang="vb">

 =Sorted(A2:A13)
 Function Sorted(Rng As Range)
     Dim SortedData() As Variant
     Dim Cell As Range
     Dim Temp As Variant, i As Long, j As Long
     Dim NonEmpty As Long
     For Each Cell In Rng
         If Not IsEmpty(Cell) Then
             NonEmpty = NonEmpty + 1
             ReDim Preserve SortedData(1 To NonEmpty)
             SortedData(NonEmpty) = Cell.Value
         End If
     Next Cell
     For i = 1 To NonEmpty
         For j = i + 1 To NonEmpty
             If SortedData(i) > SortedData(j) Then
                 Temp = SortedData(j)
                 SortedData(j) = SortedData(i)
                 SortedData(i) = Temp
             End If
         Next j
     Next i
     Sorted = Application.Transpose(SortedData)
 End Function
 =TRANSPOSE(Sorted(A16:L16))
</source>
   
  

Return number from function

   <source lang="vb">

Function NumOfDays()

  NumOfDays = 7

End Function

Sub DaysInAWeek()

   MsgBox "There are " & NumOfDays & " days in a week."

End Sub

</source>
   
  

return value from user-defined function

   <source lang="vb">

Function Tax(ProfitBeforeTax As Double) As Double

 If ProfitBeforeTax > 0 Then Tax = 0.3 * ProfitBeforeTax Else Tax = 0

End Function

Sub test()

   MsgBox Tax(100)

End Sub

</source>
   
  

Specifying the Data Type for a Function"s Result

   <source lang="vb">

Sub HowMuch()

   Dim num1 As Single
   Dim num2 As Single
   Dim result As Single
   num1 = 45.33
   num2 = 19.24
   result = MultiplyIt(num1, num2)
   MsgBox result

End Sub Function MultiplyIt(num1, num2) As Integer

   MultiplyIt = num1 * num2

End Function

</source>