- Home
Welcome !
This is the website for Black Tor Consulting Ltd, we would like to offer you some help with your technology issues.
Mainly on this site we focus on Microsoft Excel formulas, pivots and the automation of workbooks via VBA.
pWe are also interested in graphic and website design.
Cross Browser Support
This site has been tested in all major browsers.
Compatible Browsers
- Internet Explorer
- Firefox
- Opera
- Chrome
- Safari
- What else ?
- Other Stuff
Pages of Interest
Book Covers We Have Created
Ghosts from Afghanistan: A complex cover combining eight different images each layered onto a smokey background. Additional images were wrapped around the mannequin to give an interesting perspective.
Read more...
The Escape of Jack the Ripper: This cover has a more modern appearance. Colour washes and again multiple images were required to build the final cover image which can be seen here.
Read more... - Contact
Contact Us
Please fill in the following form to contact us
VBA: User Defined Funtions
UDF's
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 
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
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
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
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.
