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.

25 Responses to Calling Real Statistics Functions in VBA

  1. Steven says:

    Charles, you deserve a Excel Nobel prize! Nice piece of work

    Did anyone use your library in a Visual Studio .NET project already? And more specifically in an Excel ribbon project?

    Steven

    • Charles says:

      Steven,
      Thank you for your kind words.
      I don’t know of anyone using my library in a Visual Studio .NET project already or in an Excel ribbon project.
      Charles

  2. Matheus says:

    Hello, Charles.

    Thank you a lot for all the information. It’s been extremely helpful

    I would like to run the Real Statistics – Descriptive Statistics and Normality in my macro like I ran the Excel’s Data analysis :

    Application.Run “ATPVBAEN.XLAM!Descr”, Worksheets(“ANOVA”).Range(“A2:A6”), Worksheets(“ANOVA”).Range(“K2”), “C”, True, True, 1, 1, 95

    How could I do that? The final goal is to plot the QQ-Plot.

    I looked as much as possible in the website but I couldn’t find anything that could lead me to a solution.

    Thank you,

    Matheus

    • Charles says:

      Matheus,
      The Real Statistics subroutine that produces the QQ plot is
      Sub RunQQPlot(rg As Range, ce As Range, head As Boolean)
      where rg is the data range, ce is the address of the cell where the output display starts and head = TRUE if rg contains column headings and head = FALSE if rg does not contain column headings.
      The rest of the calling sequence should be the same as for worksheet functions (as described on the referenced webpage).
      Charles

      • Matheus says:

        Thank you for the answer, Charles.
        Now I want to call other functions, e.g. Box Plot, but I could not find anything else than this list – http://www.real-statistics.com/real-statistics-environment/supplemental-functions/

        Is there a place where I can find all the subroutines and calling sequences ?

        • Charles says:

          Matheus,

          I provide what I hope is acceptable documentation for the worksheet functions, but I have not provided similar information for subroutines. This was done on purpose since I may decide to change the arguments in some of the subroutines in the future. (I rarely change the arguments in the worksheet functions and inform the user community of any such change.)

          In general, I expect that users will access the subroutines through the Real Statistics user interface and not directly.

          In any case, the current subroutine for Box Plot is
          Sub RunBoxPlot(rg As Range, ce As Range, head As Boolean, bexc As Boolean)
          rg = the data range, ce = the location where the output will begin, head = True if rg contains column headings, bexc = True if QUARTILE.EXC is used

          Also note that the Real Statistics license agreement requires that the end-user download a copy of the Real Statistics software from the Real Statistics website. It cannot be supplied/distributed to the end-user by a third party without explicit permission from me.

          Charles

          • Matheus says:

            Charles,

            Ok. Thank you very much.

            The software will be neither supplied nor distributed to anyone. I am using it for my master thesis and just wanted to avoid using the interface.
            The proper citations will be used.

            Thank you again and congrats for your work.

          • Charles says:

            Matheus,
            Thanks for the clarification. Let me know if you need the specification of any of subroutine.
            Charles

  3. 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.
      Charles

  4. Balint says:

    Hi,
    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,
    Balint

    • 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.
      Charles

  5. 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?

    Thanks!

    • 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
      Charles

    • 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:

        Hector,
        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.
        Charles

        • 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:

            Hector,
            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).
            Charles

          • 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:

            Hector,
            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.
            Charles

  6. Martin Coetzee says:

    Charles,

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

    Regards.

  7. 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

    Martin

    • Charles says:

      Martin,

      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.

      Charles

  8. 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.
      Charles

Leave a Reply

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