My VBA Code Library

GIFT FOR YOU!

105 GENERIC, HIGHLY USEFUL VBA FUNCTIONS SUPPORTING BUSINESS APPLICATIONS

5 GENERIC USER FORMS, WITH VBA CODE, FOR USER INTERACTION

I’m Sharing My Gold Secrets With You!

Thousands of code lines, carefully crafted over 35 years, are yours as a gift from me. These functions power robust business solutions serving my customers.

Excel VBA functions library
Fill out my online form

Each function is documented and demonstrated in action. Copy and use in your own programs now!

Excel VBA Functions Pack
VBA Dialog Box

5 fully functional User Forms are included for friendly user interaction

Tutorial: How to use the VBA Functions Library

VBA Developers are using these functions in their projects

As these functions serve my own projects – they also serve many other VBA developers’ projects!

Using Excel VBA Functions

What Functions are Included?

Yes, your are getting all of these 105 functions for FREE!

Arrays

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.

Application Control

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

Data Export/Import

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.

System Input/Output

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.

Notification

Sub Notify

Presents a message in the bottom status bar.

Sub NotifyOff

Clears the bottom status bar.

Printing

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.

Protection

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.

Reports

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.

Search & Navigation

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.

Worksheets & Workbooks

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.

Windows System

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.

Tables

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.

User Interaction

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).

IT’S A “NO-BRAINER” – GET THE VBA CODE NOW!