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.

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

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

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

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

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 ?

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

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.

Matheus,

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

Charles

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.

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

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

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

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!

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

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.

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

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

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

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?

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

Charles,

Thank you for your quick response.

It is very much appreciated and sure to make my life a lot easier..

Regards.

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

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

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

Kachi from Nigeria.

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