Calling Real Statistics Functions in VBA

You can call any of the supplemental functions found in the Real Statistics Resource Pack from a program or macro that you write yourself using VBA Excel. The procedure for doing this is as follows:

Start by writing your program or macro in the usual way. For example, here is a simplistic example which uses the Real Statistics STDERR function.

Function TestExample() As Variant
TestExample = STDERR(Range(“A1:A5”))
End Function

In the VBA development interface (which can be accessed by pressing Alt-F11), click on the Tools menu and select the References… option. On the dialog box that appears click on RealStats. This will give your macro access to all the Real Statistics functions.

17 Responses to Calling Real Statistics Functions in VBA

  1. Ilia says:

    Hi Charles,

    Great stats package and great website.

    I have been having a lot of success with the real-statistics macros when running them from the excel front-end, but am having less luck when calling them directly from VBA. Do you have any examples of how you would call a logistic regression directly from a vba script?

    For example, if I use the code: sentRegressionSheet.Cells.Range(“M1:T10”).Value = logitcoeff2(testRange, testRange2, True, True, 0.05, 20) – I consistently get a “subscript out of range error.”

    I get variable success with the logitcoeff function. However, the goal would be to run a series of regression analyses with the same independent variables (“testRange” in the example above) and switch up the dependent variables (“testRange2” in the example above).

    Any advice on how I should proceed.
    Thanks for your time and help.

    • Charles says:

      Hi Ilia,
      I can’t say why you are having this problem. If you send me your code (or a part of your code) I will try to figure out what is going wrong.

  2. Balint says:

    I installed the package on MAC OS 10.7.5, Excel for Mac2011, version 14.0.0.
    Unfortunately the dialog box of the Real statistics Add-in is so small that the letters are almost unreadable, and really confusing. can I somehow change the size of the dialog box?

    Thank you,

    • Charles says:

      I don’t know any way for you to increase the font size. I actually increased the font size in the last release of the Mac version of the software. I will try to increase it further in the next release.

  3. Yan Barnett says:

    Hi Charles,

    Thanks for the resource pack and tremendous amount of information on this website. I’ve installed the resource pack, and am too trying to call logistic regression subroutines from a macro to run through a large number of x-y combinations.

    I appreciate your previous post on how to call RunLogit() subroutine. Could you also post the names and input parameters of the subroutines to run Multiple Linear, Exponential, and Multinational logistic regressions?


    • Charles says:

      Very happy to see that you are getting value out of the website.
      The names and input parameters are described under the Tools menu. In particular, please look at the webpages
      Real Statistics Functions
      Real Statistics Data Analysis Tools

    • Hector Colon says:

      Dr. Zaiontz. Hi. The post by Yan Barnett mentions another post where you showed how to run RunLogit() from VBA. I cannot find that post and frankly I am at a loss as to how to code in vba a function which will yield not a single result (like the STDERR example you have above) but multiple results. Can you tell me where the post with the RunLogit() example is or can you show me how to code in vba a logistic regression function? Thanks in advance.

      • Charles says:

        You don’t need to code a logistic regression function in vba. You should be able to call the Real Statistics RunLogit() subroutine in the same manner as a Real Statistics function.

        • Hector Colon says:

          Still at a loss. Can you tell me what am I doing wrong in the following code?

          Public Sub Testin()
          Range(“K5”).FormulaArray = LogitCoeff2(Range(“C1:E3504”), Range(“F1:F3504”), False, False, 0.05, 20)
          End Sub

          • Charles says:

            Range(“K5”) is a single cell. You need something like Range(“K5:R8”).FormulaArray = etc. You also need to make sure that you add RealStats as a Reference (using the Tools menu).

          • Hector Colon says:

            I had already added RealStats as a reference. Now, you say I need a range that is more than a single cell. So how do I know how large the reference needs to be, that is the size of the range?

          • Charles says:

            If you use the appropriate subroutine, you don’t need to know the size of this range, but Excel functions require that you do know the size of the output range. It is not so easy to figure this out, but for the LogitCoeff2 function, if I remember correctly this is m x n where m = is the number of independent variables plus 1 and n = 8.

  4. Martin Coetzee says:


    Thank you for your quick response.
    It is very much appreciated and sure to make my life a lot easier..


  5. Martin Coetzee says:

    Hi Charles

    Apologies for my previous incomplete post re. calling Logistic regression from vba

    I’m writing a macro, which will loop through a data set.
    I’m hoping to have the loop call a full Logistic regression. (as per calling “Logisic Regression” from the Real Statistics” menu…)

    All parameters will be as per default
    Input range will change, but is pre set under same variable name.
    Output range to start at the same fixed cell (M1) of the input sheet

    Is it possible to call as described above direct from VBA

    Many Thanks


    • Charles says:


      The approach described in my previous response should give you a partial answer. The subroutine that generates the output for logistic regression is as follows:

      Sub RunLogit(rg As Range, ce As Range, b1 As Boolean, b2 As Boolean, b3 As Boolean, iter As String, _
      alpha As Variant, cutoff As String, ss As String, b4 As Boolean)

      rg = input range
      ce = first cell of the output
      b1 = True if data contains column headings and False otherwise
      b2 = True if rg contains raw data and False if rg contains summary data
      b3 = True if Newton method is to be used and False if Solver is to be used
      b4 = True then summary of raw input data is output
      ss = “” (this is used to eliminate columns, but in your case you probably want this to be set to an empty string
      alpha = alpha value (usually set to .05)
      cutoff = cutoff value for Classification Table (since this is in string format, you need to use a number like “0.5”)
      iter = the number of iterations in Newton’s method (since this is in string format, you need to use a number like “20”)

      Caution: The format of this subroutine has not changed for a long time, but is subject to change. I try very hard not to change the format of the functions, but do occasionally change the format of subroutines.

      Also the user interface makes sure that all the arguments are valid. This is not part of the above subroutine and so you need to make sure that you don’t use invalid arguments.


  6. Kachi says:

    How can one do a post hoc test like Duncan’s Multiple Range using excel or realstatistic tool?
    Kachi from Nigeria.

    • Charles says:

      Hi Kachi,
      Nice to speak to someone from Nigeria.
      The Real Statistics Resource Pack does not currently support Duncan’s multiple range test, but it does support Tukey HSD and other post hoc tests. See the webpage Unplanned Comparisons for details.

Leave a Reply

Your email address will not be published. Required fields are marked *