image
 
image
M009_ExcelUtils.bas


' ========================================================================================================================
'                                 ____,__,  __,  ____,_,  _,  ____ ____,__, ,____,____,
'                                (-/_|(-|  (-|  (-|_,(-|\ |  (-|__|-/_|( |_/(-|_,(-|__)
'                                _/  |,_|__,_|__,_|__,_| \|,  _|__)/  |,_| \,_|__,_|  \,
'
'                                             Copyright � 2008 Allen Baker
'
' ------------------------------------------------------------------------------------------------------------------------
' File:          M009_ExcelUtils
' Originator:    Allen Baker (2008.11.23 16:27)
' ------------------------------------------------------------------------------------------------------------------------
' $RCSfile$
' $Revision$
' $Date$
' ========================================================================================================================
'
Option Explicit



' ========================================================================================================================
' Description
'    This module provides miscellaneous utility routines and functions that operate on Excel objects and variables.
' ========================================================================================================================



' =====================================================================================================================
' @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@[  Constants  ]@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
' =====================================================================================================================



' =====================================================================================================================
' @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@[  Module Variables  ]@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
' =====================================================================================================================



'
' ==============================================================================================
' whe we find visible (unhidden) row, we're done and we return the address of the cell in that
' row.
' ----------------------------------------------------------------------------------------------
Private mCurrentSelection     As Range
Private mActiveWorkbook       As Workbook
Private mActiveSheet          As Worksheet
Private mActiveCell           As Range
Private mActiveScreenUpdating As Boolean

Private mContextInitialized        As Boolean
Private mActiveWorkbookStack       As StackOfObjects
Private mActiveSheetStack          As StackOfObjects
Private mActiveCellStack           As StackOfObjects
Private mCurrentSelectionStack     As StackOfObjects
Private mActiveScreenUpdatingStack As StackOfVariants


' =====================================================================================================================
' @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@[  Public Routines  ]@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
' =====================================================================================================================



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns either the current Selection if it is a Range or, if the current selection is not a
' Range, it returns Nothing.
'
' return
'    This function returns the current Selection if it is a Range and not a Chart, Autoshape, or other
'    object. Otherwise it returns Nothing.
' -----------------------------------------------------------------------------------------------------------
Public Function selectionAsRangeOrNothing() As Range
   If (selectionIsRange()) Then
      Set selectionAsRangeOrNothing = Selection
   Else
      Set selectionAsRangeOrNothing = Nothing
   End If
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function test that the current Selection is a Range and not a Chart, Autoshape, or other object.
'
' return
'    This function returns True if the current Selection is a Range object, otherwise, it returns False
' -----------------------------------------------------------------------------------------------------------
Public Function selectionIsRange() As Boolean
   selectionIsRange = isRange(Selection)
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function test that the argument value passed in through the Variant parameter is a Range object.
'
' return
'    This function returns True if pVariant is a Range object, otherwise, it returns False
'
' param
'    pVariant is the thing tested to see if it is a Range object
' -----------------------------------------------------------------------------------------------------------
Public Function isRange(pVariant As Variant) As Boolean
   On Error GoTo thisIsNotARange
   isRange = (TypeName(pVariant) = "Range")
   Exit Function
thisIsNotARange:
      isRange = False
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function tries to return a Range object.
'
' return
'    If the argument value passed in through its parameter is a
'    Range, it is returned as a Range object. If no argumet is passed in, it checks if the current selection is
'    a range. If so, it returns the current selectiion as a Range object. If it can't return either the
'    parameter or the current selection as a range, it returns Nothing.
'
' param (optional)
'    pVariant is tested to see if it is a Range object and if so, it is returned as a Range object. If no
'    argument value is passed to this procedure through this optional parameter, this procedure will return
'    the current selection if it is a Range object.
' -----------------------------------------------------------------------------------------------------------
Public Function parameterOrSelectionAsRange(Optional pVariant As Variant) As Range
   If (IsMissing(pVariant)) Then
      Set parameterOrSelectionAsRange = selectionAsRangeOrNothing()
   ElseIf (isRange(pVariant)) Then
      Set parameterOrSelectionAsRange = pVariant
   Else
      Set parameterOrSelectionAsRange = Nothing
   End If
End Function


' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function sets the screentip on the current selection (if it is a range) or on a specified Range of
' cells. The Range can be specified by using the optional pRange parameter.
' parameter.
'
' param
'    pMessage is the string to use as the screen tip
' param (optional)
'    pRange is the Range to set the screen tip message for. If no argument value is passed to this procedure
'    through this optional parameter, its value defaults to the current selection.
' -----------------------------------------------------------------------------------------------------------
Public Sub setScreenTip(pMessage As String, Optional pRange As Range)
   Dim theRange As Range
   Set theRange = parameterOrSelectionAsRange(pRange)
   If (Not (theRange Is Nothing)) Then
      Dim currentCell As Range
      For Each currentCell In theRange
         currentCell.Hyperlinks(1).ScreenTip = pMessage
      Next currentCell
   End If
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function is my bomb-proof way of getting a Double from a cell.
'
' return
'    This function returns 0 if the cell contains a 0 or if the cell is vbNullString or empty or null or
'    contains non-numeric characters. Otherwise, it converts whatever is in the cell into a Double and
'    returns the resulting value.
'
' param
'    pCell is the cell whose value to get
' -----------------------------------------------------------------------------------------------------------
Public Function cellValueAsDouble(pCell As Range) As Double
   If (IsNull(pCell.Cells(1, 1).value)) Then
      cellValueAsDouble = 0#
   ElseIf (IsEmpty(pCell.Cells(1, 1).value)) Then
      cellValueAsDouble = 0#
   ElseIf (pCell.Cells(1, 1).value = vbNullString) Then
      cellValueAsDouble = 0#
   ElseIf (Not IsNumeric(pCell.Cells(1, 1).value)) Then
      cellValueAsDouble = 0#
   Else
      cellValueAsDouble = CDbl(pCell.Cells(1, 1).value)
   End If
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function tests to see if a string is a valid Hex value.
'
' return
'    True if the string is a vaild hex value, false otherwise
'
' param
'    pInput is the value to check
' -----------------------------------------------------------------------------------------------------------
Public Function isHex(ByVal pInput As Variant, Optional ByVal pHexPrefix As String = "#") As Boolean
   pInput = trimLeftSubstring(pInput, pHexPrefix)
   isHex = Not (Application.IsError(Application.Hex2Dec(pInput)))
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' These functions allow you to get the row and column dimensions in a formula
'
' return
'    These functions return the row or column dimension
'
' param
'    pRange is intended to be a cell reference or arow or column reference
' -----------------------------------------------------------------------------------------------------------
Public Function rowHeight(Optional pRange As Range) As Double
   If (pRange Is Nothing) Then
      rowHeight = Selection.rowHeight
   Else
      rowHeight = pRange.rowHeight
   End If
End Function


