Blogs

Nick's picture

VBA Jobs in Birmingham

Live VBA Jobs in Birmingham

{"what":"vba","where":"Birmingham","results_per_page":"20","country":"gb","widget":"job-feed"}
Nick's picture

VBA Jobs in Liverpool

Live VBA Jobs in Liverpool

{"what":"vba","where":"Liverpool","results_per_page":"20","country":"gb","widget":"job-feed"}
Nick's picture

VBA Jobs in Manchester

Live VBA Jobs in Manchester

{"what":"vba","where":"Manchester","results_per_page":"20","country":"gb","widget":"job-feed"}
Nick's picture

VBA Jobs London

Live feed of VBA Jobs in London.

{"what":"vba","where":"london","results_per_page":"20","country":"gb","widget":"job-feed"}
Nick's picture

Jobs for Excel VBA Specialists

Check out our new Excel Jobs pages:

Nick's picture

XLA routines: EE_CurrentRegion

EE_CurrentRegion returns the current region - the area contiguous to a cell.
Function EE_CurrentRegion(Optional Cell As range) As range
' returns the Currentregion
' defaults to activecell
'http://excelexperts.com/xla-routines-eeCurrentregion    for updates on this function
    If Cell Is Nothing Then
        Set Cell = ActiveCell
    End If
 
    Set EE_CurrentRegion = Cell.CurrentRegion
End Function
Nick's picture

XLA routines: EE_UsedRange

EE_UsedRange returns the used range - the area that has been touched on the sheet
Function EE_UsedRange(Optional ws As Worksheet) As range
' returns the usedrange
' defaults to activeworksheet
'http://excelexperts.com/xla-routines-eeUsedRange    for updates on this function
    If ws Is Nothing Then
        Set ws = ActiveSheet
    End If
 
    Set EE_UsedRange = ws.UsedRange
 
End Function
Nick's picture

XLA routines: EE_ClearRows

EE_ClearRows clears the data from the rows specified
Sub EE_ClearRows(FromRow As Long, Optional ToRow As Long)
' Clears all the data from the rows
'http://excelexperts.com/xla-routines-eeClearRows    for updates on this routine
    If ToRow = 0 Then
        ToRow = FromRow
    End If
 
    range(Cells(FromRow, 1), Cells(ToRow, 1)).EntireRow.ClearContents
End Sub
Nick's picture

XLA routines: EE_DeleteColumns

EE_DeleteColumns deletes columns
Sub EE_DeleteColumns(FromCol As Variant, Optional ToCol As Variant)
' - FromCol and ToCol can either be a column, a letter, a number, or a heading
'http://excelexperts.com/xla-routines-eeDeleteColumns    for updates on this routine

    If Not IsMissing(ToCol) Then
        If ToCol = "" Then
            ToCol = FromCol
        End If
    Else
        ToCol = FromCol
    End If
 
    If VarType(ToCol) = vbString Then
        ToCol = CLng(range(ToCol & "1").Column)
    End If
    If VarType(FromCol) = vbString Then
        FromCol = CL
Nick's picture

XLA routines: EE_InsertColumns

EE_InsertColumns inserts columns
Sub EE_InsertColumns(FromCol As Variant, Optional ToCol As Variant)
' - FromCol and ToCol can either be a column, a letter, a number, or a heading
'http://excelexperts.com/xla-routines-eeInsertColumns    for updates on this routine

    If Not IsMissing(ToCol) Then
        If ToCol = "" Then
            ToCol = FromCol
        End If
    Else
        ToCol = FromCol
    End If
 
    If VarType(ToCol) = vbString Then
        ToCol = CLng(range(ToCol & "1").Column)
    End If
    If VarType(FromCol) = vbString Then
        FromCol = CL
Nick's picture

XLA routines: EE_ClearColumns

EE_ClearColumns clears all the data in the columns specified
Sub EE_ClearColumns(FromCol As Variant, Optional ToCol As Variant)
' - FromCol and ToCol can either be a column, a letter, a number, or a heading
' clears all data from the col
'http://excelexperts.com/xla-routines-eeClearColumns    for updates on this routine
    If Not IsMissing(ToCol) Then
        If ToCol = "" Then
            ToCol = FromCol
        End If
    Else
        ToCol = FromCol
    End If
 
    If VarType(ToCol) = vbString Then
        ToCol = CLng(range(ToCol & "1").Column)
    End If
    I
Nick's picture

XLA routines: EE_ClearColumnsData

EE_ClearColumnsData clears the data but not the header for a range of columns.
Sub EE_ClearColumnsData(FromCol As Variant, Optional ToCol As Variant)
' http://excelexperts.com/xla-routines-eeClearColumnsData for updates on this routine

