Interface between Matlab/VBA/Excel

[Andy Nguyen]

Some people use Matlab and/or Excel VBA at work and there is generally a desire to take advantage of Matlab many built in functions. With little effort, we can also call VBA functions from Matlab.

Call Matlab functions from Excel/VBA
The most convinient way to perform this is to use Excel Link which is an Excel addin component that comes with Matlab. Once you install Excel Link, it will expose all Matlab functions to Excel/VBA that you can use as spreadsheet formula.

The way to call Matlab function is via matlabfnc(). Matlabfcn() takes as input arguments the name of the Matlab function as string, followed by the input arguments of the Matlab function.

This would give a quick and simple way to use many of Matlab nice operations (matrix, stat). However, there will be some big performance penalty if you load the spreadsheet with many matlabfnc() as each will call out to Matlab and back individually.

Call VBA functions from Matlab
This is a very little discussed topic that may come up in rare cases. It's useful if you have much of your code base in Matlab and want to call some customized function that you have stuck away in some other XLS sheet.

1. Excel/VBA
You have Book1.xls in C:\Code\ folder that contains a VBA function named Func1 with this simple code

2. Matlab
Type these commands into the editor

%Open a COM server on Matlab
Excel = actxserver('Excel.Application');

Workbook = Excel.Workbooks.Open('c:\Code\Book1.xls');
x = fseminf(@(x) callFunc1(x,Excel),x0,ntheta,seminfcon);

function x = callFunc1(param,Excel)

x=Excel.Run('Func1',param);
Excel.Quit;

 

[Bastian Gross]

Hello Andy,

this pdf-file was always guidance for me!
QuantNetwork - Financial Engineering Forum

 

[Tom Bob]

Thanks a lot!Zz

Zz, it is quite good tip

 

[weirluo]

I feel so happy to see this post.

I thought I was one of the few that have to build excel interface with matlab computation core applications.

It's hard to find good tips on this topic.

Glad to see champions here!

 

[Andy Nguyen]

In the latest version of Matlab (7.8 part of release R2009a), you can get Matlab to call .NET classes

File I/O and External Interfacing

* Ability to call .NET classes from MATLAB, providing direct access to a wide variety of software components
* Expanded mmreader multimedia reader support for Linux® platforms

MATLAB 7.8 - Latest Features

 

[dtjandra]

Hi everyone,

I am very new in scripting, therefore please bear with me. Ultimately, I am trying to create a stand alone excel file (with matlab as the computational engine behind the file) to be distributable to any computers without Matlab installed. I know most site would state using the Matlab Builder for Excel and the Compiler, however, these options are not available to me. Therefore my question is can it be done? If it is, how to do so?

