## What is it?

These add-in contains various User Defined Functions. They can answer the following

- Where is the Sunday in London on Friday afternoon in 2020?
- What is the wetbulb for a given humidity?
- How big should my duct / compressed air pipe / etc be
- And lots more.

Licence:

- Personal: GPLv3 – free to use and distribute unchanged.
- Commerical – free to trial. Please contact me for ongoing commerical use.

## Ok, sounds good, let’s do it

Download the latest version (Beeson Excel Functions.xlam)

Then:

- In Excel, select Tools>Macros>Security and select security level “Low” and “Allow VBA access to objects”
- In Excel, Choose WinbowButton>Excel Options>Add-Ins>Manage>Browse and open “Beeson Excel Functions.xlam

## What are the functions

A list of functions available is added to the ribbon, and a snapshot is shown below.

Category | Function | Copy To Use | Description |
---|---|---|---|

Lookup | |||

blookup | =blookup(tableBaseName, lookupVariable, lookupValue, returnVariable) | Lookup any column and return another column’s value | |

blookupUnits | =blookupUnits(tableBaseName, lookupVariable) | ||

HeatTrans | |||

test1 | =test1(r) | ||

planeWallTemp | =planeWallTemp(ByVal x_star, t, t_i, t_inf, l, k, ro, cp, h) | ||

biot | =biot(h, l, k) | ||

alpha | =alpha(k, ro, cp) | ||

fourier | =fourier(t, alpha, l) | ||

TableB3 | =TableB3(biot, nth) | Return the eigenvalues for the first biot number greater an the supplied Biot | |

sigma_star | =sigma_star(ByVal x_star, fourier, biot) | sigma_star = sigma / sigma_i = fraction of maximum/initial dT we are currently at | |

planeWallTempHelper | =planeWallTempHelper(my_sigma_star, t_i, t_inf) | ||

tableb3_test | =tableb3_test() | ||

Finance | |||

LoanRepayments | =LoanRepayments(captialAmount, amort, paymentsPerYear, rate, interestCalculationsPerYear) | Return $ per payment for a loan | |

EffectiveInterestRate | =EffectiveInterestRate(rate, interestCalculationsPerYear) | Given an annual rate, find effective interest rate | |

InterestRatePerPayment | =InterestRatePerPayment(amort, paymentsPerYear, EffectiveInterestRate) | Dunno | |

Chillers | |||

SimpleCOP | =SimpleCOP(load, coeffs) | For a given load fraction and regression coefficients, return the COP. | |

reg | =reg(x, coeffs) | Find y value for a given x, based on regression exponents | |

COP | =COP(load, t, coeffs_1, coeffs_2) | Find the interpolated COP, given a load, a temperature and two constant-temperature chiller curves | |

ThermalStore | =ThermalStore(kWh, dt, Optional sh) | Returns the mass of substance required to storage kWh of energy | |

ThermalCharge | =ThermalCharge(mass, dt, Optional sh) | Returns the thermal energy of ‘mass’ of substance with a ‘dt’ (default to specific heat of water) | |

Inter | =Inter(x, x0, y0, x1, y1) | Linear interpolation between two 2D point (x0,y0 and x1,y1). Return interpolated (or exterpolated) y value for a given x value | |

Comfort | |||

PMVWithFan | =PMVWithFan(ta, tr, rh, vel, met) | Calculate the PMV with a ceiling fan operational | |

PMV | =PMV(ta, tr, rh, vel, met, Optional CLO) | Calculate the PMV | |

PMVGreenStar | =PMVGreenStar(ta, tr, rh) | Calculate the PMV as per Green Star v2 | |

PMVBase | =PMVBase(ByVal ta, ByVal tr, ByVal rh, ByVal vel, ByVal met, ByVal CLO) | Calculate the PMV (Base function) | |

PPD | =PPD(ta, tr, rh, vel, met, CLO) | Calculate the PPD (percetnage of ppl dissatisfied) | |

autoFashion | =autoFashion(ta, tr) | Choose clothing value based on ambient temperature | |

AdComfortAuto | =AdComfortAuto(ot, limit, monthN, limits, Optional llOffset, Optional ulOffset) | Place comfort within a range, returning -1 (below), 0 (in) or +1 (above limit). | |

