Hello all. I have an Excel addin that takes spreadsheet matrix as an input. It returns a matrix on a spreadsheet in "SHIFT+CTRL+ENTER" fashion, i.e.On the spreadsheet, I have a 3 by 3 input matrix from, say, B461.0 0.9 0.70.9 1.0 0.30.7 0.3 1.0and my addin function goes from B810 as{=my_func(B46)}The problem is how to obtain output in VBA and store it as a 2-dimensional VBA array?, i.e, I want to store the result matrix asRedim Output_matrix(1 to 3, 1 to 3) as DoubleDim i, j as IntegerFor i = 1 to 3 For j = 1 to 3 Output_matrix(i,j) = ?????? Next jNext iand use this Output_matrix value in the later part of my VBA program.I know I can temporary map on the spreadsheet asSheets("Sheet1").Range("B810") = Run("my_func", Range("B46"))and read it from the spreadsheet again into VBA as:For i = 1 to 3 For j = 1 to 3 Output_matrix(i,j) = Sheets("Sheet1").Cells(7+i, 1+j).Value Next jNext iBUT, I do not want to use the temporary spreadsheet like this as this is not working for large matrix (exceeding excel column size limit, 250 or something like that??).....Does anybody has an idea how to solve this problem?Cheers,Jet

Ah, the Mr. Smile is supposed to be ":" plus "D". I don't know why it came up like that....

Normally I work in C, but here is a real quick and dirty VBA answer that I put together to in five minutes to win a bet with a colleague. It is specifically tailored to a 3x3 matrix, but you can generalize it yourself.Suppose you want to return a 3x3 matrix (the output_matrix), where each element of the output_matrix is the squared element of the input_matrix. Paste the following function code into a VBA module.Option Base 1Public Function PlayWithMatrix(input_matrix) As Variant 'This simple function takes a 3x3 matrix as an input and returns a 3x3 matrix with each element squared Dim output_matrix() As Variant ReDim output_matrix(3, 3) ‘Again, specialized to a 3x3 for illustration purposes For i = 1 To 3 For j = 1 To 3 output_matrix(i, j) = (input_matrix(i, j)) ^ 2 Next j Next i PlayWithMatrix = output_matrixEnd FunctionThe key thing here is to define your return matrix as a Variant. Note I have arbitrarily indexed the matrices from base 1, but it should be trivial to do it the right way (from 0)!Now, select another 3x3 range in the spreadsheet, push the paste function button, fill in the single argument with your 3x3 input matrix, do the “CTRL+SHIFT+ENTER” thing and you should see a 3x3 matrix with elements equal to the squared value of the elements of the input matrix. Hopefully the posting will not change any syntax!

David, thanks for your answer! I got the solution from your suggestion. The key thing was to define the output_matrix as "Variant":**********************************************Dim OutAddinMatrixResult As VariantOutAddinMatrixResult = Run("my_func", Range("B46"))**********************************************Now, OutAddinMatrixResult is Variant type and contains 3 by 3 matrix values. To check this, we can output the result on the spreadsheet again.********************************************************Dim i, j As IntegerFor i = 1 To 3 For j = 1 To 3 Sheets("Sheet1").Cells(19 + i, 1 + j) = OutAddinMatrixResult(i, j) Next jNext i********************************************************Cheers,Jet