Using xlPrecision in Your Excel VBA Code

PrecisionCalc
xl Precision
Get Your Numbers Right

Using xlPrecision In Your Programming Code
Visual Basic Projects, Excel VBA Macros, Access VBA Macros, Etc.

You can use xlPrecision in your programming code, such as Visual Basic projects, Excel VBA macros, Access VBA macros, Word VBA macros, any other application's VBA macros, or from any other code that can call a COM (ActiveX, OLE) server.

As a developer, you can freely distribute the free edition of xlPrecision to your users. However, if you want to distribute a paid edition of xlPrecision to your users, you must buy a distribution license to do so.

Here's an example:

   Dim oXlp As Object
   Dim strRet as String
   Set oXlp = CreateObject("xlPrecision.cls_xlPrecision")
   strRet = oXlp.xlpDIVIDE(67,89,,,,,50) 'Divides 67 by 89, with Max SD of 50.
   Debug.Print strRet
   Set oXlp = Nothing

Early Binding

Alternatively, if you prefer Early Binding, you could set a reference (in Excel's VBE, choose Tools | References) to xlPrecision, and do this:

   Dim oXlp As xlPrecision.cls_xlPrecision
   Dim strRet as String
   Set oXlp = CreateObject("xlPrecision.cls_xlPrecision")
   strRet = oXlp.xlpDIVIDE(67,89,,,,,50) 'Divides 67 by 89, with Max SD of 50.
   Debug.Print strRet
   Set oXlp = Nothing

Note that Early Binding probably won't be a major advantage with the kind of work xlPrecision does, and it may require removing and recreating the reference when new versions of xlPrecision are installed in the future.

Change the global Max SD

Instead of setting the Max SD (maximum significant digits) on each xlPrecision line with the maximum_signficant_digits parameter as in the above examples, you may prefer to set the global Max SD in VBA just as you can manually in the About box. To set the global Max SD in VBA, use the MaxSD property. The MaxSD property is read/write. It has the same effect as setting Max SD manually in the About box except that naturally it doesn't have the option to recalculate all the worksheet formulas (you can do that separately in VBA if you want to).

Sample code using the MaxSD property:

   Dim oXlp As Object
   Dim strRet As String
   Dim iOldMaxSD As Long
   Set oXlp = CreateObject("xlPrecision.cls_xlPrecision")
   iOldMaxSD = oXlp.MaxSD 'Optional. Stores the Max SD setting in iOldMaxSD before changing it.
   oXlp.MaxSD = 50 'Sets the global max SD.
   strRet = oXlp.xlpDIVIDE(67, 89) 'Divides 67 by 89, using the global Max SD.
   Debug.Print strRet
   Debug.Print oXlp.MaxSD 'returns (reads) the Max SD.
   oXlp.MaxSD = iOldMaxSD 'Optional. Restores the old Max SD setting.
   Set oXlp = Nothing

Arguments that must be passed as arrays

Some arguments in some xlPrecision functions must be passed as arrays when used in programming code. For example:

   Dim oXlp As Object
   Dim strRet as String
   Set oXlp = CreateObject("xlPrecision.cls_xlPrecision")
   strRet = oXlp.xlpAVERAGE(Array(67,89))
   Debug.Print strRet
   Set oXlp = Nothing

Alternatively, to pass in an Excel worksheet range of cells, convert the Range object to an array of Variants:

   Dim oXlp As Object
   Dim strRet as String
   Dim rgRange As Range
   Dim vArray() As Variant
   Set oXlp = CreateObject("xlPrecision.cls_xlPrecision")
   Set rgRange = ActiveSheet.Range("A1:A10")
   vArray = rgRange
   strRet = oXlp.xlpAVERAGE(vArray)
   Debug.Print strRet
   Set oXlp = Nothing

The affected xlPrecision functions are:

   xlpAVERAGE
   xlpCOUNT
   xlpEXCLUDE
   xlpEXCLUDENOTNUM
   xlpEXTRACT
   xlpEXTRACTNUM
   xlpGCD2
   xlpLARGE (numlist argument)
   xlpLCM
   xlpLCM2
   xlpMAX
   xlpMEDIAN
   xlpMIN
   xlpMODE
   xlpPRODUCT
   xlpRANK (numlist argument)
   xlpSMALL (numlist argument)
   xlpSORT (numlist argument)
   xlpSUM
   xlpSUM2

Example using Microsoft Word

This example enters the result in a Word document and saves it as plain text:

   Dim oXlp As Object, oWord As Object, oDoc As Object
   Dim strRet As String, strSavePath As String
   Const WORD_FILE_FORMAT_PLAIN_TEXT As Long = 2

   Set oXlp = CreateObject("xlPrecision.cls_xlPrecision")
   Set oWord = CreateObject("Word.Application")
   oWord.Visible = True 'optional, if you want to see it.
   strRet = oXlp.xlpDIVIDE(67, 89, , , , , 50)
   Set oDoc = oWord.Documents.Add
   oWord.Selection.TypeText strRet

   strSavePath = Application.GetSaveAsFilename( _
                      "xlPrecision result.txt", _
                      "Text Files (*.txt), *.txt", , "Save As")
   If strSavePath <> "False" Then
        oDoc.SaveAs strSavePath, WORD_FILE_FORMAT_PLAIN_TEXT
        MsgBox "Saved as " & strSavePath & ".", _
                vbInformation + vbOKOnly, "xlPrecision Result Saved"
   Else
        MsgBox "xlPrecision result not saved.", _
                vbCritical + vbOKOnly, "Not Saved"
   End If

   oDoc.Close False
   oWord.Quit
   Set oDoc = Nothing
   Set oWord = Nothing
   Set oXlp = Nothing

Important Note:
To maintain high precision, the return value must always be a String data type, and never converted to any numeric data type. High precision is automatically lost upon any conversion to any numeric data type.

In addition, if the return value is placed into an Excel worksheet cell, that cell must be formatted as text, not general or any numeric formatting. Alternatively, a leading apostrophe can be added to the return value before returning it to a cell.

In future versions I may need to have xlPrecision detect what client is using it and restrict random clients' usage, requiring their developers to license it. But I'll be sure to allow Excel's VBA to use it without restriction. Any future restriction would apply only to other random EXEs trying to use xlPrecision, not to Excel VBA.

xlPrecision Home Page
PrecisionCalc Home Page

As easy as 3.1415926536.