Nick's blog

Nick's picture

XLA routines: EE_RangeTrim

Use EE_RangeTrim to trim a range that is passed to date functions to make the calc quicker
Function EE_RangeTrim(rng As range) As range
'Returns a new range containing only the populated cells
'- use RangeTrim for all the date functions to prevent looping through the whole range.

    Dim rngC As range
 
'http://excelexperts.com/xla-routines-eeRangeTrim    for updates on this function

    If rng.Cells.Count = 1 Then
        If rng.value <> vbNullString Then
            Set EE_RangeTrim = rng
            Exit Function
        End If
    End If
    On Error Resume Next
 
Nick's picture

XLA routines: EE_LastFridayOfMonth

EE_LastFridayOfMonth returns the last friday of the month that the date entered is in.
Function EE_LastFridayOfMonth(dt As Date) As Date
'Takes a date, returns last friday.
    Dim intLoop     As Integer
    Dim dtLast      As Date
 
'http://excelexperts.com/xla-routines-eeLastFridayOfMonth    for updates on this function

    dtLast = EE_LastDayOfMonth(dt)
    Do While Weekday((dtLast - intLoop)) <> vbFriday
        intLoop = intLoop + 1
    Loop
 
    EE_LastFridayOfMonth = dtLast - intLoop
End Function
Nick's picture

XLA routines: EE_NthWeekdayOfMonth

EE_NthWeekdayOfMonth returns the nth weekday of the month that a date is in. - so you could use this function to find the 3rd wednesday in the month (an IMM date for example), or the 2nd Friday...
Nick's picture

XLA routines: EE_BusinessDaysInDateRange

EE_BusinessDaysInDateRange takes 2 dates, and a holiday calendar, and returns the number of business days in the date range.
Function EE_BusinessDaysInDateRange(dt1 As Date, dt2 As Date, rngHolidays As range)
'Takes date 1, date 2 and works out the amount of business days between them
'- takes range of holidays
    Dim intLoop         As Date
    Dim intDaysCount    As Integer
 