AdComfort | =AdComfort(ot, ma, limit) | Place comfort within a range, returning -1 (below), 0 (in) or +1 (above limit). | |

TempOffset | =TempOffset(velocity) | ||

ET_star | =ET_star(ta, tr, rh, vel, met, Optional CLO) | Return Effective Temperature °C | |

AdComfOptimumOT | =AdComfOptimumOT(month, loc) | ||

AdComfortRanges | =AdComfortRanges(month, loc, rangeType) | month 1-12 | |

Mech | |||

DuctCirc | =DuctCirc(litres, velocity, pa) | Size a circular duct’s diameter based on velocity and pressure | |

DuctRect | =DuctRect(litres, velocity, pa, Optional width = -1) | Size a rectangular duct based on velocity and pressure | |

Dia2Area | =Dia2Area(diameter) | Find area from a diameter | |

Rad2Area | =Rad2Area(radius) | Find area from a radius | |

Pa_M_Rect | =Pa_M_Rect(litres, width, height) | Find Pa/m pressure drop in a rect duct | |

Pa_M_Circ | =Pa_M_Circ(litres, diameter) | Find Pa/m pressure drop in a circ duct | |

PumpLoad | =PumpLoad(load_frac, design_power, min_turndown, Optional exponent) | Find pump power (kW) given a VSD | |

PumpCapacity | =PumpCapacity(q, p, eff, Optional min, Optional ro) | Calculate water pump capacity (kW) | |

VSD | =VSD(load_fraction, capacity, min, Optional is_on) | Generic linear turndown: ramp any device linearly | |

FanCapacity | =FanCapacity(q, p, eff, Optional ro) | Calculate fan capacity (kW) | |

ControllerRamp | =ControllerRamp(x, sp1, bw1, sp2, bw2, onBetween As Boolean) | Simulate a double ramping controller (as for IES) | |

CAPresDrop | =CAPresDrop(qv, l, d, p) | COMPRESSED AIR: Find pressure drop in bar. | |

CAPipeDia | =CAPipeDia(qv, l, pa, p) | COMPRESSED AIR: Find pipe diameter (mm) | |

CAStdPipe | =CAStdPipe(d) | COMPRESS AIR: Round up to standard pipe size (AS1074 Steel) | |

CAStdPipeUpsize | =CAStdPipeUpsize(d) | COMPRESS AIR: Round up to standard pipe size (AS1074 Steel), then go up one size | |

CAPipe | =CAPipe(qv, l, pa, p, Optional r) | ||

ductFormat | =ductFormat(W, Optional h) | ||

hx | =hx(OATemp, RATemp, SensEff, Optional LatEff = 0, Optional ReturnWhat = “SA”) | Sensible only for now | |

Misc | |||

SumBack | =SumBack(r, Optional cutoff) | Given a column range, sum back to the cutoff value | |

InRange | =InRange(c, l, u) | Returns 1 iff first argument is between l and u | |

AngleInRange | =AngleInRange(angle, min, max) | Returns 1 iff the “angle” is between min and max | |

FacadeText | =FacadeText(angle) | Given a facade’s normal angle, return a textual description | |

average2 | =average2(r, nrows) | ||

average3 | =average3(r, nrows) | ||

Array_Sort | =Array_Sort(ByVal NotSortedArry) | ||

ShadingDesc | =ShadingDesc(height, depth, Optional displayRatio = True, Optional displayAngle = True) | Nicely display shading ratio, and optionally angle | |

PowerToCurrent | =PowerToCurrent(watts, Optional phases) | ||

RampingOffset | =RampingOffset(x, lowThreshold, highThreshold, offset) | USE CAREFULLY – may not be what you think it is! | |

sheetName | =sheetName() | ||

ASCName | =ASCName(ascLong As Long) | ||

ASCSym | =ASCSym(ascLong As Long) | ||

Convert2 | =Convert2(value, fromUnit, toUnit) | ||

Psychrom | |||

p_ws | =p_ws(t) | Return p_ws which is saturated water vapour pressure (kPa) | |

p_w | =p_w(t, rh) | Return p_w, partial pressure of water vapour in air (kPa) | |

