Common Custom Functions in VBA

There are a variety of common custom functions you can create and reuse across multiple VBA projects. Here are a few examples:

1. Calculate the Age:

   • This function calculates the age based on a given birthdate and current date.
   • Language: VBA
   • Code snippet:

   Function CalculateAge(ByVal birthdate As Date) As Integer
       CalculateAge = DateDiff("yyyy", birthdate, Date)
   End Function

2. Concatenate Range Values:

   • This function combines the values of a range into a single string.
   • Language: VBA
   • Code snippet:

   Function ConcatenateRange(ByVal rng As Range) As String
       Dim cell As Range
       For Each cell In rng
           ConcatenateRange = ConcatenateRange & cell.Value & " "
       Next cell
   End Function

3. Generate Random Number:

   • This function generates a random number within a specified range.
   • Language: VBA
   • Code snippet:

   Function GenerateRandomNumber(ByVal min As Double, ByVal max As Double) As Double
       Randomize
       GenerateRandomNumber = WorksheetFunction.RandBetween(min, max)
   End Function

4. Get Last Non-Empty Cell:

   • This function retrieves the value of the last non-empty cell in a column.
   • Language: VBA
   • Code snippet:

   Function GetLastNonEmptyCell(ByVal columnRange As Range) As Range
       Dim lastCell As Range
       On Error Resume Next
       Set lastCell = columnRange.SpecialCells(xlCellTypeConstants).Cells(columnRange.Rows.Count)
       On Error GoTo 0
       Set GetLastNonEmptyCell = lastCell
   End Function

These are just a few examples, and the functions you can create are limitless. Consider your specific needs and create custom functions that can be reused across multiple projects to save time and improve efficiency.