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.

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