# Nick's blog

## 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```

## 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...

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

For intLoop = dt1 To dt2 Step IIf(dt1 > dt2, -1, 1)

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

Else

## 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
End Function```

## 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

Else

## 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```

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

```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.```

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

intPvs = 1
Do While True
dtPvs = DateAdd("d", 0 - intPvs, dt)
Exit Func```

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

intNext = 1
Do While True
Exit Function
```

EE_IsBusinessDay is a very useful financial function that returns whether a date is a business day.

## 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```

## 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```

## 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..```

## XLA routines: EE_ZipFile

EE_ZipFile is a routine that zips a file.

## 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```

## 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```

## 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.```

## 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 ```

## 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```

## 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```

## 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```

## 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```

## XLA routines: EE_RemoveDupes

Removing duplicates from a range is something you frequently want to do.

## 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 = ```

## 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```

## XLA routines: EE_LogError

EE_LogError - logs to temp file if no file specified... logs information.

## 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```

```Function EE_GetUsername() As String