Beeson Excel Functions

Submitted by admin on

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.

Install, download it and:

  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"
     

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

File: