PolynomReg.vba

Excel Polynomial Regression functions.

The original code is as old as from 1995 and was written by Gerhard Krucker (see http://www.krucker.ch/skripten-uebungen/IAMSkript/IAMKap3.pdf#page=14). So all credits go to him!

Luckily he allowed to reuse his code and here it is with a big refactor and some extensions.

Features

Before I present a list of features, I give a definition of a polynomial function, which then allows to list the features more easily. A polynomial function (with a single variable/indeterminante) can be written as

$$y = \sum_{i=0}^{n} a_{i} x^i$$

where $y$ is the result of the polynomial function, $x$ is the variable/indeterminante, $a_i$ are the polynomial coefficients, and $n$ is the polynomial degree.^[1]

Knowing that, this code allows

• to calculate polynomial coefficients $a_i$ for a set of $x$ and $y$ values as well as a given polynomial degree $n$ and

• to calculate the result $y$ for a given set of polynomial coefficients $a_i$ and a variable/indeterminante $x$.

• #NA input values are allowed to easier remove some outliers for the calculation of the polynomial coefficients and to prevent the need to resize the list of the polynomial coefficients when the polynomial degree is changing.

Additionally you will find

• code to register the functions to the “Function Wizard” and

• an XML file to allow showing function information as IntelliSense.

Prerequisites / Dependencies

Since this is an Excel VBA module you obviously need Microsoft Excel.

In addition you need (the sources of)

• https://github.com/VBA-tools2/modArraySupport2.vba.

If you want to have IntelliSense for the functions, you also need the (activated)

• Excel-DNA IntelliSense AddIn.

How to install / Getting started

Install the Functions

Add the file(s) from

• the src folder and

• the modArraySupport2 src folder

to your project. Yes, it’s that simple.

If you need some more advice on how to do that, expand me.

1. Open Microsoft Excel.

2. Open the Visual Basic Editor (VBE) (Alt+F11).

3. Add the file(s) in the src folder to your VBA project.

   • With Rubberduck:

     1. Right-click on the project to which you want to add the file(s) in the “Code Explorer” (to show it press Ctrl+R) and click on Add › Existing Files…​.

     2. Select all files in the src folder and click on Open.

   • Without Rubberduck:

     1. Select all files in the src folder in Windows File Explorer.

     2. Drag-and-drop them to the corresponding project in VBE’s “Project Explorer”.
        (To show it press Ctrl+R. Hit it twice if the Code Explorer shows up first.)

4. Add the file(s) from the modArraySupport2 src folder as well to the project.

5. Check, if there are obvious errors by compiling the project (Debug › Compile ‹project name›).

6. Save the file/project.

   1. Be sure that the file/project you want to save is “active” in the VBE by checking, if its name is shown in VBE’s title bar.
      (If it’s not, open a (class) module of the corresponding project (and close it again).)

   2. Press the “Save” button (the disc symbol similar to 💾) in VBE’s toolbar.

   3. Check that the file (really) was saved by having a look at the “last modified date” of the (project) file in the Windows File Explorer.

Register the Functions

Additionally you can run the AddUDFToCustomCategory procedure to register the functions (to the “Function Wizard”). To do so, place the cursor somewhere inside that procedure and hit F5.^[2]

Add IntelliSense to Functions

If you also want to have IntelliSense for the functions, the needed XML file(s) can be found in the IntelliSense folder.

If you need some more advice on how to do that, expand me.

Have a look at the awesome Excel-DNA IntelliSense project. Especially at

• the projects “README” file,

• the Wiki entry “Custom XML part in an open Workbook”, and

• the Wiki entry “External .intellisense.xml file”.

Usage / Show it in action

Because usual (user defined) functions are provided by this code, it should be clear how to use them. If you want to see them in action anyway, please have a look at the XLSX file in the src folder.

Running Tests

Yes, Unit Tests in Excel are possible. For that you need to have the awesome Rubberduck AddIn installed (and enabled).

The procedure is very similar to the one given in the Install the Functions section, but this time add the files from the tests folder (instead of the files from the src folder). When you are done with that, additionally perform the following steps.

1. Check that the files from the src folder are present in the project as well or that you have a reference to project with the source files.^[3]
   (Otherwise tests will/should fail.)

2. Add a reference to the “Rubberduck AddIn” to the project (with the test modules).

   If you don’t have a clue how to do that, expand me.

   • With Rubberduck:

     1. Right-click somewhere on the project in the “Code Explorer” and click on Add/Remove References…​.

     2. Add the reference.

        1. Type (parts of) the library name in the search box until you see it in below (left) list or it is empty.

           • If the AddIn file name is in the lower left list box:

             1. Select the library in the (left) list and

             2. click on the ➨ button to add it to the project references.
                

           • If the the AddIn file name is not in the list:

             1. Click on the Browse…​ button.

             2. Browse to the folder where the AddIn is located.

             3. Select the AddIn and

             4. press the Open button.

     3. Click on the OK button to close the window.

   • Without Rubberduck:

     1. Open the Reference manager in the VBE (Tools › References…​).

        • If the AddIn project name is in the list of available references:

          1. Add a checkmark to the corresponding library.

        • If it’s not in the list:

          1. Click on the Browse…​ button.

          2. Browse to the folder where the AddIn is located.

          3. Select the AddIn and

          4. press the Open button.

     2. Click on the OK button to close the window.

     3. Save the file/project.

3. Open the “Test Explorer” (Rubberduck › Unit Tests › Test Explorer).

4. Run the tests by clicking Run › All Tests.

Used By

This project is used by (at least) these projects:

• http://www.unitoperations.de

If you know more, I’ll be happy to add them here. In addition it is used by a lot of private, i.e. non-public, Excel AddIns created by the author.

Known issues and limitations

None that I am aware of.

Contributing

All contributions are highly welcome!!

If you are new to git/GitHub, please have a look at https://github.com/firstcontributions/first-contributions where you will find a lot of useful information for beginners.

I recently was pointed to https://www.conventionalcommits.org which sounds very promising. I will use them from now on too (and hopefully don’t forget it in a hurry).

FAQ

1. What are the '@... comments good for in the code?

   You should really have a look at the awesome Rubberduck project!

Similar Projects

I am aware of

• https://www.real-statistics.com/

• https://rosettacode.org/wiki/Polynomial_regression#VBA

The first one seems very impressive. You should check it out too.

If you know more similar projects, please let me know.

License

MIT

---

1. If you want to know more about this, please have a look at e.g. the “Polynomial Functions” Wikipedia article.

2. If you don’t know what this is all about, you could bring some light into the dark by e.g. reading Jan Karel Pieterse’s Registering a User Defined Function with Excel article.

3. See the collapsible in the next point on how to do that. If you use the Rubberduck way, before adding the reference you first need to switch to the Projects tab.