My VBA Code Library

Function IsArrayAllocated

Returns True if arr is a dimentioned array

Function Get2DArrayIndexByValue

Returns the index of varValue along lngMatchRowColIndex row (if intDimension=1)/column (if intDimension=2), in a 2-D array.

Function Get1DArrayIndexByValue

Returns the index (1-based) of varValue in arr.

Function TransposeArray

Transpose a variant array.

Function SumArrayColumn

Sums all numeric values in column intColIndex of 2D array. 1-D is rows.

Function InProduction

Returns true if production flag is set.

Sub RestoreState

Restores the application state to normal:
  - Protect SheetToProtect (if passed)
  - Protects the Workbook if in Production mode (see InProductoin function)
  - Sets the calculation mode to Automatic
  - Activates the Workbook events listener
  - Restores the mouse cursoe to its default icon
  - Turns screen updating on

Function ExportTableData

Exports an Excel Table as a csv file.

Function ExportSheetData

Export all rows within Worksheet organized in columns and rows starting at cell A1.

Function ExportArrayData

Exports an array as a csv file.

Function ImportTableData

Imports a csv file into an Excel table.

Function ImportSheetData

Imports a csv file into an Excel Worksheet.

5 more functions serving the above.

Sub DeleteFile

Deletes the file (if exists).

Function StripFileNameFromPath

Returns the file name stripped off a full path.

Function strLegalFileName

Converts a file name into a valid Windows file name.

Function DeleteOldFilesInFolder

Deletes all files older than days specified.

Function GetDrivesList

Returns the available Disk Drives as a comma separated string, e.g.: C,D,F.

Function MoveFile

Move a file between folders.

Function CopyFile

Copy a file to another folder.

Function FileExists

Checks if a file exists.

Sub Notify

Presents a message in the bottom status bar.

Sub NotifyOff

Clears the bottom status bar.

Function IsPrinterReady

Returns True if printer is turned on and ready.

Sub PrintWorksheet

Prints out a Worksheet if printer is ready.

Sub SetRepeatingRowsPrintedSheet

Sets rows as repeating rows in PageSetup for printing.

Sub UnprotectWorkbook

Unprotects a Workbook.

Sub ProtectWorkbook

Protects a Workbook.

Sub UnprotectSheet

Unprotects a single sheet.

Sub ProtectSheet

Protects a single sheet

Sub UnprotectAllSheets

Unprotects all Worksheets in a Workbook.

Sub ProtectAllSheets

Protects all Worksheets in a Workbook.

Sub NumberFormatReportDataColumn

Formats a column of cells.

Sub NumberFormatTableDataColumn

Formats a column of cells in a table.

Sub SetWrapTextToRange

Applies (or removes) WrapText to a range of cells.

Sub DrawBorderOutline

Draws border around a range: sides, color, weight.

Sub ColorFormatReportHeaderRow

Apply formatting to a row of a report.

Sub ColorFormatReportColumns

Applies fill and/or font color to a column(s) of cells.

Sub ConditionalFormatReportColumns

Applies fill and/or font color to a column(s) of cells as a conditional formatting formula.

Sub ClearReportData

Clears content and formatting from all cells of a report.

Sub FindText

Invokes the find (search) of Excel on the current Worksheet to find a text string.

Function GetColumnLetter

Returns the Worksheet column letter of a column number (index).

Sub ActivateStartupSheet

Activate the startup Worksheet specified in the Settings.

Function DoesSheetRangeNameExistsInSheet

Checks if a Worksheet-scope range name is defined.

Sub HideFlaggedSheets

Hides all Worksheets flagged with a Worksheet-scope range name.

Sub HideSheet

Hide a single Worksheet.

Sub UnhideSheet

Unhide a single Worksheet.

Function IsSheetHidden

Checks if a Worksheet is currently hidden.

Function DoesSheetExists

Checks if a Worksheets exists and visible.

Function OpenWorksheetAsNewExcel

Copies a Worksheet to a newly created Workbook.

Function OpenExcelFile

Opens an Excel file if not already open.

Function IsWorkBookOpen

Checks if a Workbook is currently open.

