I am a supporter of
St. Joseph's hospice.
 If you find this site useful or if it helped you, consider a small donation to
St. Joseph's, please.

Information on
St. Joseph's

JustGiving - Sponsor me now!

 

Excel VBA

Excel© VBA code helps you to encapsulate functionality so that it can be run safely in different environments or at different times (repeatedly).

A short introduction into Excel VBA programming which I intend to detail and to enhance over time you can find here.

Mathematical Functions

Average last n cells of a given range.
Count unique values.
Count unique values with criteria.
Count visible cells only.
Distribute parliament seats fairly with the D'Hondt method or the Largest Remainder method.
List frequencies of strings or numbers.
MedianIf calculates the median of values where some corresponding values are equal to a defined variable.
Calculate the nearest rational number to a floating point number
Show a floating number with N significant digits.
Show a number in a non-scientific format with all significant digits and all leading resp. trailing zeros: sbNum2Str.
sbCountMyColor counts the number of cells with the same background color as the calling cell.
sbSumMyFormat returns totals for a range of identical number formats.
Sum last non-empty cells.
Compute nearest rational number to a float number with a given maximal denominator: NRN
Do a linear interpolation with sbInterp, Minimax interpolation, Poor Man’s interpolation or Poorest Man’s interpolation.
Make rounded percentages sum up to exactly 100% with sbLRM (largest remainder method).
Transform decimal numbers into binaries or vice versa: sbLongDec2Bin
Or compute statistics for weighted values.

Programming Tools

Determine the number of dimensions in an array with ArrayDim.
How to do error trapping with Excel VBA.
Lock a formula so that it does not get adjusted to added or deleted cells.
Rless determines all cells which are in range 1 but not in range 2.
Check whether worksheet functions Match or Vlookup are used correctly with sbMatchCheck.
Return non-blank cells of the input with sbNoBlank.
Reshape an array with sbReShape.
Create a constant row or column vector with given start value and increment.
Test whether a value is between two other ones (uses sbNextFloat function).
WSSplit splits a cell content onto several cells with a given delimiter.

Special or Complex Tasks

Find out which subset of values sums up to a specified amount (accounts receivable problem).
Create an accumulated trade blotter.
Create a monthly birthday list for your department.
Count hours between two dates or add hours to a date.
Write data to a database with Excel and retrieve it again.
Some LOOKUP()-variants I found useful.
Do a data reconciliation - compare two lists.
Game of Risk - How many armies do you need to attack successfully?
Analyze and check any data (in a csv file) with sbDatastats
Distribute a limited budget fairly with sbDistBudget.
You can easily generate test data with convenient statistical features with sbGenerateTestData.
Sort data numerically or alphanumerically with sbGSort.
Create a tasklist for your team which shows all necessary tasks - automatically filled - with sbTasklist.
Concatenate cell or element values of ranges or arrays with sbCat.
Create a list of unique entries with sbUniq.
A simple sort function for small ranges or arrays which is based on Gnomesort.
Spell numbers: 123.56 are Onehundredandtwentythree Dollars and Fiftysix Cents.
Generate golf teams with a fair distribution of handicaps
Determine the first or the last weekday of a month (last Sunday, for example).
Store high and low watermark values for a specific cell (as well as the input parameters)
Show how old your data is with the Worksheet_Change event.

Sulprobil   Get it done   Contact   Disclaimer   Download