'http://excelexperts.com/xla-routines-eeBusinessDaysInDateRange    for updates on this function

    For intLoop = dt1 To dt2 Step IIf(dt1 > dt2, -1, 1)
        If EE_IsBusinessDay(rngHolidays, i
Nick's picture

XLA routines: EE_LastBusinessDayOfMonth

EE_LastBusinessDayOfMonth takes a date, a holiday calendar and returns the last business day of the month that the date is in.
Function EE_LastBusinessDayOfMonth(dt As Date, rngHolidays As range) As Date
'- as above but rolls to the previous business day if the last bus day is a hol
'- also takes a range with the holiday calendar
'http://excelexperts.com/xla-routines-eeLastBusinessDayOfMonth    for updates on this function

    If EE_IsBusinessDay(rngHolidays, EE_LastDayOfMonth(dt)) Then
        EE_LastBusinessDayOfMonth = EE_LastDayOfMonth(dt)
    Else
        EE_LastBusiness
Nick's picture

XLA routines: EE_LastDayOfMonth

EE_LastDayOfMonth takes a date and returns the last day of the month that the date is in.
Function EE_LastDayOfMonth(dt As Date) As Date
'Takes a date and returns the last day of the month
'http://excelexperts.com/xla-routines-eeLastDayOfMonth    for updates on this function
    EE_LastDayOfMonth = DateAdd("d", -1, EE_FirstDayOfMonth(DateAdd("m", 1, dt)))
End Function
Nick's picture

XLA routines: EE_FirstDayOfMonth

EE_FirstDayOfMonth takes a date and returns the first business day of the month that the date is in.
Function EE_FirstBusinessDayOfMonth(dt As Date, rngHolidays As range) As Date
'- as above but rolls to the next business day if the first bus day is a hol
'- also takes a range with the holiday calendar
'http://excelexperts.com/xla-routines-eeFirstBusinessDayOfMonth    for updates on this function

    If EE_IsBusinessDay(rngHolidays, EE_FirstDayOfMonth(dt)) Then
        EE_FirstBusinessDayOfMonth = EE_FirstDayOfMonth(dt)
    Else
        EE_FirstBusinessDayOfMonth = EE_NextBus
Nick's picture

XLA routines: EE_FirstDayOfMonth

EE_FirstDayOfMonth takes a date and returns the first day of the month that the date is in.
Function EE_FirstDayOfMonth(dt As Date) As Date
'Takes a date and returns the 1st day of the month
'http://excelexperts.com/xla-routines-eeFirstDayOfMonth    for updates on this function

    EE_FirstDayOfMonth = Format(Month(dt) & "/1/" & Year(dt), "mm/dd/yyyy")
End Function
Nick's picture

XLA routines: EE_SubtractBusinessDays

EE_SubtractBusinessDays takes a date, and a holiday calendar and subtracts a specified amount of business days.
Function EE_SubtractBusinessDays(dt As Date, rngHolidays As range, intBusinessDays As Integer) As Date
'Takes a date, a range of holidays, business days, and subtracts that amount of business days from the date.
    Dim intDays     As Integer
    Dim intLoop     As Integer
    Dim dtNew       As Date
 
'http://excelexperts.com/xla-routines-eeSubtractBusinessDays    for updates on this function

    If intBusinessDays = 0 Then
        EE_SubtractBusinessDays = dt
Nick's picture

XLA routines: EE_AddBusinessDays

EE_AddBusinessDays takes a date, and a holiday calendar and adds business days.
Function EE_AddBusinessDays(dt As Date, rngHolidays As range, intBusinessDays As Integer) As Date
'Takes a date, a range of holidays, business days, and adds that amount of business days to the date.
Nick's picture

XLA routines: EE_PrevBusinessDay

EE_PrevBusinessDay takes a date, and a holiday calendar and returns the previous business day.
Function EE_PrevBusinessDay(rngHolidays As range, dt As Date) As Date
'Takes a date, a range of holidays, and returns the previous business day
    Dim intPvs     As Integer
    Dim dtPvs      As Date
 
'http://excelexperts.com/xla-routines-eePrevBusinessDay    for updates on this function

    intPvs = 1
    Do While True
        dtPvs = DateAdd("d", 0 - intPvs, dt)
        If EE_IsBusinessDay(rngHolidays, dtPvs) Then
            EE_PrevBusinessDay = dtPvs
            Exit Func
Nick's picture

XLA routines: EE_NextBusinessDay

EE_NextBusinessDay takes a date, and a holiday calendar and returns the next business day.
Function EE_NextBusinessDay(rngHolidays As range, dt As Date) As Date
'Takes a date, a range of holidays, and returns the next business day
    Dim intNext     As Integer
    Dim dtNext      As Date
 
'http://excelexperts.com/xla-routines-eeNextBusinessDay    for updates on this function

    intNext = 1
    Do While True
        dtNext = DateAdd("d", intNext, dt)
        If EE_IsBusinessDay(rngHolidays, dtNext) Then
            EE_NextBusinessDay = dtNext
            Exit Function
 
Nick's picture

XLA routines: EE_IsBusinessDay

EE_IsBusinessDay is a very useful financial function that returns whether a date is a business day.
Nick's picture

XLA Routines: EE_IsWeekend

EE_IsWeekend is a function that takes a date, and returns whether it is a weekend.
Function EE_IsWeekend(dt As Date) As Boolean
'Takes a date, returns if it's a weekend
'http://excelexperts.com/xla-routines-eeIsWeekend    for updates on this function

    Select Case Format(dt, "w")
        Case vbSaturday, vbSunday
            EE_IsWeekend = True
        Case Else
            EE_IsWeekend = False
    End Select
End Function
Nick's picture

XLA routines: EE_IsWeekday

EE_IsWeekday is a function that takes a date, and returns whether it is a weekday.
Function EE_IsWeekday(dt As Date) As Boolean
'Takes a date, returns if it's a weekday
'http://excelexperts.com/xla-routines-eeIsWeekday    for updates on this function

    Select Case Format(dt, "w")
        Case vbSaturday, vbSunday
            EE_IsWeekday = False
        Case Else
            EE_IsWeekday = True
    End Select
End Function
Nick's picture

XLA routines: EE_SendReport

EE_SendReport is a hugely useful routine that sends a range as an email - can also add attachments like zipped up files - requires ms outlook
Sub EE_SendReport(rptRange As range, recipients As range, Files As range, Optional SendOrDisplay As Boolean, Optional ZipFileName As String)
'RptRange is a range containing text we want to be contained in the body
'Recipients is range containing email addresses Files is range containing
'list of files that we will zip up and add to mail SendOrDisplay - If= Send, mail is sent..
Nick's picture

XLA routines: EE_ZipFile

EE_ZipFile is a routine that zips a file.
Nick's picture

XLA routines: EE_SheetExists

EE_SheetExists is a simple function that returns whether a sheet exists
Function EE_SheetExists(strSheetName As String, Optional wb As Workbook) As Boolean
    Dim wbk             As Workbook
 
'http://excelexperts.com/xla-routines-eeSheetExists    for updates on this function

    If IsMissing(wb) Or wb Is Nothing Then
        Set wbk = ActiveWorkbook
    Else
        Set wbk = wb
    End If
 
    On Error Resume Next
        EE_SheetExists = Not (wbk.Worksheets(strSheetName) Is Nothing)
    Err.Clear: On Error GoTo 0: On Error GoTo -1
End Function
Nick's picture

XLA routines: EE_GetTempPath

EE_GetTempPath is a utility function that returns the temp path.
Function EE_GetTempPath() As String
'http://excelexperts.com/xla-routines-eeGetTempPath    for updates on this function
    EE_GetTempPath = Environ("Temp")
End Function
Nick's picture

XLA routines: EE_OpenFileFromCell

EE_OpenFileFromCell is a routine that one would use to add to a button - select a cell containing a file name, and press the button to open the file in the Excel session - useful for debugging input files - works with a file name or full path
Sub EE_OpenFileFromCell(rngFileCell As range)
    Dim rngCell As range
 
'http://excelexperts.com/xla-routines-eeOpenFileFromCell    for updates on this sub routine

    For Each rngCell In rngFileCell
        On Error Resume Next
        If InStr(rngCell.value, Application.PathSeparator) > 0 Then
            Application.Workbooks.
Nick's picture

XLA routines: EE_CombineSheets

EE_CombineSheets is a routine that combines the sheets on a workbook - works if the headers are the same
Sub EE_CombineSheets(wbkFrom As Workbook, rngTarget As range, Optional arrSheetNames As Variant)
    Dim intSheets       As Integer
    Dim rngCopy         As range
    Dim rngPaste        As range
    Dim wks             As Worksheet
    Dim wksNew          As Worksheet
    Dim x               As Integer
 
'http://excelexperts.com/xla-routines-eeCombineSheets    for updates on this sub routine
    If IsArray(arrSheetNames) = False Then
        ReDim arrSheetNames(1 
Nick's picture

XLA Routines: EE_ImportFromFile

EE_ImportFromFile is a routine that imports a sheet on a file and puts the results on the target range (the top left cell)
Sub EE_ImportFromFile(wbkFullPath As String, strSheet As String, rngTarget As range)
    Dim wbkSrc      As Workbook
    Dim wksSrc      As Worksheet
 
'http://excelexperts.com/xla-routines-eeImportFromFile    for updates on this function
    If EE_FileExists(wbkFullPath) = False Then Exit Sub
 
    Set wbkSrc = Workbooks.Open(wbkFullPath)
    Set wksSrc = wbkSrc.Worksheets(strSheet)
 
    wksSrc.UsedRange.Copy
    rngTarget.PasteSpecial xlPast
Nick's picture

XLA routines: EE_FileFolderExists

Function to return whether a folder exists
Public Function EE_FileFolderExists(strFullPath As String) As Boolean
'http://excelexperts.com/xla-routines-eeFileFolderExists    for updates on this function
    On Error GoTo EarlyExit
    If Not Dir(strFullPath, vbDirectory) = vbNullString Then EE_FileFolderExists = True
    Err.Clear: On Error GoTo 0: On Error GoTo -1
EarlyExit:
    On Error GoTo 0
End Function
Nick's picture

XLA routines: EE_FileExists

Function to return whether a file exists
Function EE_FileExists(strFile As String) As Boolean
'http://excelexperts.com/xla-routines-eeFileExists    for updates on this function
    EE_FileExists = CreateObject("Scripting.FileSystemObject").FileExists(strFile)
End Function
Nick's picture

XLA routines: EE_CurrentRegionFromCell

EE_CurrentRegionFromCell returns the area contiguous to the input cell.
Function EE_CurrentRegionFromCell(rng As range) As range
'http://excelexperts.com/xla-routines-eeCurrentRegionFromCell    for updates on this function
    Set EE_CurrentRegionFromCell = rng.currentregion
End Function
Nick's picture

XLA routines: EE_RemoveDupes

Removing duplicates from a range is something you frequently want to do.
Nick's picture

XLA routines: EE_SortArray

EE_SortArray does what it says on the tin: Sorts an array
Function EE_SortArray(ArrayToSort, Optional descending As Boolean)
'- takes array
'- sorts it
    Dim value As Variant, temp As Variant
    Dim sp As Integer
    Dim leftStk(32) As Long, rightStk(32) As Long
    Dim leftNdx As Long, rightNdx As Long
    Dim i As Long, j As Long
    Dim numEls
 
'http://excelexperts.com/xla-routines-eeSortArray    for updates on this function

    ' account for optional arguments
    numEls = UBound(ArrayToSort)
    ' init pointers
    leftNdx = LBound(ArrayToSort)
    rightNdx = 
Nick's picture

XLA routines: EE_WriteToTextFile

Similar to logging errors, this simple sub routine writes to a text file
Sub EE_WriteToTextFile(strMsg As String, FilePath As String)
    ' Writing a text file using File System Object in VBA
    ' This code requires a reference (In the VBE Tools > References) to Microsoft Scripting Runtime
    Dim fso         As Object
    Dim FSOFile     As Object
    Dim NoOfLoop    As Integer
 
'http://excelexperts.com/xla-routines-eeWriteToTextFile    for updates on this sub routine

    Set fso = CreateObject("Scripting.FileSystemObject")
    If fso.FileExists(FilePath) = True The
Nick's picture

XLA routines: EE_LogError

EE_LogError - logs to temp file if no file specified... logs information.
Nick's picture

XLA routines: EE_GetComputername

EE_GetComputername is a handy function that returns the Computer name - useful if you only want code to run on specific computers
Function EE_GetComputername()
'http://excelexperts.com/xla-routines-eeGetComputername    for updates on this function
    EE_GetComputername = Environ("computername")
End Function

Syndicate content