Function GetNewWorksheet

Create a new Worksheet.

Sub CloseWorkbook

Closes a Workbook.

Function GetWindowsUserName

Returns the logged-in Windows user name.

Function GetWindowsHostName

Returns the computer name running this application from the Windows system.

Function GetWindowsCountryCode

Returns the local PC country code (e.g. 1 = United States).

Function GetWindowsTempFolder

Returns the full path to the Windows temporary folder.

Sub WaitSeconds

Pauses execution of the program for a specified number of seconds.

Sub AddTableRow

Add a new row to a table.

Sub DeleteTableRow

Deletes row of current cell within a table.

Function AddTableRowWithFields

Adds a new row at the end of the table and fills its fields with Values array items.

Sub UpdateWorksheetRowWithFields

Fills a row of Fields in a Worksheet with values.

Sub DeleteWorksheetRowByWorksheetRow

Deletes a complete Worksheet row by its index.

Sub DeleteTableRowByKey

Deletes a row from a Worksheet where a Key Value is matched on a Column of a table on that Worksheet.

Sub RemoveTableLastRow

Resizes a table by one row and clears content of remaining row.

Function UpdateTableColumnByMatchedKey

Updates the column value of one or more rows in a table matching a column key value.

Sub ResizeTable

Resizes a table to a specifies number of rows and columns.

Function GetTableRows

Returns the number of data rows in a table.

Function GetTableColumns

Returns the number of columns in a table.

Function GetRecordRow

Returns the Worksheet row in which a Key is matched on a specific Column of a given range, or creates a new row if not matched.

Function GetNewRow

Adds a new row at the end of a table and returns the Worksheet row number of the newly added row.

Function GetKeyMatchedTableRow

Returns the Worksheet row in which a Key is matched on a Column of a given table.

Function GetSheetColumnIndexByTableColumn

Returns the Worksheet column in which a column header is found in a table.

Sub SortTable

Sorts a table in Ascending or Descending order.

Public Sub ClearTableRows

Deletes rows of a table except first rows specified.

Public Sub ClearTable

Clears contents of non-formula cells in a table and deletes all rows except the first one.

Function GetTableValueByIndex

Returns a value within a table at the intersection of the table row index and table column name.

Function GetTableValueByColumnValue

A Lookup implementation on a table.

Function UpdateTableCellByMatchedKeyAndColumnName

Looks up a row in a table by a key value, and updates this row's cell in a specified column.

Function LoadTableColumnIntoArray

Loads all rows of a specific column in a table into an array.

Sub ReplaceValueInColumnCells

Find & Replace in cells of a table column.

Public Sub ApplyArrayToTable

Resizes a table to fit the array rows and columns and throws the array data into the resized table.

Function GetTableRowIndexForUpdate

Returns the table index row of the active cell.

Sub ClearTableFilters

Resets all filters of a table showing all table data.

Function DialogLogin

Pops up a login dialog box to the user.

Sub DialogClose

Pops up a message box with a single "Close" button and a warning icon.

Function DialogInput

Pops up a dialog requesting some input from the user.

Function DialogYesNo

Pops up a message box with a "Yes" and "No" buttons.

Sub DialogConfirmed

Pops up a message box with a single "Close" button and a confirmation icon.

Sub LimitEntryLength

Controls user input length in a text control on a user form.

Function GetCellSheetName

Returns Worksheet name of active cell.

Function GetCellValue

Returns value of active cell.

Function GetCellRow

Returns Worksheet row of active cell.

Function GetCellColumn

Returns Worksheet column index of active cell.

Function LookupColumnValueOfActiveCell

Vlookup returning the value of intersection of current active cell row and a Worksheet column.

Function LookupRowValueOfActiveCell

Hlookup returning the value of intersection of current active cell column and a Worksheet row.

Sub ScreenViewFull

Expands Excel data area to full screen.

Sub ScreenViewRegular

Restore Excel data area to regular size.

Sub ShowOrHideWorksheetsHeadings

Show or hide Worksheet rows and columns headings (numbers and letters).

Sub CloseApplication

Closes the Excel application or just the active Workbook if more are open).