' - FromCol and ToCol can either be a column, a letter, a number, or a heading
' calls EE_ClearColumn and puts headings back

' Example
' Age
' 1
' 40

' =>
' Age

' i.e the data is cleared, heading remains
'http://excelexperts.com/xla-routines-eeClearColumnsData    for updates on this routine
    
    If Not IsMissing(ToCol) Then
 
Nick's picture

XLA routines: EE_CopyAndAddToTheEnd

EE_CopyAndAddToTheEnd takes the range to copy, the destination sheet, the header that the range will be copied in, and the row - useful for reconciling data
Sub EE_CopyAndAddToTheEnd(RangeToCopy As range, DestinationSheet As Worksheet, DestinationSheetPasteRow As Long, DestinationSheetPasteColHeader As String)
    'Basically the same as EE_copy, but Takes a destination sheet
    'and appends data to the end
'http://excelexperts.com/xla-routines-eeCopyAndAddToTheEnd    for updates on this routine
    Call EE_Copy(RangeToCopy, DestinationSheet.Cells(DestinationSheetPasteRow, EE_Ta
Nick's picture

XLA routines: EE_TableGetColumnDataAndHeading

EE_TableGetColumnDataAndHeading returns the data and heading from a table of data.
Function EE_TableGetColumnDataAndHeading(Heading As String) As range
    ' returns the data and header from a header
'http://excelexperts.com/xla-routines-eeTableGetColumnDataAndHeading    for updates on this function
    Set EE_TableGetColumnDataAndHeading = EE_RangeChange(EE_TableGetColumnData(Heading), -1, 0, 0, 0)
End Function
Nick's picture

XLA routines: EE_TableGetColumnData

EE_TableGetColumnData takes a heading and returns only the data
Function EE_TableGetColumnData(Heading As String) As range
    ' returns the data from a header
'http://excelexperts.com/xla-routines-eeTableGetColumnData    for updates on this function
    Set EE_TableGetColumnData = Columns(EE_TableHeadingCol(Heading, ActiveSheet.UsedRange))
    Set EE_TableGetColumnData = Intersect(EE_TableGetColumnData, ActiveSheet.UsedRange)
    Set EE_TableGetColumnData = EE_RangeChange(EE_TableGetColumnData, 1, 0, 0, 0)
End Function
Nick's picture

XLA routines: EE_TableGetColumn

EE_TableGetColumn takes a heading and returns the whole column as a range
Function EE_TableGetColumn(Heading As String, Optional Table As range) As range
'http://excelexperts.com/xla-routines-eeTableGetColumn  for updates on this routine
    ' returns the whole column from a header
    If Table Is Nothing Then
        Set Table = ActiveSheet.UsedRange
    End If
 
    Set EE_TableGetColumn = Columns(EE_TableHeadingCol(Heading, Table))
End Function
</vb
Nick's picture

XLA routines: EE_SheetName

EE_SheetName returns the sheet name...
Public Function EE_SheetName(Optional ws As Worksheet)
' returns the sheet name
' can be used on a worksheet as a function or called
'http://excelexperts.com/xla-routines-eeSheetName    for updates on this function

    If ws Is Nothing Then
        EE_SheetName = ActiveSheet.Cells(1).Parent.Name
    Else
        EE_SheetName = ws.Name
    End If
 
End Function
Nick's picture

XLA routines: EE_FileFolderExists

EE_FileFolderExists returns 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_RangeChange

EE_RangeChange is probably one of the most useful functions in the Excel Experts XLA

- it is a totally flexible function that allows you to reshape a range or extract a specific range from a larger range

- particularly useful for manipulating data

Function EE_RangeChange(RangeToChange As range, StartRowOffset As Integer, StartColOffset As Integer, _
    EndRowOffset As Integer, EndColOffset As Integer, Optional MaxRows As Long, Optional MaxCols As Long) As range
' creates a new range based on resizing the existing range
' so If we add StartRowOffset =1, we move the star
Nick's picture

XLA routines: EE_ExtractRow

EE_ExtractRow extracts a row from a data table, and puts it on a new sheet either transposed, or the same. - useful for extracting a record and analysing
Sub EE_ExtractRow(Optional SourceSht As Worksheet, Optional TargetSht As String, Optional RowToExtract As Long, Optional wb As Workbook, Optional blnTranspose As Boolean = True)
' takes the selected cell row as default
' copies and paste transpose onto a new sheet
' copies row and header onto sheet specified
'http://excelexperts.com/xla-routines-eeExtractRow    for updates on this sub

    If SourceSht Is Nothing Then
 
Nick's picture

XLA routines: EE_FirstBusinessDayOfMonth

use EE_FirstBusinessDayOfMonth to find the first business day of the month that the date you are passing 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 = 
Nick's picture

XLA routines: EE_ImportFromFile

Import a file's contents using EE_ImportFromFile
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 xlPasteValues
    rngTarget.PasteSpecial xlPasteFormats
 
    Application.
Nick's picture

XLA routines: EE_FileExists

Function to return if 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_IsArray

VBA's IsArray function doesn't work so well, so use this one for a n by n array.
Function EE_IsArray(varArgument As Variant) As Boolean
'- takes variant
'- returns whether it really is an array. (By checking whether theArray(1,1) exists)
'http://excelexperts.com/xla-routines-eeIsArray    for updates on this function

On Error GoTo IsNotArray
    EE_IsArray = True
    Dim temp
    temp = varArgument(1, 1)
    Exit Function
IsNotArray:
    EE_IsArray = False
End Function

Help Merging Data into a sorted worksheet

Total Sales By Customer By Month 2013 vs. 2014

CUSID CUS Name Oct-13 Nov-13 Dec-13 2013 Total Oct-14 Nov-14 Dec-14 2014 Total
236 VRTE Co 14,215.39 9,986.12 24,201.51 23,250.17 14,273.29 37,523.46
689 SWR LLC 97,216.85 102,346.98 199,563.83 65,302.67 201,203.22 266,505.89
782 OLLY Inc 108,779.99 93,882.14 223,765.34 74,298.97 163,555.98 237,854.95
220,212.23 206,215.24 426,427.47 632,642.71 1,059,070.18 1,691,712.89
236 1,791.17
689 142,111.93
782 267,458.11

Nick's picture

XLA routines: EE_ReplaceErrors

EE_ReplaceErrors replaces errors on your sheet with ""
Sub EE_ReplaceErrors(rng As range)
'Takes a range.. Replaces any cells containing errors with ""
'http://excelexperts.com/xla-routines-eeReplaceErrors    for updates on this sub routine

    On Error Resume Next
        rng.SpecialCells(xlCellTypeFormulas, 16).value = ""
    Err.Clear: On Error GoTo 0: On Error GoTo -1
End Sub
Nick's picture

XLA routines: EE_SaveIfMe

EE_SaveIfMe is a handy developer sub that saves your work if you are the one running it. - it does not save if someone else is running it - never lose your work to crashes in Excel again !
Sub EE_SaveIfMe(strUserName As String)
'Takes a username as string
'Looks to see if application.username or ee_getusername is the string..
'If yes, save the workbook.

'http://excelexperts.com/xla-routines-eeSaveIfMe    for updates on this sub routine

    Select Case strUserName
    Case Application.UserName, EE_GetUsername
        ThisWorkbook.Save
    End Select
End Sub
Nick's picture

XLA routines: EE_OpenFromTemp

EE_OpenFromTemp is a time-saving function that copies a file to temp dir if it's different then opens it from temp - don't use this function if you are looking to modify the file
Function EE_OpenFromTemp(strFullFilePath As String) As Boolean
'Takes a full file name and path
'Opens the same file but from temp path
'Returns false if unsuccessful
'http://excelexperts.com/xla-routines-eeOpenFromTemp    for updates on this function

    Call EE_CopyFile(strFullFilePath, Environ("Temp"))
    On Error Resume Next
        Workbooks.Open (Environ("Temp") & Application.PathSeparator & 
Nick's picture

XLA routines: EE_CopyToTempIfDifferent

EE_CopyToTempIfDifferent copies a file to the temp dir if it has changed - useful if you are opening the same file from a directory multiple times
Function EE_CopyToTempIfDifferent(strFullFilePath As String) As Boolean
'Takes a full file name and path
'Copies it to temp dir (deleting existing file if it exists) Returns false if unsuccessful
'http://excelexperts.com/xla-routines-eeCopyToTempIfDifferent    for updates on this function

    On Error Resume Next
        Kill Environ("Temp") & Application.PathSeparator & EE_FileNameFromFilePath(strFullFilePath)
        Call EE_Copy
Nick's picture

XLA routines: EE_CopyFile

EE_CopyFile copies a file from one place to the next
Sub EE_CopyFile(strFullFilePath As String, strTarget As String)
'http://excelexperts.com/xla-routines-eeCopyFile    for updates on this sub routine

    If EE_FileNameFromFilePath(strFullFilePath) = EE_FileNameFromFilePath(strTarget) Then
        'FileCopy strFullFilePath, strTarget
        Call CreateObject("Scripting.FileSystemObject").CopyFile(strFullFilePath, strTarget)
    Else
        'FileCopy strFullFilePath, strTarget & Application.PathSeparator & EE_FileNameFromFilePath(strFullFilePath)
        Call CreateObject("S
Syndicate content