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

Functions That Return an Array

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

Passing Parameters and Returning Values

 
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

Return a string from funtion

 
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

Returning a sorted list

 
  =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))

Return number from function

 
Function NumOfDays()
   NumOfDays = 7
End Function
      
Sub DaysInAWeek()
    MsgBox "There are " & NumOfDays & " days in a week."
End Sub

return value from user-defined function

 
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

Specifying the Data Type for a Function"s Result

 
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