Nick's blog

Nick's picture

Excel Jobs in London

Live Excel Jobs in London

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

VBA Jobs in Cardiff

Live VBA Jobs in Cardiff

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

VBA Jobs in Dundee

Live VBA Jobs in Dundee

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

VBA Jobs in Perth

Live VBA Jobs in Perth 

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

VBA Jobs in Inverness

Live VBA Jobs in Inverness

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

VBA Jobs in Aberdeen

Live VBA Jobs in Aberdeen

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

VBA Jobs in Edinburgh

Live VBA Jobs in Edinburgh

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

VBA Jobs in Glasgow

Live VBA Jobs in Glasgow

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

VBA Jobs in Belfast

Live VBA Jobs in Belfast

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

VBA Jobs in Leeds

Live VBA Jobs in Leeds

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

VBA Jobs in Bristol

Live VBA Jobs in Bristol

{"what":"vba","where":"Bristol","results_per_page":"20","country":"gb","widget":"job-feed"}
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

Syndicate content