W | =W(t, rh) | Return W, humidity ratio (kg/kg) (water/air) | |

ah | =ah(t, rh) | Return ah, absolute humidity | |

W_s | =W_s(t) | Return W_s, saturated humidity ratio (kg/kg) (water/air) | |

h | =h(t, rh) | Return h, enthalpy (kJ/kg) | |

t_d | =t_d(t, rh) | Return t_d, dewpoint (°C) | |

wb | =wb(t, rh) | Return wb, wetbulb (°C) aka t_star | |

RHS35 | =RHS35(t, t_star) | ||

relh | =relh(t, wb) | Return rh, relative humidity (0-1) | |

percentError | =percentError(p, q) | Returns 0-100 | |

wb_approx | =wb_approx(t, rh) | APRROXIMATE Wet-bulb, for start of iterations in wb(…) | |

Sun | |||

Azimuth | =Azimuth(ByVal fLatitude, ByVal fLongitude, ByVal fTimeZone, ByVal dDate, ByVal dTime) | Return Sun Azimuth, in degrees | |

Altitude | =Altitude(ByVal fLatitude, ByVal fLongitude, ByVal fTimeZone, ByVal dDate, ByVal dTime) | Return Sun Altitude, in degrees | |

Declination | =Declination(fLatitude, fLongitude, fTimeZone, dDate, dTime) | Return Solar Declination in degrees (angle between plane of earth’s equator and plane of sun’s rotation | |

SolarIncidence | =SolarIncidence(fLatitude, fLongitude, dDate, dTime, dSurfaceAzi, Optional dSurfaceTilt = 0) | Angle below a normal to the surface and the sun, in degrees | |

SolarIncidenceHelper | =SolarIncidenceHelper(dSolarAzi, dSolarAlt, dSurfaceAzi, Optional dSurfaceTilt = 0) | Angle below a normal to the surface and the sun, in degrees | |

SunRise | =SunRise(fLatitude, fLongitude, fTimeZone, dDate) | Return time of sunrise in hours from midnight | |

SunSet | =SunSet(fLatitude, fLongitude, fTimeZone, dDate) | Return time of sunset in hours from midnight | |

Solartime | =Solartime(fLatitude, fLongitude, fTimeZone, dDate, dTime) | Return solar time in hours from midnight | |

timeCorrection | =timeCorrection(fLatitude, fLongitude, _
fTimeZone, dDate, dTime) |
Return required correction from local to solar time, in minutes. | |

sunPositionHelper | =sunPositionHelper(fLatitude, fLongitude, _
fTimeZone, dDate, dTime, iReturnSwitch As ReturnValue) |
(Base Function) | |

sunPosition | =sunPosition(fLatitude, fLongitude, _
fTimeZone, iDay, iMonth, _ fLocalTime, iReturnSwitch As ReturnValue) |
(Base Function) | |

JulianDate | =JulianDate(day, month) | Return Julian Day (1-365), with February always as 28 days | |

asin | =asin(x) | ||

acos | =acos(x) | ||

DateRadFrac | =DateRadFrac(d, lat, lon, h) | Using a normal distribution, return the approximate radiation fraction for that hour | |

YDayRadFrac | =YDayRadFrac(yday, lat, lon, h) | Unfinished | |

AngleOfRefraction | =AngleOfRefraction(a) | Unknown | |

FresnelReflectance | =FresnelReflectance(i) | Return Fresnel Reflectance (0-1). 0.05 means 5% of light is reflected. i: angleofIncidence (radians) | |

TAS | |||

YHour2YDay | =YHour2YDay(h) | Convert Year hour(1-8760) to day (365) | |

YHour2DHour | =YHour2DHour(yhour) | Convert Year hour(1-8760) to day hour (1-24) | |

Date2YDay | =Date2YDay(d) | Convert an Excel date to a TAS day (1-365) | |

Date2YHour | =Date2YHour(dhour, mday, month) | Convert date to Year hour(1-8760) | |

YDay2Date | =YDay2Date(yday) | For a TAS day (1-365) return an Excel date | |

VBAMisc | |||

Workbook_Activate | =Workbook_Activate() | Move to ThisWorkbook for mega-workbooks | |

Workbook_Deactivate | =Workbook_Deactivate() | Move to ThisWorkbook for mega-workbooks | |

SheetExists | =SheetExists(sheetName) As Boolean | ||

Recalc | =Recalc() | ||

RecalcSheet | =RecalcSheet() | ||

RecalcSelection | =RecalcSelection() | ||

reverse | =reverse() | ||

splitString | =splitString(s, Optional delimit = ” “) | Array formula to return a splitup string | |

split2 | =split2(ByVal InputText, _
Optional ByVal delimiter) |
As for VBA Split, but handled multiple delimiters as one | |

monthDays | =monthDays(m) | Returns number of days in a month | |

rFind | =rFind(find_text, within_text, Optional start_pos) | Reverse find (see find function) | |

ListAdd | =ListAdd(item, ByRef list, Optional delimiter) | ||

parseLine | =parseLine(l, delim, ParamArray v()) | ||

parseLineTest | =parseLineTest() | ||

ASC | =ASC(r) | ||

getError | =getError() | ||

showError | =showError(prefixLine) | ||

InsertPictureLinkInWord | =InsertPictureLinkInWord() | ||

firstBlank | =firstBlank(r) | ||

hlookup_concat | =hlookup_concat(v, r, n) | As for HLOOKUP, except get all matching (operator is LIKE) value and return a vbNewline seperated string | |

vlookup_idr | =vlookup_idr(value, range_string, col, t As Boolean) | As for VLOOKUP, except takes range as a string (not range). Note INDIRECT function doesn’t work to do this. | |

clearContentsExceptItalicised | =clearContentsExceptItalicised(r) | ||

SumHLOOKUP | =SumHLOOKUP(lookup_value_list, table_array, row_index, Optional lookup_method = True) | ||

sqrt | =sqrt(x) | ||

ConcatDelimited | =ConcatDelimited(r, d) | ||

RegMatch | =RegMatch(ByVal Source, Pattern, Optional IgnoreCase As Boolean = True, Optional MultiLine As Boolean = True) As Long | Using RegExp return 0 or 1 depending on if pattern matched | |

RegMatchCapture | =RegMatchCapture(ByVal Source, Pattern, Optional nMatch = 1, Optional IgnoreCase As Boolean = True, Optional MultiLine As Boolean = True) | Using RegExp, return a captured group – pattern in () with option to return nth match | |

ExtractFloorSigned | =ExtractFloorSigned(ByVal zoneName) | Extracts the first two digits occuring in any string. If a B prefixes these,return -ve number | |

ExtractFloorPrefixed | =ExtractFloorPrefixed(ByVal zoneName) | Extracts the first two digits occuring in any string. If a B prefixes these, prefix with B; other L | |

FilePath | =FilePath(ByVal fullFilename) | Return the path of the input filename. | |

filename | =filename(ByVal strfilename) | Return the name of the file, without the path. | |

FileExtension | =FileExtension(ByVal strfilename) | Return the extension of the input filename. | |

Open_ExplorerWindow | =Open_ExplorerWindow(mydir) | ||

KillProcess | =KillProcess(NameProcess) | ||

SplitPath | =SplitPath(ByVal fullpath, Optional Drive, Optional Path, Optional filename, Optional file, Optional Extension) | ||

isnulloremptyorwhite | =isnulloremptyorwhite(s) | ||

wssNamesInWb | =wssNamesInWb(wb As Workbook) As Collection | Return a collection of names (strings), which are the names of the worksheets in the workbook given | |

Lighting | |||

Transmissivity | =Transmissivity(transmittance) | ||

Structure | |||

smoaRHS | =smoaRHS(b, d, t, Optional offset) | b : length of parallel (to axis of bending) side (m) | |

smoaTube | =smoaTube(diaOuter, t, Optional offset) | diaOuter : diameter (m) | |

smoaBar | =smoaBar(diaOuter, Optional offset) | ||

smoaRect | =smoaRect(b, d, Optional offset) | b : length of parallel (to axis of bending) side (m) | |

pat | =pat(ByVal smoaCent, area, ByVal d) | Parallel Axis Theorm |