ProgrammingSoftware

Beeson Excel Function

What is it?

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

  1. Where is the Sunday in London on Friday afternoon in 2020?
  2. What is the wetbulb for a given humidity?
  3. How big should my duct / compressed air pipe / etc be
  4. 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:

  1. In Excel, select Tools>Macros>Security and select security level “Low” and “Allow VBA access to objects”
  2. 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

Leave a Reply

Your email address will not be published. Required fields are marked *