26 January 2011

How to Create a Scalar DIST in VBA

Whether as a placeholder, or to plug in an actual to replace an estimate, it's useful to replace a DIST with a scalar without recoding every formula that expects it to be a DIST. Here's code to do it.

'Create a repeated scalar DIST.
'This takes advantage of an undocumented XLSIM
'feature, so that a scalar can be put where a DIST
'is expected without breaking code that expects a DIST.
'
'=scalarDist(1000,5,"temp")
' will produce
'<dist name="temp" avg="5" min="5" max="5" 
'                     count="1000" type="Double" />
'
Public Function scalarDist( _
    count As Long, _
    v As Double, _
    Optional name As String = "Unnamed...") _
    As String
    
    scalarDist = "<dist " & q("name", name) _
            & q("avg", v) & q("min", v) _
            & q("max", v) & q("count", count) _
            & q("type", "Double") & " />"
End Function

'Format an attribute in quotes
Private Function q(a As String, b As Variant) _
    As String
    q = a & "=""" & CStr(b) & """ "
End Function

No comments:

Post a Comment