• C++ Programming for Financial Engineering
    Highly recommended by thousands of MFE students. Covers essential C++ topics with applications to financial engineering. Learn more Join!
    Python for Finance with Intro to Data Science
    Gain practical understanding of Python to read, understand, and write professional Python code for your first day on the job. Learn more Join!
    An Intuition-Based Options Primer for FE
    Ideal for entry level positions interviews and graduate studies, specializing in options trading arbitrage and options valuation models. Learn more Join!

VBA question

Sanket Patel

i do stuff
Joined
3/4/08
Messages
543
Points
28
In the process of writing some code to automate some calculations, I need to estimate a correlations matrix. I know I can use Application.WorkSheetFunction.Correl but that I have a huge range of returns and I don't feel like writing loops. I also don't feel like writing code to manually calculate the correlations.

I installed the Analysis ToolPak - VBA addin hoping that I could use the Correlation function that comes with the addin. I added a reference to the library and went through Object Browser and found the MCorrel() function that comes with addin.

My question is, how the heck do I actually call the function from my sub routine? It seems I have to tell VBA that I want to use a function from the atpvbaen.xls library, but how exactly do I do that?
 
Hi Sanket, as I undestand your question just make sure that in the tools->reference in vba editor you have atpvbaen.xls checked. After that you should be able to use all fncs of that library.
 
Macro Recorder helps

Try the Macro recorder first - it helps set all the syntax and parameters in the yet-unknown function call to some sort of default settings, then you can Google them later to tailor them to your needs.

According to the Macro recorder, the function call should be something like (all on 1 line of code):

Application.Run "ATPVBAEN.XLA!Mcorrel", ActiveSheet.Range("$B$2:$L$181"), ActiveSheet.Range("$N$16"), "C", False

www.google.com/search?sourceid=navclient&ie=UTF-8&rlz=1T4GGLL_en&q=Application.Run+%22ATPVBAEN.XLA%21Mcorrel%22%2c+
 
Thank you. I was having problem because of the Application.Run "ATPVBAEN.XLA!Mcorrel", I couldn't figure out the proper syntax for it. I'd found a couple variations online. But I think my problem was largely due to the fact that I had written ATPVBAEN.XLS! instead of "ATPVBAEN.XLA!.
 
This may not answer your question, but depending on whether your problem is of commercial nature or not, you might want to consider R-Com interface to hook up to the R engine from Excel:

http://www.sciviews.org/_rgui/projects/RDcom.html

It's really easy to call R functions from within VBA code or as Excel formulas. I found it works great - full R functionality within Excel :D
 
Back
Top