Excel VBA Code

Array Formulas
Use CTRL-SHIFT-ENTER when entering or editing array formulas. Excel supplies the braces.


 * AverageOnlyPositive - Average a column, discarding negative values
 * LongestText - Returns the text of the cell with the longest text
 * PrimeOrNotPrime - Checks if a value is prime
 * SumConditionally - Return only the total fax machines sold by Brown
 * SumEveryThirdRow - Adds the data in every third row

Event-Handlers

 * BeforeSave - Events triggered when saving workbook
 * '''BinaryOnly - Limits data entry to 1 or 0
 * DataTypeErrorTrap - Prevent non-numeric data from being entered in cell A1
 * DateStamp - Automatically enter today's date next to any changed cell
 * Tock / StopClock - Start and stop cell A1 updated with time every second
 * ToggleBold - Toggle cell content as bold or not bold with a double-click

Formulas

 * ConditionalMath - Add or subtract values based on a + or - symbol
 * Countdown - Display formatted difference between two date-time groups
 * DateExtensions - Add date extensions (1st, 2nd, 3rd, 4th, etc) to a cell containing today's date
 * DaysHoursMinutes - Show total time as days, hours, and minutes
 * FillOddRows - Insert asterisk on every odd row
 * LastFriday - Returns the last Friday of the month
 * Normalize - Dynamically normalize data to the range 0 -> 1
 * Spherical - Convert star data from the Cartesian to the spherical coordinate system

Functions

 * CountBold - Counts cells in range with bold format
 * DaysInMonth - Calendar function not built into Excel
 * Derivative - Returns derivative of x to power r
 * Dice - Returns result of rolling n dice of custom size
 * ExtractElement - Returns the nth element of a string with a user-defined delimiter
 * ExtractNum - Extract the numeric portion of a text cell
 * Gather - Combine elements of a range into one cell with custom delimiter
 * GetCellColor - Return the color code of the background of a referenced cell
 * GetCellFontColor - Return the color code of the character font of a referenced cell
 * GetText - Strips numeric characters out of a cell
 * InverseSum - Returns the reciprocal of the sum of a range of reciprocals
 * Number_of_Words - Returns a word count of a string
 * PrimeCount - Returns the number of primes between two numbers
 * Reverse - Reverse text in a cell
 * ShowFormula - Returns the text of the formula in a cell
 * SpellNumber - Spells out currency values
 * Synodic - Returns the synodic period between two planets
 * TopAvg - Compute average of highest n items in a range
 * XorHexStrings - Logical XOR of hex strings, with optional pad right

Hacks

 * Compare - Highlight data in set A that does not match set B
 * Convert2data - Convert a range of formulas to its present data
 * FindDupes - Highlight duplicate data
 * GrayEveryThirdRow - Apply gray background on every third row
 * IsFormula - Mark formula cells on a sheet

Procedures

 * Add3AndTile -- Add three workbooks and tile into a grid
 * AutoFill - Fill a range based on the first two values
 * AutoFitColumns -- Use VBA to resize columns to fit the data
 * BackupAsToday - Save workbook with new filename using today's date
 * Blank2Zero - Insert a value of zero in all blank cells of a selection
 * BoldPos - Make only positive values in a selection bold
 * CalendarMaker - Generate a month calendar from user input
 * CopyRegion - Copy variable-sized region from Sheet 2 to Sheet 3
 * DeleteBlankRows - Removed unused rows
 * Excel2Word - Copy data from Excel to a Word Document
 * Export2scsv - Export sheet as semicolon delimited file
 * FileSearchExample - Search hard drive for file
 * FillBlanks - Fill a column containing blanks with valid data
 * FillRnd - Fill 10 x 20 area with random numbers
 * Form2Val - Convert selected formulas to current values
 * GotoFirstEmptyRow - Select first empty row in a column
 * HighlightDupes - Highlight duplicate data in a column
 * Insert_Blank_Rows - Insert one blank row between all existing rows in a sheet
 * LastRow - Return the last used row in a worksheet.
 * ListAllSheets - Lists all sheets in a workbook
 * ListFilesInFolder - Fills column A with list of files in path A1
 * MakeCheckerboard
 * MakeSample - Builds 20 x 10 spreadsheet with sample data
 * OpenFileInA1 - Open file named in cell A1
 * OpenSheetsInRange - Open all worksheets listed in column A
 * PopulateSheet - Fill a column with file names from a directory
 * Randomize - Fill a user selected area with random data
 * RandomSort - Randomly sort a column of data
 * Recenter - Put origin at next star on list and recalculate position data
 * SaveAsA1 - Save worksheet as filename given in cell A1
 * SecondHighest - Identifies the second-highest value in a range selection
 * SendToNotepad -- Export a range to Notepad
 * '''SelectRows - Select rows using data in cells A1 and A2
 * SeparateString - Parse comma-delimited string into columns
 * Sorter - VBA callable sort procedure
 * UseNextAvailableRow - Copies three cells in column A to next unused rows in column B