Public Function columnWidth(Optional pRange As Range) As Double
   If (pRange Is Nothing) Then
      columnWidth = Selection.columnWidth
   Else
      columnWidth = pRange.columnWidth
   End If
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function creates a new worksheet in the active workbook and names it
'
' return
'    This function returns a reference to the newly created sheet
'
' param
'    pSheetName is the name to give to the new sheet
' -----------------------------------------------------------------------------------------------------------
Public Function newSheet(ByVal pSheetName As String) As Worksheet
   saveExcelContext
   '
   ' ==============================================================================================
   ' We don't want to tromp all over an existing sheet so find a name that is not already being
   ' used for our new sheet using pSheetName as the default.
   ' ----------------------------------------------------------------------------------------------
   Dim count        As Long
   Dim newSheetName As String
   count = 1
   newSheetName = pSheetName
   While (worksheetExists(newSheetName))
      newSheetName = pSheetName & count
      count = count + 1
   Wend
   '
   ' ==============================================================================================
   ' now create the sheet as the last sheet in the workbook and return a reference to it.
   ' ----------------------------------------------------------------------------------------------
   Worksheets.Add(after:=Worksheets(Worksheets.count)).Name = newSheetName
   Set newSheet = Sheets(newSheetName)
   restoreExcelContext
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine changes the tab color for a Sheet in the Active workbook
'
' param
'    pSheetName is the name of the sheet to change the tab color for
' param
'    pRgbColor is the color as an RGB value to set the tab color to.
' -----------------------------------------------------------------------------------------------------------
Public Sub setSheetTabColor(pSheetName As String, pRgbColor As Long)
   Dim hue         As Long
   Dim Saturation  As Long
   Dim lightness   As Long
   Dim tintOrShade As Double
   Call rgbToHsl(pRgbColor, hue, Saturation, lightness)
   tintOrShade = (CDbl(lightness) / 128#) - 0.5
   Sheets(pSheetName).Select
   With ActiveWorkbook.Sheets(pSheetName).Tab
      .color = pRgbColor
      .TintAndShade = tintOrShade
   End With
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine changes the tab color for the Active Sheet
'
' param
'    pRgbColor is the color as an RGB value to set the tab color to.
' -----------------------------------------------------------------------------------------------------------
Public Sub setActiveSheetTabColor(pRgbColor As Long)
    Call setSheetTabColor(ActiveSheet.Name, pRgbColor)
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine changes the tab color for the Active Sheet
'
' param
'    pRgbColor is the color as an RGB value to set the tab color to.
' -----------------------------------------------------------------------------------------------------------
Public Sub setActiveSheetTabColorToRed()
    Call setSheetTabColor(ActiveSheet.Name, hslToRgb(0, 255, 80))
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine changes the tab color for the Active Sheet
'
' param
'    pRgbColor is the color as an RGB value to set the tab color to.
' -----------------------------------------------------------------------------------------------------------
Public Sub setActiveSheetTabColorToGreen()
    Call setSheetTabColor(ActiveSheet.Name, hslToRgb(85, 255, 80))
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine changes the tab color for the Active Sheet
'
' param
'    pRgbColor is the color as an RGB value to set the tab color to.
' -----------------------------------------------------------------------------------------------------------
Public Sub setActiveSheetTabColorToYellow()
    Call setSheetTabColor(ActiveSheet.Name, hslToRgb(42, 255, 128))
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine changes the tab color for the Active Sheet
'
' param
'    pRgbColor is the color as an RGB value to set the tab color to.
' -----------------------------------------------------------------------------------------------------------
Public Sub setActiveSheetTabColorToBlue()
    Call setSheetTabColor(ActiveSheet.Name, hslToRgb(170, 255, 128))
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine stores the current Excel context on a stack.  So saveExcelContexts must be matched by
' restoreExcelContexts
' -----------------------------------------------------------------------------------------------------------
Public Sub saveExcelContext()
   If (Not mContextInitialized) Then
      mActiveWorkbookStack = newStackOfObjects()
      mActiveSheetStack = newStackOfObjects()
      mActiveCellStack = newStackOfObjects()
      mCurrentSelectionStack = newStackOfObjects()
      mActiveScreenUpdatingStack = newStackOfVariants()
      mContextInitialized = True
   End If
      Call pushStackOfObjects(mActiveWorkbookStack, ActiveWorkbook)
      Call pushStackOfObjects(mActiveSheetStack, ActiveSheet)
      Call pushStackOfObjects(mActiveCellStack, ActiveCell)
      Call pushStackOfObjects(mCurrentSelectionStack, Selection)
      Call pushStackOfVariants(mActiveScreenUpdatingStack, Application.ScreenUpdating)
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine restores the Excel context from a stack.  So saveExcelContexts must be matched by
' restoreExcelContexts
' -----------------------------------------------------------------------------------------------------------
Public Sub restoreExcelContext()
   If (Not mContextInitialized) Then
      mActiveWorkbookStack = newStackOfObjects()
      mActiveSheetStack = newStackOfObjects()
      mActiveCellStack = newStackOfObjects()
      mCurrentSelectionStack = newStackOfObjects()
      mActiveScreenUpdatingStack = newStackOfVariants()
      mContextInitialized = True
      Exit Sub
   End If
      popStackOfObjects(mActiveWorkbookStack).Activate
      popStackOfObjects(mActiveSheetStack).Activate
      popStackOfObjects(mActiveCellStack).Activate
      popStackOfObjects(mCurrentSelectionStack).Select
      Application.ScreenUpdating = popStackOfVariants(mActiveScreenUpdatingStack)
End Sub


' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This deals with a problem encountered when importing numeric data into one spreadsheet from certain other
' types of excel files.  Some of the numbers may come in as text.  This procedue converts numbers in text
' format to actual numbers.
' -----------------------------------------------------------------------------------------------------------
Public Sub convertTextNumbersToActualNumbers()
   Dim cell As Range
   For Each cell In Selection
      cell.value = Val(cell.value)
   Next
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine undoes all autofiltering that is in effect on the ActiveSheet.  A side effect is that all
' rows that are hidden as a result of the filtering, are unhidden.
' -----------------------------------------------------------------------------------------------------------
Public Sub resetAutofiltering()
   If ActiveSheet.AutoFilterMode = True Then
      ActiveSheet.AutoFilterMode = False
   End If
   If ActiveSheet.AutoFilterMode = False Then
      Range("SortRange").AutoFilter
   End If
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine capitalizes all the String values in the specified range.
'
' param
'    pRange is the Range of cells to capitalize the values
' -----------------------------------------------------------------------------------------------------------
Public Sub capitalizeRange(pRange As Range)
   Dim currentCell As Range
   For Each currentCell In pRange
      With currentCell
         If Not IsEmpty(currentCell) Then
            If VarType(currentCell.value) = vbString Then
               currentCell.value = UCase(currentCell.value)
            End If
         End If
      End With
   Next currentCell
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine executes an existing autofilter sort
'
' param
'    pSheet is the sheet containing the Range (pKey) that will be sorted.
' param
'    pKey is a Range that references a SINGLE column that has an auto filter set up on.
' param
'    pOrder is either xlAscending or xlDescending
' -----------------------------------------------------------------------------------------------------------
Public Sub performAutoFilterSort(pSheet As Worksheet, pKey As Range, pOrder As XlSortOrder)
   pSheet.AutoFilter.Sort.SortFields.Clear
   pSheet.AutoFilter.Sort.SortFields.Add Key:=pKey, SortOn:=xlSortOnValues, Order:=pOrder, DataOption:=xlSortNormal
   With pSheet.AutoFilter.Sort
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
   End With
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns the value in the first unhidden row above pCell.  It's usefull if the visible rows
' are not continuous because a filter was applied and hid some rows for example.
'
' return
'    this function returns the value in the first unhidden row above pCell
'
' param
'    pCell is a Range that references a SINGLE cell.
' -----------------------------------------------------------------------------------------------------------
Public Function prevVisibleRowValue(pCell As Range) As Variant
   Dim done      As Boolean
   Dim rowOffset As Long
   '
   ' ==============================================================================================
   ' pCell(1,1) is the same as pCell.  pCell(0,1) is the cell in the same column and the row
   ' directly above pCell. pCell(-1,1) is the cell in the same column and two rows above pCell.
   ' etc.  We'll start with the cell directly above pCell i.e. pCell(0,1) and search towards the
   ' top of the sheet until we find a visible (unhidden) row. as soon as we find one, we return
   ' the value of the cell in pCell's column on that row.  If we bump into the top of the sheet
   ' before finding a visible row, we stop the search and return an empty Variant.
   ' ----------------------------------------------------------------------------------------------
   done = False
   rowOffset = 0
   While (Not done) And (Not (pCell(rowOffset, 1) = vbNullString))
      '
      ' ==============================================================================================
      ' whe we find visible (unhidden) row, we're done and we return the value of the cell in that
      ' row.
      ' ----------------------------------------------------------------------------------------------
      If pCell(rowOffset, 1).EntireRow.Hidden = False Then
         prevVisibleRowValue = pCell(rowOffset, 1).value
         done = True
      End If
      '
      ' ==============================================================================================
      ' to continue the search, back up one more row toward the top of the sheet.
      ' ----------------------------------------------------------------------------------------------
      rowOffset = rowOffset - 1
   Wend
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns the address of the first unhidden row above pCell.  It's usefull if the visible rows
' are not continuous because a filter was applied and hid some rows for example.
'
' return
'    this function returns the addresss of the first unhidden row above pCell in this form "C15" for the
'    cell in row 15 column "C"
'
' param
'    pCell is a Range that references a SINGLE cell.
' -----------------------------------------------------------------------------------------------------------
Public Function prevVisibleRowAddress(pCell As Range) As String
   Dim done      As Boolean
   Dim rowOffset As Long
   '
   ' ==============================================================================================
   ' pCell(1,1) is the same as pCell.  pCell(0,1) is the cell in the same column and the row
   ' directly above pCell. pCell(-1,1) is the cell in the same column and two rows above pCell.
   ' etc.  We'll start with the cell directly above pCell i.e. pCell(0,1) and search towards the
   ' top of the sheet until we find a visible (unhidden) row. as soon as we find one, we return
   ' the address of the cell in pCell's column on that row.  If we bump into the top of the sheet
   ' before finding a visible row, we stop the search and return an empty String.
   ' ----------------------------------------------------------------------------------------------
   done = False
   rowOffset = 0
   While (Not done) And (Not (pCell(rowOffset, 1) = vbNullString))
      '
      ' ==============================================================================================
      ' whe we find visible (unhidden) row, we're done and we return the address of the cell in that
      ' row.
      ' ----------------------------------------------------------------------------------------------
      If pCell(rowOffset, 1).EntireRow.Hidden = False Then
         prevVisibleRowAddress = pCell(rowOffset, 1).Address(0, 0)
         done = True
      End If
      '
      ' ==============================================================================================
      ' to continue the search, back up one more row toward the top of the sheet.
      ' ----------------------------------------------------------------------------------------------
      rowOffset = rowOffset - 1
   Wend
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine trims all leading and trailing whitespace from all the cells in the selected Range that
' contain String values.
' -----------------------------------------------------------------------------------------------------------
Public Sub trimSelectedCells()
   Dim cell   As Range
   Dim str    As String
   Dim nAscii As Integer

   For Each cell In Selection.Cells
      If cell.HasFormula = False Then
         str = Trim(CStr(cell))
         cell = str
      End If
   Next
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine trims all leading and trailing whitespace from all the cells in the selected Range that
' contain String values.  Since it uses an array, it will also delete any formulas in the cells
' -----------------------------------------------------------------------------------------------------------
Public Sub trimSelectedCellsAndDeleteFormulas_20K_X_Faster()
   Dim arrData()       As Variant
   Dim arrReturnData() As Variant
   Dim rng             As Range
   Dim lRows           As Long
   Dim lCols           As Long
   Dim i               As Long
   Dim j               As Long

   lRows = Selection.Rows.count
   lCols = Selection.Columns.count

   ReDim arrData(1 To lRows, 1 To lCols)
   ReDim arrReturnData(1 To lRows, 1 To lCols)

   Set rng = Selection
   arrData = rng.value

   For j = 1 To lCols
      For i = 1 To lRows
         arrReturnData(i, j) = Trim(arrData(i, j))
      Next i
   Next j

   rng.value = arrReturnData

   Set rng = Nothing
End Sub

' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine inserts # new rows ABOVE the selected cell(s) and copies the formatting from the row ABOVE
' the selected cell(s) into the new rows
' -----------------------------------------------------------------------------------------------------------
Public Sub insert32Rows()
   Call insertRows(32)
End Sub

Public Sub insert16Rows()
   Call insertRows(16)
End Sub

Public Sub insert08Rows()
   Call insertRows(8)
End Sub

Public Sub insert04Rows()
   Call insertRows(4)
End Sub

Public Sub insert02Rows()
   Call insertRows(2)
End Sub

Public Sub insert01Rows()
   Call insertRows(1)
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine inserts pCount new rows ABOVE the selected cell(s) and copies the formatting from the row
' ABOVE the selected cell(s) into the new rows
'
' param
'    pCount is the number of rows to insert
' -----------------------------------------------------------------------------------------------------------
Public Sub insertRows(pCount As Long)
   Dim count As Long
   Dim scrn  As Boolean
   scrn = Application.ScreenUpdating
   Application.ScreenUpdating = False
   For count = 1 To pCount
      Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
   Next count
   Application.ScreenUpdating = scrn
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine positions the specified cell in the top left of the window
' -----------------------------------------------------------------------------------------------------------
Public Sub gotoCell(pCell As String)
   ' ==============================================================================================
   ' position the top left cell in the top left corner of the display
   ' ----------------------------------------------------------------------------------------------
   Application.GoTo Range(pCell), Scroll:=True
   Range(pCell).Select
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function uses an AppActivate statement to activate an application (e.g. "Calculator") if it�s already
' running. The argument for AppActivate is the Caption of the application�s title bar. If the AppActivate
' statement generates an error, it means the Calculator isn�t running. If it�s not running, the routine
' starts the application using the Shell function and the program's name on disk (e.g. "calc.exe").
'
' return
'    this function returns True if the application is successfully started, otherwise it returns False
'
' param
'    pAppCaption is the Caption of the application�s title bar.
' param
'    pAppProgramName is the name of the program file on disk
' -----------------------------------------------------------------------------------------------------------
Public Function startApplication(pAppCaption As String, pAppProgramName As String) As Boolean
   startApplication = False
   On Error Resume Next
   '
   ' ==============================================================================================
   ' uses an AppActivate statement to activate an application (e.g. "Calculator") if it�s already
   ' running. The argument for AppActivate is the Caption of the application�s title bar. If the
   ' AppActivate statement generates an error, it means the Calculator isn�t running.
   ' ----------------------------------------------------------------------------------------------
   AppActivate pAppCaption
   If Err = 0 Then
      startApplication = True
   '
   ' ==============================================================================================
   ' If it�s not running, the routine starts the application using the Shell function
   ' ----------------------------------------------------------------------------------------------
   Else
      Err = 0
      Dim taskID As Double
      taskID = Shell(pAppProgramName, 1)
      If Err = 0 Then
         startApplication = True
      End If
   End If
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function uses an ActivateMicrosoftApp statement to activate a Microsoft application (e.g. Word,
' PowerPoint, Project, etc). If the Microsoft application is already running when this function is executed,
' the application is is activated
'
' return
'    this function returns True if the application is successfully started, otherwise it returns False
'
' param
'    pAppCode is the code identifying the application to start.  Valid codes are:
'        Code                     Application
'        -----------------------  ------------
'        xlMicrosoftWord          (Word)
'        xlMicrosoftPowerPoint    (PowerPoint)
'        xlMicrosoftMail          (Outlook)
'        xlMicrosoftAccess        (Access)
'        xlMicrosoftFoxPro        (FoxPro)
'        xlMicrosoftProject       (Project)
'        xlMicrosoftSchedulePlus  (SchedulePlus)
' -----------------------------------------------------------------------------------------------------------
Public Function startMicrosoftApplication(pAppCode As Long) As Boolean
   startMicrosoftApplication = False
   On Error Resume Next
   Application.ActivateMicrosoftApp pAppCode
   If Err = 0 Then
      startMicrosoftApplication = True
   End If
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns and array of values containing a sequence of numbers. The sequence of numbers begins
' at 1 and each successive number in the array is 1 larger than the previous number. The beginning number
' can optionally be changed from 1 to some other number by passing an argument for the pStart parameter. The
' increment can also be changed from 1 to some other number by passing an argument for the pIncrement
' parameter.
'
' To use an array function like this you must enter it as an "array formula" in excel. To enter an array
' formula, select a Range of cells. Then type =numberSequence(). Then press CTRL+SHIFT+ENTER. Excel will
' display the formula in the formula bar inclosed in curly braces like this {=numberSequence()}. Excel will
' place each sucessive number from the result array in order into the selected cells.
'
' return
'    this function returns an array of vales containing a sequence of numbers
'
' param
'    pStart is an optional parameter that specifies the starting number of result sequence.
' param
'    pIncrement is an optional parameter that specifies the how much each number in the results sequence is
'    incrementd from the number immediately proceeding it.
' -----------------------------------------------------------------------------------------------------------
Public Function numberSequence(Optional pStart As Long = 1, Optional pIncrement As Long = 1) As Variant
   '
   ' ==============================================================================================
   ' ----------------------------------------------------------------------------------------------
   Dim callerRows As Long
   Dim callerCols As Long
   Dim result()   As Long
   Dim num        As Long
   Dim rowIdx     As Long
   Dim colIdx     As Long
   gMsg(1) = ""
   '
   ' ==============================================================================================
   ' the numbers in the resulting array begin with pStart
   ' ----------------------------------------------------------------------------------------------
   num = pStart
   '
   ' ==============================================================================================
   ' figure out the dimensions of the selected cells that the call to this function was entered
   ' into
   ' ----------------------------------------------------------------------------------------------
   With Application.Caller
      callerRows = .Rows.count
      callerCols = .Columns.count
   End With
   '
   ' ==============================================================================================
   ' change the dimension of the result array to match
   ' ----------------------------------------------------------------------------------------------
   ReDim result(1 To callerRows, 1 To callerCols)
   '
   ' ==============================================================================================
   ' build the results array in row major order
   ' ----------------------------------------------------------------------------------------------
   For rowIdx = 1 To callerRows
      For colIdx = 1 To callerCols
         result(rowIdx, colIdx) = num
         num = num + pIncrement
      Next colIdx
   Next rowIdx
   '
   ' ==============================================================================================
   ' and return the result array into the selected cells
   ' ----------------------------------------------------------------------------------------------
   numberSequence = result
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine sequentially numbers all the cells in the selected Range
' -----------------------------------------------------------------------------------------------------------
Public Sub sequentiallyNumberSelection()
   Dim arrData()       As Variant
   Dim arrReturnData() As Variant
   Dim rng             As Range
   Dim lRows           As Long
   Dim lCols           As Long
   Dim i               As Long
   Dim j               As Long
   Dim number          As Long

   lRows = Selection.Rows.count
   lCols = Selection.Columns.count

   ReDim arrData(1 To lRows, 1 To lCols)
   ReDim arrReturnData(1 To lRows, 1 To lCols)

   Set rng = Selection
   number = 1
   For j = 1 To lCols
      For i = 1 To lRows
         arrReturnData(i, j) = number
         number = number + 1
      Next i
   Next j
   rng.value = arrReturnData

   Set rng = Nothing
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine sequentially numbers all the cells in the specified Range
'
' param
'    pRange is the Range of cells to number
' -----------------------------------------------------------------------------------------------------------
Public Sub sequentiallyNumberRange(pRange As Range)
   pRange.Select
   Call sequentiallyNumberSelection
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function tests for the presence of a value in a range.
'
' return
'    this function returns true if it finds the value anywhere in the Range otherwise, it returns false
'
' param
'    pValue is the value to search for in the Range
' param
'    pRange is the Range to check for the value
' -----------------------------------------------------------------------------------------------------------
Public Function isInStringList(pValue As Variant, pRange As Range) As Boolean
   Dim result As Boolean
   Dim value  As String
   Dim rowIdx As Long
   Dim colIdx As Long
   gMsg(1) = ""
   result = False
   value = pValue
   For rowIdx = 1 To pRange.Rows.count
      For colIdx = 1 To pRange.Columns.count
         If (pRange(rowIdx, colIdx) = value) Then
            result = True
            Exit For
         End If
      Next colIdx
   Next rowIdx
   isInStringList = result
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function concatenates a string representation of all the cells in a Range that contain any value.  It
' separates the individual cell values with a delimiter string and returns the resulting string
'
' return
'    this function returns a string conttaining the string representation of the values in all the cells in
'    a Range separated by a delimiter string.
'
' param
'    pRange is the Range of cells to concatenate
' param
'    pDelimiter is the string to use to separate the concatenated values from each other in the resulting
'    string.
' -----------------------------------------------------------------------------------------------------------
Public Function concatenateCellsInRange(pRange As Range, pDelimiter As String) As String
   gMsg(1) = ""
   '
   ' ==============================================================================================
   ' default result
   ' ----------------------------------------------------------------------------------------------
   concatenateCellsInRange = ""
   '
   ' ==============================================================================================
   ' change the dimension of the result array to match
   ' ----------------------------------------------------------------------------------------------
   Dim rowIdx      As Long
   Dim colIdx      As Long
   Dim isFirstCell As Boolean
   isFirstCell = True
   For rowIdx = 1 To pRange.Rows.count
      For colIdx = 1 To pRange.Columns.count
         If (Not IsEmpty(pRange(rowIdx, colIdx))) Then
            If (isFirstCell) Then
               concatenateCellsInRange = pRange(rowIdx, colIdx)
               isFirstCell = False
            Else
               concatenateCellsInRange = concatenateCellsInRange & pDelimiter & pRange(rowIdx, colIdx)
            End If
         End If
      Next colIdx
   Next rowIdx
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function seraches each row of a column (pLookupColumn) within a table (pRange) for a value
' (pLookupValue). If the column's row contains the value, then the string in the same row but in another
' column (pDataColumn) is conctenated onto the end of a result string that this function returns.
'
' return
'    this function returns a string containing the concatenation of all the strings selected from
'    pDataColumn, each separated by pDelimiter.
'
' param
'    pRange is the table containing the lookup and data columns.
' param
'    pLookupColumn is the column (relative to the start of the table) to look in for pLookupValues
' param
'    pLookupvalue is the value to look for in the column pLookupColumn
' param
'    pDataColumn is the column (relative to the start of the table) containing strings that are concatenated
'    together.
' param
'    pDelimiter is the string to use to separate the concatenated values from each other in the resulting
'    string.
' -----------------------------------------------------------------------------------------------------------
Public Function concatenateLookedupCellsInRange(pRange As Range, pLookupColumn As Long, pLookupValue As Variant, pDataColumn As Long, pDelimiter As String) As String
   gMsg(1) = ""
   '
   ' ==============================================================================================
   ' default result
   ' ----------------------------------------------------------------------------------------------
   concatenateLookedupCellsInRange = ""
   '
   ' ==============================================================================================
   ' change the dimension of the result array to match
   ' ----------------------------------------------------------------------------------------------
   Dim rowIdx      As Long
   Dim colIdx      As Long
   Dim isFirstCell As Boolean
   isFirstCell = True
   For rowIdx = 1 To pRange.Rows.count
      If (pRange(rowIdx, pLookupColumn) = pLookupValue) Then
         If (Not IsEmpty(pRange(rowIdx, pDataColumn))) Then
            If (isFirstCell) Then
               concatenateLookedupCellsInRange = pRange(rowIdx, pDataColumn)
               isFirstCell = False
            Else
               concatenateLookedupCellsInRange = concatenateLookedupCellsInRange & pDelimiter & pRange(rowIdx, pDataColumn)
            End If
         End If
      End If
   Next rowIdx
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function concatenates all the values in a column (pDataColumn) within a table (pRange).
'
' return
'    this function returns a string containing the concatenation of all the strings selected from
'    pDataColumn, each separated by pDelimiter.
'
' param
'    pRange is the table containing the lookup and data columns.
' param
'    pDataColumn is the column (relative to the start of the table) containing strings that are concatenated
'    together.
' param
'    pDelimiter is the string to use to separate the concatenated values from each other in the resulting
'    string.
' param (optional)
'    pSkipFirstRow tells this procedure whether or not to skip the first row of data in pDataColumn. The
'    intent is to be able to have this procedure skip a title row if one is present. If no argument value is
'    passed to this procedure through this optional parameter, its value defaults to True, meaning it will
'    skip the first row.
' -----------------------------------------------------------------------------------------------------------
Public Function concatenateAllCellsInRange(pRange As Range, pDataColumn As Long, pDelimiter As String, Optional ByVal pSkipFirstRow As Boolean = True) As String
   gMsg(1) = ""
   '
   ' ==============================================================================================
   ' default result
   ' ----------------------------------------------------------------------------------------------
   concatenateAllCellsInRange = ""
   '
   ' ==============================================================================================
   ' change the dimension of the result array to match
   ' ----------------------------------------------------------------------------------------------
   Dim rowIdx      As Long
   Dim colIdx      As Long
   Dim rowStart    As Long
   Dim isFirstCell As Boolean
   rowStart = 1
   If (pSkipFirstRow) Then rowStart = 2
   isFirstCell = True
   For rowIdx = rowStart To pRange.Rows.count
      If (Not IsEmpty(pRange(rowIdx, pDataColumn))) Then
         If (isFirstCell) Then
            concatenateAllCellsInRange = pRange(rowIdx, pDataColumn)
            isFirstCell = False
         Else
            concatenateAllCellsInRange = concatenateAllCellsInRange & pDelimiter & pRange(rowIdx, pDataColumn)
         End If
      End If
   Next rowIdx
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns a subRange of the specified range.  The subRange consists of all the cells from the
' first cell in the Range to the last cell in the Range that contains any data.
'
' return
'    this function returns subRange containing all the data in the range.
'
' param
'    pRange is the Range to return the subRange of.
' -----------------------------------------------------------------------------------------------------------
Public Function rangeWithData(pRange As Range) As Range
   Dim x As Range
   Dim y As Range
   Dim z As Range
   Set x = getLastCell(pRange, xlByRows)
   Set y = pRange.Cells(1, 1)
   Set z = Range(y, x)
   Set rangeWithData = z
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function scans a Range backwards looking for the last cell containing a value.  It returns that cell
' as a range.
'
' return
'    this function returns athe last cell in the Range that contains any data.
'
' param
'    pRange is the Range to find the end of
' -----------------------------------------------------------------------------------------------------------
Public Function lastCellWithData(pRange As Range) As Range
   lastCellWithData = pRange.End(xlUp)
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function scans a single row backwards looking for the highest column number containing a value.  It
' returns that value as an Integer.
'
' return
'    this function returns an Integer interpretation of the value from the highest column number in the
'    Range that has a value of any kind
'
' param
'    pRowRange is the Range to check for the value
' -----------------------------------------------------------------------------------------------------------
Public Function lastValueInRow(pRowRange As Range) As Integer
   gMsg(1) = ""
   '
   ' ==============================================================================================
   ' default result
   ' ----------------------------------------------------------------------------------------------
   lastValueInRow = 0
   '
   ' ==============================================================================================
   ' change the dimension of the result array to match
   ' ----------------------------------------------------------------------------------------------
   Dim colIdx As Integer
   For colIdx = pRowRange.Columns.count To 1 Step -1
      lastValueInRow = pRowRange(1, colIdx)
      If (lastValueInRow <> 0) Then
         Exit For
      End If
   Next colIdx
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function scans a single row backwards looking for the highest column number containing a value.  It
' interprets that value as an Integer number of minutes.  It converts the value to days, hours, and minutes,
' adds the result to the data/time value passed in through pOldTime and returns the new date/time value.
'
' return
'    this function returns a date/time value
'
' param
'    pOldTime is the date/time to be incremented
' param
'    pRowRange is the Range to check for the value
' -----------------------------------------------------------------------------------------------------------
Public Function incrementDateTimeFromLastTimeInRow(pOldTime As Double, pRowRange As Range) As Double
   gMsg(1) = ""
   '
   ' ==============================================================================================
   ' default result
   ' ----------------------------------------------------------------------------------------------
   incrementDateTimeFromLastTimeInRow = pOldTime
   '
   ' ==============================================================================================
   ' find the last number of minutes in the range, convert it to days and time and add it to
   ' pOldTime
   ' ----------------------------------------------------------------------------------------------
   Const minutesInOneDay As Integer = 1440
   Dim days              As Integer
   Dim minutes           As Integer
   days = 0
   minutes = lastValueInRow(pRowRange)
   If (minutes > minutesInOneDay) Then
      days = minutes / minutesInOneDay
      minutes = minutes Mod minutesInOneDay
   End If
   incrementDateTimeFromLastTimeInRow = incrementDateTimeFromLastTimeInRow + days + TimeSerial(0, minutes, 0)
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function counts the number of instances of a string of chars within a Range
'
' return
'    this function returns the number of instances of the string of chars within the Range
'
' param
'    pRange is the Range to search
' param
'    pString is the string to search for anc count
' -----------------------------------------------------------------------------------------------------------
Public Function countStrsInRange(pRange As Range, pString As String) As Long
   Dim cell As Range
   Dim x    As Integer
   gMsg(1) = ""

   For Each cell In pRange
      For x = 1 To Len(cell.value)
         If Mid(cell.value, x, Len(pString)) = pString Then
            countStrsInRange = countStrsInRange + 1
         End If
      Next x
   Next cell
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine
' -----------------------------------------------------------------------------------------------------------
Public Sub displayInteriorColorValues()
   Selection.value = brightness(Selection.Interior.color)
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine hides the text in a Range of cells by setting the font color of each cell in the Range the
' same as its fill color
' -----------------------------------------------------------------------------------------------------------
Public Sub hideFont(pRange As Range)
   Dim rowIdx As Long
   Dim colIdx As Long
   gMsg(1) = ""

   For rowIdx = 1 To pRange.Rows.count
      For colIdx = 1 To pRange.Columns.count
         pRange(rowIdx, colIdx).Font.color = pRange(rowIdx, colIdx).Interior.color
      Next colIdx
   Next rowIdx
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine sorts an array of variants in place using the Quicksort algorithm. The array that is
' passed in through the pAray parameter is sorted and when This subroutine returns, the array passed in by
' the calling routine is sorted.
'
' param
'    pArray is the array that is sorted
' param
'    pLowerBound is the lower bound index of the pArray
' param
'    pUpperBound is the upper bound index of the pArray
' -----------------------------------------------------------------------------------------------------------
Public Sub quickSort(pArray As Variant, pLowBound As Long, pUpprBound As Long)
   Dim pivot   As Variant
   Dim tmpSwap As Variant
   Dim tmpLow  As Long
   Dim tmpHi   As Long
   gMsg(1) = ""

   tmpLow = pLowBound
   tmpHi = pUpprBound

   pivot = pArray((pLowBound + pUpprBound) \ 2)  ' uses Integer Division

   While (tmpLow <= tmpHi)

      While (pArray(tmpLow) < pivot And tmpLow < pUpprBound)
         tmpLow = tmpLow + 1
      Wend

      While (pivot < pArray(tmpHi) And tmpHi > pLowBound)
         tmpHi = tmpHi - 1
      Wend

      If (tmpLow <= tmpHi) Then
         tmpSwap = pArray(tmpLow)
         pArray(tmpLow) = pArray(tmpHi)
         pArray(tmpHi) = tmpSwap
         tmpLow = tmpLow + 1
         tmpHi = tmpHi - 1
      End If

   Wend

   If (pLowBound < tmpHi) Then quickSort pArray, pLowBound, tmpHi
   If (tmpLow < pUpprBound) Then quickSort pArray, tmpLow, pUpprBound

End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns an array of values containing the unique values it finds in an input list.
'
' return
'    this function returns an array of values containing the unique values it finds in an input list.
'
' param
'    pList is the input list from which this function returns the unique values
' -----------------------------------------------------------------------------------------------------------
Public Function uniqueValuesList(pList As Range) As Variant
   Dim listSize   As Long
   Dim result()   As Variant
   Dim temp()     As Variant
   Dim idx        As Long
   Dim rowIdx     As Long
   Dim colIdx     As Long
   Dim value      As Variant
   Dim duplicate  As Boolean
   Dim i          As Long
   Dim numUnique  As Long
   gMsg(1) = ""
   '
   ' ==============================================================================================
   ' figure out how many cells are in the input list Range and dimension the result array to match
   ' ----------------------------------------------------------------------------------------------
   listSize = pList.Rows.count * pList.Columns.count
   ReDim temp(1 To listSize)
   '
   ' ==============================================================================================
   ' go through the input list and find all the unique values. put those unique values into the
   ' temp array. the temp is a linear array of the unique values in the input list.
   ' ----------------------------------------------------------------------------------------------
   numUnique = 0
   idx = 1
   For rowIdx = 1 To pList.Rows.count
      For colIdx = 1 To pList.Columns.count
         '
         ' ==============================================================================================
         ' get the next value from the input list
         ' ----------------------------------------------------------------------------------------------
         value = pList(rowIdx, colIdx)
         '
         ' ==============================================================================================
         ' scan the values already in the temp array to see if this new value is a duplicate
         ' ----------------------------------------------------------------------------------------------
         duplicate = False
         For i = 1 To idx
            If (temp(i) = value) Then duplicate = True
            If (duplicate) Then Exit For
         Next i
         '
         ' ==============================================================================================
         ' if its not a duplicate, add it to the temp array and keep track of how many unique values are
         ' there.
         ' ----------------------------------------------------------------------------------------------
         If (Not duplicate) Then
            temp(idx) = value
            numUnique = idx
            idx = idx + 1
         End If
      Next colIdx
   Next rowIdx
   '
   ' ==============================================================================================
   ' create a properly sized array and copy the unique values into it
   ' ----------------------------------------------------------------------------------------------
   ReDim result(1 To numUnique)
   For rowIdx = 1 To numUnique
      result(rowIdx) = temp(rowIdx)
   Next rowIdx
   '
   ' ==============================================================================================
   ' and return the properly dimensioned unique values array
   ' ----------------------------------------------------------------------------------------------
   uniqueValuesList = result
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns an array of values containing the unique values it finds in an input list.
'
' return
'    this function returns an array of values containing the unique values it finds in an input list.
'
' param
'    pList is the input list from which this function returns the unique values
' param
'    pIgnoreList is a list of values to ignore and not put into the list that is returned
' -----------------------------------------------------------------------------------------------------------
Public Function uniqueValuesListIgnoreRange(pList As Range, pIgnoreList As Range) As Variant
   Dim listSize   As Long
   Dim result()   As Variant
   Dim temp()     As Variant
   Dim idx        As Long
   Dim rowIdx     As Long
   Dim colIdx     As Long
   Dim value      As Variant
   Dim duplicate  As Boolean
   Dim i          As Long
   Dim numUnique  As Long
   gMsg(1) = ""
   '
   ' ==============================================================================================
   ' figure out how many cells are in the input list Range and dimension the result array to match
   ' ----------------------------------------------------------------------------------------------
   listSize = pList.Rows.count * pList.Columns.count
   ReDim temp(1 To listSize)
   '
   ' ==============================================================================================
   ' go through the input list and find all the unique values. put those unique values into the
   ' temp array. the temp is a linear array of the unique values in the input list.
   ' ----------------------------------------------------------------------------------------------
   numUnique = 0
   idx = 1
   For rowIdx = 1 To pList.Rows.count
      For colIdx = 1 To pList.Columns.count
         '
         ' ==============================================================================================
         ' get the next value from the input list
         ' ----------------------------------------------------------------------------------------------
         value = pList(rowIdx, colIdx)
         '
         ' ==============================================================================================
         ' scan the values already in the temp array to see if this new value is a duplicate
         ' ----------------------------------------------------------------------------------------------
         duplicate = False
         For i = 1 To idx
            If (temp(i) = value) Then duplicate = True
            If (duplicate) Then Exit For
         Next i
         '
         ' ==============================================================================================
         ' if its not a duplicate, add it to the temp array and keep track of how many unique values are
         ' there.
         ' ----------------------------------------------------------------------------------------------
         If ((Not duplicate) And (Not isInStringList(value, pIgnoreList))) Then
            temp(idx) = value
            numUnique = idx
            idx = idx + 1
         End If
      Next colIdx
   Next rowIdx
   '
   ' ==============================================================================================
   ' create a properly sized array and copy the unique values into it
   ' ----------------------------------------------------------------------------------------------
   ReDim result(1 To numUnique)
   For rowIdx = 1 To numUnique
      result(rowIdx) = temp(rowIdx)
   Next rowIdx
   '
   ' ==============================================================================================
   ' and return the properly dimensioned unique values array
   ' ----------------------------------------------------------------------------------------------
   uniqueValuesListIgnoreRange = result
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns an array of values containing the unique values it finds in an input list in sorted
' order.
'
' return
'    this function returns an array of values containing the unique values it finds in an input list.
'
' param
'    pList is the input list from which this function returns the unique values
' -----------------------------------------------------------------------------------------------------------
Public Function uniqueValuesListSorted(pList As Range) As Variant
   Dim result As Variant
   gMsg(1) = ""
   '
   ' ==============================================================================================
   ' get the list
   ' ----------------------------------------------------------------------------------------------
   result = uniqueValuesList(pList)
   '
   ' ==============================================================================================
   ' sort it
   ' ----------------------------------------------------------------------------------------------
   Call quickSort(result, LBound(result), UBound(result))
   '
   ' ==============================================================================================
   ' return it
   ' ----------------------------------------------------------------------------------------------
   uniqueValuesListSorted = result
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns an array of values containing the unique values it finds in an input list in sorted
' order.
'
' return
'    this function returns an array of values containing the unique values it finds in an input list.
'
' param
'    pList is the input list from which this function returns the unique values
' param
'    pIgnoreList is a list of values to ignore and not put into the list that is returned
' -----------------------------------------------------------------------------------------------------------
Public Function uniqueValuesListIgnoreRangeSorted(pList As Range, pIgnoreList As Range) As Variant
   Dim result As Variant
   gMsg(1) = ""
   '
   ' ==============================================================================================
   ' get the list
   ' ----------------------------------------------------------------------------------------------
   result = uniqueValuesListIgnoreRange(pList, pIgnoreList)
   '
   ' ==============================================================================================
   ' sort it
   ' ----------------------------------------------------------------------------------------------
   Call quickSort(result, LBound(result), UBound(result))
   '
   ' ==============================================================================================
   ' return it
   ' ----------------------------------------------------------------------------------------------
   uniqueValuesListIgnoreRangeSorted = result
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns an array of values containing the unique values it finds in an input list.
'
' This function differs from uniqueValuesList() in that this one CAN be entered as an array function.
'                                        ----
'
' To use an array function like this you must enter it as an "array formula" in excel. To enter an array
' formula, select a Range of cells. Then type =uniqueValues(). Then press CTRL+SHIFT+ENTER. Excel will
' display the formula in the formula bar inclosed in curly braces like this {=uniqueValues()}. Excel will
' place each sucessive number from the result array in order into the selected cells.
'
' return
'    this function returns an array of values containing the unique values it finds in an input list.
'
' param
'    pList is the input list from which this function returns the unique values
' -----------------------------------------------------------------------------------------------------------
Public Function uniqueValues(pList As Range) As Variant
   Dim callerRows As Long
   Dim callerCols As Long
   Dim result()   As Variant
   Dim temp()     As Variant
   Dim rowIdx     As Long
   Dim colIdx     As Long
   Dim idx        As Long
   gMsg(1) = ""
   '
   ' ==============================================================================================
   ' figure out the dimensions of the selected cells that the call to this function was entered
   ' into
   ' ----------------------------------------------------------------------------------------------
   With Application.Caller
      callerRows = .Rows.count
      callerCols = .Columns.count
   End With
   '
   ' ==============================================================================================
   ' change the dimension of the result array to match
   ' ----------------------------------------------------------------------------------------------
   ReDim result(1 To callerRows, 1 To callerCols)
   '
   ' ==============================================================================================
   ' ----------------------------------------------------------------------------------------------
   temp = uniqueValuesList(pList)
   '
   ' ==============================================================================================
   ' build the results array in row major order by copying the elements of the temp array to the
   ' results array. if there are more elements in the results array than there are unique values
   ' in the temp array, fill the excess results array elements with the empty string. if there are
   ' fewer elements in the results array, then cut the results short by not returning all the
   ' unique values in the temp array.
   ' ----------------------------------------------------------------------------------------------
   idx = 1
   For rowIdx = 1 To callerRows
      For colIdx = 1 To callerCols
         If (idx > UBound(temp)) Then
            result(rowIdx, colIdx) = ""
         Else
            result(rowIdx, colIdx) = temp(idx)
         End If
         idx = idx + 1
      Next colIdx
   Next rowIdx
   '
   ' ==============================================================================================
   ' and return the result array into the selected cells
   ' ----------------------------------------------------------------------------------------------
   uniqueValues = result
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns an array of values containing the unique values it finds in an input list. It does
' not include in its results any values that are in the ignore list.
'
' This function differs from uniqueValuesListIgnoreRange() in that this one CAN be entered as an array
' function.                              ----
'
' To use an array function like this you must enter it as an "array formula" in excel. To enter an array
' formula, select a Range of cells. Then type =numberSequence(). Then press CTRL+SHIFT+ENTER. Excel will
' display the formula in the formula bar inclosed in curly braces like this {=numberSequence()}. Excel will
' place each sucessive number from the result array in order into the selected cells.
'
' return
'    this function returns an array of values containing all the unique values it finds in an input list
'    that it does not also find in the ignorelist.
'
' param
'    pList is the input list from which this function returns the unique values
' param
'    pIgnoreList is a list of values that will be excluded from the array of values that this function
'    returns.
' -----------------------------------------------------------------------------------------------------------
Public Function uniqueValuesIgnoreRange(pList As Range, pIgnoreList As Range) As Variant
   Dim callerRows As Long
   Dim callerCols As Long
   Dim result()   As Variant
   Dim temp()     As Variant
   Dim rowIdx     As Long
   Dim colIdx     As Long
   Dim idx        As Long
   gMsg(1) = ""
   '
   ' ==============================================================================================
   ' figure out the dimensions of the selected cells that the call to this function was entered
   ' into
   ' ----------------------------------------------------------------------------------------------
   With Application.Caller
      callerRows = .Rows.count
      callerCols = .Columns.count
   End With
   '
   ' ==============================================================================================
   ' change the dimension of the result array to match
   ' ----------------------------------------------------------------------------------------------
   ReDim result(1 To callerRows, 1 To callerCols)
   '
   ' ==============================================================================================
   ' ----------------------------------------------------------------------------------------------
   temp = uniqueValuesListIgnoreRange(pList, pIgnoreList)
   '
   ' ==============================================================================================
   ' build the results array in row major order by copying the elements of the temp array to the
   ' results array. if there are more elements in the results array than there are unique values
   ' in the temp array, fill the excess results array elements with the empty string. if there are
   ' fewer elements in the results array, then cut the results short by not returning all the
   ' unique values in the temp array.
   ' ----------------------------------------------------------------------------------------------
   idx = 1
   For rowIdx = 1 To callerRows
      For colIdx = 1 To callerCols
         If (idx > UBound(temp)) Then
            result(rowIdx, colIdx) = ""
         Else
            result(rowIdx, colIdx) = temp(idx)
         End If
         idx = idx + 1
      Next colIdx
   Next rowIdx
   '
   ' ==============================================================================================
   ' and return the result array into the selected cells
   ' ----------------------------------------------------------------------------------------------
   uniqueValuesIgnoreRange = result
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns an array of values containing the unique values it finds in an input list.
'
'
' This function differs from uniqueValuesListSorted() in that this one CAN be entered as an array function.
'                                        ----
' To use an array function like this you must enter it as an "array formula" in excel. To enter an array
' formula, select a Range of cells. Then type =uniqueValues(). Then press CTRL+SHIFT+ENTER. Excel will
' display the formula in the formula bar inclosed in curly braces like this {=uniqueValues()}. Excel will
' place each sucessive number from the result array in order into the selected cells.
'
' return
'    this function returns an array of values containing the unique values it finds in an input list.
'
' param
'    pList is the input list from which this function returns the unique values
' -----------------------------------------------------------------------------------------------------------
Public Function uniqueValuesSorted(pList As Range) As Variant
   Dim callerRows As Long
   Dim callerCols As Long
   Dim result()   As Variant
   Dim temp()     As Variant
   Dim rowIdx     As Long
   Dim colIdx     As Long
   Dim idx        As Long
   gMsg(1) = ""
   '
   ' ==============================================================================================
   ' figure out the dimensions of the selected cells that the call to this function was entered
   ' into
   ' ----------------------------------------------------------------------------------------------
   With Application.Caller
      callerRows = .Rows.count
      callerCols = .Columns.count
   End With
   '
   ' ==============================================================================================
   ' change the dimension of the result array to match
   ' ----------------------------------------------------------------------------------------------
   ReDim result(1 To callerRows, 1 To callerCols)
   '
   ' ==============================================================================================
   ' ----------------------------------------------------------------------------------------------
   temp = uniqueValuesListSorted(pList)
   '
   ' ==============================================================================================
   ' build the results array in row major order by copying the elements of the temp array to the
   ' results array. if there are more elements in the results array than there are unique values
   ' in the temp array, fill the excess results array elements with the empty string. if there are
   ' fewer elements in the results array, then cut the results short by not returning all the
   ' unique values in the temp array.
   ' ----------------------------------------------------------------------------------------------
   idx = 1
   For rowIdx = 1 To callerRows
      For colIdx = 1 To callerCols
         If (idx > UBound(temp)) Then
            result(rowIdx, colIdx) = ""
         Else
            result(rowIdx, colIdx) = temp(idx)
         End If
         idx = idx + 1
      Next colIdx
   Next rowIdx
   '
   ' ==============================================================================================
   ' and return the result array into the selected cells
   ' ----------------------------------------------------------------------------------------------
   uniqueValuesSorted = result
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns an array of values containing the unique values it finds in an input list. It does
' not include in its results any values that are in the ignore list.
'
' This function differs from uniqueValuesListIgnoreRangeSorted() in that this one CAN be entered as an array
' function.                              ----
'
' To use an array function like this you must enter it as an "array formula" in excel. To enter an array
' formula, select a Range of cells. Then type =numberSequence(). Then press CTRL+SHIFT+ENTER. Excel will
' display the formula in the formula bar inclosed in curly braces like this {=numberSequence()}. Excel will
' place each sucessive number from the result array in order into the selected cells.
'
' return
'    this function returns an array of values containing all the unique values it finds in an input list
'    that it does not also find in the ignorelist.
'
' param
'    pList is the input list from which this function returns the unique values
' param
'    pIgnoreList is a list of values that will be excluded from the array of values that this function
'    returns.
' -----------------------------------------------------------------------------------------------------------
Public Function uniqueValuesIgnoreRangeSorted(pList As Range, pIgnoreList As Range) As Variant
   Dim callerRows As Long
   Dim callerCols As Long
   Dim result()   As Variant
   Dim temp()     As Variant
   Dim rowIdx     As Long
   Dim colIdx     As Long
   Dim idx        As Long
   gMsg(1) = ""
   '
   ' ==============================================================================================
   ' figure out the dimensions of the selected cells that the call to this function was entered
   ' into
   ' ----------------------------------------------------------------------------------------------
   With Application.Caller
      callerRows = .Rows.count
      callerCols = .Columns.count
   End With
   '
   ' ==============================================================================================
   ' change the dimension of the result array to match
   ' ----------------------------------------------------------------------------------------------
   ReDim result(1 To callerRows, 1 To callerCols)
   '
   ' ==============================================================================================
   ' ----------------------------------------------------------------------------------------------
   temp = uniqueValuesListIgnoreRangeSorted(pList, pIgnoreList)
   '
   ' ==============================================================================================
   ' build the results array in row major order by copying the elements of the temp array to the
   ' results array. if there are more elements in the results array than there are unique values
   ' in the temp array, fill the excess results array elements with the empty string. if there are
   ' fewer elements in the results array, then cut the results short by not returning all the
   ' unique values in the temp array.
   ' ----------------------------------------------------------------------------------------------
   idx = 1
   For rowIdx = 1 To callerRows
      For colIdx = 1 To callerCols
         If (idx > UBound(temp)) Then
            result(rowIdx, colIdx) = ""
         Else
            result(rowIdx, colIdx) = temp(idx)
         End If
         idx = idx + 1
      Next colIdx
   Next rowIdx
   '
   ' ==============================================================================================
   ' and return the result array into the selected cells
   ' ----------------------------------------------------------------------------------------------
   uniqueValuesIgnoreRangeSorted = result
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine tests whether or not the named sheet exists in the current workbook.
'
' return
'    true if the named sheet exists, false otherwise
'
' param
'    pWorksheetName is the name of the sheet to search for.
' -----------------------------------------------------------------------------------------------------------
Public Function worksheetExists(ByVal pWorksheetName As String) As Boolean
   gMsg(1) = ""
   On Error Resume Next
   worksheetExists = (Sheets(pWorksheetName).Name <> "")
   On Error GoTo 0
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine creates a worksheet named "SheetList" and lists the names of all the sheets in the current
' workbook on it.
' -----------------------------------------------------------------------------------------------------------
Public Sub listWorkSheetNames()
   '
   ' ==============================================================================================
   ' remember how many sheets are already in the workbook before adding our new one at the end.
   ' ----------------------------------------------------------------------------------------------
   Dim sheetCount   As Long
   sheetCount = Sheets.count
   '
   ' ==============================================================================================
   ' create a clean new sheet for this
   ' ----------------------------------------------------------------------------------------------
   Dim sht As Worksheet
   Set sht = newSheet("SheetList")
   '
   ' ==============================================================================================
   ' make damn sure we're not on some other sheet except our new one before we start writting all
   ' over it
   ' ----------------------------------------------------------------------------------------------
   sht.Activate
   sht.Select
   Range("A1").Select
   If (Not startsWith(ActiveSheet.Name, "SheetList")) Then
      raiseException "Incorrect ActiveSheet", "ListWorkSheetNames()", "M009_ExcelUtils"
   End If
   '
   ' ==============================================================================================
   ' List all the sheets except our new one
   ' ----------------------------------------------------------------------------------------------
   Dim idx As Long
   For idx = 1 To sheetCount
      Range("B" & idx + 1) = Sheets(idx).Name
   Next idx
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine replaces all instances of pOldString with pNewString on all sheets in the current Excel
' workbook. It is not case sensitive.
'
' param
'    pOldString is the string to replace
' param
'    pNewString is the string to replace all instances of pOldString with.
' -----------------------------------------------------------------------------------------------------------
Public Sub workbookSubstringReplace(pOldString As String, pNewString As String)
   Dim wks   As Worksheet
   Dim hLink As Hyperlink
   gMsg(1) = ""
   For Each wks In ActiveWorkbook.Worksheets
      '
      ' ==============================================================================================
      ' replace all occurrences of pOldString in the displayed text in the file
      ' ----------------------------------------------------------------------------------------------
      wks.Cells.Replace what:=pOldString, Replacement:=pNewString, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
      '
      ' ==============================================================================================
      ' replace all occurrences of pOldString in the hyperlinks in the file
      ' ----------------------------------------------------------------------------------------------
      For Each hLink In wks.Hyperlinks
         hLink.Address = Replace(hLink.Address, pOldString, pNewString)
      Next hLink
   Next wks
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine replaces all instances of each pOldString with its corresponding pNewString on all sheets
' in the specified Excel workbook. It is not case sensitive.
'
' param
'    pFilePath is the pathname of the Excel file to change
' param
'    pOldStrings is an array of the strings to replace
' param
'    pNewStrings is an array of the strings to replace all instances of the corresponding pOldStrings with.
' -----------------------------------------------------------------------------------------------------------
Public Sub excelFileSubstringsReplace(pFilePath As String, pOldStrings() As String, pNewStrings() As String)
   gMsg(1) = ""
   If fileExists(pFilePath) Then
      '
      ' ==============================================================================================
      ' remember where we are
      ' ----------------------------------------------------------------------------------------------
      Dim currentWorkbookName As String
      Dim currentSheetName    As String
      currentWorkbookName = ActiveWorkbook.Name
      currentSheetName = ActiveWorkbook.ActiveSheet.Name
      '
      ' ==============================================================================================
      ' open the file, activate it, and remember its active sheet
      ' ----------------------------------------------------------------------------------------------
      Dim workbookName As String
      Dim sheetName    As String
      Workbooks.Open fileName:=pFilePath
      workbookName = workbookNameFromFileName(pFilePath)
      Workbooks(workbookName).Activate
      sheetName = ActiveWorkbook.ActiveSheet.Name
      '
      ' ==============================================================================================
      ' do the string replacements
      ' ----------------------------------------------------------------------------------------------
      Dim j As Long
      For j = LBound(pOldStrings) To UBound(pOldStrings)
         Call workbookSubstringReplace(pOldStrings(j), pNewStrings(j))
      Next j
      '
      ' ==============================================================================================
      ' set the changed file back to its original active sheet, supress compatibility checking, save
      ' it, and close it.
      ' ----------------------------------------------------------------------------------------------
      ActiveWorkbook.Sheets(sheetName).Activate
      ActiveWorkbook.CheckCompatibility = False
      ActiveWorkbook.Save
      ActiveWorkbook.Close
      '
      ' ==============================================================================================
      ' now go back to where we were
      ' ----------------------------------------------------------------------------------------------
      Workbooks(currentWorkbookName).Activate
      ActiveWorkbook.Sheets(currentSheetName).Activate
   End If
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function searches the entire current workbook for any cell containing the specified substring. It
' is not case sensitive.
'
' return
'    This function returns True if a cell is found in the workbook that contains the given substring,
'    otherwise, it returns False
'
' param
'    pStringToFind is the string to search for
' -----------------------------------------------------------------------------------------------------------
Public Function workbookContainsSubstring(pStringToFind As String) As Boolean
   Dim rngFound As Range
   Dim wks      As Worksheet
   Dim hLink As Hyperlink
   gMsg(1) = ""
   workbookContainsSubstring = False
   Set rngFound = Nothing
   For Each wks In ActiveWorkbook.Worksheets
      '
      ' ==============================================================================================
      ' search the cell values
      ' ----------------------------------------------------------------------------------------------
      Set rngFound = wks.Cells.Find(what:=pStringToFind, LookIn:=xlFormulas, LookAt:=xlPart, MatchCase:=False)
      workbookContainsSubstring = Not (rngFound Is Nothing)
      If workbookContainsSubstring Then
         Exit Function
      End If
      '
      ' ==============================================================================================
      ' search the hyperlinks
      ' ----------------------------------------------------------------------------------------------
      For Each hLink In wks.Hyperlinks
         workbookContainsSubstring = containsSubstring(hLink.Address, pStringToFind)
         If workbookContainsSubstring Then
            Exit Function
         End If
      Next hLink
   Next wks
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function searches the specified Excel file for any cell containing any of the specified substrings.
' It is not case sensitive.
'
' return
'    This function returns True if a cell is found in the Excel file that contains any of the given
'    substrings, otherwise, it returns False
'
' param
'    pFilePath is the pathname of the Excel file to search
' param
'    pStringsToFind is an array of the strings to search for
' -----------------------------------------------------------------------------------------------------------
Public Function excelFileContainsSubstrings(pFilePath As String, pStringsToFind() As String) As Boolean
   Dim found As Boolean
   gMsg(1) = ""
   found = False
   If fileExists(pFilePath) Then
      '
      ' ==============================================================================================
      ' remember where we are
      ' ----------------------------------------------------------------------------------------------
      Dim currentWorkbookName As String
      Dim currentSheetName    As String
      currentWorkbookName = ActiveWorkbook.Name
      currentSheetName = ActiveWorkbook.ActiveSheet.Name
      '
      ' ==============================================================================================
      ' open the file, activate it, and remember its active sheet
      ' ----------------------------------------------------------------------------------------------
      Dim workbookName As String
      Dim sheetName    As String
      Workbooks.Open fileName:=pFilePath
      workbookName = workbookNameFromFileName(pFilePath)
      Workbooks(workbookName).Activate
      sheetName = ActiveWorkbook.ActiveSheet.Name
      '
      ' ==============================================================================================
      ' do the string replacements
      ' ----------------------------------------------------------------------------------------------
      Dim j As Long
      For j = LBound(pStringsToFind) To UBound(pStringsToFind)
         If workbookContainsSubstring(pStringsToFind(j)) Then
            found = True
            Exit For
         End If
      Next j
      '
      ' ==============================================================================================
      ' set the changed file back to its original active sheet and close it.
      ' ----------------------------------------------------------------------------------------------
      ActiveWorkbook.Sheets(sheetName).Activate
      ActiveWorkbook.Close
      '
      ' ==============================================================================================
      ' now go back to where we were
      ' ----------------------------------------------------------------------------------------------
      Workbooks(currentWorkbookName).Activate
      ActiveWorkbook.Sheets(currentSheetName).Activate
   End If
   excelFileContainsSubstrings = found
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns the first row it finds within a specified Range with pName as a header.  More
' precisely, it returns the row number of the first row found in Range pRange at or after pStartRow on row
' pStartRow that contains the value pName.
'
' return
'    This function returns the first row it finds within pRange with pName as a header or 0 if the header is
'    not found
'
' param
'    pName is the name of the row header to find
' param
'    pStartColumn is an optional parameter that tells this function which column to begin the search in.  If
'    pStartColumn is ommitted, it defaults to 1.
' param
'    pStartRow is an optional parameter that tells this function which row to look in for the column header.
'    If pStartRow is ommitted, it defaults to 1.
' -----------------------------------------------------------------------------------------------------------
Public Function rangeRelativeRowByName(pRange As Range, pName As String, Optional pStartColumn As Long = 1, Optional pStartRow As Long = 1) As Long
   Dim rowIdx As Long
   rangeRelativeRowByName = 0
   For rowIdx = pStartRow To pRange.Rows.count
      If ((Not IsEmpty(pRange.Cells(rowIdx, pStartColumn))) And equalsNoCase(pName, pRange.Cells(rowIdx, pStartColumn).value)) Then
         rangeRelativeRowByName = rowIdx
         Exit For
      End If
   Next rowIdx
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns the first column it finds within a specified Range with pName as a header.  More
' precisely, it returns the column number of the first column found in Range pRange at or after pStartColumn
' on row pStartRow that contains the value pName.
'
' return
'    This function returns the first column it finds within pRange with pName as a header or 0 if the header
'    is not found
'
' param
'    pName is the name of the column header to find
' param
'    pStartRow is an optional parameter that tells this function which row to look in for the column header.
'    If pStartRow is ommitted, it defaults to 1.
' param
'    pStartColumn is an optional parameter that tells this function which column to begin the search in.  If
'    pStartColumn is ommitted, it defaults to 1.
' -----------------------------------------------------------------------------------------------------------
Public Function rangeRelativeColumnByName(pRange As Range, pName As String, Optional pStartRow As Long = 1, Optional pStartColumn As Long = 1) As Long
   Dim colIdx As Long
   rangeRelativeColumnByName = 0
   For colIdx = pStartColumn To pRange.Columns.count
      If ((Not IsEmpty(pRange.Cells(pStartRow, colIdx))) And equalsNoCase(pName, pRange.Cells(pStartRow, colIdx).value)) Then
         rangeRelativeColumnByName = colIdx
         Exit For
      End If
   Next colIdx
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns a cell (Range object) given a Range object within which the cell resides and a row
' and column name.  The row and column names are expected to be String values in the first column and row
' respectively.
'   +---------+--------+--------+--------+
'   |         | "foo1" | "foo2" | "foo3" | <-- Column Names
'   +---------+--------+--------+--------+
'   | "tuna1" |   1    |   2    |   3    |
'   +---------+--------+--------+--------+
'   | "tuna2" |   4    |   5    |   6    | <-- The cell at index ["tuna2", "foo3"] contains a value of 6
'   +---------+--------+--------+--------+
'   | "tuna3" |   7    |   8    |   9    |
'   +---------+--------+--------+--------+
'        ^
'        |
'    Row Names
'
' return
'    This function returns a cell (Range object) given a Range object within which the cell resides and a row
'    and column name.  The row and column names are expected to be String values in the first column and row
'    respectively. If the requested row name or column name is not found in the range, this function returns
'    the value "Nothing".
'
' param
'    pRange is the Range in which the target cell resides.
' param
'    pRowName is the row name * within the Range * of the target cell.
' param
'    pColName is the column name * within the Range * of the target cell
' -----------------------------------------------------------------------------------------------------------
Public Function rangeRelativeCellByName(pRange As Range, pRowName As String, pColName As String) As Range
   '
   ' ==============================================================================================
   ' As a performance optimization that prevents the Range from having to be scanned every time
   ' this function is called, a circular array of the most recently requested names is maintained
   ' for both the row names and column names.
   ' ----------------------------------------------------------------------------------------------
   Const circularArraySize                   As Long = 2
   Const circularArrayLastIdx                As Long = circularArraySize - 1
   Static rowName(0 To circularArrayLastIdx) As String
   Static colName(0 To circularArrayLastIdx) As String
   Static rowNum(0 To circularArrayLastIdx)  As Long
   Static colNum(0 To circularArrayLastIdx)  As Long
   Static mostRecentRow                      As Long
   Static mostRecentCol                      As Long
   '
   ' ==============================================================================================
   ' search the circular array of the most recently requested row names to try and find a quick
   ' match.  If the whole circular array is searched without a match:
   ' -- place this requested row name into the array in place of the *least* recently requested
   '    row name in the array.
   ' -- then set it as the *most* recently requested row name.
   ' ----------------------------------------------------------------------------------------------
   Dim targetString As String
   Dim count        As Long
   targetString = UCase(pRowName)
   For count = 0 To circularArrayLastIdx
      If rowName(mostRecentRow) = targetString Then
         Exit For
      Else
         If count = circularArrayLastIdx Then
            rowName(mostRecentRow) = targetString
            rowNum(mostRecentRow) = rangeRelativeRowByName(pRange, targetString)
         Else
            mostRecentRow = (mostRecentRow + 1) Mod circularArraySize
         End If
      End If
   Next count
   '
   ' ==============================================================================================
   ' search the circular array of the most recently requested column names to try and find a quick
   ' match.  If the whole circular array is searched without a match:
   ' -- place this requested column name into the array in place of the *least* recently
   '    requested column name in the array.
   ' -- then set it as the *most* recently requested column name.
   ' ----------------------------------------------------------------------------------------------
   targetString = UCase(pColName)
   For count = 0 To circularArrayLastIdx
      If colName(mostRecentCol) = targetString Then
         Exit For
      Else
         If count = circularArrayLastIdx Then
            colName(mostRecentCol) = targetString
            colNum(mostRecentCol) = rangeRelativeColumnByName(pRange, targetString)
         Else
            mostRecentCol = (mostRecentCol + 1) Mod circularArraySize
         End If
      End If
   Next count
   '
   ' ==============================================================================================
   ' if the either the row or column name is not found then the cell at pRange[pRowName, pColName]
   ' does not exist
   ' ----------------------------------------------------------------------------------------------
   If (rowNum(mostRecentRow) = 0) Or (colNum(mostRecentCol) = 0) Then
      Set rangeRelativeCellByName = Nothing
   '
   ' ==============================================================================================
   ' otherwise, return the cell at pRange[pRowName, pColName]
   ' ----------------------------------------------------------------------------------------------
   Else
      Set rangeRelativeCellByName = pRange.Cells(rowNum(mostRecentRow), colNum(mostRecentCol))
   End If
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns the file name associated with ThisWorkbook
'   VBA�s ThisWorkbook property is similar to the ActiveWorkbook property, but whereas ActiveWorkbook
'   evaluates the workbook with the focus, ThisWorkbook refers to the workbook that�s running the current
'   code. This added flexibility is great because the active workbook isn�t always the workbook that�s
'   running code.
' -----------------------------------------------------------------------------------------------------------
Public Function thisWorkbookFileName() As String
   thisWorkbookFileName = ThisWorkbook.Name
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns the full file path associated with ThisWorkbook
'   VBA�s ThisWorkbook property is similar to the ActiveWorkbook property, but whereas ActiveWorkbook
'   evaluates the workbook with the focus, ThisWorkbook refers to the workbook that�s running the current
'   code. This added flexibility is great because the active workbook isn�t always the workbook that�s
'   running code.
' -----------------------------------------------------------------------------------------------------------
Public Function thisWorkbookFilePath() As String
   thisWorkbookFilePath = ThisWorkbook.FullName
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns the full file path associated with ThisWorkbook
'   VBA�s ThisWorkbook property is similar to the ActiveWorkbook property, but whereas ActiveWorkbook
'   evaluates the workbook with the focus, ThisWorkbook refers to the workbook that�s running the current
'   code. This added flexibility is great because the active workbook isn�t always the workbook that�s
'   running code.
' -----------------------------------------------------------------------------------------------------------
Public Function thisWorkbookFolder() As String
   thisWorkbookFolder = ThisWorkbook.path
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns the file name associated with ActiveWorkbook
'   VBA�s ThisWorkbook property is similar to the ActiveWorkbook property, but whereas ActiveWorkbook
'   evaluates the workbook with the focus, ThisWorkbook refers to the workbook that�s running the current
'   code. This added flexibility is great because the active workbook isn�t always the workbook that�s
'   running code.
' -----------------------------------------------------------------------------------------------------------
Public Function activeWorkbookFileName() As String
   activeWorkbookFileName = ActiveWorkbook.Name
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns the full file path associated with ActiveWorkbook
'   VBA�s ThisWorkbook property is similar to the ActiveWorkbook property, but whereas ActiveWorkbook
'   evaluates the workbook with the focus, ThisWorkbook refers to the workbook that�s running the current
'   code. This added flexibility is great because the active workbook isn�t always the workbook that�s
'   running code.
' -----------------------------------------------------------------------------------------------------------
Public Function activeWorkbookFilePath() As String
   activeWorkbookFilePath = ActiveWorkbook.FullName
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns the full file path associated with ActiveWorkbook
'   VBA�s ThisWorkbook property is similar to the ActiveWorkbook property, but whereas ActiveWorkbook
'   evaluates the workbook with the focus, ThisWorkbook refers to the workbook that�s running the current
'   code. This added flexibility is great because the active workbook isn�t always the workbook that�s
'   running code.
' -----------------------------------------------------------------------------------------------------------
Public Function activeWorkbookFolder() As String
   activeWorkbookFolder = ActiveWorkbook.path
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns the first column it finds with pName as a header.  More precisely, it returns the
' column number of the first column found at or after pStartColumn on row pStartRow that contains the value
' pName.
'
' return
'    This function returns the first column it finds with pName as a header or 0 if the header is not found
'
' param
'    pName is the name of the column header to find
' param
'    pStartRow is an optional parameter that tells this function which row to look in for the column header.
'    If pStartRow is ommitted, it defaults to 1.
' param
'    pStartColumn is an optional parameter that tells this function which column to begin the search in.  If
'    pStartColumn is ommitted, it defaults to 1.
' -----------------------------------------------------------------------------------------------------------
Public Function columnByName(pName As String, Optional pStartRow As Long = 1, Optional pStartColumn As Long = 1) As Long
   Dim lastCell As Range
   Dim colIdx   As Long
   columnByName = 0
   Set lastCell = ActiveCell.SpecialCells(xlLastCell)
   For colIdx = pStartColumn To lastColumnInRange(lastCell)
      If ((Not IsEmpty(Cells(pStartRow, colIdx))) And equalsNoCase(pName, Cells(pStartRow, colIdx).value)) Then
         columnByName = colIdx
         Exit For
      End If
   Next colIdx
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns the first row it finds with pName as a header.  More precisely, it returns the row
' number of the first row found at or after pStartRow in column pStartColumn that contains the value pName.
'
' return
'    This function returns the first row it finds with pName as a header or 0 if the header is not found
'
' param
'    pName is the name of the row header to find
' param
'    pStartColumn is an optional parameter that tells this function which column to look in for the row
'    header.  If pStartColumn is ommitted, it defaults to 1.
' param
'    pStartRow is an optional parameter that tells this function which row to begin the search in.  If
'    pStartRow is ommitted, it defaults to 1.
' -----------------------------------------------------------------------------------------------------------
Public Function rowByName(pName As String, Optional pStartColumn As Long = 1, Optional pStartRow As Long = 1) As Long
   Dim lastCell As Range
   Dim rowIdx   As Long
   rowByName = 0
   Set lastCell = ActiveCell.SpecialCells(xlLastCell)
   For rowIdx = pStartRow To lastRowInRange(lastCell)
      If ((Not IsEmpty(Cells(rowIdx, pStartColumn))) And equalsNoCase(pName, Cells(rowIdx, pStartColumn).value)) Then
         rowByName = rowIdx
         Exit For
      End If
   Next rowIdx
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns the number of the last row in the Range specified by pRange.
'
' return
'    This function returns the number of the last row in the Range specified by pRange.
'
' param
'    pRange is the Range for which the last row number is returned.
' -----------------------------------------------------------------------------------------------------------
Public Function lastRowInRange(pRange As Range) As Long
   lastRowInRange = pRange.Rows(pRange.Rows.count).row
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns the number of the last column in the Range specified by pRange.
'
' return
'    This function returns the number of the last column in the Range specified by pRange.
'
' param
'    pRange is the Range for which the last column number is returned.
' -----------------------------------------------------------------------------------------------------------
Public Function lastColumnInRange(pRange As Range) As Long
   lastColumnInRange = pRange.Columns(pRange.Columns.count).Column
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns the number of the last row in the sheet.
'
' return
'    This function returns the number of the last row in the sheet.
' -----------------------------------------------------------------------------------------------------------
Public Function lastRowInSheet() As Long
   lastRowInSheet = Range("A1").SpecialCells(xlLastCell).row
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns the number of the last column in the sheet.
'
' return
'    This function returns the number of the last column in the sheet.
' -----------------------------------------------------------------------------------------------------------
Public Function lastColumnInSheet() As Long
   lastColumnInSheet = Range("A1").SpecialCells(xlLastCell).Column
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns the letter name of the column specified by pColumnNumber.
'
' return
'    This function returns a String containing the letter name of the column specified by pColumnNumber.
'
' param
'    pColumnNumber is the number of the column for which the letter name is returned.
' -----------------------------------------------------------------------------------------------------------
Public Function columnLetter(pColumnNumber As Long) As String
   columnLetter = Left(Cells(1, pColumnNumber).Address(1, 0), InStr(1, Cells(1, pColumnNumber).Address(1, 0), "$") - 1)
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns the column number from the column's letter name as specified by pColumnNumber.
'  For Example: columnNumber("AQ") returns 43
'
' return
'    This function returns the column number from the column's letter name as specified by pColumnNumber.
'
' param
'    pColumnName is the letter name of a column.
' -----------------------------------------------------------------------------------------------------------
Public Function columnNumber(pColumnName As String) As Long
   columnNumber = Range(pColumnName & 1).Column
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns a Range name that is defined on any sheet and translates it to a Range in the same
' location in the active sheet.
'   For example:
'      If sheet1 has a named Range called myRangeName that refers to sheet1!A1:C10 and sheet14 is the
'      ActiveSheet, then calling this function lke this
'      -- myRangeVar = worksheetRelativeNamedRange("myRangeName")
'      -- will set the value of myRangeVar to the Range sheet14!A1:C10.
'
' return
'    This function returns a Range that corresponds in size and location on the ActiveSheet to the size and
'    location on the sheet where the nnamed Range is defined.
'
' param
'    pName is the Range name without the sheet reference.
' -----------------------------------------------------------------------------------------------------------
Public Function worksheetRelativeNamedRange(pName As String) As Range
   Dim startRow As Long
   Dim startCol As Long
   Dim rowCount As Long
   Dim colCount As Long
   Dim endRow As Long
   Dim endCol As Long
   startRow = Range(pName).row
   startCol = Range(pName).Column
   rowCount = Range(pName).Rows.count
   colCount = Range(pName).Columns.count
   endRow = startRow + rowCount - 1
   endCol = startCol + colCount - 1
   Set worksheetRelativeNamedRange = Range(Cells(startRow, startCol), Cells(endRow, endCol))
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns the R1C1 notation style name of a Range
'
'   !! NOTE !!  I learned something when writing this function.  You CANNOT start the name of a Function
'   with the string "R1C1"
'
'   =RangeToR1C1(ScenarioSupplyTotal) returns something like: varsity!R84C6:R84C16 when AM1.Value = "am1"
'     =RangeToR1C1(AM1) '             returns varsity!R1C39
'
' return
'    This function returns the R1C1 notation style name of a Range
'
' param
'    pRange is the Range for which the R1C1 name is returned.
' -----------------------------------------------------------------------------------------------------------
Public Function rangeToR1C1(pRange As Range) As String
   Dim startRow As Long
   Dim startCol As Long
   Dim rowCount As Long
   Dim colCount As Long
   Dim endRow As Long
   Dim endCol As Long
   startRow = pRange.row
   startCol = pRange.Column
   rowCount = pRange.Rows.count
   colCount = pRange.Columns.count
   endRow = startRow + rowCount - 1
   endCol = startCol + colCount - 1
   If ((startRow = endRow) And (startCol = endCol)) Then
      rangeToR1C1 = pRange.Worksheet.Name & "!" & "R" & startRow & "C" & startCol
   Else
      rangeToR1C1 = pRange.Worksheet.Name & "!" & "R" & startRow & "C" & startCol & ":" & "R" & endRow & "C" & endCol
   End If
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function returns the R1C1 notation style name of the Range identified by the pRangeName
'
'   !! NOTE !!  I learned something when writing this function.  You CANNOT start the name of a Function
'   with the string "R1C1"
'
'   =nameToR1C1("ScenarioSupplyTotal") returns something like: varsity!R84C6:R84C16
'
'   =nameToR1C1("AM1")                 returns varsity!R1C39
'
' return
'    This function returns the R1C1 notation style name of a Range
'
' param
'    pRangeName is the name of a Range for which the R1C1 name is returned.
' -----------------------------------------------------------------------------------------------------------
Public Function nameToR1C1(pRangeName As String) As String
   nameToR1C1 = rangeToR1C1(Range(pRangeName))
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function determines if a specified named Range exists in a specified workbook.
'
' return
'    This function returns the True if the specified named Range exists in the specified workbook,
'    otherwise, it returns False
'
' param
'    pWorkbook is a Workbook object that specifies the workbook to search for the named Range
' param
'    pName is the name of the named Range to search for.
' -----------------------------------------------------------------------------------------------------------
Public Function namedRangeExistsInWorkbook(pWorkbook As Workbook, pName As String) As Boolean
   Dim namedRangeName As Name
   namedRangeExistsInWorkbook = False
   For Each namedRangeName In pWorkbook.Names
      If (namedRangeName.Name = pName) Then
         namedRangeExistsInWorkbook = True
         Exit For
      End If
   Next
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function determines if a specified named Range exists in the active workbook.
'
' return
'    This function returns the True if the specified named Range exists in the active workbook, otherwise,
'    it returns False
'
' param
'    pName is the name of the named Range to search for.
' -----------------------------------------------------------------------------------------------------------
Public Function namedRangeExistsInActiveWorkbook(pName As String) As Boolean
   namedRangeExistsInActiveWorkbook = namedRangeExistsInWorkbook(ActiveWorkbook, pName)
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine displays a listing of the ActiveWorkbook's named ranges starting in the cell pStartCell.
'
' param
'    pStartCell is the cell in the top left corner of the list thast is output
' -----------------------------------------------------------------------------------------------------------
Public Sub listNamedRangesAndTheirR1C1s(pStartCell As Range)
   Dim rowIdx         As Long
   Dim namedRangeName As Name
   rowIdx = 1
   For Each namedRangeName In ActiveWorkbook.Names
      pStartCell.Cells(rowIdx, 1).value = namedRangeName.Name
      pStartCell.Cells(rowIdx, 2).value = "'" & namedRangeName.RefersToR1C1
      pStartCell.Cells(rowIdx, 3).value = "'" & namedRangeName.RefersTo
      pStartCell.Cells(rowIdx, 4).value = "'" & namedRangeName.RefersToLocal
      rowIdx = rowIdx + 1
   Next
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine displays a listing of the ActiveWorkbook's named ranges starting in the currently selected
' cell on the active sheet.
' -----------------------------------------------------------------------------------------------------------
Public Sub listNamedRangesAndTheirR1C1sStartingHere()
   Call listNamedRangesAndTheirR1C1s(Selection)
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine changes the Range that is referenced by an active workbook-level named Range
'
' !! WARN !! 2014.02.18: This is not working in TempermentIdentifier.xlsm
'
' param
'    pName is the name of the named Range within the ActiveWorkbook that will be redefined
' param
'    pNewRange is a Range object to that will henceforth go by the Range name
' -----------------------------------------------------------------------------------------------------------
Public Sub redefineNamedRange(ByRef pName As String, ByRef pNewRange As Range)
   If (namedRangeExistsInActiveWorkbook(pName)) Then
      ActiveWorkbook.Names(pName).Delete
   End If
   ActiveWorkbook.Names.Add Name:=pName, RefersTo:="=" & pNewRange.Address
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine changes the Range that is referenced by an active workbook-level named Range
'
' param
'    pName is the name of the named Range within the ActiveWorkbook that will be redefined
' param
'    pNewRange is the Range identifier in A1 format to that will henceforth go by the Range name
' -----------------------------------------------------------------------------------------------------------
Public Sub redefineNamedRangeA1(pName As String, pNewRange As String)
   If (namedRangeExistsInActiveWorkbook(pName)) Then
      ActiveWorkbook.Names(pName).Delete
   End If
   ActiveWorkbook.Names.Add Name:=pName, RefersTo:="=" & pNewRange
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine changes the Range that is referenced by an active workbook-level named Range
'
' param
'    pName is the name of the named Range within the ActiveWorkbook that will be redefined
' param
'    pNewRange is the Range identifier in R1C1 format to that will henceforth go by the Range name
' -----------------------------------------------------------------------------------------------------------
Public Sub redefineNamedRangeR1C1(pName As String, pNewRange As String)
   If (namedRangeExistsInActiveWorkbook(pName)) Then
      ActiveWorkbook.Names(pName).Delete
   End If
   ActiveWorkbook.Names.Add Name:=pName, RefersToR1C1:="=" & pNewRange
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine resizes a named range.  It will not resize it out of existence nor will it move the top
' left corner of the named Range
'
' param
'    pName is the name of the named Range within the ActiveWorkbook that will be resized
' param
'    pRowChange is the number of rows to extend (if a positive value) or reduce (if a negative value) the
'    size.
' param
'    pColChange is the number of columns to extend (if a positive value) or reduce (if a negative value) the
'    size.
' -----------------------------------------------------------------------------------------------------------
Public Sub resizeNamedRange(pName As String, pRowChange As Long, pColChange As Long)
   Dim startRow As Long
   Dim startCol As Long
   Dim rowCount As Long
   Dim colCount As Long
   Dim endRow   As Long
   Dim endCol   As Long
   Dim theRange As Range
   Dim theR1C1  As String
   '
   ' ==============================================================================================
   ' get the current dimensions of the named Range
   ' ----------------------------------------------------------------------------------------------
   Set theRange = ActiveWorkbook.Names(pName).RefersToRange
   theR1C1 = ActiveWorkbook.Names(pName).RefersToR1C1
   startRow = theRange.row
   startCol = theRange.Column
   rowCount = theRange.Rows.count
   colCount = theRange.Columns.count
   endRow = startRow + rowCount - 1
   endCol = startCol + colCount - 1
   '
   ' ==============================================================================================
   ' this stRange thing ensures that the Range is not sized out of existence.  It will not let
   ' either the number of rows or the number of columns in the Range to be set to less than one.
   ' ----------------------------------------------------------------------------------------------
   endRow = max(startRow, endRow + pRowChange)
   endCol = max(startCol, endCol + pColChange)
   '
   ' ==============================================================================================
   ' construct a new R1C1-notation name for the new size
   ' ----------------------------------------------------------------------------------------------
   theR1C1 = trimRightSubstring(theR1C1, stripLeftOfSubstring(theR1C1, "!"))
   If ((startRow = endRow) And (startCol = endCol)) Then
      theR1C1 = theR1C1 & "!" & "R" & startRow & "C" & startCol
   Else
      theR1C1 = theR1C1 & "!" & "R" & startRow & "C" & startCol & ":" & "R" & endRow & "C" & endCol
   End If
   '
   ' ==============================================================================================
   ' give it the new Range reference
   ' ----------------------------------------------------------------------------------------------
   ActiveWorkbook.Names(pName).RefersToR1C1 = theR1C1
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function clears a Range without disturbing its formatting or any formulas
'
' param
'    pRange is the Range to clear all values from
' -----------------------------------------------------------------------------------------------------------
Public Sub clearRangeKeepFormulasAndFormats(pRange As Range)
   Dim specialRange As Range
   On Error Resume Next
   Set specialRange = pRange.SpecialCells(xlCellTypeConstants, xlErrors + xlLogical + xlNumbers + xlTextValues)
   If Err = 0 Then
      specialRange.ClearContents
   End If
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function clears a Range without disturbing its formatting
'
' param
'    pRange is the Range to clear all values and formulas from
' -----------------------------------------------------------------------------------------------------------
Public Sub clearRangeKeepFormats(pRange As Range)
   pRange.ClearContents
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function is used from within a worksheet to display the worksheet's name
' -----------------------------------------------------------------------------------------------------------
Public Function thisWorksheetName() As String
   thisWorksheetName = ActiveSheet.Name
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine positions the top left cell of the specified Range of the active sheet in the top left
' corner of the window.
' -----------------------------------------------------------------------------------------------------------
Public Sub gotoRange(pRange As Range, pFreezeRow As Long, pFreezeCol As Long)
   '
   ' ==============================================================================================
   ' unfreeze panes so we can move the freeze pane cell to a different location
   ' ----------------------------------------------------------------------------------------------
   ActiveWindow.FreezePanes = False
   ' ==============================================================================================
   ' position the top left cell in the top left corner of the display
   ' ----------------------------------------------------------------------------------------------
   Application.GoTo pRange.Cells(1, 1), Scroll:=True
   '
   ' ==============================================================================================
   ' freeze the pane in a location appropriate for this view
   ' ----------------------------------------------------------------------------------------------
   If (pRange.Column = 1) Then pFreezeCol = max(pFreezeCol, 2)
   If (pRange.row = 1) Then pFreezeRow = max(pFreezeRow, 2)
   pRange.Cells(pFreezeRow, pFreezeCol).Select
   ActiveWindow.FreezePanes = True
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine positions the top left cell of the specified Range of the active sheet in the top left
' corner of the window.
' -----------------------------------------------------------------------------------------------------------
Public Sub gotoName(pName As String, pFreezeRow As Long, pFreezeCol As Long)
   Call gotoRange(Range(pName), pFreezeRow, pFreezeCol)
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine positions the top left cell of the specified Range of the active sheet in the top left
' corner of the window.
' -----------------------------------------------------------------------------------------------------------
Public Sub gotoRangeNoFreeze(pRange As Range)
   ' ==============================================================================================
   ' position the top left cell in the top left corner of the display
   ' ----------------------------------------------------------------------------------------------
   Application.GoTo pRange.Cells(1, 1), Scroll:=True
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine positions the top left cell of the specified Range of the active sheet in the top left
' corner of the window.
' -----------------------------------------------------------------------------------------------------------
Public Sub gotoNameNoFreeze(pName As String)
   Call gotoRangeNoFreeze(Range(pName))
End Sub




' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine forces Excel to apply the conditional formatting.  When VBA changes the value of a cell,
' the conditional formatting in that cell is not triggered.  This is a long-know bug in Excel and there is
' no MS fix for it.  So the workaround is in This subroutine.  Call it before returning control from the VBA
' program to the Excel UI.
' -----------------------------------------------------------------------------------------------------------
Public Sub forceConditionalFormatUpdates(pRange As Range)
   Dim tmp As Variant
   pRange.Cells(1, 1).Select
   tmp = Selection.value
   Selection.value = tmp
''   Application.ScreenUpdating = Not Application.ScreenUpdating
''   Application.ScreenUpdating = Not Application.ScreenUpdating
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine finds the Workbook-Scoped named Range specified by pRangeName and assigns its Range object
' to pRange.  if the named Range is missing, it does an error exit from the program.
'
' param (output)
'    pRange is the Range object that is returned through the parameter to the caller.
' param
'    pWorkbook is the workbook the Range must be found in
' param
'    pRangeName is the name of the Workbook-scoped named Range to find
' -----------------------------------------------------------------------------------------------------------
Public Sub mustGetExcelWorkbookRange(ByRef pRange As Range, pWorkbook As Workbook, pRangeName As String)
   Dim result As Range
   Set result = pWorkbook.Names(pRangeName).RefersToRange
   If (result Is Nothing) Then
      Call errorExit("E005 Workbook-Scoped Named Range Missing Error - Cannot find " & pRangeName & " named Range in workbook " & pWorkbook.Name)
   End If
   Set pRange = result
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine finds the Sheet-Scoped named Range specified by pRangeName and assigns its Range object to
' pRange.  if the named Range is missing, it does an error exit from the program.
'
' param (output)
'    pRange is the Range object that is returned through the parameter to the caller.
' param
'    pSheet is the sheet the Range must be found in
' param
'    pRangeName is the name of the Sheet-scoped named Range to find
' -----------------------------------------------------------------------------------------------------------
Public Sub mustGetExcelSheetRange(ByRef pRange As Range, pSheet As Worksheet, pRangeName As String)
   Dim result As Range
   Set result = pSheet.Names(pRangeName).RefersToRange
   If (result Is Nothing) Then
      Call errorExit("E005 Sheet-Scoped Named Range Missing Error - Cannot find " & pRangeName & " named Range in sheet " & pSheet.Name)
   End If
   Set pRange = result
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine finds the sheet specified by pSheetName and assigns its sheet object to pSheet.  if the
' sheet is missing, it does an error exit from the program.
'
' param
'    pSheet is the sheet object that is returned through the parameter to the caller.
' param
'    pWorkbook is the workbook the sheet must be found in
' param
'    pSheetName is the name of the sheet to find
' -----------------------------------------------------------------------------------------------------------
Public Sub mustGetExcelSheet(ByRef pSheet As Worksheet, pWorkbook As Workbook, pSheetName As String)
   '
   ' ==============================================================================================
   ' save the calling context and establish the right context for this routine
   ' ----------------------------------------------------------------------------------------------
   Call saveExcelContext
   pWorkbook.Activate
   '
   ' ==============================================================================================
   ' save the calling context and establish the right context for this routine
   ' ----------------------------------------------------------------------------------------------
   If (worksheetExists(pSheetName)) Then
      Set pSheet = pWorkbook.Worksheets(pSheetName)
   Else
      Call errorExit("E005 Sheet Missing Error - Cannot find " & pSheetName & " sheet in active workbook.")
   End If
   '
   ' ==============================================================================================
   ' restore the calling context
   ' ----------------------------------------------------------------------------------------------
   Call restoreExcelContext
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine finds the sheet specified by pWorkbookPathname and assigns its sheet object to pWorkbook.
' if the sheet is missing, it does an error exit from the program.
'
' param
'    pWorkbook is the sheet object that is returned through the parameter to the caller.
' param
'    pWorkbook is the workbook the sheet must be found in
' param
'    pWorkbookPathname is the name of the sheet to find
' -----------------------------------------------------------------------------------------------------------
Public Sub mustGetExcelWorkbook(ByRef pWorkbook As Workbook, pWorkbookPathname As String)
   '
   ' ==============================================================================================
   ' save the calling context and establish the right context for this routine
   ' ----------------------------------------------------------------------------------------------
   Call saveExcelContext
   '
   ' ==============================================================================================
   ' open or die
   ' ----------------------------------------------------------------------------------------------
   If (fileExists(pWorkbookPathname)) Then
      Set pWorkbook = Workbooks.Open(fileName:=pWorkbookPathname)
      If (pWorkbook Is Nothing) Then
         Call errorExit("E007 Workbook Open Error - Cannot open " & pWorkbookPathname)
      End If
   Else
      Call errorExit("E006 Workbook Missing Error - Cannot find " & pWorkbookPathname)
   End If
   '
   ' ==============================================================================================
   ' restore the calling context
   ' ----------------------------------------------------------------------------------------------
   Call restoreExcelContext
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine draws normal borders around all the cells in the specified range.
'
' param
'    pRange is the Range of cells to set the borders on
' -----------------------------------------------------------------------------------------------------------
Public Sub drawNormalBorders(pRange As Range)
   pRange.Select
   Selection.borders(xlDiagonalDown).LineStyle = xlNone
   Selection.borders(xlDiagonalUp).LineStyle = xlNone
   With Selection.borders(xlEdgeLeft)
      .LineStyle = xlContinuous
      .ColorIndex = 0
      .TintAndShade = 0
      .weight = xlThin
   End With
   With Selection.borders(xlEdgeTop)
      .LineStyle = xlContinuous
      .ColorIndex = 0
      .TintAndShade = 0
      .weight = xlThin
   End With
   With Selection.borders(xlEdgeBottom)
      .LineStyle = xlContinuous
      .ColorIndex = 0
      .TintAndShade = 0
      .weight = xlThin
   End With
   With Selection.borders(xlEdgeRight)
      .LineStyle = xlContinuous
      .ColorIndex = 0
      .TintAndShade = 0
      .weight = xlThin
   End With
   With Selection.borders(xlInsideVertical)
      .LineStyle = xlContinuous
      .ColorIndex = 0
      .TintAndShade = 0
      .weight = xlThin
   End With
   With Selection.borders(xlInsideHorizontal)
      .LineStyle = xlContinuous
      .ColorIndex = 0
      .TintAndShade = 0
      .weight = xlThin
   End With
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine draws normal borders around all the cells in the specified Range but leaves off the top
' border of each cell
'
' param
'    pRange is the Range of cells to set the borders on
' -----------------------------------------------------------------------------------------------------------
Public Sub drawToplessBorders(pRange As Range)
   pRange.Select
   Selection.borders(xlDiagonalDown).LineStyle = xlNone
   Selection.borders(xlDiagonalUp).LineStyle = xlNone
   With Selection.borders(xlEdgeLeft)
      .LineStyle = xlContinuous
      .ColorIndex = 0
      .TintAndShade = 0
      .weight = xlThin
   End With
   With Selection.borders(xlEdgeTop)
      .LineStyle = xlNone
   End With
   With Selection.borders(xlEdgeBottom)
      .LineStyle = xlContinuous
      .ColorIndex = 0
      .TintAndShade = 0
      .weight = xlThin
   End With
   With Selection.borders(xlEdgeRight)
      .LineStyle = xlContinuous
      .ColorIndex = 0
      .TintAndShade = 0
      .weight = xlThin
   End With
   With Selection.borders(xlInsideVertical)
      .LineStyle = xlContinuous
      .ColorIndex = 0
      .TintAndShade = 0
      .weight = xlThin
   End With
   With Selection.borders(xlInsideHorizontal)
      .LineStyle = xlNone
   End With
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine draws normal borders around all the cells in the specified Range but leaves off the top
' border of each cell
'
' param
'    pRange is the Range of cells to set the borders on
' -----------------------------------------------------------------------------------------------------------
Public Sub drawToplessBottomlessBorders(pRange As Range)
   pRange.Select
   Selection.borders(xlDiagonalDown).LineStyle = xlNone
   Selection.borders(xlDiagonalUp).LineStyle = xlNone
   With Selection.borders(xlEdgeLeft)
      .LineStyle = xlContinuous
      .ColorIndex = 0
      .TintAndShade = 0
      .weight = xlThin
   End With
   With Selection.borders(xlEdgeTop)
      .LineStyle = xlNone
   End With
   With Selection.borders(xlEdgeBottom)
      .LineStyle = xlNone
   End With
   With Selection.borders(xlEdgeRight)
      .LineStyle = xlContinuous
      .ColorIndex = 0
      .TintAndShade = 0
      .weight = xlThin
   End With
   With Selection.borders(xlInsideVertical)
      .LineStyle = xlContinuous
      .ColorIndex = 0
      .TintAndShade = 0
      .weight = xlThin
   End With
   With Selection.borders(xlInsideHorizontal)
      .LineStyle = xlNone
   End With
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' getLastCell By Chip Pearson, chip@cpearson.com, www.cpearson.com
'
' This returns the last used cell in a worksheet or range. If pRangeToSearch is a single cell, the last cell
' of the entire worksheet if found. If pRangeToSearch contains two or more cells, the last cell in that
' Range is returned.
'
' If pSearchOrder is xlByRows (= 1), the last cell is the last (right-most) non-blank cell on the last row
' of data in the worksheet's UsedRange. If pSearchOrder is xlByColumns (= 2), the last cell is the last
' (bottom-most) non-blank cell in the last (right-most) column of the worksheet's UsedRange. If pSearchOrder
' is xlByColumns + xlByRows (= 3), the last cell is the intersection of the last row and the last column.
' Note that this cell may not contain any value.
'
' If pSearchOrder is anything other than xlByRows, xlByColumns, or xlByRows+xlByColumns, an error 5 is
' raised.
'
' pSheetContainingRangeToSearch is an optional specifier of which sheet to search. If omitted, the
' ActiveSheet is used.
'
' pProhibitEmptyFormula indicates how to handle the case in which the last cell is a formula that evaluates
' to an empty string. If this setting is omitted for False, the last cell is allowed to be a formula that
' evaluates to an empty string. If this setting is True, the last cell must be either a static value or a
' formula that evaluates to a non-empty string. The default is False, allowing the last cell to be a formula
' that evaluates to an empty string.
'
' Example:
'       a   b   c
'               d   e
'       f   g
'
' If pSearchOrder is xlByRows, the last cell is 'g'. If pSearchOrder is xlByColumns, the last cell is 'e'.
' If pSearchOrder is xlByRows+xlByColumns, the last cell is the intersection of the row containing 'g' and
' the column containing 'e'. This cell has no value in this example.
' -----------------------------------------------------------------------------------------------------------
Public Function getLastCell _
   ( _
            pRangeToSearch As Range, _
            pSearchOrder As XlSearchOrder, _
   Optional pSheetContainingRangeToSearch As Worksheet, _
   Optional pProhibitEmptyFormula As Boolean = False _
   ) _
   As Range

   Dim sheetContainingRangeToSearch As Worksheet
   Dim lastCellInRangeToSearch      As Range
   Dim lastUsedCellInRangeToSearch  As Range
   Dim lastInRow                    As Range
   Dim lastInColumn                 As Range
   Dim lookInFormulasOrValues       As XlFindLookIn
   Dim rangeToSearch                As Range
   '
   ' ==============================================================================================
   ' are we searching in the active sheet or in a parameter-specified sheet
   ' ----------------------------------------------------------------------------------------------
   If pSheetContainingRangeToSearch Is Nothing Then
      Set sheetContainingRangeToSearch = ActiveSheet
   Else
      Set sheetContainingRangeToSearch = pSheetContainingRangeToSearch
   End If
   '
   ' ==============================================================================================
   ' are we going to look in formulas or not
   ' ----------------------------------------------------------------------------------------------
   If pProhibitEmptyFormula = False Then
      lookInFormulasOrValues = xlFormulas
   Else
      lookInFormulasOrValues = xlValues
   End If
   '
   ' ==============================================================================================
   ' has a valid search order been specified
   ' ----------------------------------------------------------------------------------------------
   Select Case pSearchOrder
      Case XlSearchOrder.xlByColumns, XlSearchOrder.xlByRows, XlSearchOrder.xlByColumns + XlSearchOrder.xlByRows
         ' OK
      Case Else
         Err.Raise 5
         Exit Function
   End Select
   '
   ' ==============================================================================================
   ' with the sheet we expect to find the search range...
   ' ----------------------------------------------------------------------------------------------
   With sheetContainingRangeToSearch
      '
      ' ==============================================================================================
      ' what is the Range we are going to search
      ' ----------------------------------------------------------------------------------------------
      If pRangeToSearch.Cells.count = 1 Then
         Set rangeToSearch = .UsedRange
      Else
         Set rangeToSearch = pRangeToSearch
      End If
      '
      ' ==============================================================================================
      ' where is the last cell in the Range we are going to search
      ' ----------------------------------------------------------------------------------------------
      Set lastCellInRangeToSearch = rangeToSearch(rangeToSearch.Cells.count)
      '
      ' ==============================================================================================
      ' search
      ' ----------------------------------------------------------------------------------------------
      If pSearchOrder = xlByColumns Then
         Set lastUsedCellInRangeToSearch = rangeToSearch.Find _
            ( _
            what:="*", _
            after:=lastCellInRangeToSearch, _
            LookIn:=lookInFormulasOrValues, _
            LookAt:=xlPart, _
            SearchOrder:=xlByColumns, _
            searchdirection:=xlPrevious, _
            MatchCase:=False _
            )
      ElseIf pSearchOrder = xlByRows Then
         Set lastUsedCellInRangeToSearch = rangeToSearch.Find _
            ( _
            what:="*", _
            after:=lastCellInRangeToSearch, _
            LookIn:=lookInFormulasOrValues, _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            searchdirection:=xlPrevious, _
            MatchCase:=False _
            )
      ElseIf pSearchOrder = xlByColumns + xlByRows Then
         Set lastInColumn = rangeToSearch.Find _
            ( _
            what:="*", _
            after:=lastCellInRangeToSearch, _
            LookIn:=lookInFormulasOrValues, _
            LookAt:=xlPart, _
            SearchOrder:=xlByColumns, _
            searchdirection:=xlPrevious, _
            MatchCase:=False _
            )
         Set lastInRow = rangeToSearch.Find _
            ( _
            what:="*", _
            after:=lastCellInRangeToSearch, _
            LookIn:=lookInFormulasOrValues, _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            searchdirection:=xlPrevious, _
            MatchCase:=False _
            )
         Set lastUsedCellInRangeToSearch = Application.Intersect(lastInRow.EntireRow, lastInColumn.EntireColumn)
      Else
         Err.Raise 5
         Exit Function
      End If
   End With
   '
   ' ==============================================================================================
   ' return a proper Range reference for the last cell used in the Range to search on the sheet
   ' containing the Range to search
   ' ----------------------------------------------------------------------------------------------
   Set getLastCell = Range(sheetContainingRangeToSearch.Name & "!" & lastUsedCellInRangeToSearch.Address)
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' getFirstCell By Chip Pearson, chip@cpearson.com, www.cpearson.com
'
' This returns the first used cell in a worksheet or range. If pRangeToSearch is a single cell, the first
' cell of the entire worksheet if found. If pRangeToSearch contains two or more cells, the first cell in
' that Range is returned.
'
' If pSearchOrder is xlByRows (= 1), the first cell is the first (left-most) non-blank cell on the first row
' of data in the worksheet's UsedRange. If pSearchOrder is xlByColumns (= 2), the first cell is the first
' (top-most) non-blank cell in the first (left-most) column of the worksheet's UsedRange. If pSearchOrder is
' xlByColumns + xlByRows (= 3), the first cell is the intersection of the first row and the first column.
' Note that this cell may not contain any value.
'
' If pSearchOrder is anything other than xlByRows, xlByColumns, or xlByRows+xlByColumns, an error 5 is
' raised.
'
' pSheetContainingRangeToSearch is an optional specifier of which sheet to search. If omitted, the
' ActiveSheet is used.
'
' pProhibitEmptyFormula indicates how to handle the case in which the first cell is a formula that evaluates
' to an empty string. If this setting is omitted for False, the first cell is allowed to be a formula that
' evaluates to an empty string. If this setting is True, the first cell must be either a static value or a
' formula that evaluates to a non-empty string. The default is False, allowing the first cell to be a
' formula that evaluates to an empty string.
'
' Example:
'       a   b   c
'               d   e
'       f   g
'
' If pSearchOrder is xlByRows, the first cell is 'g'. If pSearchOrder is xlByColumns, the first cell is 'e'.
' If pSearchOrder is xlByRows+xlByColumns, the first cell is the intersection of the row containing 'g' and
' the column containing 'e'. This cell has no value in this example.
' -----------------------------------------------------------------------------------------------------------
Public Function getFirstCell _
   ( _
            pRangeToSearch As Range, _
            pSearchOrder As XlSearchOrder, _
   Optional pSheetContainingRangeToSearch As Worksheet, _
   Optional pProhibitEmptyFormula As Boolean = False _
   ) _
   As Range

   Dim sheetContainingRangeToSearch As Worksheet
   Dim lastCellInRangeToSearch      As Range
   Dim lookInFormulasOrValues       As XlFindLookIn
   Dim rangeToSearch                As Range
   '
   ' ==============================================================================================
   ' are we searching in the active sheet or in a parameter-specified sheet
   ' ----------------------------------------------------------------------------------------------
   If pSheetContainingRangeToSearch Is Nothing Then
      Set sheetContainingRangeToSearch = ActiveSheet
   Else
      Set sheetContainingRangeToSearch = pSheetContainingRangeToSearch
   End If
   '
   ' ==============================================================================================
   ' are we going to look in formulas or not
   ' ----------------------------------------------------------------------------------------------
   If pProhibitEmptyFormula = False Then
      lookInFormulasOrValues = xlFormulas
   Else
      lookInFormulasOrValues = xlValues
   End If
   '
   ' ==============================================================================================
   ' has a valid search order been specified
   ' ----------------------------------------------------------------------------------------------
   Select Case pSearchOrder
      Case XlSearchOrder.xlByColumns, XlSearchOrder.xlByRows, XlSearchOrder.xlByColumns + XlSearchOrder.xlByRows
         ' OK
      Case Else
         Err.Raise 5
         Exit Function
   End Select
   '
   ' ==============================================================================================
   ' with the sheet we expect to find the search range...
   ' ----------------------------------------------------------------------------------------------
   With sheetContainingRangeToSearch
      '
      ' ==============================================================================================
      ' what is the Range we are going to search
      ' ----------------------------------------------------------------------------------------------
      If pRangeToSearch.Cells.count = 1 Then
         Set rangeToSearch = .UsedRange
      Else
         Set rangeToSearch = pRangeToSearch
      End If
      '
      ' ==============================================================================================
      ' where is the first cell in the Range we are going to search
      ' ----------------------------------------------------------------------------------------------
      If rangeToSearch(1, 1).Formula <> vbNullString Then
         Set getFirstCell = rangeToSearch(1, 1)
         Exit Function
      End If
      '
      ' ==============================================================================================
      ' search
      ' ----------------------------------------------------------------------------------------------
      If pSearchOrder = xlByColumns Then
         Set lastCellInRangeToSearch = rangeToSearch.Find _
            ( _
            what:="*", _
            after:=rangeToSearch.Cells(1, 1), _
            LookIn:=lookInFormulasOrValues, _
            LookAt:=xlPart, _
            SearchOrder:=xlByColumns, _
            searchdirection:=xlNext, _
            MatchCase:=False _
            )
      ElseIf pSearchOrder = xlByRows Then
         Set lastCellInRangeToSearch = rangeToSearch.Find _
            ( _
            what:="*", _
            after:=rangeToSearch.Cells(1, 1), _
            LookIn:=lookInFormulasOrValues, _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            searchdirection:=xlNext, _
            MatchCase:=False _
            )
      ElseIf pSearchOrder = xlByRows + xlByColumns Then
         Set lastCellInRangeToSearch = rangeToSearch.Cells(1, 1)
      Else
         Err.Raise 5
         Exit Function
      End If
   End With
   '
   ' ==============================================================================================
   ' return a proper Range reference for the first cell used in the Range to search on the sheet
   ' containing the Range to search
   ' ----------------------------------------------------------------------------------------------
   Set getFirstCell = Range(sheetContainingRangeToSearch.Name & "!" & lastCellInRangeToSearch.Address)
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine copys all of pRangeToCopyFrom to pRangeToCopyTo starting in the first cell after the last
' occupied cell in pRangeToCopyTo
'
' param
'    pNameOfSheetToCopyFrom is the sheet containing pRangeToCopyFrom
' param
'    pRangeToCopyFrom is the Range to copy to the end of pRangeToCopyTo
' param
'    pNameOfSheetToCopyTo is the sheet containing pRangeToCopyTo
' param
'    pRangeToCopyTo is the Range to copy pRangeToCopyFrom's data to the end of
' -----------------------------------------------------------------------------------------------------------
Public Sub appendRangeToRange(pNameOfSheetToCopyFrom As String, pRangeToCopyFrom As Range, pNameOfSheetToCopyTo As String, pRangeToCopyTo As Range)
   Dim lastCell        As Range
   Dim rangeToCopyFrom As Range
   Dim rangeToCopyTo   As Range
   Dim sheetToCopyFrom As Worksheet
   Dim sheetToCopyTo   As Worksheet
   '
   ' ==============================================================================================
   ' save the calling context and establish the right context for this routine
   ' ----------------------------------------------------------------------------------------------
   Call saveExcelContext
   '
   ' ==============================================================================================
   ' go get the data to copy
   ' ----------------------------------------------------------------------------------------------
   Set sheetToCopyFrom = ActiveWorkbook.Sheets(pNameOfSheetToCopyFrom)
   sheetToCopyFrom.Activate
   Set lastCell = getLastCell(pRangeToCopyFrom, XlSearchOrder.xlByColumns, sheetToCopyFrom, True)
   Set rangeToCopyFrom = Range(pRangeToCopyFrom(1, 1), lastCell)
   rangeToCopyFrom.Copy
   '
   ' ==============================================================================================
   ' paste it where it goes
   ' ----------------------------------------------------------------------------------------------
   Set sheetToCopyTo = ActiveWorkbook.Sheets(pNameOfSheetToCopyTo)
   sheetToCopyTo.Activate
   Set lastCell = getLastCell(pRangeToCopyTo, XlSearchOrder.xlByColumns, ActiveWorkbook.Sheets(pNameOfSheetToCopyTo), True)
   Set rangeToCopyTo = lastCell.Offset(1, 0)
   rangeToCopyTo.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
   '
   ' ==============================================================================================
   ' unselect the copy ranges and go home
   ' ----------------------------------------------------------------------------------------------
   sheetToCopyFrom.Activate
   Call gotoNameNoFreeze("A1")
   sheetToCopyTo.Activate
   Call gotoNameNoFreeze("A1")
   '
   ' ==============================================================================================
   ' restore the calling context
   ' ----------------------------------------------------------------------------------------------
   Call restoreExcelContext
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine copys all of pRangeToCopyFrom to pRangeToCopyTo, completely overwritting pRangeToCopyTo
'
' param
'    pNameOfSheetToCopyFrom is the sheet containing pRangeToCopyFrom
' param
'    pRangeToCopyFrom is the Range to copy to the end of pRangeToCopyTo
' param
'    pNameOfSheetToCopyTo is the sheet containing pRangeToCopyTo
' param
'    pRangeToCopyTo is the Range to copy pRangeToCopyFrom's data to the end of
' -----------------------------------------------------------------------------------------------------------
Public Sub replaceRangeWithRange(pNameOfSheetToCopyFrom As String, pRangeToCopyFrom As Range, pNameOfSheetToCopyTo As String, pRangeToCopyTo As Range)
   Dim lastCell        As Range
   Dim rangeToCopyFrom As Range
   Dim rangeToCopyTo   As Range
   Dim sheetToCopyFrom As Worksheet
   Dim sheetToCopyTo   As Worksheet
   '
   ' ==============================================================================================
   ' save the calling context and establish the right context for this routine
   ' ----------------------------------------------------------------------------------------------
   Call saveExcelContext
   '
   ' ==============================================================================================
   ' clear out everything in the Range to copy to
   ' ----------------------------------------------------------------------------------------------
   Call clearRangeKeepFormats(pRangeToCopyTo)
   '
   ' ==============================================================================================
   ' go get the data to copy
   ' ----------------------------------------------------------------------------------------------
   Set sheetToCopyFrom = ActiveWorkbook.Sheets(pNameOfSheetToCopyFrom)
   sheetToCopyFrom.Activate
   Set lastCell = getLastCell(pRangeToCopyFrom, XlSearchOrder.xlByColumns, sheetToCopyFrom, True)
   Set rangeToCopyFrom = Range(pRangeToCopyFrom(1, 1), lastCell)
   rangeToCopyFrom.Copy
   '
   ' ==============================================================================================
   ' paste it where it goes
   ' ----------------------------------------------------------------------------------------------
   Set sheetToCopyTo = ActiveWorkbook.Sheets(pNameOfSheetToCopyTo)
   sheetToCopyTo.Activate
   Set rangeToCopyTo = pRangeToCopyTo(1, 1)
   rangeToCopyTo.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
   '
   ' ==============================================================================================
   ' unselect the copy ranges and go home
   ' ----------------------------------------------------------------------------------------------
   sheetToCopyFrom.Activate
   Call gotoNameNoFreeze("A1")
   sheetToCopyTo.Activate
   Call gotoNameNoFreeze("A1")
   '
   ' ==============================================================================================
   ' restore the calling context
   ' ----------------------------------------------------------------------------------------------
   Call restoreExcelContext
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine sorts the rows rows in the specified Range by column.  The columns on which to sort are
' listed in an array parameter - pSortColumns.
'
' Here's an example of how to use This subroutine:
'    Dim theRange As Range
'    Set theRange = Range("FullRangeToDedup")
'    Dim sortColumns(1 To 3) As Long
'    sortColumns(1) = 2
'    sortColumns(2) = 3
'    sortColumns(3) = 4
'    Call sortRangeRows(theRange, sortColumns)
'
' param
'    pRange is the Range of data to sort
' param
'    pSortColumns is an array containing column numbers.  The column numbers identify the row columns on
'    which the specified Range will be sorted.  The column numbers in the sort array are assumed to be in
'    descending order of sort importance; ie., the first column number is the most important column and the
'    last column number is the least important.  The order in which the data is sorted is least important
'    first to most important last. Therefore, the column numbers in the array are used in reverse order.
' -----------------------------------------------------------------------------------------------------------
Public Sub sortRangeRows(pRange As Range, pSortColumns() As Long)
   pRange.Select
   Call sortSelectionRows(pSortColumns)
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine sorts the rows rows in the selected Range by column.  The columns on which to sort are
' listed in an array parameter - pSortColumns.
'
' Here's an example of how to use This subroutine:
'    Dim theRange As Range
'    Set theRange = Range("FullRangeToDedup")
'
'    Dim sortColumns(1 To 3) As Long
'    sortColumns(1) = 2
'    sortColumns(2) = 3
'    sortColumns(3) = 4
'
'    theRange.Select
'    Call sortSelectionRows(sortColumns)
'
' param
'    pRange is the Range of data to sort
' param
'    pSortColumns is an array containing column numbers.  The column numbers identify the row columns on
'    which the specified Range will be sorted.  The column numbers in the sort array are assumed to be in
'    descending order of sort importance; ie., the first column number is the most important column and the
'    last column number is the least important.  The order in which the data is sorted is least important
'    first to most important last. Therefore, the column numbers in the array are used in reverse order.
' -----------------------------------------------------------------------------------------------------------
Public Sub sortSelectionRows(pSortColumns() As Long)
   Dim sortRange   As Range
   Dim arrayIdx    As Long
   Dim colIdx      As Long
   '
   ' ==============================================================================================
   ' if autofiltering is on in the active sheet, turn it off.
   ' ----------------------------------------------------------------------------------------------
   If (ActiveWorkbook.ActiveSheet.AutoFilterMode) Then ActiveWorkbook.ActiveSheet.AutoFilterMode = False
   '
   ' ==============================================================================================
   ' toggle it.  since we turned of autofiltering above, we know that toggling it here turns it
   ' ON, not OFF
   ' ----------------------------------------------------------------------------------------------
   Selection.AutoFilter
   '
   ' ==============================================================================================
   ' sort the selected Range so that duplicate rows are adjacent to each other.  The column
   ' numbers in the sort array are assumed to be in descending order of sort importance; ie., the
   ' first column number is tha most important column and the last column number is the least
   ' important. The order in which the data is sorted is least important first to most important
   ' last. Therefore, the column numbers in the array are used in reverse order.
   ' ----------------------------------------------------------------------------------------------
   With ActiveWorkbook
      For arrayIdx = UBound(pSortColumns) To LBound(pSortColumns) Step -1
         colIdx = pSortColumns(arrayIdx)
         Set sortRange = Range(Selection(1, colIdx), Selection(Selection.Rows.count, colIdx))
         .ActiveSheet.AutoFilter.Sort.SortFields.Clear
         .ActiveSheet.AutoFilter.Sort.SortFields.Add _
            Key:=sortRange, _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending, _
            DataOption:=xlSortNormal
         With .ActiveSheet.AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
         End With
      Next arrayIdx
   End With
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine finds and removes duplicate rows in the specified range.  It does so by first sorting the
' specified Range by column.  The columns on which to sort are listed in an array parameter - pSortColumns.
' Upon identifying a row as a duplicate, it erases the data from the columns of that row.  The columns to
' erase are listed in an array parameter - pEraseColumns.
'
' The sorted Range can be restored to its original order if it has a row number column in it containing
' constant values representing the row number (not formulas).
'
' Here's an example of how to use This subroutine:
'    Dim theRange As Range
'    Set theRange = Range("FullRangeToDedup")
'
'    Dim sortColumns(1 To 3) As Long
'    sortColumns(1) = 2
'    sortColumns(2) = 3
'    sortColumns(3) = 4
'
'    Dim eraseColumns(1 To 6) As Long
'    eraseColumns(1) = 1
'    eraseColumns(2) = 2
'    eraseColumns(3) = 3
'    eraseColumns(4) = 4
'    eraseColumns(5) = 5
'    eraseColumns(6) = 9
'
'    Call dedupRangeRows(theRange, sortColumns, eraseColumns)
'
' param
'    pRange is the Range of data to eliminate duplicate rows from
' param
'    pSortColumns is an array containing column numbers.  The column numbers identify the row columns on
'    which the specified Range will be sorted.  The column numbers in the sort array are assumed to be in
'    descending order of sort importance; ie., the first column number is the most important column and the
'    last column number is the least important.  The order in which the data is sorted is least important
'    first to most important last. Therefore, the column numbers in the array are used in reverse order.  The
'    column numbers in the sort array also identify which columns to use to determine if a row is a
'    duplicate.  The data in each of the sort columns is compared to the corresponding columns' data in the
'    previous row.  If all the sort columns data matches, the row is determined to be a duplicate.  All other
'    data columns in the row are ignored during the process of duplicate identification.
' param
'    pEraseColumns is an array containing column numbers.  The column numbers identify the row columns for
'    which the data will be erased if the row is determined to be a duplicate.  The column numbers in the
'    erase array are not assumed to be in any particular order and their ordering does not effect the results
'    of This subroutine.
' -----------------------------------------------------------------------------------------------------------
Public Sub dedupRangeRows(pRange As Range, pSortColumns() As Long, pEraseColumns() As Long)
   pRange.Select
   Call dedupSelectionRows(pSortColumns, pEraseColumns)
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine finds and removes duplicate rows in the selected range.  It does so by first sorting the
' selected Range by column.  The columns on which to sort are listed in an array parameter - pSortColumns.
' Upon identifying a row as a duplicate, it erases the data from the columns of that row.  The columns to
' erase are listed in an array parameter - pEraseColumns.
'
' The sorted Range can be restored to its original order if it has a row number column in it containing
' constant values representing the row number (not formulas).
'
' Here's an example of how to use This subroutine:
'    Dim theRange As Range
'    Set theRange = Range("FullRangeToDedup")
'
'    Dim sortColumns(1 To 3) As Long
'    sortColumns(1) = 2
'    sortColumns(2) = 3
'    sortColumns(3) = 4
'
'    Dim eraseColumns(1 To 6) As Long
'    eraseColumns(1) = 1
'    eraseColumns(2) = 2
'    eraseColumns(3) = 3
'    eraseColumns(4) = 4
'    eraseColumns(5) = 5
'    eraseColumns(6) = 9
'
'    theRange.Select
'    Call dedupSelectionRows(sortColumns, eraseColumns)
'
' param
'    pSortColumns is an array containing column numbers.  The column numbers identify the row columns on
'    which the selected Range will be sorted.  The column numbers in the sort array are assumed to be in
'    descending order of sort importance; ie., the first column number is the most important column and the
'    last column number is the least important.  The order in which the data is sorted is least important
'    first to most important last. Therefore, the column numbers in the array are used in reverse order.  The
'    column numbers in the sort array also identify which columns to use to determine if a row is a
'    duplicate.  The data in each of the sort columns is compared to the corresponding columns' data in the
'    previous row.  If all the sort columns data matches, the row is determined to be a duplicate.  All other
'    data columns in the row are ignored during the process of duplicate identification.
' param
'    pEraseColumns is an array containing column numbers.  The column numbers identify the row columns for
'    which the data will be erased if the row is determined to be a duplicate.  The column numbers in the
'    erase array are not assumed to be in any particular order and their ordering does not effect the results
'    of This subroutine.
' -----------------------------------------------------------------------------------------------------------
Public Sub dedupSelectionRows(pSortColumns() As Long, pEraseColumns() As Long)
   Dim arrayIdx    As Long
   Dim rowIdx      As Long
   Dim colIdx      As Long
   Dim isDuplicate As Boolean
   '
   ' ==============================================================================================
   ' first, sort the selected Range so that duplicate rows are adjacent to each other
   ' ----------------------------------------------------------------------------------------------
   Call sortSelectionRows(pSortColumns)
   '
   ' ==============================================================================================
   ' iterate through the rows looking for adjacent duplicates.  when one is found, erase the data
   ' in that row
   ' ----------------------------------------------------------------------------------------------
   For rowIdx = 2 To Selection.Rows.count
      If (IsEmpty(Selection(rowIdx, 2))) Then Exit For
      '
      ' ==============================================================================================
      ' determine if this row is a duplicate row by comparing all its sort columns to the
      ' corresponding columns in the previous row
      ' ----------------------------------------------------------------------------------------------
      isDuplicate = True
      For arrayIdx = UBound(pSortColumns) To LBound(pSortColumns) Step -1
         colIdx = pSortColumns(arrayIdx)
         isDuplicate = isDuplicate And (UCase(Selection(rowIdx, colIdx)) = UCase(Selection(rowIdx - 1, colIdx)))
      Next arrayIdx
      '
      ' ==============================================================================================
      ' if this row is a duplicate, erase all the erase columns of this row
      ' ----------------------------------------------------------------------------------------------
      If (isDuplicate) Then
         For arrayIdx = LBound(pEraseColumns) To UBound(pEraseColumns)
            colIdx = pEraseColumns(arrayIdx)
            Selection(rowIdx, colIdx) = Empty
         Next arrayIdx
      End If
   Next rowIdx
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This displays text in a pop-up message box that automatically closes after pSecondsToWait seconds
'
' param
'    pMessage is the message to display in the message box
' param
'    ppSecondsToWait is a numeric value indicating the number of seconds you want the pop-up message box
'    displayed.
' -----------------------------------------------------------------------------------------------------------
Public Sub messageBoxTimed(pMessage As String, pSecondsToWait As Long)
   Dim InfoBox As Object
   Set InfoBox = CreateObject("WScript.Shell")
   '
   ' ==============================================================================================
   ' Set the Infobox to close after pSecondsToWait seconds
   ' ----------------------------------------------------------------------------------------------
   Select Case InfoBox.Popup(pMessage, pSecondsToWait, "Message")
      Case 1, -1
      Exit Sub
   End Select
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function copies a Range to an array.  If a destination array is passed in, the Range is copied to it,
' otherwise, a new array is created and that is used as the destination array.
'
' If a destination array is passed in, only the portion of the source Range that overlays the dimensions of
' the destination array are copied to the destination array.  If no destination array is passed in, a new
' array with dimensions matching the source Range is created and used as the destination array.
'
' return
'    This function returns a reference to the destination array
'
' param
'    pRange is the Range that data is copied from
' param (optional)
'    pArray is an optional parameter and is the array that data is copied to
' -----------------------------------------------------------------------------------------------------------
Public Function rangeToArray(pRange As Range, Optional pArray As Variant) As Variant
   Dim i As Long
   Dim j As Long
   Dim returnArray() As Variant

   If (IsMissing(pArray)) Then
      ReDim returnArray(1 To pRange.Rows.count, 1 To pRange.Columns.count)
   Else
      ReDim returnArray(1 To UBound(pArray, 1), 1 To UBound(pArray, 2))
   End If

   For i = 1 To pRange.Rows.count
      If (i > UBound(returnArray, 1)) Then Exit For
      For j = 1 To pRange.Columns.count
         If (j > UBound(returnArray, 2)) Then Exit For
         returnArray(i, j) = pRange(i, j)
      Next j
   Next i

   rangeToArray = returnArray
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function copies an array to a range.
'
' Only the portion of the source array that overlays the dimensions of the destination Range are copied to
' the destination range.
'
' param
'    pArray is the array that data is copied from
' param
'    pRange is the Range that data is copied to
' -----------------------------------------------------------------------------------------------------------
Public Sub arrayToRange(pArray() As Variant, pRange As Range)
   Dim i As Long
   Dim j As Long
   For i = 1 To UBound(pArray, 1)
      If (i > pRange.Rows.count) Then Exit For
      For j = 1 To UBound(pArray, 2)
         If (j > pRange.Columns.count) Then Exit For
         pRange(i, j) = pArray(i, j)
      Next j
   Next i
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine deletes existing conditional formatting with a specific formula from the cells in the
' specified range.  This is used to prevent multiple layers of the same rule being built up in the same
' cells
'
' param
'    pRange is the Range of cells to check for the specified conditional format rule
' param
'    pFormula1 is the rule to search for and delete
' -----------------------------------------------------------------------------------------------------------
Public Sub deleteSpecificConditionalFormat(pRange As Range, pFormula1 As String)
   Dim rule                  As Long
   Dim conditionalRulesCount As Long

   conditionalRulesCount = pRange.FormatConditions.count
   For rule = conditionalRulesCount To 1 Step -1
      With pRange.FormatConditions(rule)
         If (.formula1 = pFormula1) Then
            .Delete
         End If
      End With
   Next rule
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine lists all the functions and subroutines in all the modules in the active workbook. It
' creates a new sheet as the last sheet in the workbook to write the list out to.
' -----------------------------------------------------------------------------------------------------------
Public Sub listAllProcedures()
   '
   ' ==============================================================================================
   ' create a clean new sheet for this
   ' ----------------------------------------------------------------------------------------------
   Dim sht As Worksheet
   Set sht = newSheet("ProcedureList")
   '
   ' ==============================================================================================
   ' make damn sure we're not on some other sheet except our new one before we start writting all
   ' over it
   ' ----------------------------------------------------------------------------------------------
   sht.Activate
   sht.Select
   Range("A1").Select
   If (Not startsWith(ActiveSheet.Name, "ProcedureList")) Then
      raiseException "Incorrect ActiveSheet", "ListWorkSheetNames()", "M009_ExcelUtils"
   End If
   '
   ' ==============================================================================================
   ' iterate thru all the modules listing the procedures in each one
   ' ----------------------------------------------------------------------------------------------
   Dim startCell As Range
   Dim vbc       As VBComponent
   Set startCell = Range("A1")
   For Each vbc In ThisWorkbook.VBProject.VBComponents
      If vbc.Type = vbext_ct_StdModule Then
         If ((vbc.Name Like "M###_*") Or (vbc.Name Like "A###_*")) Then
            With ThisWorkbook.VBProject.VBComponents
               Call listModuleProcedures(vbc.Name, startCell)
            End With
         End If
      End If
   Next
End Sub



' ==============================================================================================
' Now here's a kludge, and it is a result of one of the biggest pain's in the ass of
' programming Excel applications: there are several VBA-coded functions used on this Workbook's
' sheets that do not take any parameters from other cells in the sheet in which they are
' embedded. Consequently, Excel does not know to call them when the page is recalculated. To
' force Excel to call functions like this when the page they are imbeded in is recalculated,
' the functions must be marked as "Application.Volatile". Unfortunately, marking them as such
' is done by placing a call to Application.Volatile() INSIDE the functions themselves. So Excel
' cannot see the marker until it has actually run the function one time and it doesn't know to
' run the function the first time because it hasn't been run already to allow Excel to see the
' marker. (confused? It's chicken and egg thing.) Therefore, Excel has to be forced to run the
' functions one time (the first time) for it to find out that they are volatile. After it knows
' they are volatile, it will run them whenever it recalculates the sheet in which calls to the
' functions are embedded.
'
' Microsoft's documentation of Application.Volatile() contains the explanation and it goes like
' this:
'
'    http://msdn.microsoft.com/en-us/library/office/ff195441(v=office.15).aspx
'
'       Application.Volatile Method (Excel)
'
'          Marks a user-defined function as volatile. A volatile function must be recalculated
'          whenever calculation occurs in any cells on the worksheet. A nonvolatile function is
'          recalculated only when the input variables change. This method has no effect if it's
'          not inside a user-defined function used to calculate a worksheet cell.
'
'          This example marks the user-defined function "My_Func" as volatile. The function
'          will be recalculated whenever calculation occurs in any cells on the worksheet on
'          which this function appears.
'
'             Function My_Func()
'                Application.Volatile
'             End Function
'
' So what fireUpVolatileFunctions() does is go through the range of cells containing volatile
' functions and forces those functions to be executed.
'
' NOTE: It currently does not work. I've tried a number of different approaches but have not
'       found a way to make it work. I'm keeping it here instead of deleting it simply to keep
'       keep comment section around someplace where I can reference it.
'
' param
'    pRange is a range of cells to give the treatment that forces the volatile functions within
'    to be executed. Every cell in the range will have its value copied to itself and if there
'    is a function call embedded in the cell, Excel will execute it.
' ----------------------------------------------------------------------------------------------
Public Sub fireUpVolatileFunctions(ByRef pRange As Range)
   pRange.Calculate  ' this doesn't work.
End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This function launches the Excel color picker and returns the Rgb Long color value selected by the user of
' the color picker.
'
' return
'    This function returns the color selected by the user of the Excel color picker.
'
' param (optional)
'    pCurrentRgb is the color to show as the current color when the color picker is launched. If no argument
'    value is passed in through this optional parameter, then the value defaults to the background color
'    of an undefined color in the color picker -- which is the dialog background color.
' -----------------------------------------------------------------------------------------------------------
Public Function RgbFromExcelColorPicker(Optional pCurrentRgb As Double = xlNone) As Double
   Const dialogRgb             As Long = 13160660  ' background color of dialogue
   Const lastColorPaletteIndex As Long = 32        ' index of last custom color in palette
   '
   ' ==============================================================================================
   ' original color of color index 32
   ' ----------------------------------------------------------------------------------------------
   Dim lastColorPaletteIndexRgb As Double
   '
   ' ==============================================================================================
   ' RGB values of the color that will be displayed in the dialogue as "Current" color
   ' ----------------------------------------------------------------------------------------------
   Dim rgbR As Integer
   Dim rgbG As Integer
   Dim rgbB As Integer
   '
   ' ==============================================================================================
   ' save original palette color, because we don't really want to change it
   ' ----------------------------------------------------------------------------------------------
   lastColorPaletteIndexRgb = ActiveWorkbook.colors(lastColorPaletteIndex)
   '
   ' ==============================================================================================
   ' get RGB values of background color, so the "Current" color looks empty
   ' ----------------------------------------------------------------------------------------------
   If pCurrentRgb = xlNone Then
      Call RGBfromRgb(dialogRgb, rgbR, rgbG, rgbB)
   '
   ' ==============================================================================================
   ' get RGB values of pCurrentRgb
   ' ----------------------------------------------------------------------------------------------
   Else
      Call RGBfromRgb(pCurrentRgb, rgbR, rgbG, rgbB)
   End If
   '
   ' ==============================================================================================
   ' call the color picker dialogue and if it comes bac as True, "OK" was pressed, so Excel
   ' automatically changed the palette. Return the new color from the palette and then reset the
   ' palette color to its original value
   ' ----------------------------------------------------------------------------------------------
   If Application.Dialogs(xlDialogEditColor).Show(lastColorPaletteIndex, rgbR, rgbG, rgbB) Then
      RgbFromExcelColorPicker = ActiveWorkbook.colors(lastColorPaletteIndex)
      ActiveWorkbook.colors(lastColorPaletteIndex) = lastColorPaletteIndexRgb
   '
   ' ==============================================================================================
   ' else, "Cancel" was pressed and palette wasn't changed. Return old color (or xlNone if no color
   ' was passed to the function)
   ' ----------------------------------------------------------------------------------------------
   Else
      RgbFromExcelColorPicker = pCurrentRgb
   End If
End Function



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
'  This function is equivalent to the Excel VLOOKUP() function with the following exception:
'     If the pValueToLookUp cannot be found in the first column of table_array, this function returns a zero
'     (0) instead of "#N/A".
'
'  Note:
'     -- When searching text values in the first column of pTableToLookIn, ensure that the data in the first
'        column of pTableToLookIn does not contain leading spaces, trailing spaces, inconsistent use of
'        straight ( ' or " ) and curly ( � or �) quotation marks, or nonprinting characters. In these cases,
'        VLOOKUP might return an incorrect or unexpected value.
'
'        -- For more information, see CLEAN function and TRIM function.
'
'     -- When searching number or date values, ensure that the data in the first column of pTableToLookIn is
'        not stored as text values. In this case, VLOOKUP might return an incorrect or unexpected value.
'
'     -- If pApproximateMatchAllowed is FALSE and pValueToLookUp is text, you can use the wildcard characters
'        � the question mark (?) and asterisk (*) � in pValueToLookUp. A question mark matches any single
'        character; an asterisk matches any sequence of characters. If you want to find an actual question
'        mark or asterisk, type a tilde (~) preceding the character.
'
' return
'    This function searches the first column of a range of cells for a value, and then, if it finds the value
'    in the first column, it returns the value from the cell in any column on the same row where it found the
'    searched for value in the first column.
'
' param
'     pValueToLookUp is the value to search for in the first column of pTableToLookIn. The pValueToLookUp
'     argument can be a value or a reference. If the value you supply for the pValueToLookUp argument is
'     smaller than the smallest value in the first column of the pTableToLookIn argument, VLookUpOrZero
'     returns zero (0) instead of VLOOKUP's #N/A error value.
' param
'     pTableToLookIn is the range of cells that contains the data. You can use a reference to a range (for
'     example, A2:D8), or a range name. The values in the first column of pTableToLookIn are the values
'     searched in order to find pValueToLookUp. These values can be text, numbers, or logical values.
'     Uppercase and lowercase text are equivalent.
' param
'     pColToGetValueFrom is the column number (column numbers start at 1) in the pTableToLookIn argument from
'     which the matching value must be returned. A pColToGetValueFrom argument of 1 returns the value in the
'     first column in pTableToLookIn; a pColToGetValueFrom of 2 returns the value in the second column in
'     pTableToLookIn, and so on.
'
'     If the pColToGetValueFrom argument is:
'     -- Less than 1, VLookUpOrZero returns the #VALUE! error value.
'     -- Greater than the number of columns in pTableToLookIn, VLookUpOrZero returns the #REF! error value.
' param (optional)
'     pApproximateMatchAllowed is a logical value that specifies whether you want VLookUpOrZero to find an
'     exact match or an approximate match: If pApproximateMatchAllowed is either TRUE or is omitted, an exact
'     or approximate match is returned. If an exact match is not found, the next largest value that is less
'     than pValueToLookUp is returned.
'     -- Important If pApproximateMatchAllowed is either TRUE or is omitted, the values in the first column of
'        pTableToLookIn must be placed in ascending sort order; otherwise, VLookUpOrZero might not return the
'        correct value.
'        -- For more information, see Sort data in a range or table.
'     -- If pApproximateMatchAllowed is FALSE, the values in the first column of pTableToLookIn do not need to
'        be sorted.
'     -- If the pApproximateMatchAllowed argument is FALSE, VLookUpOrZero will find only an exact match. If
'        there are two or more values in the first column of pTableToLookIn that match the pValueToLookUp, the
'        first value found is used. If an exact match is not found, the error value #N/A is returned.
' -----------------------------------------------------------------------------------------------------------
Public Function VLookupOrZero(pValueToLookUp As Variant, pTableToLookIn As Variant, pColToGetValueFrom As Variant, Optional pApproximateMatchAllowed As Variant = True) As Variant
   VLookupOrZero = Application.VLookup(pValueToLookUp, pTableToLookIn, pColToGetValueFrom, pApproximateMatchAllowed)
   If (WorksheetFunction.IsNA(VLookupOrZero)) Then
      VLookupOrZero = 0
   End If
End Function



' =====================================================================================================================
' @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@[  Private Routines  ]@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
' =====================================================================================================================



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine lists all the functions and subroutines in the specified module.
'
' param
'    pModuleName is the name of the module whose procedures are listed
' param (input / output)
'    pStartCell is the cell where the first procedure name is written, each subsequent name is written to
'    the cell on the next row down. When this subroutine exits, pStartCell is pointing to the cell that
'    should be used in the next call to this subroutine as the starting cell.
' -----------------------------------------------------------------------------------------------------------
Private Sub listModuleProcedures(ByVal pModuleName As String, ByRef pStartCell As Range)
   Dim VBProj As VBIDE.VBProject
   Dim VBComp As VBIDE.VBComponent
   Dim codeMod As VBIDE.codeModule
   Dim lineNum As Long
   Dim numLines As Long
   Dim procName As String
   Dim procKind As VBIDE.vbext_ProcKind

   Set VBProj = ActiveWorkbook.VBProject
   Set VBComp = VBProj.VBComponents(pModuleName)
   Set codeMod = VBComp.codeModule

   With codeMod
      lineNum = .CountOfDeclarationLines + 1
      Do Until lineNum >= .CountOfLines
         procName = .ProcOfLine(lineNum, procKind)
         pStartCell.value = procName
         pStartCell(1, 2).value = procKindString(procKind)
         lineNum = .ProcStartLine(procName, procKind) + .ProcCountLines(procName, procKind) + 1
         Set pStartCell = pStartCell(2, 1)
      Loop
   End With

End Sub



' ===========================================================================================================
' ===========================================================================================================
' ===========================================================================================================
' This subroutine writes writes into the active cell a readable version of a procedure type
'
' param
'    pProcKind is the procedure type to be translated into something more readable.
' -----------------------------------------------------------------------------------------------------------
Private Function procKindString(ByVal pProcKind As VBIDE.vbext_ProcKind) As String
   Select Case pProcKind
      Case vbext_pk_Get
         procKindString = "Property Get"
      Case vbext_pk_Let
         procKindString = "Property Let"
      Case vbext_pk_Set
         procKindString = "Property Set"
      Case vbext_pk_Proc
         procKindString = "Sub Or Function"
      Case Else
         procKindString = "Unknown Type: " & CStr(pProcKind)
   End Select
End Function