My computer has Excel (VBA), Matlab and VB.net(c#) installed.

Thank you.

 

[Andy Nguyen]

The short answer is no. If there are free alternatives to replace Builder for Excel, Matlab won't sell.
You can always replace the built-in Matlab functions that you want to distribute by C++/C# functions and just build an Excel add-in the normal way.

If you have Matlab compiler, you can create an XLL that will call the dll generated by the matlab compiler.

 

[dtjandra]

Hi Andy,

Thank you for the quick respond. I am not sure if rebuilding the function in C/C++ is the path to pursue as the goal is to input value to excel and retrieve the final value (eg. bond price - applying our prebuilt model in matlab) in the single worksheet.

What I have been doing is for testing purpose to see if this is plausible by generating dll of the matlab function in C++, however I am facing one of the 2 errors (below) calling the dll from vba. The function requires no input and will produce an integer value of 12.

1. can't find DLL entry point * in *.dll - this error I read is often derived from non-existing function name or incorrect data type, but when I uses the undecorated C++ function name/ordinal number as alias, I received the 2nd error type.
2. excel crashes and gives windows error of 0xc0000005 (access problem)

Therefore my questions are:
i. prior to the error I am receiving, is this also a plaussible solution?
ii. any idea as to why the errors?
iii. as for the XLL idea - the XLL is to help in translating the matlab functions and data type from the dll?

Thank you again for the assist.

Dan

 

[ahmadi]

Run a macro(VBA) from matlab

hi

I'm working with a mfile and have a forloop within it.For i=1:100 I need to run a macro for i=1 and then continue mfile and again for i=2 . . . . . .
Can you help me please?I need a code to write in matlab

Some people use Matlab and/or Excel VBA at work and there is generally a desire to take advantage of Matlab many built in functions. With little effort, we can also call VBA functions from Matlab.

Call Matlab functions from Excel/VBA
The most convinient way to perform this is to use Excel Link which is an Excel addin component that comes with Matlab. Once you install Excel Link, it will expose all Matlab functions to Excel/VBA that you can use as spreadsheet formula.

The way to call Matlab function is via matlabfnc(). Matlabfcn() takes as input arguments the name of the Matlab function as string, followed by the input arguments of the Matlab function.

This would give a quick and simple way to use many of Matlab nice operations (matrix, stat). However, there will be some big performance penalty if you load the spreadsheet with many matlabfnc() as each will call out to Matlab and back individually.

Call VBA functions from Matlab
This is a very little discussed topic that may come up in rare cases. It's useful if you have much of your code base in Matlab and want to call some customized function that you have stuck away in some other XLS sheet.

1. Excel/VBA
You have Book1.xls in C:\Code\ folder that contains a VBA function named Func1 with this simple code

2. Matlab
Type these commands into the editor

%Open a COM server on Matlab
Excel = actxserver('Excel.Application');
 
Workbook = Excel.Workbooks.Open('c:\Code\Book1.xls');
x = fseminf(@(x) callFunc1(x,Excel),x0,ntheta,seminfcon);
 
function x = callFunc1(param,Excel)
 
x=Excel.Run('Func1',param);
Excel.Quit;

 

[sriniipr]

Some people use Matlab and/or Excel VBA at work and there is generally a desire to take advantage of Matlab many built in functions. With little effort, we can also call VBA functions from Matlab.

Call Matlab functions from Excel/VBA
The most convinient way to perform this is to use Excel Link which is an Excel addin component that comes with Matlab. Once you install Excel Link, it will expose all Matlab functions to Excel/VBA that you can use as spreadsheet formula.

The way to call Matlab function is via matlabfnc(). Matlabfcn() takes as input arguments the name of the Matlab function as string, followed by the input arguments of the Matlab function.

This would give a quick and simple way to use many of Matlab nice operations (matrix, stat). However, there will be some big performance penalty if you load the spreadsheet with many matlabfnc() as each will call out to Matlab and back individually.

Call VBA functions from Matlab
This is a very little discussed topic that may come up in rare cases. It's useful if you have much of your code base in Matlab and want to call some customized function that you have stuck away in some other XLS sheet.

1. Excel/VBA
You have Book1.xls in C:\Code\ folder that contains a VBA function named Func1 with this simple code

2. Matlab
Type these commands into the editor

%Open a COM server on Matlab
Excel = actxserver('Excel.Application');

Workbook = Excel.Workbooks.Open('c:\Code\Book1.xls');
x = fseminf(@(x) callFunc1(x,Excel),x0,ntheta,seminfcon);

function x = callFunc1(param,Excel)

x=Excel.Run('Func1',param);
Excel.Quit;

Hi,

I am a student i would like to read the value from excel computed from an add-in function installed dynamically.

That is, from the MATLAB program i have passed a parameter to excel and using these values the add-in will calculate a value and i need to read it dynamically at runtime.

I tried this code

function [ sum ] = add1( a );
xlswrite('2.xlsx',[a],'A4:A4');
sum = xlsread('2.xlsx','A5:B7');
end

The value of sum in this code is calculated using the add-in but i am unable to read it and the return value contains NaN.

Any help for this problem will be appreciated.

Regards,
Srinivasa M

 

[tuncalik]

Hi all

Is there an excel/VBA add-in with matrix, vector and set functions (or operations) like one has in matlab or R?

If there were such an add-in, one could write higher level functions and scripts directly with excel/VBA based on these elementary matrix/vector functions, just like one writes them with matlab or R.

Regards
Tunc