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