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 that 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 in Windows 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.
Note that you may need to unblock the file containing the RealStats file, as described in the Troubleshooting section of Downloading and Installing the Real Statistics Resource Pack.
Reference
Stack Overflow (2018) How to call function from another specific workbook in VBA
https://stackoverflow.com/questions/10012463/how-to-call-function-from-another-specific-workbook-in-vba
Hi Charles,
In using the ICC function on a dataset of arrays (each with a coefficient), I have gotten values higher than 1 (which should be impossible). I’ve tried all variations of input class and type, just in case, and they vary but still give values higher than 1.
The same dataset in SPSS gets coefficients close to 0, and never over 1 (which should be impossible).
Ben
Good Afternoon, Mt. CHARLES
Thank you for share your work. It’s a great Work.
I ‘m working with Excel2016, from vba with RealStat.
I’m testing some capabilities, to help a friend with statistics.
I find a limitation when only can work with ranges to feed the functions. I have several sheets, one of then with the data, but the data ar no complet, and if I pass a range with cells without data, the functions don’t work. I want to put in an array the available data and feed the funtions to obtein preliminary results.
An other problem is that not allways the data are in join colums, and the function don’t work with composed ranges.
There is any way to work with arrays insted of ranges ?. I’m working with FRIEDMAN function now.
Thnak you.
Antonio,
Many of the Real Statistics functions can use either arrays or ranges, but some can only take ranges. It was my intention to convert all the functions that only accept ranges to arrays or ranges over time. If you let me know which functions that take only ranges are most important to you I will make sure that they also take arrays. E.g. I can convert the FRIEDMAN function.
As far as composed ranges, you can use the MERGE function to merge the separate ranges. E.g. for a function XX that takes arrays as well as ranges, you would use the formula =XX(MERGE(R1,R2)).
Charles
Charles,
Thank you very much for your quick response.
Indeed, I have tried other functions (Spearman, Wilcoxon, …) that did provide the functionality of working with arrays. Possibly Friedman’s is the only one I’m going to use that doesn’t allow it. I appreciate your advice on using the MERGE function.
I take this opportunity to feed back to you with my experience in the use of REAL STATISTICS.
I have created three sheets, each with a matrix of about 60 rows by 20 columns to correlate variables in a study, with a formula in each cell. In the creation process I have had many incidents of the type in which Excel keeps recalculating and does not respond. Observing from the task monitor you could see a high CPU consumption and how the memory consumed by the process was increasing. Finally it was necessary to cancel the task. I think the source of the problems was in the input data; some cell calculated with error, or as text instead of number …. Perhaps it would be convenient, to give more stability to REAL STATISTICS functions, check the input data that is provided to the functions to prevent it from looping.
In any case, REAL STATISTICS is helping me a lot and I think you have done a great job. Thank you very much again for sharing your work and for your help.
Antonio
Hello Antonio,
Glad I could help, at least a little. Interrupting loops is not so easy to implement, but I can see why it would be useful.
I will add array support for Friedman’s test in the next release.
Charles
Hello Charles,
Ok, thank you very much for your work and for keeping this project up to date, which helps so many of us. Thank you.
I installed Real Statistics on Windows 10 with Office 365. I am trying to find the eigenvalues of a matrix using the Real Statistics function eValues in a VBA macro I am building, but VBA says it does not recognize the function. eValues is recognized if entered on a worksheet. Real Statistics is check when got to Excel Add-ins on the worksheet, but does not appear in the Add-in menu in Visual Basic.
Dennis,
You don’t need to use the Add-Ins menu. You need to follow the instructions on the webpage, namely:
“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
Thank you for the reply. I don’t see RealStats in the available references. Does that mean I have not installed it correctly. (And I’m sorry for the duplicate question about this problem in FAQs.)
Charles, I would like to let you know I solved the problem by “unblocking” RealStats.xlam. RealStats is now an available reference.
Dennis,
To check whether RealStats has been installed try the following
– When you enter the formula =VER() in any cell, you should not get an error message
– When you press Ctrl-m, you should see a dialog box.
I just tried the steps described on the webpage and it all worked perfectly.
Charles
Hello Charles,
Is there a way to store results of jenks (or other statistical routines) in local variables in a macro. I tried either to pass something else than a range as ‘ce’ in the routine or to find a local type of variable (array) on the left of the jenks function and didn’t suceed.
RunJenks(rg As Range, ce As Range, k As Long, iter As String)
JENKS(rg As Range, [k As Long = 0], [lab As Boolean = Falsch], [iter As Long = 0])
By writing the functions to the spreadsheet Excel is permanently updating resource-intensive operations. It would be better to handle results in the macro itself.
Thank you for any hints and many thanks for your contributions to making excel a powerful statistics tool!
Phil,
Glad that you are getting value from Real Statistics.
In your macro, you need to do something along the following lines:
Dim rg as Range
Set rg = Range(…)
Dim arr as Variant
Redim arr(1 to k+1, 1 to 4)
arr = JENKS(rg, k, lab, iter)
Charles
When accessing RealStats-2010.xlam via VBA development interface, the tool is asking for a password. You may want to protect this portion of your work and it’s ok.
Anyhow thank you for all your time, sharing of knowledges and this website.
Jike,
See the following:
Password Prompt
Charles
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
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