mod_ExcelAPIs

Download this module here!

This module contains many useful functions for interacting with Excel. Below are brief descriptions of what each function does, and how I personally use them.

ActiveRow() As Long

• Returns the row number of the currently selected cell.

ActiveCol() As Long

• Returns the column number of the currently selected cell.

GetLastRow(ByRef TargetWorksheet As Worksheet, ByVal ColumnNo As Long) As Long

• Returns the row number of the bottom-most cell, of the given column number, for the given worksheet.

GetLastCol(ByRef TargetWorksheet As Worksheet, ByVal RowNo As Long) As Long

• Returns the column number of the right-most cell, of the given row number, for the given worksheet.

GetHeader(ByRef TargetWorksheet As Worksheet, ByVal HeaderRow As Long, ByVal HeaderStr As String) As Long

• Returns the column number of a full cell value found in a given row, of a given worksheet.
• Useful if you expect columns to not be in the same order in different worksheets, but still spelled identically.

GetHeaders(ByRef TargetWorksheet As Worksheet, ByVal HeaderRow As Long, Optional CaseSensitive As Boolean) As Object

• Returns a Dictionary object containing all the cells within a given row, of a given worksheet. The cell values are stored as the keys for this dictionary, with the column numbers stored as the items. If TRUE, the case sensitive boolean will store dictionary keys as they are in the cells, or will convert all values to upper case if FALSE.
• This function is most useful if you are intending on using the GetHeader function repeatedly, as you will not need to search for a value every time you need it.

Expand(ByRef Target As Range, ByVal Direction As XlDirection) As Range

• This function returns a range reference that is expanded from a given range in the direction passed.
• This functions identically to holding CTRL+SHIFT and pressing an arrow key in Excel.
• Useful for identifying contiguous cells, or finding the first cell containing data in a given location, relative to a starting point.

PeekFileCell(ByVal FilePath As String, ByVal FileName As String, ByVal WorksheetName As String, ByVal CellRow As Long, ByVal CellCol As Long) As Variant

• This function returns a cell value from a given cell via the cell's row and column numbers, from a given worksheet, in a given workbook file.
• This function utilizes the ancient ExecuteExcel4Macro method, and thus should only be used sparingly. Its strength is not requiring the user to open a workbook to access information within it, but requires you to know exactly where that information is located. This function is also somewhat time consuming and prone to errors if used in very rapid succession.

IsWBProtected(ByRef TWB As Workbook) As Boolean

• Returns a boolean value of whether or not a given workbook is password protected.

IsWorkBookOpen(ByVal WorkbookName As String) As Boolean

• Returns a boolean value of whether or not a given workbook is currently open.

AddPicture(ByRef TargetSheet As Worksheet, ByVal Path As String, ByVal Left As Single, ByVal Top As Single, _ Width As Single, ByVal Height As Single, Optional ByVal ShapeName As String) As Shape

• This function returns a shape object that is created to host an image on a worksheet. The shape is created on the given worksheet, displaying a given image file located at the given X/Y (Left/Top) coordinates on a worksheet with the given width and height values, and optionally named via the given name argument.

CheckBoxExists(ByVal Name As String, ByRef TargetWorksheet As Worksheet) As Boolean

• This function returns a boolean value if a checkbox with the given name exists in a given worksheet.

ShapeExists(ByVal Name As String, Optional ByRef TargetWorksheet As Worksheet) As Boolean

• This function returns a boolean value if a shape with the given name exists in a given worksheet.

GetSheet(ByVal SheetName As String, Optional ByRef WB As Workbook) As Worksheet

• This function returns a worksheet reference if a worksheet with a given name exists. An optional workbook argument can also be passed for multiple workbook projects.

SheetExists(ByVal SheetName As String, Optional ByRef WB As Workbook) As Boolean

• Returns a boolean value if a worksheet with a given name exists. An optional workbook argument can also be passed for multiple workbook projects.

HasComment(ByRef TargetCell As Range) As Boolean

• Returns a boolean value is a given cell contains a comment.

CurrentCell() As Range

• Returns the range reference of the cell currently executing code.
• Useful for UDFs if a function needs to refer to the cell it is being executed from.

GetURL(ByRef Target As Range) As String

• Returns the URL of a cell if it contains a hyperlink via the Range.Hyperlinks method, or a HYPERLINK() formula.

CloseWB(ByRef TWorkbook As Workbook)

• Closes the passed workbook object without saving or displaying any notifications. (I'd written this a long time ago and never bothered to delete it from this module)

UnmergeAndFill(ByRef WorkArea As Range)

• This subroutine will unmerge a given range and fill all cells within that range with the original merged cell value. (I may edit it to return the range reference as a function, but that might be unnecessary considering you're already passing the range to it to perform this action)

LudicrousMode(ByVal Toggle As Boolean)

• This subroutine will toggle Excel settings that may slow down the execution of VBA code. (I'll need to edit this with an optional argument that allows it to save the initial state of these flags in case some of them are already enabled/disabled)