Excel Functions for APA number and estimate/CI formatting

apanum.vb

' Functions to format numbers and estimates with confidence intervals in an APA-like format
' 
' Author: Brenton M. Wiernik
' Date: 2020-05-12
'
' License: CC0
'
' Usage examples:
'   Single number:
'     =apanum(value, lead_zero, pos_sign, neg_sign, big_sep)
'     =apanum(.123) -> default, see values below
'     =apanum(.123, FALSE, "") -> to omit positive sign rather than use figure space
'     =apanum(.123, FALSE, "", "-") -> to omit positive sign and use hyphen instead of minus sign
'
'   Estimate and interval:
'     =esci(est, cil, ciu, lead_zero, pos_sign, neg_sign, big_sep)
'     =esci(.123, .100, .1345) -> default, see values below
'     =esci(.123, .100, .1345, FALSE, "") -> to omit positive sign rather than use figure space
'     =esci(.123, .100, .1345, FALSE, "", "-") -> to omit positive sign and use hyphen instead of minus sign

Function APANUM(value As Double, Optional lead_zero As Variant, Optional pos_sign As Variant, Optional neg_sign As Variant, Optional big_sep As Variant)
' Function to format a number in APA-like style, returns a String.
' Required arguments:
'   'value': Double. The number to be formatted.
' Optional arguments:
'   'lead_zero': Boolean. Should leading zeroes be printed (default FALSE)
'   'pos_sign': String. Character to use for positive sign (default 'figure space': unichar(8199))
'   'neg_sign': String. Character to use for negative sign (default 'minus sign': unichar(8722))
'   'big_sep': String. Character to use as thousands separator (default 'narrow no-break space': unichar(8239))

If IsMissing(lead_zero) Then
  lead_zero = False
End If

If IsMissing(pos_sign) Then
  pos_sign = WorksheetFunction.Unichar(8199)
End If

If IsMissing(neg_sign) Then
  neg_sign = WorksheetFunction.Unichar(8722)
End If

If IsMissing(big_sep) Then
  If value >= 1000 Then
    big_sep = WorksheetFunction.Unichar(8239)
  Else
    big_sep = ""
  End If
End If

If lead_zero Then
  Base = "#" & big_sep & "##0.00"
Else
  Base = "#" & big_sep & "###.00"
End If

fm = pos_sign & Base & ";" & neg_sign & Base

APANUM = Format(value, fm)

End Function

Function ESCI(est As Double, cil As Double, ciu As Double, Optional lead_zero As Variant, Optional pos_sign As Variant, Optional neg_sign As Variant, Optional big_sep As Variant)
' Function to format a number and its interval in APA-like style, returns a String.
' Required arguments:
'   'est': Double. The number to be formatted.
'   'cil': Double. The lower bound of the interval.
'   'ciu': Double. The upper bound of the interval.
' Optional arguments:
'   'lead_zero': Boolean. Should leading zeroes be printed (default FALSE)
'   'pos_sign': String. Character to use for positive sign (default 'figure space': unichar(8199)
'   'neg_sign': String. Character to use for negative sign (default 'minus sign': unichar(8722))
'   'big_sep': String. Character to use as thousands separator (default 'narrow no-break space': unichar(8239))

est_fm = APANUM(est, lead_zero, pos_sign, neg_sign, big_sep)
cil_fm = APANUM(cil, lead_zero, pos_sign, neg_sign, big_sep)
ciu_fm = APANUM(ciu, lead_zero, pos_sign, neg_sign, big_sep)

ESCI = est_fm & " [" & cil_fm & ", " & ciu_fm & "]"

End Function