• Home
  • Other Stuff
  • Contact
Black Tor

VBA: User Defined Funtions

UDF's

Excel VBA

As your understanding of VBA improves and you utilise more of its power you may find that there are occasions when you wish to repeat simple operation often or have seperate routines that perform processes, or even have these routines available from the workbook without seeming to activate any VBA. It is these User Defined Functions which can greatly improve your use of Excel. Some of the below examples have been written by myself some I have found useful on occasion or are examples of how you can utilise UDF's to make your coding life easier.

Get the Last Row Number

This routine mirrors the common sub routine single line code "Last Row and/or Column", however this routine can be used from a cell in a worksheet or called from other sub routines. This does not work with a alpha numeric used as the column identifier
Here is the code...

Public Function GetLastRow(Optional dCol As Double, Optional wsName As String) As Double
' Uncomment the next line to allow the function to become volatile and recalculate
' Application.Volatile
' Check if a column number was provided, if not use 1
If dCol = 0 Then dCol = 1
' Check if a worksheet name was provided, if not use the active sheet
If Len(wsName) = 0 Then wsName = ActiveSheet.Name
GetLastRow = Sheets(wsName).Cells(Rows.Count, dCol).End(xlUp).row
End Function

Using the option to make the function Volatile forces the UDF to recalculate as other Formula's recalculate within Excel. There is a drawback to this, which is that as fast as this is it is not as fast as the native Formula's embeded within Excel excessive use of this will imped a workbooks performance.

Return to the  TOP Up Arrow

Get the Last Column Number

This routine mirrors the common sub routine single line code "Last Row and/or Column", however this routine can be used from a cell in a worksheet or called from other sub routines, as above this produces a number of the column not letters.
Here is the code...

Public Function GetLastCol(Optional dRow As Double, Optional wsName As String) As Double
' Uncomment the next line to allow the function to become volatile and recalculate
' Application.Volatile
' Check if a row number was provided, if not use 1
If dRow = 0 Then dRow = 1
' Check if a worksheet name was provided, if not use the active sheet
If Len(wsName) = 0 Then wsName = ActiveSheet.Name
GetLastCol = Sheets(wsName).Cells(dRow,Application.Columns.Count).End(xlToLeft).Column
End Function

As previously stated using the option to make the function Volatile forces the UDF to recalculate as other Formula's recalculate within Excel. There is a drawback to this, which is that as fast as this is it is not as fast as the native Formula's embeded within Excel excessive use of this will imped a workbooks performance.

Return to the  TOP Up Arrow

Get the Last Column Letter

This UDF utilises a second function which is also shown below. The second function calculates routine mirrors the common sub routine single line code "Last Row and/or Column", however this routine can be used from a cell in a worksheet or called from other sub routines, as above this produces a number of the column not letters.
Here is the code...

Public Function GetLastColAlpha(Optional dRow As Double, Optional wsName As String) As String
' Uncomment the next line to allow the function to become volatile and recalculate
' Application.Volatile
' Check if a row number was provided, if not use 1
If dRow = 0 Then dRow = 1
' Check if a worksheet name was provided, if not use the active sheet
If Len(wsName) = 0 Then wsName = ActiveSheet.Name
GetLastColAlpha = ConvertToLetter(Sheets(wsName).Cells(dRow,Application.Columns.Count).End(xlToLeft).Column)
End Function

Function ConvertToLetter(iCol As Integer) As String
Dim iAlpha As Integer
Dim iRemainder As Integer
iAlpha = Int(iCol / 27)
iRemainder = iCol - (iAlpha * 26)
If iAlpha > 0 Then
ConvertToLetter = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
End If

End Function

As previously stated using the option to make the function Volatile forces the UDF to recalculate as other Formula's recalculate within Excel. There is a drawback to this, which is that as fast as this is it is not as fast as the native Formula's embeded within Excel excessive use of this will imped a workbooks performance.

Return to the  TOP Up Arrow

Check a File Exists

Called from a main subroutine this function checks if a file is located on the path given.
Here is the code...

Public Function bFileExists(sFlAs String) As Boolean
'check if named files exists
Dim oFSO As Object
Set oFSO = CreateObject("Scripting.FileSystemObject")
If oFSO.FileExists(sFl) Then
bFileExists = True
Else
bFileExists = False
End If
Set oFSO = Nothing
End Function

Updating

Working on it!

Currently this site is under re-construction and this page amongst many is constantly being updated with information. Please revisit the site over the next few days and weeks as we add more content.