question still about using array variables in formula

[novice_2010]

Hello, All,

I have following codes:

Sub test()
Dim Array_1 As Variant
Dim Array_2 As Variant


Array_1 = Array(2, 4, 6, 8, 10)
Array_2 = Application.WorksheetFunction.Transpose(Array_1)


Range("A1").FormulaArray = "=mmult(" & Array_1 & "," & Array_2 & ")"

End Sub

As usual, I got a "Type mismatch" error message. How should I re-write the red line of code to make it work?

 

[shg]

You can't transpose a string.

Sub test()
    Dim sMat1       As String
    Dim sMat2       As String
 
    sMat1 = "{" & Join(Array(1, 2, 3), ",") & "}"
    sMat2 = "{" & Join(Array(4, 5, 6), ",") & "}"
 
    Range("A1").FormulaArray = "=mmult(" & sMat1 & ",transpose(" & sMat2 & "))"
End Sub

... or

Sub test()
    Dim sMat1       As String
    Dim sMat2       As String
 
    sMat1 = "{" & Join(Array(1, 2, 3), ",") & "}"
    sMat2 = "{" & Join(Array(4, 5, 6), ";") & "}"
 
    Range("A1").FormulaArray = "=mmult(" & sMat1 & "," & sMat2 & ")"
End Sub

Also, this formula returns a single result, so it need not be an array formula.

 

[pgc01]

thank you all for suggesting.
I think pgc 's suggestion is good for 1-dimensional array; however it won't work for 2-dimensional array. As shown in the following codes:

...
sMatrix = "{" & Join(Matrix, ",") & "}"
...

How to make the formula in this example work?

Hi again

You are right, that line converts a 1d array into the corresponding string.

If you have a 2d array, you must use other code to get the string.

For ex., a simple way is to do it in a Function, which leaves the main code simple.

Just replace that line of code with:

Option Explicit
Option Base 1

Sub test()
Dim Array_1 As Variant, Array_2 As Variant, vMatrix As Variant
Dim sMatrix As String
 
Array_1 = Array(1, 2, 3)
Array_2 = Application.WorksheetFunction.Transpose(Array_1)
vMatrix = Application.WorksheetFunction.MMult(Array_2, Array_1)

'Matrix is a 2 dimensional array variable
[COLOR=red][B][I]sMatrix = Conv2DArray(vMatrix)[/I][/B][/COLOR]
 
Range("A1").Resize(3, 3).FormulaArray = "=mmult(" & sMatrix & ",transpose(" & sMatrix & "))"
End Sub

The only difference with the previous code is the conversion of the 2d array to string.

To convert the array to string, use, for ex.:

Function Conv2DArray(vArray As Variant) As String
Dim j As Long
Dim s As String
 
For j = LBound(vArray, 1) To UBound(vArray, 1)
    s = s & ";" & Join(Application.Index(vArray, j, 0), ",")
Next j
Conv2DArray = "{" & Mid(s, 2) & "}"
End Function

Remark: I did not care about error handling.

 

[novice_2010]

Thank you, pgc01. Your method is really smart.

And thank you all for helping me.

 

[pgc01]

I'm glad it helped.

Remark:

I assumed this is a learning experience and you just want to know how to convert vba arrays to worksheet format.

If this was a real solution, what would make sense to me would be to do the calculations in vba and just write the result to the worksheet, like:

Sub test()
Dim Array_1 As Variant, vMatrix As Variant
 
Array_1 = Array(1, 2, 3)
 
With Application.WorksheetFunction
    vMatrix = .MMult(.Transpose(Array_1), Array_1)
    Range("A1").Resize(3, 3).Value = .MMult(vMatrix, .Transpose(vMatrix))
End With
End Sub