Failed to get MMult property for Worksheet function.

Question

Everyone, I have a strange problem, please help.

error message:

Runtime Error '1004'

Unable to get MMult property of WorksheetFunction class

1, I use the program name - “ schedule ” to automatically run my VBA program every day, but the VBA program is interrupted every day, but when I control my “schedule program” to automatically run it again to try to reproduce this error, I don’t I can get it, it works smoothly.

2. When this error occurs, Excel will display the [end] [debug] window, press [debug] and press [F5] , it works smoothly; it makes a show error if the parameters for MMult are incorrect.

3, I wrote a sub to dump my data that is used in MMult , the same thing happens with an error and without errors.

therefore, I can assume that the two parameters for MMult are correct, but why did I get an error every day?

This is the hardest thing - it's hard to reproduce this mistake.

code:

 Public Function Regression(ByVal X As Variant, ByVal y As Variant) writelog ("Regression") writelog ("dump x") Call dumpRange(X, 2) writelog ("dump y") Call dumpRange(y, 1) Dim xtrans, temp, temp2, b xtrans = Application.WorksheetFunction.Transpose(X) temp = Application.WorksheetFunction.MMult(xtrans, X) ' occour error on this line temp = Application.WorksheetFunction.MInverse(temp) temp2 = Application.WorksheetFunction.MMult(xtrans, y) b = Application.WorksheetFunction.MMult(temp, temp2) Regression = b End Function 

X is a range like this

 1 0.34343323 1 1.32323323 1 1.21111221 1 0.33444232 . ...... 

Window 7 home preminum 64bit

Office 2010 Professional 64bit / 32bit sp1

Answer 1

I reinstalled my OS and office, now it works great.

Answer 2

Try it. I think this is where you get your error

 temp = Application.WorksheetFunction.MMult(xtrans, Application.Transpose(X)) 

Answer 3

I had the same problem. But it turned out that mine was caused by a measurement problem.

I did:

 Redim Y(obs) Redim X(obs,3) Xtransposed = Application.worksheetfunction.transpose(X) MMult = Application.worksheetfunction.mmult(Xtransposed, Y) 

So far I had to do Redim Y (obs, 1) to get the code to work.

So:

 Redim Y(obs,1) Redim X(obs,3) Xtransposed = Application.worksheetfunction.transpose(X) MMult = Application.worksheetfunction.mmult(Xtransposed, Y)