How to multiply two ranges and use the result as an argument fo a worksheetfunction in VBA

[bgexcel]

I am trying to mutliply two ranges together and use the result as a worksheet function in VBA. I get a "type mismatch" error when running this code in VBA. Here is the code:

Please Login or Register  to view this content.

[Dennis7849]

To debug this, I would need the wb. The code is no good without sheets(funds,bench and weights) and the data they contain.

[JBeaucaire]

Get a working formula in a regular cell first. Once you get it working in a cell, share with us that working formula and we can suggest how to make it a VBA formula.

[bgexcel]

Thanks for the quick replies. Assuming my work doesn't block it, I'll upload the workbook tomorrow (at home now). FYI, I already have the formula working in the workbook, just need to get it working in VBA...

[bgexcel]

Here is the working formula in Excel:

Please Login or Register  to view this content.

Unfortunately I keep getting an error when I try to add the wb as an attachment to this post (xlsm file size is less than 1000k, its a differnet error).

I just need to get this function working in VBA. All of the cells referenced and passed into the function contain numeric values.

FYI, this code works in VBA:

Please Login or Register  to view this content.

This code does not work:

Please Login or Register  to view this content.

[lecxe]

Hi

You are populating the cell with with the result of the formula. You can use the value that the formula returns:

Please Login or Register  to view this content.

Remark: In your last post you use Linest() with the 4th parameter equal to True. In that case you'd need a range of cells 5 rows * several columns to store the value of the array that Linest() returns.

Does this help?

[bgexcel]

I know how linest works (it contains an array of results, but by default it will return the coefficient for a simple regression if you don't use the index function to identify which stat you want returned). The VBA code returns an error because I'm trying to mutlipy a weight vector by my dependent and independent variables ("funds" and "bench" respectively). I think I get an error in VBA because of the vector multiplication (by the way, I'm not looking for a dot product, rather component wise mutliplication).

Also, according to Microsoft help, even if you set the last parameter of LINEST to FALSE it will still return a array of values (and you can use the INDEX function to specify which you want).

stats Optional. A logical value specifying whether to return additional regression statistics.
If stats is TRUE, LINEST returns the additional regression statistics; as a result, the returned array is {mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid}.

If stats is FALSE or omitted, LINEST returns only the m-coefficients and the constant b.

[Pichingualas]

Please try this:

Please Login or Register  to view this content.

I hope that helps.

[bgexcel]

Thanks but it returns an error: "Unable to get the LinEst property of the WorksheetFunction class".

Also, I do NOT want matrix multiplication. I need component wise multiplication. For instance, If I have two vectors:

vector1 = (1, 2, 3)
vector2 = (4, 5, 6)

Then I want to pass vector3 = vector1 * vector2 = (4, 10, 18) into the LINEST function.

[lecxe]

Hi bgexcel

You are right. The error you get in VBA because of the vector multiplication.

I just thought that since what you want is to populate the cell with the value of the formula it would be much easier to let the formula calculate the value and then use it.

Sorry, I did not understant if you think it was a good idea and your problem is solved or if you still prefer to do it using the vba worksheetfunction method.

[bgexcel]

I'm open to solutions but I need to pass the ranges (vectors) into the LINEST function dynamicly because I'm running this regression model on a bunch of different time series. If I can just get this part of the VBA to work I should be on my way

[lecxe]

Well, if you had vba arrays you'd have to build their multipication using a loop. In this case, however, since you have ranges, you can evaluate their product.

For a case like the one you posted you could use something like:

Please Login or Register  to view this content.

[bgexcel]

lecxe - that worked! I just didn't know the syntax for the multiplication in VBA.

Thank you!

[lecxe]

I'm glad it helped.

Notice that that is not really a multiplication in vba, the [] are the shorthand for the Evaluate() method and what you are doing, in fact, is getting the result of a formula.

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.