Excel VBA Code

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


 * '''AverageBetweenLimits - Averages data that falls between two values
 * AverageOnlyPositive - Average a column, discarding negative values
 * AverageNonZero - Average a column, discarding non-zero values
 * FirstMaximum - Returns the address of the first maximum in a range
 * FirstMinimum - Returns the address of the first minimum in a range
 * LastMaximum - Returns the address of the last maximum in a range
 * LastMinimum - Returns the address of the last minimum in a range
 * 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
 * SumValuesBetweenLimits - Sums only those values which fall between two numbers

Conditional Formatting

 * 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

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
 * HighlightSelection - Highlight selection dynamically
 * RGB - Display the color defined by data in three cells
 * 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

 * Age - Calculate a person's age
 * ArcLen - Returns the arc length of a parabola of height a and width b
 * CompareLists - Build a new list from matching elements in two lists
 * Column2Matrix - Convert a column of data to a 4 x 4 matrix
 * ConditionalMath - Add or subtract values based on a + or - symbol
 * CountChars - Count the number of instances of the letter P, lower or upper case, in a string
 * 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
 * Determinant - Returns the determinant of a 3 x 3 square matrix
 * DaysHoursMinutes - Show total time as days, hours, and minutes
 * DeleteFirstWord - Return string without first word
 * ExponentialDecay - Determine initial population from two data points
 * EquationOfLine - Returns slope-intercept form of the equation of a line through two points
 * FeetandInches - Convert text representation of feet and inches to a numeric value
 * 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
 * TrueWind - Calculates true wind speed and direction
 * TwoLineIntercept - Returns x,y point of the intersection of two lines
 * WallisProduct - Calculates pi
 * WeightedAverages - Calculate averages where elements have differing contributions

Functions

 * CentralAngle - Returns distance between two points given as lat and long
 * CountBold - Counts cells in range with bold format
 * DaysInMonth - Calendar function not built into Excel
 * DefiniteIntegral - Evaluates an integral over a given range using a given resolution
 * Derivative - Returns derivative of x to power r
 * Dice - Returns result of rolling n dice of custom size
 * Easter - Returns the date of Easter, accurate from 1900 to 2368 CE.
 * ExtractElement - Returns the nth element of a string with a user-defined delimiter
 * ExtractNum - Extract the numeric portion of a text cell
 * Factors - Returns the factors of a number as a string
 * FirstMonday - Returns date of first workday of specified year
 * 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
 * IsLeapYear - Returns TRUE if specified year is a leap year
 * LastDayofMonth - Returns the date of the last specified day of a specified month
 * Lotto - Return z unique values between x and y
 * 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
 * Thanksgiving - Returns the date of Thanksgiving for a given year
 * TopAvg - Compute average of highest n items in a range
 * Vcs - Returns circular orbital velocity for a given radius and standard gravitational parameter
 * XorHexStrings - Logical XOR of hex strings, with optional pad right

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
 * Compare - Highlight data in set A that matches data in set B
 * CopyRegion - Copy variable-sized region from Sheet 2 to Sheet 3
 * DeleteBlankRows - Removed unused rows
 * DeleteDuplicatesViaFilter - Delete duplicate rows using advanced filter
 * DeleteEmptyRowsAndColumns - Deletes empty rows and columns
 * 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
 * FlipSelection - Flip the data in a selected column or row
 * FormulasToValues - Convert all formulas to values
 * 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
 * LocateInfoBox - Override Excel's default center location for a user input box
 * 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
 * ProtectFormulas - Protect the formulas in a range of data from being edited
 * Quadratic - Solves quadratic equations with real roots
 * 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
 * RunningMax - Uses self-reference to retain the highest value that has ever been present in B2:B11.
 * 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
 * TrimSelection - Trim a selected range of leading spaces
 * UseNextAvailableRow - Copies three cells in column A to next unused rows in column B